SQL Server 统计信息相关操作（转）

叁歲伎倆 2022-09-30 00:41 362阅读 0赞

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  
你可以稍后按照下面调度作业：

*  在你强迫禁用autostat期间的所有表上运行UPDATE STATISTICS。

并且

*  重新启用AutoStat，因为当UPDATE STATISTICS运行时每一个表的更改计数器会重置为0。

**控制UPDATE STATISTICS是否在表上运行**  
当AutoStat被证明有问题的时候，最明显的解决方法是禁用自动统计，从而让DBA在数据库不忙的时候自由调度UPDATE STATISTICS。你可以通过使用UPDATE STATISTICS或sp\_autostats存储过程来完成，UPDATE STATISTICS的语法如下：  
  UPDATE STATISTICS <table>...with NORECOMPUTE  
sp\_autostats存储过程的语法如下：  
sp\_autostats <table\_name>, <stats\_flag>, <index\_name>  
stats\_flag的取值为"on"或"off"。  
你也可以使用sp\_dboption在每个数据库级禁用UPDATE STATISTICS或CREATE STATISTICS：  
sp\_dboption <dbname>,'auto update statistics', <on | off>  
或者  
sp\_dboption <dbname>,'auto create statistics', <on | off>  
**控制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表里选择来查看这些锁。

\---查询索引操作的信息  
select \* from sys.dm\_db\_index\_usage\_stats

\--查询指定表的统计信息(sys.stats和sysobjects联合查询)  
select  
  o.name,--表名  
  s.name,--统计信息的名称  
  auto\_created,--统计信息是否由查询处理器自动创建  
  user\_created--统计信息是否由用户显示创建  
from  
  sys.stats  
inner join  
  sysobjects o  
on  
  s.object\_id=o.id  
where  
  o.name='表名'  
go

\--查看统计信息中列的信息  
select  
  o.name,--表名  
  s.name,--统计信息的名称  
  sc.stats\_column\_id,  
  c.name---列名  
from  
  sys.stats\_columns sc  
inner join  
  sysobjects o  
on  
  sc.object\_id=o.id  
inner join  
  sys.stats s  
on  
  sc.stats\_id=s.stats\_id and sc.object\_id=s.object\_id  
inner join  
  sys.columns c  
on  
  sc.column\_id=c.column\_id and sc.object\_id=c.object\_id  
where  
  o.name='表名'

\--查看统计信息的明细信息  
dbcc show\_statistics

\--查看索引自动创建的统计信息  
exec sp\_autostats '对象名'

\--关闭自动生成统计信息的数据库选项  
alter datebase 数据库名 set auto\_create\_statistics off

\--创建统计信息  
create statistics 统计信息名称 on 表名(列名)  
\[with  
\[\[fullscan  
   sample number\{percent|rows\}\]  
\[norecompute\]  
\]  
go  
解释一下上面的参数：  
fullscan:指定对表或视图中所有的行收集统计信息  
sample number\{percent|rows\}:指定随机抽样应读取的数据行数或者百分比 sample选项不能与fullscan选项同时使用  
norecompute:指定数据库引擎不自动重新计算统计信息

\--计算随机抽样统计信息  
create statistics 统计信息名称 on 表名(列名)  
with sample 5 percent---创建统计信息，按5%计算随机抽样统计信息  
go

\--创建统计信息  
exec sp\_createstats--参数自己去查下帮助，在这里不一一列举

\--修改统计信息  
update statistics 表名|视图名  
    索引名|统计信息名,索引名|统计信息名,.....  
\[with  
\[\[fullscan  
   sample number\{percent|rows\}\]  
\[norecompute\]  
\]  
\---参数与create statistics 语句相似，下面介绍几种常用应用  
1.更新指定表的所有统计信息  
update statistics 表名

2.更新指定表的单个索引的统计信息  
update statistics 表名 索引名

3.对表进行全面扫描，更新统计信息  
update statistics 表名(列名) with fullscan