第十二章——SQLServer统计信息(1)——创建和更新统计信息

简介: 原文: 第十二章——SQLServer统计信息(1)——创建和更新统计信息 简介:查询的统计信息:目前为止,已经介绍了选择索引、维护索引。
原文: 第十二章——SQLServer统计信息(1)——创建和更新统计信息

简介:

查询的统计信息:

目前为止,已经介绍了选择索引、维护索引。如果有合适的索引并实时更新统计信息,那么优化器会选择有用的索引供查询之用,因为SQLServer优化器是基于开销的优化。当在whereon上的列上的数据需要显示在结果集的时候,如果有实时的统计信息,优化器会选择最好的执行方式,因为优化器会从统计信息中获得这些数据的明细情况。

在创建索引的时候,SQLServer就会在索引列上创建统计信息。简单来说,统计信息就是索引或者列上能够描述数据分布的数据。

 

查询选择性:

公式:列上不重复数据的总数/列上的数据总数

选择性越高,索引性能越好,当上述公式的值为1时,可以用于做为主键或者唯一键。

 

创建和更新统计信息:

统计信息有助于SQLServer优化引擎选择合适的索引及相关操作用于执行SELECT语句。有两个方式创建和更新统计信息:

1、  手动创建和更新统计信息

2、  自动创建和更新统计信息

 

准备工作:

在开始之前,先来看看如何查找数据库的当前统计信息设置:

SELECT  CASE WHEN DATABASEPROPERTYEX('master', 'IsAutoCreateStatistics') = 1
             THEN 'Yes'
             ELSE 'No'
        END AS 'IsAutoCreateStatistics?' ,
        CASE WHEN DATABASEPROPERTYEX('Master', 'IsAutoUpdateStatistics') = 1
             THEN 'Yes'
             ELSE 'No'
        END AS 'IsAutoUpdateStatistics?' ,
        CASE WHEN DATABASEPROPERTYEX('Master', 'Is_Auto_Update_stats_async_on') = 1
             THEN 'Yes'
             ELSE 'No'
        END AS 'IsAutoUpdateStatsaAyncOn?'
GO


 

 

下面的语句用于显示where子句中的数据库或者表的统计信息情况:

 

SELECT  object_id ,

        OBJECT_NAME(object_id) AS TableName ,

        name AS StatisticsName ,

        auto_created

FROM    sys.stats

--where object_id=OBJECT_ID('Sales.SalesOrderHeader')

ORDER BY object_id DESC 

GO


 

 

还可以使用以下方式查看:

sp_helpstats 'Sales.SalesOrderHeader'
 


 

 

步骤:

1、  现在开始来看看创建和更新统计信息的不同方式,在数据库级别,有一个选项,默认为ON,这个选项是:Auto_Create_Statistics

 ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON
 


 

2、  启用同步创建列上统计信息的选项,Auto_Create_Statistics,当执行一个查询一个精确数据量的数据时,优化引擎会在这个列上创建一个柱状图表。由SQLServer创建的统计信息以_WA开头,可以看看这些列表:

 

SELECT  st.name AS StatName ,

        COL_NAME(stc.object_id, stc.column_id) AS ColumnName ,

        OBJECT_NAME(st.object_id) AS TableName

FROM    sys.stats AS st

        INNER JOIN sys.stats_columns AS stc ON st.object_id = stc.object_id

                                               AND st.stats_id = stc.stats_id

WHERE   st.name LIKE '_WA%'


 

 

3、  上面的统计信息不会因为Auto_Create_Statistics选项设为ON而结束。这些是强制你的统计信息更新以保证性能优秀。这个只是定义你的统计信息是否同步更新。默认情况下这个选项是为ON的。但是有时候不一定符合你的要求,此时可以使用手动更新计划:

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON


 

4、  Auto_Update_Statistics选项会在创建索引时、通过Auto_Create_Statistics或者用户使用CREATE STATISTICS命令手动创建统计信息时自动更新统计信息,下面命令使用异步方式更新统计信息:

ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON


 

5、  此时来看看执行上面语句后的数据库统计信息配置情况:

 

 SELECT  is_auto_update_stats_async_on ,

        is_auto_create_stats_on ,

        is_auto_update_stats_on

FROM    sys.databases

WHERE   name = 'AdventureWorks'
 


 

6、  上面的方式均为自动创建和更新统计信息,现在来看看如何手动实现:

--创建统计信息在Sales.SalesOrderHeader表的DueDate列上

CREATE STATISTICS st_DueDate_SalesOrderHeader ON Sales.SalesOrderHeader(DueDate)

GO

--更新Sales.SalesOrderHeader表的全部统计信息

