mssql 统计信息
查看统计信息
SELECT object_id ,
OBJECT_NAME(object_id) AS TableName ,
name AS StatisticsName ,
auto_created
FROM sys.stats
where objectproperty(object_id,'IsUserTable')=1
ORDER BY object_id DESC;
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
