性能优化——统计信息——SQLServer自动更新和自动创建统计信息选项

简介: 原文: 性能优化——统计信息——SQLServer自动更新和自动创建统计信息选项 原文译自:http://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-a...
原文: 性能优化——统计信息——SQLServer自动更新和自动创建统计信息选项

原文译自:http://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012913

 

        统计信息是如何提高SQLServer查询性能的?统计直方图用作在查询执行计划中查询优化器的选择依据。如果一个查询谓词包含统计信息的列,那么查询优化器不需要预测该查询中影响行数,因此,查询优化器有足够的信息去创建执行计划。SQLServer创建执行计划有一下几种不同的方式:

  • 统计信息会在每个新创建的索引中自动创建统计信息。
  • 如果数据库中AUTO_CREATE_STATISTICS被设置为ON,SQLServer将会自动对查询中用到的,且没有索引的列自动创建统计信息。

 

AUTO_CREATE_STATISTICS选项:

当把该选项设为ON时,查询优化器会对在谓词中使用的到列,如果这些列的统计信息不可用,则会单独对每列创建统计信息。这些统计信息对创建一个查询计划非常必要。它们创建于那些现有统计对象中不存在直方图的列上,名字包括列名和对象ID的十六进制格式:_WA_Sys_<column_name>_<XXXX>。这些统计信息用于查询优化器决定使用何种优化后的执行计划。

可以通过以下语句启用自动统计信息创建功能:

ALTER  DATABASE[你的库名]

SET AUTO_CREATE_STATISTICS ON

 

Auto Update Statistics选项:

         统计信息会在查询编译或者执行缓存执行计划前被检查。当在以下情况下,统计信息会被认为过期:

1、  在一个空表中有数据的改动。

2、  当统计信息创建时,表的行数只有500或以下,且后来统计对象中的引导列的更改次数大于500.

3、  当表的统计信息收集时,超过了500行,且统计对象的引导列后来更改次数超过500+表总行数的20%时。

4、  在Tempdb中的表,少于6行且最少有6行被更改。

更多的信息可以查看MSDN

 

可以使用一下语句来开启自动更新统计信息:

ALTER  DATABASE[你的库名]

SET AUTO_UPDATE_STATISTICS ON

 

过时的统计信息会引起大量的性能问题,所以建议开启自动更新。它的默认设置是ON。没有更新统计信息常见的影响是选择了次优的执行计划,然后性能下降。有时候,过期的统计信息可能比没有统计信息更加糟糕。

使用以下语句来开启异步更新统计信息:

ALTER  DATABASE[你的库名]

SET AUTO_UPDATE_STATISTICS_ASYNC ON

如果开启了这个选项,查询优化器将先执行一次查询,然后更新过期的统计信息。当你把这个选项设为OFF时,查询优化器将在编译查询之前更新过期统计信息。这个选项在OLTP环境下很有用,但在数据仓库中有负面影响。

 

如何关闭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

当使用数据库级别的禁用时,表、索引或者统计对象的设置将全部失效。

 

何时创建统计信息?

         其中一个答案是当使用数据库引擎优化顾问(DTA)时建议创建。另外一个情况是当你查看执行计划是,出现丢失统计信息的警告(missing statistics warnings),如下图的黄色三角叹号:

 

可以使用SQLServer Profiler 去监控丢失列统计信息的事件,你也可以考虑当你的查询从子集或者查询谓词中包含关联列的那些列上创建统计信息。

创建统计信息的语句如下:

--Create statistics on all rows

CREATE STATISTICSstatistics_name   ONYourDBName.YourSchema.YourTable(YourColumn1,YourColumn2)  

WITH FULLSCAN

 --Create statistics using a random 10 percent sampling rate

CREATE STATISTICSstatistics_name   ONYourDBName.YourSchema.YourTable(YourColumn1,YourColumn2)   

WITH SAMPLE 10PERCENT

 

何时更新统计信息?

         如果你的查询执行得很慢,那么是时候更新统计信息了。并且建议当你插入大量数据到升序或者降序的列时,更新统计信息,因为在这种情况下,统计信息直方图将不包含新插入的值,同时,强烈建议在除索引维护(当你重建、整理碎片或者重组索引时,数据分布不会改变)外的维护工作之后更新统计信息。

         如果数据库的数据更改频繁,建议最低限度每天更新一次统计信息。一般来说,在数据仓库中,可以降低更新统计信息的频率,当更新时,通常建议执行sp_updatestats存储过程来实现。


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
4月前
|
开发框架 .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
|
11月前
|
开发框架 监控 前端开发
云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)以及操作系统的,这时我们就需要查询他们的一些设置和内容,下面讲的就是如何查询它们的相关信息。
1061 0
|
SQL 索引 数据库
sql server 索引阐述系列八 统计信息
原文:sql server 索引阐述系列八 统计信息 一.概述     sql server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql server就知道了数据的分布情况。
956 0
|
SQL 缓存 数据库
SqlServer性能优化之获取缓存的查询计划中的聚合性能统计信息
SqlServer性能优化之获取缓存的查询计划中的聚合性能统计信息
4277 0
|
索引
SqlServer性能优化之获取表的数据行数,数据大小,索引大小等
SqlServer性能优化之获取表的数据行数,数据大小,索引大小等
11109 0