Welcome to the party not going to be as exciting as yesterday evening's.
But hopefully of some interest so I'll start off with a quick intended audience for this your time is valuable so I want good value for your time so this is a very competitive slot. There are lots of other brilliant sessions on this session. I want to make sure I got the right audience for this so this is going to be focusing around simplistic using cases of partitioning majore.
Sort of the online workloads less so on the Analytics. We're not going too. Deep into any of the subjects were not going to be visiting moving windows, but we're going to be covering the principles that things that should be involved should be involved. And who should be inviting to your party quick introduction of who I am my name is James I work for over energies.
User group, so we run meet up events regularly if you want to come along to any Val meetups. Just please register on the meet up site and you can come and see these with a collection of other great talks so moving on.
What is partitioning so partitioning is realistically 2 main concepts you've got partition views and partition tables there used to be a long caveat of.
Which was which and what was available in? What version do to? What licensing models and which version you're running on Microsoft have made this so much easier for me, so I can basically say Azoff SQL Server 2016. SP one there. Both now options to you so partition keys is then.
What is a partition G? How do we pick this? Who designs these and who should be implementing them?
So.
Partition keys in most cases, depending on how you're coming into this and I'll get a quick sample to see if I can get an idea. So who considers themself just and only a primarily a DBA have a quick raise of hands.
Developer.
Infrastructure.
Oh, sorry some mix of all of those are the above brilliant.
So developers you're the guys who are going to be very, very key to getting this conversation going and these are thoughts that I want you to take forwards as you're starting to design your tables. 'cause partitioning often seems to come up after you've put maybe 5,000,000,000 records in much, much easier to implement.
So.
As I've got a lot of developers here. This actually makes this bit. A lot easier. So you know what table with your designing you know how you're normalizing you know the queries. You're running so quite often. It's seen as a DBA's aspect to figure out what partitioning there going to be doing so. They've got to find ways of deciding what that partition key is going to be so depending on.
Or with your developers can be and I see the conversation or in some cases for dbas you may be looking at implementing this on a vendor application.
It's much, much trickier to find out if your vendor is going to support it here with there is going to be to ask you. You're going to go in a gauge with quite often. You'll write an email about say something, you want to partition in you'll get reply, saying it supported. It's up to you how.
So you've got options of how do you collect and verify this information?
As your most developers are skip it. But if you're dbas you've got things to look at you can go with traces extended events just ways of trying to capture and analyze your workload.
So I did say it's fundamentally 2 things so you've got partition tables. So these are the ones I've been around for a long time. This is the 2005. You have the partition tables came out and this was the options where you could go and put a function over the top.
Table.
So this was great could use on no schemas. No code changes depending on how your code was source controlled. This should be in it, but could be in a separate source control to the rest of your actual scheme of your objects your codes.
They could be actually an entirely separate codebases 'cause. In theory, or this can be implemented on you need to make no changes. A tool to your table design, so this was often easy. One is often the go to choice. If you're dealing with vendors. The other side partition views, so this one we actually have physically separate tables so.
Beneath and then we're joining up with up with views so this actually opened some interesting options. 'cause your tables need to be similar. They do not need to actually be the same, so you can actually have variances in data types that different indexing's different index types. So, your row base your column based a Nope.
Interesting options.
You could actually even have not recommending you should do this. But I have been in situations where we've had every single record had a wonderful. Var char Max for memo information. But like a percent of them were populated so we actually had a condition in our view.
It.
And if the cost was successful, we went to one table if it didn't went in the others, and it's saved us terabytes and Terabytes, but these are options to consider so you've also got the other aspects of alignments.
And this is in 2, simple options, so this is focused around your table partitioning. You can do indexes on them. Hopefully we've all using some form of long clustered indexing or our workloads, so these can be aligned so very simple here with names.
You have new employees who are joining your company.
I find it really hard not to Frank mentor change. This 'cause it's really hard. Hiring people in name order. We tried it. It didn't work well so they do tend to come and get new records, creating a database. You create a nonclustered name on this 'cause sometimes. We want to be searching by the name if you align them to party.
This index may not work in the way you want as you can see on the right hand side.
Quite different order to on the left once again, depending on how you're designing your partitions. These are things you need to factor in and say, hopefully somewhere between your developers more dbas. These aspects are being considered because one of you may be responsible for the indexing whatever movie for the workloads so that's the conversations that should be having between the 2 of you.
Partitions we can chop these on many different data types so.
You can shop on dates so I'm going to come round date. Since this is a common use case but numbers is an easy representation. So you've got left and right? That's the equation. I'm assuming everyone is happy with that equation.
Normally, it's fine with the color representation, I find much easier.
So if we're taking that value of 10, depending on what hardware they were cutting it on the left or the right depends on which boundary ends up in so if you take something like a date format. If you think of. We've been here for this event, so we've got 27th 28th first.
Depending on which way you want to cut that on the left the last value in that boundary is just before midnight, 2359, 59999, depending on various going to that's really tricky, and will vary on every single month. So if we can stop this on the right hand side you can have the first uh the beginning.
There is much easier and seems to.
Be switched or seems to Karen both so the other people who want to potentially come along to a party.
Who's going to be getting the benefits from this design?
So initially you've got your developers so query performance can be benefited can would be benefited. I am always curious to whether this shouldn't even be this high in this or that weather should be ordered partitioning is not where you should be going for performance. We should be looking at queries indexing partitioning.
Benefits performance can be interpreted and there are many different aspects to which you can gain performance. But if you're just looking for outright performance. This isn't where you should be starting scaleability. If you want to start scaling up large difference activities to different customers so for example, you have a multi customer sis.
You could start to look at Partitionings on customers. But this then potentially means some feedback from your business. Have you started up a very successful business? Where you've got one customer you're now starting sign up. Most of our customers. I've been we've all done. It your copy and paste your database into another database and then you start to append each customer name on the end.
Got several 100 databases. For every one of our customers partitioning could be an easier way of doing this and controlling. This data loads. There is some really cool stuff, which can do partition switching not going to be getting into much detail on this archiving strategy. Now the GDPR sort of a thing we do tend to keep our data SL.
But more often than not there is probably a table somewhere in a system that you have designed several years ago that has had a record or billions of records written into it and it is now just this wonderful several terabyte table, which someone has to look after and maintain so coming over to the DBA side.
We do run at operations could index rebuilds so if you're adding records in you've got that fragmentation of rebuild has to go through these do hit your systems quite hard, so if you consider you adding let's say 51020 customers every seconds to your system that's fine. But when you've got those billion customers. You want to do and.
Keyboard operation would have to reorder a billion customers.
This can upset your networks teams your storage tunes your infrastructure teams, many, many people were depending on the size of your organization who you're dealing with so if you can make this easier for them. It's good also potentially also lowers the specs of your servers 'cause if you're having to spec servers around having to maintain some huge tables.
You're not going to be particularly popular with your DBA is in your infrastructure tasks teams, so coming over to the infrastructure again.
Reduced IO, we can reduce iOS like maintenance operations. We can choose the variety of IO. So there are infrastructure teams. There he will be using different technologies, so you've got auto tearing solutions on storage. This basically means as your regular accessing records. The storage tiers will treat these very well so you'll get good results on.
They're less frequently used they'll start to age down the storage tiers. That's fine. 'cause they're not being used unless Quince only it's on those rebuild operations where you're trying to pull up all these old records that your company never really needs. They just there for maintenance.
It causes a lot of unnecessary hassle for your infrastructure teams.
And say you're maintaining records that could be 7 years old. However, many years old. Do these actually need to be maintained are they being written to anymore, the structure is.
At a sort of the actual logical table part that does it need to physically probably not.
So.
Schemas and functions. I like to think this is what and where what are you going to do where you're going to do it so I've got very simplistic example here Alpha beta.
One is called Alpha one areas called Beta and going back to the colour charts earlier. We had with the blue and the red cut off, which likely quickly flick back to that's the value. I'm going to pick.
Depending on whether that's a left or right so you can see, there the right cut off will depend on which way that's going to be sliced that's literally it quite simple on this example. But of course, you're probably going to be shopping a lot more than once.
So what does this actually look like?
So starting off with an example.
Standard SQL install here 2017 unpatched you should Patch your production service. This is a demo just literally going to rebuild this from scratch. I'm not using worldwide imports, which I now regret as apparently there was some type of comment that went round. Apparently, we're all supposed to have used it or at least Microsoft decided this but.
I like well that's exciting. I have not pointed out for those of you who got the additional fun thing there that did fail.
The reason why is full text searching I've not got installed this is fine but it still works will have our demos so?
So once again demo I'm using the C drive do not use your C Drive.
So we're just going to start to finish here, we're going to check on some additional files on to that group.
Going to pick up table.
And it would be good if I showed you the results so.
We have just picked a table that we're going to look at partitioning so going to try and do the sequel side and the query side on this one.
So if you ever take a look at this on all the lovely GUI.
Norton intentional break point.
Storage crate partitions got lovely Wizards Wizards will take you through this so.
What are we going to basically pick there is as you see huge variety for this demo we're going to pick the transaction. ID is in this case transactions. I DS should in theory, be nice and easy to manage this is a sequential key that we picked.
It's naturally ages 'cause we're going to be creating transactions sequentially so we care less and less about the old ones, which could be writing them forwards. This actually works quite nicely for us and it's a reasonable assumption is probably going to be creating these by transaction IDs so.
We will go to the fun party. I am going to type this out, then cancel out this after 'cause never type in a demo.
So what we doing where are we doing it?
Boundaries left and right as I said left is generally less useful and this is what is defaulting us too, and then here you're far groups so that one of the other big differences between partition tables and partition views is it's mandatory for multiple filegroups so as developers.
Recreating needs this will be our dbas your dbas will be creating these.
And where they create them will probably depend on your infrastructure teams. Assuming that separate I mean. You sometimes I think we've got a few people here, who are one man armies and doing all of this, but in some organisations you get a request in from a developer wants you to do something you pass this to a DBA team will then look at it passes to an infrastructure team will then pass this to a store.
This this is Chinese whispers in some organizations and other organizations. It's a much easier way to communicate and make sure that what you're actually trying to achieve is known by all and this is partly where the partition party story comes around.
Invite them to your party start the dialogues of tried to make sure that you all know what you're trying to do.
And hopefully you won't fall into any of the pitfalls, which I will give a couple of examples, too, so here we can put in parts. We can get some calculations on storage roughly telling us how we're going to split the data.
I should never typing demos will take a look at the scripts this generates.
So.
This will generate a nice little script, so you've got partition function. This is what you're going to do your schema where you're going to do it exactly the same as we had in the demos and then you've got a little bit of how that's implemented in the op stable operation where you drop the constraints rebuild the primary keys.
These can get quite big and heavy much easier to implement as an early thought.
So what does this actually look like last I said the separations in this are logical and physical so this is what the table. Physically looks like it has been broken down into those different sections into different partitions on different filegroups that can be.
Disks that depending on what visibility you've got could be on different drives, which could be on different storage volumes could be on different VMDK's.
How far the benefits go depend on how this has been implemented and how many of those other teams have visibility of what you're trying to do.
So how does this look?
Exactly the same.
No difference in how you interact with it.
Cool.
And say vendor applications. This is where I've used this most often.
Simply, the fact if I don't need to worry about wearing any code changes in vendor applications. This can be a very slow process or sometimes on developers there's he wants to manage what?
And this would be a conversation again is there something you want the DVD's to take a look at that. They maintained they need to have input from you as its majoritively developers put it on the GPS have a conversation explain what you wanted to do and then let him go do it.
So this one is majoritively out there for the dbas, but sometimes when you have your query and it's running slow and you'll go have a chat with our dbas. They will say it's a stats problem and then go and update some starts and things seemed to go fine again. That's have got this histogram here.
Points on it, 200 points on several billion records.
Cannot be particularly useful is your data started to school so we do actually have.
Something called incremental stats that has come out and I see we've done a quick implementation already of partitioning on the table, so we now have our lovely partitions table.
But it's not made incremental for us.
And small number TV I say, but who is actually manually created statistics and we want to put up their hands going to qualify. This used manually create statistics in the last 5 years.
Still, 13 years.
What version of SQL order we've seen it snowed one?
OK, we don't tend to do this anymore, so cool.
Let the SQL server do the work for us, we can just go create auto statistics on that's cool so.
That's not changed anything cousin statistic already existed.
So.
We can update individual parts of statistics.
I can't do that, but it's my partition table and my partition statistics. So I also have to go through.
Rebuild them telling him to be incremental, so this is another thing of if you're implementing this. This is some of the value that hopefully or debase should be able to add they should be able to understand these things so this is a conversations as developers. There's a lot of simple things you would think I've implemented a partition table. I should be getting some of the benefits.
No, don't tickle the buttons and you've got to go through and do some of these things but now we've done some of these things are cool. I can now update that individual partition so thinking again for an aging data scenario. We may have partitions for years of data.
We only really care about updating the statistics in our most recent years, most recent months, most recent million records.
So this reduces our maintenance heads in our sampling.
So another cool benefit. We have which are quickly hop over to show The Wizard side of this again now that we have partitions.
Going to get to play with compression types.
So creating new records.
The most beneficial compression is nothing you just want to be able to write the data. If you're reading the data. You've got 2 compression options. Here Page Andro page. The heavy level of compression. Compression is good 'cause. It sits on the desk and it holds in memory.
But there is a cost to updating it, but I say if your data is naturally aging.
You're not going to be updating those early transactions in the company's history. We're not stopping you updating them. But we're making it smaller on disk. I will be born foreign food query. The reason why the compression makes it a larger number of records to fit into the same space. You can actually records faster that's a quite nicely.
So, in here.
Another from one of the gui so pick a page compression create script immediately.
Not actually quite what I want to do that. 'cause they don't actually need all that was only changing a single one as you know is there. The wizard produces 3. It goes and rebuilds every single one of them. We actually don't want to do that. In this scenario. So The Wizard is done something slightly different for us, we actually just want to.
Rebuilt the one and that's our most recent well in this one. Sorry our oldest partition say the age data. The older transactions one was not interested in.
Page compression, wonderful smaller on disk people will be happier using less disk space. That's all good. So we focus very heavily on the table side. The other side partition views. These used to be the go to before partitioning became a feature and also for awhile after.
Of the level difference between your enterprise and standard addition, so say. Luckily, now Microsoft have aligned a loss of this feature set.
But this is one of the ways we used to be able to achieve it and this is where we're making a separate changing the physical structures. So here we're going to create 2 separate tables and then we check of you over the top so they seem the same, So what does this look like in a demo?
So very quickly.
We are going to pick a vendor table and she had Mr. Select on this one, I would do a quick select from here.
So that's why table currently looks like, at the moment, so we're going to make a quick change. This we are going to create a new table.
Now, depending on how you want to implement some production will also depend on the size your records. You may want to look at select into's 'cause. These are unlocked operations and faster, but we're not going into the details of these these are conversations. You want to consider have with your dbas so ignore the plan on there. That's good will come up on the end.
Vendor current table so here we basically adding back on some constraints. The reason we're adding on some of these constraints is we are going to use one of those fields to constrain our data and what the constraints is here is going to do is stop certain day.
Orchestrating on it, entering the 2 partitions, so this gives us some quite fun. Little features say for example, you can constrain on.
Dates for example, date ranges, so you could say that a record has to be within a date range will say transactions. They it's gotta be the first billion transactions or the second billion transactions or however. Many y'all reading so this is how you split them into separate tables. This will mean that when we put the view on the top records cannot be pushed.
Table and makes it slightly nicer for you to deal with so.
There we just check the constraints on we will do this over a old copy of the table. This is exactly the same script, but with the old rather than current.
And our view, so this was the bit that sits over the top so here you've got the same columns were using them together.
Cool.
We've actually now got the table, so in theory.
Assuming this looks correct we should just be able to push all the data straight in through the view.
And it should work so why does this not work?
The reason why is the constraint has to be part.
Off the primary keys on this which comes back to our key conversation. So we went from originally having those original ID values to now also needing to include that constraint condition. So now that we've added in at that constraint concession condition even.
It works for us.
So.
How does is actually look from?
The original table.
Still looks the same on the view now Steve notice on this scenario. I've created a new view with DBO, but depending on how you want to obfuscate this? How often is your code object aware.
In theory, there is nothing stopping me from renaming the old table's that I've taken the data out of it and insert it into 2 new tables. There's nothing. Stopping me renaming that's to a placeholder name and then actually recreating this view as the old name.
And have now implemented this with depending on how we're using that table there real code changes. But under the hood. We've got some interesting little benefits.
So.
We've got the for old ones that we've constrained out.
So what happens if we start to update these right there in 2 separate tables.
Sometimes it mean but we're running short time, not get their hands up to think what would happen if you would update eh record would you get a constraint violation well actually the constraints work for us as well update that record?
Let's move tables that's cool right.
So.
We're always have the options if you know, archiving out old data. What if you can get users to go through doing the archiving for us.
Common examples, we've we've looked implementing this orders, you have an orders table, which may have a certain status.
Through time you end up with a huge number of these with order complete status. Whatever you're using something like this to put them into a non current orders complete stable. What if you want to have separate tables with orders in different states in 'cause, you may not need to be querying these.
The other fun thing? How does this look again.
Exactly the same.
So what I was saying about partitioning in performance? How does this change our part performance?
So the estimate.
Actually, still hang both.
Uh the reason why is the sequel server actually can't really figure out where that is and this comes back to our key design conversation again, which although say in theory, a DBA could have implemented this in obfuscate this from the developers developers aren't working around this pattern.
They're missing out some of the benefits in this too.
So.
How does this look different?
If I've made a change another change there.
Is looking at that constraint condition?
The flag so common use case even in this scenario.
Is a vendor is no longer your current vendor?
And you've got a large number of companies who are querying that table to look at current vendor information do they really need to be hitting your database for old in your application? Can you just assume there only ever going to be dealing with current vendors?
And then you can give them a different interface to look at previous or old vendors depending on your use patterns say for example, in stores.
As a quick breakdown just to show you the split between the workload 6733, so you've got half performance on that in the estimators. It works out slightly differently, but we can see by the plans one of those are substantially more efficient.
So common words do say quite often. You've got vendors. They're not need it. You've got a large number of stores. We've had till systems querying to look at what things are currently in stock. We don't want them here in the out of stock items they don't need to look at those stock values. We can just hide them. There may be other teams like the peers that Istic seems to actually need to.
Those tables, but the sales assistant like Y +2 points don't need to do. It just cut out for them. So we put this pattern of using these keys into our application logic.
Problem solved.
So.
The party time combination of features and people so we've looked at partition views and partition tables and look to some of the benefits of compression.
These aren't unique they can be used together, we can have a partition. View across partition tables. So you've got multiple points of elimination, which comes back into this. This key conversation and this pattern that you will be designing a building so how are you going to eliminate safer exam?
One dimension, cutting across say transaction ID's and you can cut in a separate partition in a separate dimension for something like vendors or bills. Mobile phone bills for example, you will be logging into your account that's your ID.
Depending on how your group in his records up. You may have certain phone plans, that phone plan isn't going to change frequently enough if it is, we can also look at potential change in these or other scenarios bicycles. Valley bikes, you're only going to really be dealing with those.
Good partition off the Valley bikes into an area so this comes into the points of multiple databases for multiple different customers cool. You can have Valley bikes in a single location and then we can then split those on different bike model numbers as they going to look up what's there.
Other examples, we've used online holiday companies. We split them onto brands, so you go through a drill through to like Thomas Cook Thomas Cook is going into one split and then you've got the package holidays across another one quite often. Not browsing across different brands. If you ever think of the arg off site as well, another scenario.
Do you want to look at something that's not quite in that product group and you've got a drill backup levels so sometimes. That's a design pattern that's being put in place for performance. 'cause you're looking at price ranges. Alright, you're not looking at price range across all you know, potentially every item in our God. You're looking at price ranges between a particular type of items like TV's radio.
So these all fit together.
And there's person important person person you should be inviting to the party 'cause. They can do an awful lot of hard worker, they don't understand what you're trying to achieve.
The lovely infrastructure, guys, so, although we've gone through we've got our single database there.
In the blue tear we've done the bit where we've created multiple files across multiple filegroups at the very least, we should have data files and log files. These have different patterns. They should really be on different drives. Our infrastructure teams aware of what you're trying to do and what you're trying to achieve. These can also be on different disks.
Different drives doesn't necessarily mean that different disks did one see a unique place where they had multiple drives and they would just spend on discs. So when we were taking backups. It was hitting the same drive as our data file sat on and our logs.
So you watched the whole system have a nice dip now.
Bing developers you shouldn't need to worry about this, but you should have some understanding 'cause if one of those other 2 teams. The DBA teams are the infrastructure teams are blaming you for poor performance anywhere app.
And you know how many controller, not had any influence.
You need to find yourself ignorance can sometimes be blessed and sometimes people just point. The thing they understand least and developers. Sometimes you understand? What eBay is doing and infrastructures infrastructure won't understand what the dbas developers are doing so, it ends up into a conversation off lame game. But if you're on the same part page. You will understand what you're trying to achieve you can all off.
Control is anyone here running their SQL server databases on VM S.
Few numbers how many of you guys know how many controllers you've gotten your VMS.
One to prove you can have a large number of drives large number of disks all trying to fit through a single controller.
That could also be a bottleneck you can get huge performance benefits by adding in additional virtual controllers just like physical machines, so if you got physical states out there? How many controllers if you actually got VMS. It's much easier. It's a like a right click provision very easy to do. But if the teams who are administering this for you don't understand what you're trying to achieve.
Do they can do a lot of your good work?
So.
These are things to think of when implementing these as you start to breakdown.
What you're trying to do? What you're trying to achieve and those keys. These are the people you should be taking 3 or journeys. These are the people who should be talking to so in most cases, some of these will be conversations will start off with your development teams.
Try and involve them. It's early as you can in the process. 'cause earlier people try to be involved in the process. There's often less resistance come through the storage side of it. There maybe storage admins in your company in most cases, they want you to use less space and use it less frequently. How many people have just gone can you just put everything on SSD?
How many times of your son Admin said yes everyone? Yeah, it doesn't really happen if they understand what you're trying to do your trying to achieve the conversations can go in lot smoother quite often. You've got a finite amount of resources so if you know that you're going to be writing.
How's your new transaction IDs into a certain part of your database. You can put that into a certain file group?
That may have enough capacity to go in the SSD TS cool the rest of it doesn't necessarily need to same things with the transaction logs. Those there are benefits. So the transaction logs. This effects directly the speed to how quickly you can write an insert records.
If this is on a slower storage tier.
This can also hold you back so you may have a very good database design on the way it's been implemented is what is slowing down? What you're trying to achieve?
So these are people who you should be talking to.
And these are the people who should be inviting to your party so going over the conversations again. What have we discussed one of the questions. We've been trying to answer? What are your partition keys so most cases these are going to be simpler sequential? I DS but there are going to be use cases where they go.
And you can also choose to add in a little bit of complexity now to give you a lot of performance down the line. Yes, considering how we're going to deal with brandings adds the complexity now. Yes, you're going to be adding in.
Figure the multiple dimensions question is.
When do you want to do this you want to make sure that you've done enough of this early enough that you've not got into the position where you've got a really successful system with lots of customers 5,000,000,000 records in a single table. You've had a discussion about implementing partitioning brilliant. We're going to need 10 hours downtime.
That's not a conversation that anyone wants to have.
10 hours down time you're not going to get to do, and 'cause. It's all in one big structure. You can't start to break it out without starting to go down very convoluted options make it simple think about these easier, that early so who designs these.
So developers you guys are the key but your DBA's are there to help and support you implement this and in the same way that dbas can often will be the people implementing these but they need to understand what you're trying to achieve.
So that they can then have their conversations potentially with the infrastructure in storage teams and say apologies for those of you who are one minus armies who can do all this yourself. 'cause you know you're in a brilliant position, but quite often. We need to deal with other people. Other teams because there's an awful lot to do so. I want to focus on my bet at hand this over the 2 teams who.
Be able to do it themselves, but for them to go away, and do it do it. They need to understand what we're trying to do so who should implement this you will do to some regards.
And that's the biggest lesson I've learned from seeing many, many partitioning implementations. Some of them done very well. Some of them done very poorly. Some of them had unnecessary amount of complexity and some of them you had developers would think they've done a good job. The Dbas, who's miss implemented it. The Dbas who's misunderstood it from what?
Were they validate him where the keys haven't been used the infrastructure guy? Who put you on the same storage tiers take them through the journeys all try and make sure you're trying to achieve the same goal. That's the biggest thing I've learned through a collection of partitioning implementations.
You're all in this together, y'all team. You need to talk and then this becomes a lot lot easier and you're not scrambling trying to figure out how you scale something that was never designed and built to scale at 11 o'clock at night trying to squeeze this into an overnight maintenance window.
So.
That's what we've got for this talk.