This wiki is a transcript of a previously recorded video.
Related content assets:
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.
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.
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.
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.
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.
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.
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.
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)
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