SQL Server幕后英雄－统计信息

SQL Server查询优化器对于执行计划成本的评估是基于数据库统计信息的。所以，数据库统计信息直接影响到数据库查询效率，是数据库系统快速响应，低延迟特性的幕后英雄，但是我们又经常忽视数据库统计信息的存在和维护，怀着为英雄正名和唤醒大家对幕后英雄尊重的目的写作这篇文章。

什么是统计信息

SQL Server查询优化器使用统计信息来评估表或索引视图的一个或多个列中值的分布，这个分布信息提供了用于创建高质量的执行计划的基础（称为基数）。更为通俗一点说，SQL Server的执行计划是基于统计信息来评估的，优化器最终会选择最优的执行计划来为数据库系统提供数据存取功能。这位躲在幕后的英雄便是统计信息。

统计信息的作用

在关系型数据库系统（RDBMS）中，统计信息非常重要，当然MSSQL Server也不例外，它的准确与否直接影响到执行计划的优劣，数据库系统查询效率是否高效。具体表现在以下几个方面：
查询优化器需要借助统计信息来判断是否使用索引。
查询优化器需要根据统计信息来判断是使用嵌套循环连接，合并连接还是哈希连接。
查询优化器根据表统计信息来找出最佳的执行顺序。

统计信息包含的内容

前面章节我们讲的都是比较枯燥的理论知识，这一小节我们来具体揭露幕后英雄的庐山真面目，它包含了哪些内容。
查询SQL Server统计信息非常简单，只需要使用DBCC命令传入表名字和统计信息名称即可，DBCC SHOW_STATISTICS('Table_Name','Statistics_name')。我们以AdventureWorks2008R2数据库下表Sales.SalesOrderDetail中统计信息AK_SalesOrderDetail_rowguid为例：

USE AdventureWorks2008R2
GO
--DBCC SHOW_STATISTICS('Table_Name','Statistics_name')
DBCC SHOW_STATISTICS('Sales.SalesOrderDetail','AK_SalesOrderDetail_rowguid')

揭晓幕后英雄的真实面貌如下：

查看统计信息的设置

关于统计信息设置，有四个重要的选项。
Auto Create Statistics：SQL Server是否自动创建统计信息，默认开启。
Auto Update Statistics：SQL Server是否自动更新统计信息，默认开启。
Auto Update Statistics Asynchronously：SQL Server是否采用异步方式更新统计信息，默认关闭。
Auto Create Incremental Statistics：SQL Server是否自动创建增量统计信息，这个选项是SQL Server 2014以来新增选项，默认关闭。
检验模版数据库Model统计信息设置，新增数据库会以这个数据库为模版。

SELECT
    database_name = name
    ,[IsAutoCreateStatistics?] =
        CASE is_auto_create_stats_on
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END
    ,[IsAutoUpdateStatistics?] =
        CASE is_auto_update_stats_on
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END
    ,[IsAutoUpdateStatsaAyncOn?] =
        CASE is_auto_update_stats_async_on
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END
    ,[IsAutoCreateStatisticsIncremental?] =
        CASE is_auto_create_stats_incremental_on
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END
FROM sys.databases
WHERE   name = 'model'

结果展示如下：

我们以AdventureWorks2008R2为例来观察SQL Server数据库关于统计信息的设置。除了上面使用的检查方法以外，我们还可以使用下面的方法。

SELECT
    [IsAutoCreateStatistics?] =
    CASE
        WHEN DATABASEPROPERTYEX('AdventureWorks2008R2', 'IsAutoCreateStatistics') = 1
            THEN 'Yes'
            ELSE 'No'
    END,
    [IsAutoUpdateStatistics?] =
    CASE
        WHEN DATABASEPROPERTYEX('AdventureWorks2008R2', 'IsAutoUpdateStatistics') = 1
            THEN 'Yes'
            ELSE 'No'
    END,
    [IsAutoUpdateStatsaAyncOn?] =
    CASE
        WHEN DATABASEPROPERTYEX('AdventureWorks2008R2', 'Is_Auto_Update_stats_async_on') = 1
            THEN 'Yes'
            ELSE 'No'
    END
    ,[IsAutoCreateStatisticsIncremental?] =
    CASE
        WHEN DATABASEPROPERTYEX('AdventureWorks2008R2', 'IsAutoCreateStatisticsIncremental') = 1
            THEN 'Yes'
            ELSE 'No'
    END
GO

结果显示与Model数据库保持一致，如下：

当然我们也可以使用SSMS GUI方式查看：Right Click On Database => Properties => Options

统计信息对查询的影响

为了看清楚SQL Server统计信息是如何影响查询的，我们在AdventureWorks2008R2库下创建一个测试表dbo.tb_TestStats，并向测试表中插入10000条数据。

