SqlServer数据库统计信息的操作

一、创建/开启统计信息
统计信息会在每个新创建的索引中自动创建统计信息。
如果数据库中AUTO_CREATE_STATISTICS被设置为ON，SQLServer将会自动对查询中用到的，且没有索引的列自动创建统计信息。

--查看数据库统计信息配置情况
SELECT  name,is_auto_update_stats_async_on,  
        is_auto_create_stats_on,  
        is_auto_update_stats_on 
FROM    sys.databases  
WHERE   name = 'Baike'; --指定数据库名

--启用自动统计信息创建功能（这个选项默认为ON）
ALTER  DATABASE[你的库名]
SET AUTO_CREATE_STATISTICS ON

--开启自动更新统计信息：（这个选项默认为ON）
ALTER  DATABASE[你的库名]
SET AUTO_UPDATE_STATISTICS ON

--开启异步更新统计信息：（这个选项默认为OFF）
ALTER  DATABASE[你的库名]
SET AUTO_UPDATE_STATISTICS_ASYNC ON
如果开启了这个选项，查询优化器将先执行一次查询，然后更新过期的统计信息。
当你把这个选项设为OFF时，查询优化器将在编译查询之前更新过期统计信息。
这个选项在OLTP环境下很有用，但在数据仓库中有负面影响。


--手动创建统计信息:
CREATE STATISTICS 统计名称 ON 表名 (列名 [,...n])
示例：
CREATE STATISTICS [filter_statistics]
ON [dbo].[SalesOrderDetail] (OrderQty,ProductID)
WHERE SpecialOfferID = 1;--加上这段就成了过滤统计信息了。


二、查询统计信息

----查看某个统计信息 ：
DBCC SHOW_STATISTICS('表名','索引名')

DBCC SHOW_STATISTICS('[dbo].[SalesOrderDetail]','_WA_user_00000001_00000001')  

--通过系统视图sys.stats查看统计信息：
SELECT  object_id ,  
        OBJECT_NAME(object_id) AS TableName ,  
        name AS StatisticsName ,  
        auto_created  
FROM    sys.stats
--where object_id=OBJECT_ID('dbo.SalesOrderHeader')  
where objectproperty(object_id,'IsUserTable')=1
ORDER BY object_id DESC;

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

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

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


三、更新统计信息
需要用到sys.sysindexes
系统表sysindexes的列rowmodctr，它记录自上次更新统计信息后插入、删除、更新行的累计总次数

SELECT name,rows,rowmodctr FROM sys.sysindexes

自动更新统计规则：
•表中行范围rows=0行增长rows>0行；
•表中行范围 0<rows<=500行，只要变化的次数rowmodctr>500；
•表中行范围rows>500行，只要变化的次数rowmodctr>500+20%rows；
•临时表行数rows<6，只要变化的次数rowmodctr>6；

--手动更新指定表名的统计信息：
UPDATE STATISTICS 表名[索引名]

UPDATE STATISTICS [dbo].[SalesOrderDetail] [_WA_user_00000001_00000001] WITH FULLSCAN  

UPDATE STATISTICS  dbo.SalesOrderDetail

--更新数据库中所有可用的统计信息  
EXEC sys.sp_updatestats;

--手动更新SQL Server实例中所有数据库表的统计信息 
DECLARE @sql nvarchar(300)

DECLARE UpdateStatsForAllDBs CURSOR
READ_ONLY
FOR select name from sysdatabases

DECLARE @name nvarchar(255)
OPEN UpdateStatsForAllDBs

FETCH NEXT FROM UpdateStatsForAllDBs INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SET @sql = N'EXEC ' + QUOTENAME(@name) + N'.sys.sp_updatestats'
        EXEC sp_executesql @sql
    END
    FETCH NEXT FROM UpdateStatsForAllDBs INTO @name
END

CLOSE UpdateStatsForAllDBs
DEALLOCATE UpdateStatsForAllDBs
GO


四、关闭/删除统计信息

--手动删除统计信息  
DROP STATISTICS dbo.SalesOrderHeader.st_DueDate_SalesOrderHeader;
DROP STATISTICS [dbo].[SalesOrderDetail].[_WA_user_00000001_00000001];

--关闭SQLServer自动更新统计信息:
1、使用sp_autostats来在表、索引或者统计对象上显式并更改自动更新统计信息选项。
2、在表级别中，可以使用NORECOMPUTEoption of the UPDATE STATISTICS命令。
3、你也可以在CREATESTATISTICS命令中使用NORECOMPUTE选项，但之后需要删除并重建统计信息。
4、在CREATE INDEX命令中使用STATISTICS_NORECOMPUTE。
5、在数据库级别，可以使用以下命令来禁用：
ALTER DATABASE[你的库名]
SET AUTO_UPDATE_STATISTICS OFF
当使用数据库级别的禁用时，表、索引或者统计对象的设置将全部失效。


五、统计信息的应用

--快速查询指定表名的总记录数
SELECT rows FROM sysindexes WHERE id= OBJECT_ID('rpt2014' ) AND indid< 2

--快速统计所有表中的记录总数
SELECT object_name(i.id) TableName,  
       rows as RowCnt
FROM sysindexes i   
INNER JOIN sysObjects o   
    ON (o.id = i.id AND o.xType = 'U ')   
WHERE indid < 2   
ORDER BY TableName 

--利用sys.indexes表也可以查看所有表的记录总数
SELECT o.name,
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY o.NAME