本文深入探讨了SQL Server的AutoStat功能，介绍了其如何通过自动更新统计来优化查询性能。文中详细解释了AutoStat的工作原理、触发条件以及如何手动控制统计更新。
--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
-- 原帖地址
摘要
最新引进的统计维护功能AutoStat可能在生产系统上通过执行下面的行为产生不必要的开销：
关于SQLServer2000里autostats更多的信息， 请查看
注 如果你使用的是SQLServer2005看下面 微软白皮书里关于查询优化器怎样使用统计的信息。
更多信息
常规信息
SQLServer使用基于成本的优化（CBO），所以对表和索引提供的统计信息非常敏感。如果没有正确和最新的统计信息，那么SQLServer在为特定的查询产生最好的执行计划时会面临挑战。
SQLServer每个表上的统计维护使用下面的信息帮助优化器做出基于成本的决定：
• 表的行数
• 表使用的页数
•自上次统计更新以来表的键被更改的数量
为索引（每一个索引）存储的另外信息包括：
•第一列上等高的柱状图
•所有列前缀的密度
• 平均键长
当一个新索引创建时索引上的统计就自动创建了。另外，现在也可以在其他列上创建和维护统计了。
为了尽可能的维护统计信息使其最新，SQLServer引进了AutoStat功能，通过追踪表的更改，当达到某一个更改阈值时，它能为表自动更新统计。SQLServer还引进了auto-create-statistics功能，它能为正确优化特定的查询而使服务器自动的产生所有需要的统计。
AutoStat触发的时机
正如上面所述，当达到一个更改阈值（"change threshold"）时，AutoStat会为特定的表自动更新统计。系统表sysindexes的列rowmodctr用来记录自上次更新表的统计后插入、删除、更新的行的总数，随着时间的推移，它可以影响查询处理器的决策过程。这个计数器每当下面的事件发生时被更新：
•插入一行
• 删除一行
• 更新了索引列
注：TRUNCATE TABLE不会更新rowmodctr。
表统计更新后，rowmodctr的值重新设置为0，并且更新表的统计架构版本。
更进一步，在存储过程从缓存中采用执行计划并且计划对统计比较敏感的情形下，统计架构版本将和目前的版本比较。如果有新的版本可用，存储过程的计划将被重新编译。
自动更新统计的基本法则：
• 如果表的集的势小于6并在tempdb数据库里，对表的每6个更改就会自动更新统计。
• 如果表的集的势大于6且小于等于500，则每500个更改就自动更新统计。
• 如果表的集的势大于500，则当有500加上表的20％个更改时自动更新统计。
• 对于表变量，更改集的势不会触发自动更新统计。
注：在此严谨的场合，SQLServer用表的行数计算集的势。
注：除了集的势之外，断言的选择也会影响AutoStats的触发时机。这意味着如果集的势小于500且每500个更改之后或者如果集的势大于500且每20％的更改之后统计不会被更新。一个按比例增加的因素（取值范围从1到4，包括1和4）的产生依赖于选择性、该因素的积、根据法则得到的更改行数（该行数是对于触发AutoStats要求更改的实际行数）。
上面的法则可用下表的形式来展现：
下面有两个例子来帮助示范这个概念：
示例1
考虑pubs数据库里的authors表，它有23行，2个索引。在一列au_id上有一个唯一聚集索引UPKCL_auidind，在列au_lname和au_fname上有一个非聚集的复合索引aunmind。因为这个表小于500行，AutoStat将在表数据的500个更改之后开始。更改可以是针对索引的列如au_lname或它的任何联合的500或更多的insert、delete中的一个。
因此，你能通过追踪随着每次更改增加的sysindexes.rowmodctr的值来预计UPDATE STATISTICS将在什么时候开始。当它达到或超过500时，你可以预计UPDATE STATISTICS要启动了。
示例2
考虑另一个集的势为1000的表t2。对于大于500行的表来说，SQLServer将在有（500+20％*集的势）个更改时启动UPDATE STATISTICS。按照规则，1000的20％是200，所以你可以预计在对表做出大约700个更改之后会触发AutoStat。
统计更新自动化
为了在AutoStat将运行时自动更新统计，你可以检测sysindexes表，确定表更改次数何时达到触发点。下面是基本的算法：
if (sysindexes.rows > 500)
if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
hours) //500 change leeway
begin
disable autostats
log autostats disable
end
else
begin
stats ok
end
else
if (sysindexes.rowmodctr >= 425) //75 change leeway
begin
disable autostats
log autostats disable
end
你可以稍后按照下面调度作业：
sp_autostats , ,
stats_flag的取值为"on"或"off"。
你也可以使用sp_dboption在每个数据库级禁用UPDATE STATISTICS或CREATE STATISTICS：
sp_dboption ,'auto update statistics',
或者
sp_dboption ,'auto create statistics',
控制UPDATE STATISTICS进程的并发数
通常，只要没有为特定表禁用AutoStat，就不可能同时自动产生大量的UPDATE STATISTICS语句（DCR 51539已对此存档）。不管怎样，服务器限制UPDATE STATISTICS进程的并发数为每处理器4个。
何时运行Autostats当依赖于统计的存储过程重新编译时，你可以使用跟踪标记205来报告AutoStat的结果。这个标记将写下面的信息到错误日志：
1998-10-15 11:10:51.98 spid9 Recompile issued : ProcName: sp_helpindex
LineNo: 75 StmtNo: 29
当跟踪标记205打开时，更新统计的来自跟踪8721的AutoStat信息也包括下面的信息。开始信息会在sysindexes的列RowModCnt中以大于0的值存储。在运行UPDATE STATISTICS后，结束信息会在列RowModCnt中以0存储：
1998-10-15 11:38:43.68 spid8 Schema Change: Tbl Dbid: 7 Objid:
133575514 RowModCnt: 60500 RowModLimit: 60499
对于该信息，"RowModCnt"是对表的更改总数。"RowModLimit"是阈值，当超过这个阈值时，UPDATE STATISTICS语句将为表而执行。
打开跟踪标记8721也可能在AutoStat运行时将信息存入错误日志。下面是你希望看到的信息类型的例子：
1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED Tbl: [authors]
Rows: 23 Mods: 501 Bound: 500 Duration: 47ms UpdCount: 2
对于该信息，"Mods"是对表的更改总数，"Bound"是更改阈值，"Duration"是语句UPDATE STATISTICS执行完成需要的时间，"UpdCount"更新统计的计数。
你也可以使用事件探查器来分辨UPDATE STATISTICS语句是否运行，操作步骤详见文章《SQLServer UPDATE STATISTICS提示》。
注：如果很多统计是用AutoStat来更新的，那么大量的信息会写到错误日志里。所以在生产服务器或其他关键的服务器上使用它们之前要对这些跟踪标记进行彻底的试验。
架构锁
SQLServer使用两种类型的架构锁，当对表进行统计更新时都要用到：
Sch-S: 架构稳定锁
----------------------------
该锁确定任何会话在架构元素如表或索引上有架构稳定锁时架构元素不被删除。
Sch-M-UPD-STATS: 架构更改锁
-----------------------------------------
这是一个非阻塞锁，系统用来确定在给定时刻表上只有一个自动更新统计进程在运行。
Sp_lock存储过程将使用类型为TAB，资源为UPD-STATS、模式为SCH-M来报告该锁。
你可以通过运行sp_lock或从syslockinfo表里选择来查看这些锁。
-- 原帖地址
摘要
最新引进的统计维护功能AutoStat可能在生产系统上通过执行下面的行为产生不必要的开销：
- 在繁重的生产期间开始统计更新
- 在特定点开始大量的更新统计进程
关于SQLServer2000里autostats更多的信息， 请查看
注 如果你使用的是SQLServer2005看下面 微软白皮书里关于查询优化器怎样使用统计的信息。
更多信息
常规信息
SQLServer使用基于成本的优化（CBO），所以对表和索引提供的统计信息非常敏感。如果没有正确和最新的统计信息，那么SQLServer在为特定的查询产生最好的执行计划时会面临挑战。
SQLServer每个表上的统计维护使用下面的信息帮助优化器做出基于成本的决定：
• 表的行数
• 表使用的页数
•自上次统计更新以来表的键被更改的数量
为索引（每一个索引）存储的另外信息包括：
•第一列上等高的柱状图
•所有列前缀的密度
• 平均键长
当一个新索引创建时索引上的统计就自动创建了。另外，现在也可以在其他列上创建和维护统计了。
为了尽可能的维护统计信息使其最新，SQLServer引进了AutoStat功能，通过追踪表的更改，当达到某一个更改阈值时，它能为表自动更新统计。SQLServer还引进了auto-create-statistics功能，它能为正确优化特定的查询而使服务器自动的产生所有需要的统计。
AutoStat触发的时机
正如上面所述，当达到一个更改阈值（"change threshold"）时，AutoStat会为特定的表自动更新统计。系统表sysindexes的列rowmodctr用来记录自上次更新表的统计后插入、删除、更新的行的总数，随着时间的推移，它可以影响查询处理器的决策过程。这个计数器每当下面的事件发生时被更新：
•插入一行
• 删除一行
• 更新了索引列
注：TRUNCATE TABLE不会更新rowmodctr。
表统计更新后，rowmodctr的值重新设置为0，并且更新表的统计架构版本。
更进一步，在存储过程从缓存中采用执行计划并且计划对统计比较敏感的情形下，统计架构版本将和目前的版本比较。如果有新的版本可用，存储过程的计划将被重新编译。
自动更新统计的基本法则：
• 如果表的集的势小于6并在tempdb数据库里，对表的每6个更改就会自动更新统计。
• 如果表的集的势大于6且小于等于500，则每500个更改就自动更新统计。
• 如果表的集的势大于500，则当有500加上表的20％个更改时自动更新统计。
• 对于表变量，更改集的势不会触发自动更新统计。
注：在此严谨的场合，SQLServer用表的行数计算集的势。
注：除了集的势之外，断言的选择也会影响AutoStats的触发时机。这意味着如果集的势小于500且每500个更改之后或者如果集的势大于500且每20％的更改之后统计不会被更新。一个按比例增加的因素（取值范围从1到4，包括1和4）的产生依赖于选择性、该因素的积、根据法则得到的更改行数（该行数是对于触发AutoStats要求更改的实际行数）。
上面的法则可用下表的形式来展现：
| 表类型 | 清空条件 | 清空阈值 | 不清空阈值 |
| 永久表 | <500行 | 更改数>=500 | 更改数>=500+20%*集的势 |
| 临时表 | <6行 | 更改数>=6 | 更改数>=500+20%*集的势 |
| 表变量 | 更改集的势不会触发AutoStats |
下面有两个例子来帮助示范这个概念：
示例1
考虑pubs数据库里的authors表，它有23行，2个索引。在一列au_id上有一个唯一聚集索引UPKCL_auidind，在列au_lname和au_fname上有一个非聚集的复合索引aunmind。因为这个表小于500行，AutoStat将在表数据的500个更改之后开始。更改可以是针对索引的列如au_lname或它的任何联合的500或更多的insert、delete中的一个。
因此，你能通过追踪随着每次更改增加的sysindexes.rowmodctr的值来预计UPDATE STATISTICS将在什么时候开始。当它达到或超过500时，你可以预计UPDATE STATISTICS要启动了。
示例2
考虑另一个集的势为1000的表t2。对于大于500行的表来说，SQLServer将在有（500+20％*集的势）个更改时启动UPDATE STATISTICS。按照规则，1000的20％是200，所以你可以预计在对表做出大约700个更改之后会触发AutoStat。
统计更新自动化
为了在AutoStat将运行时自动更新统计，你可以检测sysindexes表，确定表更改次数何时达到触发点。下面是基本的算法：
if (sysindexes.rows > 500)
if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
hours) //500 change leeway
begin
disable autostats
log autostats disable
end
else
begin
stats ok
end
else
if (sysindexes.rowmodctr >= 425) //75 change leeway
begin
disable autostats
log autostats disable
end
你可以稍后按照下面调度作业：
- 在你强迫禁用autostat期间的所有表上运行UPDATE STATISTICS。
- 重新启用AutoStat，因为当UPDATE STATISTICS运行时每一个表的更改计数器会重置为0。
控制UPDATE STATISTICS是否在表上运行
当AutoStat被证明有问题的时候，最明显的解决方法是禁用自动统计，从而让DBA在数据库不忙的时候自由调度UPDATE STATISTICS。你可以通过使用UPDATE STATISTICS或sp_autostats存储过程来完成，UPDATE STATISTICS的语法如下：
UPDATE STATISTICS
sp_autostats , ,
stats_flag的取值为"on"或"off"。
你也可以使用sp_dboption在每个数据库级禁用UPDATE STATISTICS或CREATE STATISTICS：
sp_dboption ,'auto update statistics',
或者
sp_dboption ,'auto create statistics',
控制UPDATE STATISTICS进程的并发数
通常，只要没有为特定表禁用AutoStat，就不可能同时自动产生大量的UPDATE STATISTICS语句（DCR 51539已对此存档）。不管怎样，服务器限制UPDATE STATISTICS进程的并发数为每处理器4个。
何时运行Autostats当依赖于统计的存储过程重新编译时，你可以使用跟踪标记205来报告AutoStat的结果。这个标记将写下面的信息到错误日志：
1998-10-15 11:10:51.98 spid9 Recompile issued : ProcName: sp_helpindex
LineNo: 75 StmtNo: 29
当跟踪标记205打开时，更新统计的来自跟踪8721的AutoStat信息也包括下面的信息。开始信息会在sysindexes的列RowModCnt中以大于0的值存储。在运行UPDATE STATISTICS后，结束信息会在列RowModCnt中以0存储：
1998-10-15 11:38:43.68 spid8 Schema Change: Tbl Dbid: 7 Objid:
133575514 RowModCnt: 60500 RowModLimit: 60499
对于该信息，"RowModCnt"是对表的更改总数。"RowModLimit"是阈值，当超过这个阈值时，UPDATE STATISTICS语句将为表而执行。
打开跟踪标记8721也可能在AutoStat运行时将信息存入错误日志。下面是你希望看到的信息类型的例子：
1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED Tbl: [authors]
Rows: 23 Mods: 501 Bound: 500 Duration: 47ms UpdCount: 2
对于该信息，"Mods"是对表的更改总数，"Bound"是更改阈值，"Duration"是语句UPDATE STATISTICS执行完成需要的时间，"UpdCount"更新统计的计数。
你也可以使用事件探查器来分辨UPDATE STATISTICS语句是否运行，操作步骤详见文章《SQLServer UPDATE STATISTICS提示》。
注：如果很多统计是用AutoStat来更新的，那么大量的信息会写到错误日志里。所以在生产服务器或其他关键的服务器上使用它们之前要对这些跟踪标记进行彻底的试验。
架构锁
SQLServer使用两种类型的架构锁，当对表进行统计更新时都要用到：
Sch-S: 架构稳定锁
----------------------------
该锁确定任何会话在架构元素如表或索引上有架构稳定锁时架构元素不被删除。
Sch-M-UPD-STATS: 架构更改锁
-----------------------------------------
这是一个非阻塞锁，系统用来确定在给定时刻表上只有一个自动更新统计进程在运行。
Sp_lock存储过程将使用类型为TAB，资源为UPD-STATS、模式为SCH-M来报告该锁。
你可以通过运行sp_lock或从syslockinfo表里选择来查看这些锁。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-604120/，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备09055130号-4 北京市公安局海淀分局网监中心备案编号：11010802021510
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
转载于:http://blog.itpub.net/35489/viewspace-604120/
扫一扫
3130
到【灌水乐园】发言
