SQL Server幕后英雄 - 统计信息-阿里云开发者社区

开发者社区> 风移> 正文

SQL Server幕后英雄 - 统计信息

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

揭晓幕后英雄的真实面貌如下:
01.png

查看统计信息的设置

关于统计信息设置,有四个重要的选项。
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'

结果展示如下:
02.png
我们以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数据库保持一致,如下:
03.png

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

统计信息对查询的影响

为了看清楚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优化器对执行计划评估不准确,从而选择了次优的执行计划,最终影响数据库查询效率。
05.png

有统计信息的执行计划

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

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优化器的幕后英雄。
06.png

创建统计信息

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

创建索引时自动创建

还是以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')

查询结果如下图所示:
07.png

自动创建

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

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

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

手动创建

在很多时候,我们需要使用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),如下图所示:
09.png

那么这个时候就需要我们在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数据库下过期的统计信息截图:
10.png

更新统计信息

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

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统计信息这个躲在幕后的英雄。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
8648 0
查看SQLServer 代理作业的历史信息
原文: 查看SQLServer 代理作业的历史信息 不敢说众所周知,但是大部分人都应该知道SQLServer的代理作业情况都存储在SQLServer5大系统数据库(master/msdb/model/tempdb/resources)中的MSDB中,而由于代理作业的长期运行和种类较多,所以一般可以看到msdb的大小往往比其他库加起来还大。
830 0
SQL Server 2008 R2——使用FULL OUTER JOIN实现多表信息汇总
原文:SQL Server 2008 R2——使用FULL OUTER JOIN实现多表信息汇总 =================================版权声明================================= 版权声明:原创文章 谢绝转载  请通过右侧公告中的“联系邮箱(wlsandwho@foxmail.com)”联系我 勿用于学术性引用。
966 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10474 0
VisualSVN Server仓库迁移到Linux(包含所有版本, 权限,用户信息)
公司开发服务器从Windows换成CentOS,所以要把原服务都转移到Linux下,MySQL、SMB的迁移都很顺利,但是SVN的转移却遇到了些问题,花费了三天时间,走了不少弯路,现在总算解决了SVN迁移问题,记录下: 在Windows上我们是用VisualSVN Server作SVN服务的,Linux上是编译安装的subversion 1.7.7。
1531 0
Sql Server 不常见应用之一:获取表的基本信息、字段列表、存储过程参数列表
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/7847416   【推荐】Sql Server 不常见应用之一 获取表的基本信息、字段列表、存储过程参数列表   ——通过知识共享树立个人品牌。
559 0
RDS SQL Server - 专题分享 - 巧用执行计划缓存之统计信息缺失警告
# 问题引入 SQL Server 数据库查询优化器对执行计划成本的评估是基于统计信息的,换句话说,统计信息的准确与否直接关系着查询语句是否能够高效运行。那么,在SQL Server中,表对象中统计信息的缺失是一个影响查询语句性能的风险点,我们如何能够通过非常自动化的方式来侦查,发现统计信息的缺失呢?这个问题的答案就是我们今天这篇文章要分享的内容 - 使用执行计划缓存来发现统计信息的缺失警告。
1866 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载