SqlServer数据库统计信息的操作

ゞ浴缸里的玫瑰 2022-07-15 09:15 382阅读 0赞

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