次の方法で共有

SQL Server 2012 partitioned table statistics update behavior change when rebuilding index

  • [アーティクル]

In this blog, I will talk about a couple of things related to statistics update when rebuilding index on a partitioned table.

 

In past versions, when you rebuild an index, you will get statistics update equivalent to FULLSCAN for free.   This is true regardless if the table is partitioned table or not.

But SQL Server 2012 changed the behavior for partitioned table.   If a table is partitioned, ALTER INDEX REBUILD will only update statistics for that index with default sampling rate.  In other words, it is no longer a FULLSCAN.  This is documented in https://technet.microsoft.com/en-us/library/ms188388.aspx.  But lots of users do not realized that.  If you want fullscan, you will need to run UPDATE STATISTCS WITH FULLSCAN.   This change was made because we started to support large number of partitions up to 15000 by default.  Previous versions did support 15000 partitions.  But it’s not on by default.  Supporting large number of partitions will cause high memory consumption if we track the stats with old behavior (FULLSCAN).  With partitioned table, ALTER INDEX REBUILD actually first rebuilds index and then do a sample scan to update stats in order to reduce memory consumption.

 

Another behavior change is actually a bug.  In SQL 2012, ALTER INDEX REBUILD doesn’t preserve norecompute property for partitioned tables.   In other words, if you specify norecompute on an index, it will be gone after you run ALTER INDEX REBUILD for SQL 2012.   We have corrected this issue in a newly released CU 3 of SQL Server 2012 SP1.  Here is the KB: https://support.microsoft.com/kb/2814780 

 

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

Comments

  • Anonymous
    July 11, 2013
    That's great, but there is still no support for statistics update per partition. At the moment the only way to keep statistics up do date after data are loaded into new partition is full scan, which is unacceptable for extremely large tables. Can you comment on the development in this direction?

  • Anonymous
    March 12, 2014
    Is it possible to SWAP the partition out to an AUX table perform update stats on the AUX table then swap the partition back in?