UPDATE STATISTICS Sales.SalesOrderHeader

GO

 

--更新Sales.SalesOrderHeader表的st_DueDate_SalesOrderHeader统计信息

UPDATE STATISTICS Sales.SalesOrderHeader st_DueDate_SalesOrderHeader

GO

 

--更新数据库中所有可用的统计信息

EXEC sys.sp_updatestats

GO

--手动删除统计信息

DROP STATISTICS Sales.SalesOrderHeader.st_DueDate_SalesOrderHeader

GO


 

分析:

当索引创建时,优化器会创建统计信息到索引列所在的表或者视图上,除此之外,如果对Auto_Create_Statistics选项设置了ON,优化器会创建一个单列统计信息,及时它没有出现在查询的所需列上。如果你觉得一些查询性能有问题,检查所有谓词,如果这些列缺失了统计信息,你可以手动增加,有时候,DTA(数据库优化顾问)也会建议你创建统计信息。

一般情况下,在查询编译之前,如果开启了同步更新统计信息,SQLServer如果发现统计信息过时,会引发更新统计信息的操作,然后你的查询就会使用上实时的统计信息。而这个操作会阻塞查询,知道更新结束,但是不会保留这些查询,它会更新统计信息以便下次运行查询的时候可以使用上较新的统计信息。

 

扩充知识:

默认情况下,只有sysadmin/db_owner/对象的创建者这三种角色的成员才有权限创建和更新统计信息。

 

柱状图:

柱状图是一类由SQLServer为了统计信息而生成的表。可以认为是一个显示对应列上统计信息最大和最小值范围的报表。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
5月前
|
开发框架 .NET 数据库
asp.net企业费用报销管理信息系统VS开发sqlserver数据库web结构c#编程Microsoft Visual Studio
asp.net 企业费用报销管理信息系统是一套完善的web设计管理系统,系统具有完整的源代码和数据库,系统主要采用B/S模式开发。开发环境为vs2010,数据库为sqlserver2008,使 用c#语言开发 应用技术:asp.net c#+sqlserver 开发工具:vs2010 +sqlserver
33 0
|
12月前
|
开发框架 监控 前端开发
云LIS平台源码,基于B/S架构的实验室信息系统,技术架构:Asp.NET CORE 3.1 MVC + SQLserver + Redis
支持Westguard,Gubbuss+T(n)等多种质控规则,自动判断是否失控,可自动计算靶值、SD,多个质控品可列于一个图表上;每个质控品每天可多达7次结果,可使用平均值、最后一次结果,最好一次结果画图等;靶值可自动计算,免疫等支持按季度或者自定义日期画图
云LIS平台源码,基于B/S架构的实验室信息系统,技术架构:Asp.NET CORE 3.1 MVC + SQLserver + Redis
|
SQL 运维 Go
sql server 运维时CPU,内存,操作系统等信息查询(用sql语句)
原文:sql server 运维时CPU,内存,操作系统等信息查询(用sql语句) 我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统的,这时我们就需要查询他们的一些设置和内容,下面讲的就是如何查询它们的相关信息。
1062 0
|
SQL 索引 数据库
sql server 索引阐述系列八 统计信息
原文:sql server 索引阐述系列八 统计信息 一.概述     sql server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql server就知道了数据的分布情况。
957 0
|
SQL 缓存 数据库
SqlServer性能优化之获取缓存的查询计划中的聚合性能统计信息
SqlServer性能优化之获取缓存的查询计划中的聚合性能统计信息
4281 0
|
数据库 存储
Sqlserver获取所有数据库名,表信息,字段信息,主键信息,以及表结构等。
原文:Sqlserver获取所有数据库名,表信息,字段信息,主键信息,以及表结构等。 --获取所有数据库名: SELECT name FROM master.
1196 0
|
SQL 数据库 .NET
SqlServer的一个函数DATABASEPROPERTYEX,查询内部信息
http://msdn.microsoft.com/zh-cn/library/ms186823.aspx DATABASEPROPERTYEX (Transact-SQL) SQL Server 2012 ...
897 0
|
SQL Go 存储
SQL点滴13—收集SQLServer线程等待信息
要知道线程等待时间是制约SQL Server效率的重要原因,这一个随笔中将学习怎样收集SQL Server中的线程等待时间,类型等信息,这些信息是进行数据库优化的依据。    sys.dm_os_wait_stats 这是一个系统视图,里面存储线程所遇到的所有的等待信息,具体的列如下表 列名 数据类型 说明 Wait_type Nvarchar(60) 等待类型名称 waiting_tasks_count Bigint 等待类型的等待数。
736 0