统计信息自动更新导致查询超时

数据库有个大的接口日志表InterfaceRecord,表中有XML字段保存了接口报文,每个月新增6000多万的数据(大约300多GB),数据库有个JOB每周日晚上定时把表中1个月前的数据归档到历史库再保存3年时间(客户要求),表上有个复合索引createtime(创建时间),bipcode(接口编码),有一天运维反映程序页面通过createtime+bipcode精确的查询某个时间点的数据会一直超时。正常情况,这个查询是可以通过索引查找，在数毫秒之内就能得到结果的。

于时我直接到数据库去运行查询语句(select * from InterfaceRecord where createtime='' and bipcode=''),发现数十分钟查询也没有运行完,查看系统线程

      select SUBSTRING(ST.text, ( QS.stmt_start / 2 ) + 1,              
                   ( ( CASE stmt_end              
                         WHEN -1 THEN DATALENGTH(st.text)              
                         ELSE QS.stmt_end              
                       END - QS.stmt_start ) / 2 ) + 1),* from sys.sysprocesses  qs              
      outer apply sys.dm_exec_sql_text(sql_handle) st              
   where spid>50 and lastwaittype<>'MISCELLANEOUS' and spid<>@@SPID                
        or spid in(select blocked from sys.sysprocesses);  

发现语句没有被其他进程阻塞(blocked=0),等待类型是I/O(lastwaittype=PAGEIOLATCH_SH),正在执行的语句(第一列)如下：

SELECT StatMan([SC0], [SC1], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], step_direction([SC0]) over (order by NULL) AS [SB0000] 

 FROM (SELECT [Createtime] AS [SC0], [Bipcode] AS [SC1] FROM [dbo].[Interfacelog] TABLESAMPLE SYSTEM (1.483665e+000 PERCENT) WITH (READUNCOMMITTED) ) 

AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

（PS:第一反应，这是什么鬼-_-,跟我运行的语句没有半毛钱关系)仔细分析查询,正在对InterfaceRecord表的createtime和bipcode字段做抽样查询(TABLESAMPLE),那就很明显了----正在抽样读取数据，以更新统计信息.

所以事情的经过应该是这样子的：

1、周日晚上的作业，删掉了表中1个月前的数据。

2、运行查询语句时，如果统计信息已经过期，就会先更新统计信息，再运行实际的查询

3、程序的超时设置的是30秒钟,每次还没有等更新完统计信息就超时了,所以统计信息一直没有更新成功。

找到问题，临时解决方案如下：

alter database <dbname> set AUTO_UPDATE_STATISTICS_ASYNC on

作用是运行查询语句时，发现统计信息已经过期,如果AUTO_UPDATE_STATISTICS_ASYNC设置为OFF,数据库会更新统计信息，再运行实际的查询(前面查询语句超时的原因),

如果设置为ON，数据库会根据过期的统计信息先运行查询语句，并在后台继续更新统计信息。设置完之后,查询语句恢复正常。

对于这种大的日志表定期进行归档，最好还是把表变成分区表,直接把整个分区SWITCH给一个临时表,然后TRUNCATE清空临时表，就可以从原来删掉数据所须的数小时变成数秒钟。这个等后面讨论分区的时候再做介绍。