Statistics Auto Update

SQL SERVER 2008 QUERY PERFORMANCE TUNING DISTILLED中写到:

To optimize the update process, SQL Server uses an efficient algorithm to decide when to execute the update statistics procedure, based on factors such as the number of modifications and the size of the table:

1.when a table with no rows gets a row

2.when a table has fewer than 500 rows and is increased by 500 or more rows

3.when a table has more than 500 rows and is increased by 500 rows+20 percent of the number of the rows

 

环境：我们先建立一张表，10000条数据，

CREATE TABLE [dbo].[Xquery](
 [fld1] [varchar](10) NULL,
 [fld2] [varchar](10) NULL,
 [fld3] [int] NULL,
 [FLD4] [varchar](200) NULL
) ON [PRIMARY]

go

 

declare @nint int

set @nint=1

while( @nint<=10000)

begin

   insert into Xquery(fld1,fld2) values('A'+right('00000'+ltrim(rtrim(convert(varchar,@nint))),5),

                                                          'B'+right('00000'+ltrim(rtrim(convert(varchar,@nint))),5))

   set @nint=@nint+1

end

go

 

 

建立一个clustered index on fld2 ,一个nonclustered index on fld1

 

 

说明一下nonclustered index 与clustered index:

从第一个execution plan看出，查询走了nonclustered index，确实SQL SERVER对优化算法实行了自动化。

从第二个execution plan看出，查询走了nonclustered index+bookmark lookup,nonclustered index存储了fld1的key值，同时也存储了fld2的key值，但是没有存储其他column的值，而这个query要求查询其他columns，因此走clustered index look up.

 

 

从这个<='A09888'来看，query要求返回的数据集相当大，因此不走nonclustered index,直接走clustered index scan.

因此要返回大数据量，sql server optimizer会自动选择table scan 或者clustered index scan.

 

我们分别在这张表插入一条数据和3000条数据，来看看auto stats事件在 profiler中的反应：

 

 

 

 

大家可以看到，在insert 一条语句之后，立即查询select * from Xquery where fld1='A00050'，statistics并没有立即更新

但是在insert 3000条数据之后，查询select * from Xquery where fld1='A00050'，statistics立即更新了，说明大于500 rows，更新的数据量在500+20 %总记录，在此例中是3000条，statistics会自动更新。当然这是在数据库的  auto update statistics为true的情况下。