Presentation Transcript: Introducing SQL Server 2012 Table Partitioning Improvements

  • Article

SQL11UPD02-TSCRIPT-03

This wiki is a transcript of a previously recorded video.

Related content assets: 

  • Presentation: Introducing SQL Server 2012 Table Partitioning Improvements (SQL11UPD02-DECK-02) 
  • Video: Introducing SQL Server 2012 Table Partitioning Improvements (SQL11UPD02-REC-03)

Slide: Title Slide

Hey everybody! This is Joe Sack and I’m a Principal Consultant with SQLskills and for this presentation I’m going to be covering SQL Server 2012 partitioning enhancements.

Slide: Improvements for Table Partitioning

So a few of the things I’ll cover include the increase to 15 thousand supported partitions for a partitioned index. You may recall this was offered through service packs in previous versions and now this is natively supported. A second improvement will be around partition statistics, so I’ll discuss a little bit about how that’s changed – and then also the wizard that you can use for partitioning has a couple of extra features in it and if you have a chance, have a look at the demonstration as well because I show you a couple of those options. And the last thing would be that you can use partitioning with columnstore indexing. And that’s actually in another demonstration as well in the columnstore module so be sure to check that out because there’s some interoperability with columnstore indexing, even though you can’t do an insert/update/delete or merge when you have a columnstore index added to a table, you can still use partitioning behind the scenes.

Slide: What Happens When…

So in 2008 and 2008 R2, if you wanted to have partitioning by day, or partitioning by hour, how much data could you store and the answer would be up to 1000 partitions, unless you installed a service pack and then enabled that database for 15 thousand partitions. So that was something that was allowed and that was added later, however then you have interoperability considerations, so for example if you’re using mirroring, or if you’re upgrading a server and it’s not immediately at that service pack out of the gate, you have extra things that you have to worry about in an environment with mixed versions.

So SQL Server 2012 brings in that 15 thousand partition support out of the box so anything up and above from that point forward, you don’t have to worry about anything additional if you want to start partitioning, for example, 15 thousand hours, 15 thousand hours – whatever granularity you’re looking for, for your application requirements.

Slide: 15,000 Partitions

And a little bit more about 15 thousand partitions – just because you can do it, doesn’t necessarily mean that you’ll want to do that. You still want to have an archiving strategy; you still want to be mindful about getting the data out of the table that you might not be accessing frequently, or might not need to be there for whatever reasons from a compliance perspective, or data archiving compliance perspective. So the idea is that 15 thousand partitions are there if you need it and it has that native interoperability with log shipping, with mirroring, with availability groups, and SQL Server Management Studio has that management of 15 thousand partitions.

So a couple of exceptions that you should be aware of… if you’re on 32-bit systems, or if you’re using non-aligned indexes, it’s not recommended or supported to do more than 1000 partitions. So you will be able to do it but it’s not a supported scenario – and the main reason for that is performance considerations. So you have a risk of degraded performance or for example, on 32-bit systems if you have 14 thousand, up to 15 thousand partitions – one aspect of that being insufficient memory.

Slide: What Happens When…

So switching gears, the next thing is partitioned indexing. What would happen before SQL Server 2012, if you rebuilt or created a new partitioned index, if you looked at the histogram, so if you did a DBCC SHOW_STATISTICS and you looked at the histogram, you would notice that all rows are being sampled in order to create that histogram.

So this is a little bit small on the screen, but if you can a look, the first 2008 version of the histogram that I did a screenshot of, it shows 483 thousand rows in the table and of that Rows Sampled is 483 thousand rows. So it was looking at the complete set of data in order to create the histogram.

Now in 2012, the default sampling algorithm is in effect. So when you create it, it’s going to do a sampling – so in the second example, the second screenshot I have here is Rows Sampled is 60 thousand out of 483 thousand.

So the best way to look at this behavior is just to be cognizant of it. So if you end up having a situation where the performance of queries have changed between 2008 R2, let’s say, and 2012 – take a look at the statistics, take a look at the quality of the histogram, so if there was some kind of benefit of full scan, the equivalent of full scan, you might want to then, in addition to creating or rebuilding your partitioned index, actually do an UPDATE STATISTICS with a full scan, for example.

So just be aware of this – there are many cases where I’m sure you won’t see any difference, but for those situations where data distributions are particularly sensitive to how the statistics were originally generated, you’ll want to check this out as one of the reasons that behavior might be affecting your query performance.

Slide: Manage Partition Wizard

And the third aspect is the management within SQL Server Management Studio. So if you launch the wizard through the Storage option for a specific table, prior to 2012 you had a couple of options including sliding-window and creating an actual archive table. Couple of options you’ll see here are Switch out partition and Switch in partition. So now you have isolated operations that you can say, look, I want to switch out from this partitioned table into an archive table or into another table that you’ve already pre-created – so it’s just another way to isolate out that workflow and it gives you a couple of extra choices.

Slide: Switch Out/In Partition

Here are just a couple of screenshots on selecting the partition and then if you look down here and I go over this in the demo as well, but if you look down here you have the option of creating that new table and it will create all the dependent objects that are needed in order for you to be able to switch things out, or you can pick an existing table and assuming that you’ve put in the required dependencies, that will work. And on the flip side you can also switch in so you can designate an archive, for example, or a delta table or a trickle table, and then switch that in to the main partitioned index.

Slide: Summary

So that was just a real quick summary of the partitioning changes in SQL Server 2012 and so an increased number of partitions – 15 thousand partition support so if you have the hourly or the daily requirements. Statistics generation footprint and then enhanced wizard options as well.

Thank you for watching and I hope you found this helpful.

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]

See Also

Another important place to find a huge amount of SQL Server General & Database Engine related articles is the TechNet Wiki itself. The best entry point is SQL Server General & Database Engine Resources on the TechNet Wiki

** **