SQL Server 统计释疑

SQL Server 统计释疑

 

原文名称：SQL Server Statistics: Explained

原文地址：http://blogs.msdn.com/b/srgolla/archive/2012/09/04/sql-server-statistics-explained.aspx

 

一．            很多DBA会在不同场合问我下面这些关于SQL Server统计的问题：

a)       我们应该停止数据库的统计自动更新，用一个维护计划来代替吗？

b)       自动统计所使用的采样率是多少？可以自己控制吗？

c)       在维护计划中重建索引，需要更新统计信息吗？

 

下面是对这些问题的解释：

在SQL Server数据库有3种类型的统计：

1）  创建索引而建立的索引统计，这类统计和索引名字相同。

2）  由优化器创建的所有（所谓的列统计），名字以_WA_*开始。

3）  DBA用CREATE STATISTICS创建的自定义统计。

 关于统计的一些事实：

• 在重建索引（rebuild）时，索引统计总是全扫描（fullscan）更新（唯一例外是在SQL 2012的分区索引中，当分区超过1000时，它就使用缺省采样）。 
• 在重建索引时，不更新列统计。列统计或者是由数据库自动更新，或者是我们手工运行UPDATE STATISTICS命令更新。
• 在运行UPDATE STATISTICS命令时，如果我们没有指定采样率，你们系统就会采用缺省采样率，缺省采样率算法见下。
• 如果在运行UPDATE STATISTICS命令时只指定了表名，你们就会更新表上所有的统计，包括所有的索引统计。所以，你可能会失去通过重建索引而得到的全扫描（fullscan）索引。

 自动更新统计算法：

自动更新统计会在表每变化（译注：应该是列每变化）500 + 20%数据行时触发。当然，在SQL 2012中我们有一个改进了的算法：SQRT(1000 * 数据行)，这个更好一点。

 

当自动更新触发时，系统会使用缺省的采样率，计算采样率的算法如下：

1）  如果表<8M，那么就使用全扫描更新统计。

2）  如果表>8M，会有一套算法。该算法随着表中数据行数的增加而逐渐减少采样率，这样确保不会扫描太多的数据。这不是一个固定值，是由优化器控制的。同时，它也不是一个线性算法。

比如：如果一个表有1,000,000行，可能的采样率为30%，但是当行数增到8,000,000时，可能采样率减为10%了。这个采样率是不受DBA控制的，而是由优化器决定的。

 

二．            问：如果我们停止数据库上的自动统计更新，而通过DBA使用定时任务来手工更新统计，行吗？ 

答：这得看优化器使用的采样率和手工更新索引所指定的采样率。采样率越大的效果越好。

 下面是自动更新算法中如何计算采样率（也就是优化器使用的采样率）。运行DBCC SHOW_STATISTICS(表名，统计名)，会得出数据行数以及采样行数，进行下面的数学运算，采样率=(采样行数/实际数据行数) * 100。

如果你运行SQL SERVER 2008 R2 SP2或者SQL SERVER 2012 SP1的话，那么有一个新的DMV sys.dm_db_stats_properties，它可以得到数据库中所有表的统计的采样率。 

 你可以对每个表进行检查，看看哪个方法的采样率最大，然后选择它。如果你认为手工更新统计会产生更高的采样率，你们可以执行sp_autoststs ‘OFF’，然后手工使用任务来更新统计。通过这种方法，无论是自动更新统计还是手工更新都能为优化器产生更好的执行计划。

 手工更新的劣处：

1） 需要DBA额外的负担来为客户的每个统计写更新脚本。

2） 如果任务 失败，直到下次运行前，我们一直使用没有更新的统计。

3） 如果SQL AGENT失败，那么你的更新统计任务就不能运行了。

 推荐：我们推荐你让数据库的auto stats选项置为ON，如果你发现一些表上的查询不理想，你可以停止该表的自动更新，然后用手工任务来更新该表的统计。

 

三．            参考文献

http://msdn.microsoft.com/en-us/library/dd535534.aspx

http://www.sqlskills.com/blogs/erin/post/New-Statistics-DMF-in-SQL-Server-2008R2-SP2.aspx