USE AdventureWorks2008R2
GO
IF OBJECT_ID('dbo.tb_TestStats', 'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE dbo.tb_TestStats
    DROP TABLE dbo.tb_TestStats
END
GO

CREATE TABLE dbo.tb_TestStats
(
    RowID INT IDENTITY(1,1) NOT NULL
    ,refID INT NOT NULL
    ,anotherID INT NOT NULL
    ,CONSTRAINT PK_tb_TestStats PRIMARY KEY
    (RowID)
);

USE AdventureWorks2008R2
GO

SET NOCOUNT ON
DECLARE
    @do int = 0
    ,@loop int = 10000
;
WHILE @do < @loop
BEGIN
    IF @do < 100
        INSERT INTO dbo.tb_TestStats(refID,anotherID) VALUES(@do, @do);
    ELSE
        INSERT INTO dbo.tb_TestStats(refID,anotherID) VALUES(200, 200);

    SET @do = @do + 1;
END;

无统计信息的执行计划

为了防止统计信息在执行计划评估阶段自动创建造成对我们测试的影响，手动关闭Auto Create Statistics选项。

USE master
GO

ALTER DATABASE AdventureWorks2008R2
SET AUTO_CREATE_STATISTICS OFF;
GO

接下在SSMS中选择显示实际的实行计划，然后执行下面的查询语句。

USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE anotherID = 100;

从实际的执行计划来看，实际满足条件的记录数没有，即Actual Numbers of Rows为0，而预估满足条件的记录数Estimated Numbers of Rows为1000条，差异巨大，并且存在统计信息缺失的警告。这个巨大的差异足以导致SQL Server优化器对执行计划评估不准确，从而选择了次优的执行计划，最终影响数据库查询效率。

有统计信息的执行计划

无统计信息的执行计划是从反面看统计信息对执行计划的影响，现在我们从正面看有统计信息对执行计划的影响。当我们手动创建统计信息以后，再看看实际的执行计划。

USE AdventureWorks2008R2
GO
CREATE STATISTICS st_anotherID ON dbo.tb_TestStats(anotherID)
GO

再次执行查询，这里需要特别注意，为了防止执行计划缓存对测试结果的影响，在执行查询语句前，我们需要清空执行计划缓存，执行查询语句后，我们将Auto Create Statistics设置恢复为默认值。

DBCC FREEPROCCACHE
GO

USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE anotherID = 100;

USE master
GO
--change back to auto create statistics
ALTER DATABASE AdventureWorks2008R2
SET AUTO_CREATE_STATISTICS ON;
GO

最后查看实际执行计划，统计信息缺失的警告消失了，预估满足条件的行数Estimated Numbers of Rows为1行和实际满足条件的行数Actual Numbers of Rows为0行，已经非常接近了。说明统计信息的存在为优化器提供了正确的数据分布图，给优化器选择最优路径带来了积极的影响，统计信息在此充当了SQL Server优化器的幕后英雄。

创建统计信息

既然统计信息对查询的效率影响如此大，那么我们要如何来创建和维护数据库系统的统计信息呢？这小节会从统计信息的自动创建，手动创建两个大的方面来具体阐述。

创建索引时自动创建

还是以AdventureWorks2008R2库的测试表dbo.tb_TestStats为例，从上面建表的代码来看，测试表创建了一个主键，主键是一个特殊的索引，SQL Server系统会为每一个索引自动创建一个统计信息，检验方法如下：

USE AdventureWorks2008R2
GO

SELECT
    statistics_name = st.name
    ,table_name = OBJECT_NAME(st.object_id)
    ,column_name = COL_NAME(stc.object_id, stc.column_id)
FROM    sys.stats AS st WITH(NOLOCK)
        INNER JOIN sys.stats_columns AS stc WITH(NOLOCK)
            ON st.object_id = stc.object_id
            AND st.stats_id = stc.stats_id
WHERE st.object_id = object_id('dbo.tb_TestStats', 'U')

查询结果如下图所示：

自动创建

在上面的例子中，当我们手动创建索引时，SQL Server会为我们手动创建一个同名的统计信息。其实，当我们执行一个精确查询语句时，查询优化器会判断谓词中使用的到列，统计信息是否可用，如果不可用则会单独对每列创建统计信息。这些统计信息对创建一个高效的执行计划非常必要。

--Query Test
USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE refID = 100;

当执行了精确查询以后，发现多了一个名为_WA_Sys_00000002_1D114BD1的统计信息，这个统计信息就是SQL Server自动为我们创建的，因为我们开启了自动创建统计信息的选项。

手动创建

在很多时候，我们需要使用CREATE STATISTICS语句手动创建统计信息。为了重现这种场景，我们再次手动关闭数据库AdventureWorks2008R2的Auto Create Statistics选项，然后再执行anotherID列上的精确查询。

USE master
GO

ALTER DATABASE AdventureWorks2008R2
SET AUTO_CREATE_STATISTICS OFF;
GO

USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE anotherID = 200;

查询语句的执行计划会有统计信息缺失的警告（missing statistics warnings），如下图所示：

那么这个时候就需要我们在anotherID字段上手动创建统计信息：

USE AdventureWorks2008R2
GO
CREATE STATISTICS st_anotherID ON dbo.tb_TestStats(anotherID)
GO

更新统计信息

SQL Server除了自动更新统计信息以外，当我们发现统计信息过期时，也可以手动更新统计信息。在讲手动更新统计信息之前，首先我们来看看如何发现过期的统计信息。

何时更新统计信息

更新统计信息最需要回答的第一个问题是：我什么时候需要更新我的统计信息呢？以下几种场景，请考虑更新统计信息：
查询执行缓慢，或者查询语句突然执行缓慢，那么是时候更新统计信息了。这种场景很可能是由于统计信息没有及时更新而遭遇了参数嗅探的问题。详情参见文章《SQL Server · 最佳实践 · 参数嗅探问题》中“老鸟的解决方法”章节的“方法四：更新表对象统计信息”

当大量数据更新（INSERT/DELETE/UPDATE）到升序或者降序的列时，更新统计信息。因为在这种情况下，统计信息直方图可能没有及时更新。
强烈建议在除索引维护（当你重建、整理碎片或者重组索引时，数据分布不会改变）外的维护工作之后更新统计信息。
如果数据库的数据更改频繁，建议最低限度每天更新一次统计信息。数据仓库可以适当降低更新统计信息的频率。
当执行计划出现统计信息缺失警告时，需要手动建立统计信息，在“手动创建”章节就属于这种情况。

查找过期的统计信息

过期的统计信息会引起大量的查询性能问题，没有及时更新统计信息常见的影响是优化器选择了次优的执行计划，然后导致性能下降。有时候，过期的统计信息可能比没有统计信息更加糟糕。所以，我们可以使用系统视图sys.stats和系统函数STATS_DATE来获取到统计信息最后更新的时间。假如我们定义超过30天未更新的统计信息算过期的话，那么查找过期的统计信息语句如下：

USE AdventureWorks2008R2
GO
DECLARE
    @day_before int = 30
;

SELECT
    Object_name = OBJECT_NAME(object_id)
    ,Stats_Name = [name]
    ,Stats_Last_Updated = STATS_DATE([object_id], [stats_id])
FROM sys.stats WITH(NOLOCK)
WHERE STATS_DATE([object_id], [stats_id]) <= DATEADD(day, -@day_before, getdate())
;

AdventureWorks2008R2数据库下过期的统计信息截图：

更新统计信息

查找到过期的统计信息以后，接下来需要手动更新统计信息，我们可以从下面三个维度来达到目的：
更新索引级别统计信息
更新单表级别统计信息
更新整个数据库级别统计信息

USE AdventureWorks2008R2
GO
--update statistcis for a specify statistic
UPDATE STATISTICS dbo.tb_TestStats PK_tb_TestStats;
GO 

--update statistcis for a specify table
UPDATE STATISTICS dbo.tb_TestStats WITH FULLSCAN;
GO

--update statistcis for a specify database
USE AdventureWorks2008R2
GO
EXEC sys.sp_updatestats
GO

更新实例级别统计信息

从上面章节我们可以做到更新单索引，表级别和数据库级别统计信息，那么如何快速的更新整个实例级别的所有表统计信息呢？我们可以使用系统存储过程sys.sp_updatestats和微软未公开的存储过程sys.sp_msforeachdb来遍历更新整个实例级别统计信息。

USE master
GO

DECLARE
    @sql NVARCHAR(MAX)
;

SET
    @sql = N'
USE [?]
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'')
BEGIN
    RAISERROR(N''--------------------------------------------------------------
Search on database: ?'', 10, 1) WITH NOWAIT
    EXEC sys.sp_updatestats
END
'
;

EXEC SYS.SP_MSFOREACHDB @sql,@replacechar=N'?'

写在最后

这篇文章从什么是统计信息，统计信息的作用，统计信息对查询的影响，如何设置数据库统计信息更新策略，如何创建统计信息，以及如何更新统计信息等角度，方方面面了解了SQL Server统计信息这个躲在幕后的英雄。

时间： 2024-04-27 03:05:31

SQL Server幕后英雄－统计信息

什么是统计信息

统计信息的作用

统计信息包含的内容

查看统计信息的设置

统计信息对查询的影响

无统计信息的执行计划

有统计信息的执行计划

创建统计信息

创建索引时自动创建

自动创建

手动创建

更新统计信息

何时更新统计信息

查找过期的统计信息

更新统计信息

更新实例级别统计信息

写在最后