sqlskills-logo-2015-white.png

SQL Server 2012 Partitioned Index Default Sampling Algorithm Changes

When moving from SQL Server 2008+ to SQL Server 2012, be aware that the sampling algorithm has changed.

For example, I created a partitioned clustered index on two identical tables (same schema, rows and distribution), one in SQL Server 2008 R2 SP1 and the other in SQL Server 2012 RC0. 

Below shows the partial output (STAT_HEADER) of a DBCC SHOW_STATISTICS on SQL Server 2008 R2 SP1:

clip_image001

All rows were evaluated for the statistics generation.

Now compare this to SQL Server 2012 RC0:

clip_image002

SQL Server 2012 now uses default sampling for the partition creation and rebuild.

Is this good or bad?

Certainly if you’ve required a full scan in the past in order to get higher quality stats, this may not be a good thing.  Otherwise you may not notice anything at all from a query performance perspective if the default is good (or accurate) enough.

You can see the number of steps decreased in my examples in this post between 2008 R2 SP1 to SQL Server 2012.  My histograms for this identical table also changed of course.  For example, moving from average rows on one step of 5504 down to 5305, distinct range rows from 343 up to 361… The real question is, will these changes be enough to turn a good plan bad. 

Post-upgrade, if you see changes in query performance against partitioned tables, evaluate the histograms and test performance of the workload after the default sampling versus after a full scan update of the statistics (via UPDATE STATISTICS for example).  

3 thoughts on “SQL Server 2012 Partitioned Index Default Sampling Algorithm Changes”

  1. Hi Joe-

    Quick confirmation – this was the sample after you simply CREATED the index, right? Have you seen the same behavior when you create a non-partitioned clustered index? And…if you rebuild the index, does it sample at 100%? BTW, I can test this all 🙂 Just wondered if you had already.

    Thanks!

    Erin

  2. Hi Erin!

    Good to hear from you. This new sampling happened after a creation on SQL Server 2012, correct.

    For non-partitioned clustered index, I haven’t test that but I don’t think that should have changed. Only partitioning.

    Regarding rebuilds – good question, and I didn’t test that (yet). 🙂

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course