SQLServer统计信息理解 - 数据库编程

序或降序键列上发生插入操作。

在维护操作后。

--创建测试表
create table test(id int identity(1,1),name char(20),value numeric(18,4),meno varchar(50))

create clustered index IX_test on test(name)

alter table test add constraint PK_test primary key nonclustered(id)


--以[dbo].[test]表为例,先查看
select i.name,rows,rowmodctr,stats_date(s.object_id,s.stats_id) AS update_date
from sys.sysindexes i inner join sys.stats s on i.name=s.name
where s.object_id = OBJECT_ID('[dbo].[test]')

--此时观看两个索引的直方图,什么都没有
DBCC SHOW_STATISTICS('[dbo].[test]','IX_test')
DBCC SHOW_STATISTICS('[dbo].[test]','PK_test')

--插入1行数据,统计信息没有更新?
insert into test(name,value,meno)
select 'name',0,'meno'

--最终增删了506*2行,统计信息都没有生成
insert into test(name,value,meno)
select 'name',0,'meno'
go 500

delete from test

--重新测试：重新删除创建表。在插入数据前,每个字段搜索一次，非索引字段会自动生成统计信息.
select * from test where id=1
select * from test where name=''
select * from test where value=0
select * from test where meno=''

--插入一行数据,否则操作任何列统计信息都没有更新
insert into test(name,value,meno)
select 'name',0,'meno'
go 

--上面的查询并没有自动更新统计信息,只有作为where条件的更改或删除了才更新统计信息
update test set name='name' where name='name'
update test set value=0 where value=0
update test set meno='meno' where id=1
delete from test where meno='meno'

--再重新插入数据,准备测试用
insert into test(name,value,meno)
select 'name',0,'meno'
go 
--查看统计情况
select i.name,rows,rowmodctr,stats_date(s.object_id,s.stats_id) AS update_date
from sys.sysindexes i inner join sys.stats s on i.name=s.name
where s.object_id = OBJECT_ID('[dbo].[test]')

--当我更新索引的统计信息到 rowmodctr = 500 行的时候，统计信息并没有更新
update test set name='name' where name='name'
go 497

--z再更新一次，使索引更新累计rowmodctr = 501行
update test set name='name' where name='name'
go

结果发现：索引的统计信息更新了，rowmodctr重新设置为1行。按相同的方法更新value为501次，非键列是没有更新的！
update test set value=0 where value=0

也就是这个条件是符合的：表中行范围 0500 ；
--插入数据到501行
insert into test(name,value,meno)
select 'name',0,'meno'
go 500

当数据大于500行达到501行时，rowmodctr此时大于500行并没有更新索引的统计信息。
--现在更新501行数据的20%，统计信息并没有更新。
with tab as(select top 20 percent * from test)
update tab set name='name'

现在行数602行，理论上超过601.2(501+501*0.2)行会更新，现在在更新一次，如果统计信息自动更新就对了

上图看到，真的更新了！所以这个条件是符合的：表中行范围rows>500行，只要变化的次数rowmodctr>500+20%rows；

统计信息更新总结如下：

?表中行范围rows=0 行增长 rows>0行（插入时不更新，更新删除行才更新）；

?表中行范围 0500 ；

?表中行范围rows>500 行，只要变化的次数 rowmodctr>500+20%rows；

?临时表行数rows<6，只要变化的次数rowmodctr>6（未测试）；

创建索引时自动生成同名统计信息

非索引列在表有数据时首次作为条件查询时自动生成统计信息

对表首次插入多少数据都不自动更新统计信息

非键列的rows总是不更新（因为不存储数据）

统计信息相关操作：

--查看只索引的统计信息更新时间
SELECT name AS index_name,STATS_DATE(object_id, index_id) AS update_date
FROM sys.indexes 
WHERE object_id = OBJECT_ID('[Sales].[SalesOrderDetail]');

--查看所有统计信息更新时间
select s.name,STATS_DATE(s.object_id, stats_id) AS update_date
from sys.stats s 
WHERE s.object_id = OBJECT_ID('[Sales].[SalesOrderDetail]');

--查看所有统计信息更新时间
exec sp_helpstats N'[Sales].[SalesOrderDetail]', 'ALL'
GO

--创建统计信息
CREATE STATISTICS [_WA_user_00000001_00000001] ON [Sales].[SalesOrderDetail](ProductID, SalesOrderDetailID)  

--查看某个统计信息
DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','_WA_user_00000001_00000001')

--更新1个统计信息
UPDATE STATISTICS [

SQLServer统计信息理解(四)