首页 > 代码库 > 统计维护<第四篇>

统计维护<第四篇>

2024-10-24 18:58:40   208人阅读

　SQL Server允许用户手工地控制单独数据库中的统计维护。SQL Server的4个主要的控制紫铜统计的维护的配置如下：

  • 在无索引的列上新建统计(自动创建统计)；
  • 更新现有统计(自动更新统计)；
  • 用于收集统计的采样度；
  • 现有统计的异步更新(自动异步更新统计)；

　　可以在数据库级别(所有表上的所有索引和统计)或基于单个索引或统计情况下来控制上述配置。自动创建统计设置仅适用于非索引列，因为SQL Server在索引创建时始终为一个索引键创建统计。自动更新统计设置的异步版本适用于索引和WHERE子句中没有索引的列。

一、自动维护

　　默认情况下，SQL Server自动处理统计。自动创建统计和自动更新统计设置默认都是开启的，这两个特性被称为自动统计。自动异步统计默认是设置为关闭的。

　　1、自动创建统计

　　自动创建统计特性将自动在查询WHERE子句中引用的非索引列上创建统计。

　　例如，当以下SELECT语句在Person表的没有索引的Name列上运行时，就创建Name列的统计。

SELECT * FROM Person WHERE Name = ‘张三‘

　　我们看看Profiler的跟踪输出：

　　技术分享

　　当然你的Profiler里的事件选择得选了Auto Stats。

　　另外，创建非聚集索引的时候，会创建一个与该非聚集索引同名的统计。删除非聚集索引时，该同名统计信息也会同时删除。

　　2、自动更新统计

　　如果统计被标记为过时的，自动更新统计特性自动更新索引上以及表在查询中引用时永久性表的列上的现有统计。修改的类型是操作语句，如INSERT、UPDATE、DELETE。修改的阈值取决于表中的行。

　　在SQL Server中，基数是表中的行数。使用内部阈值降低了自动更新统计的频率，使用列修改而不是行修改次数进一步降低频率。

行数 修改次数阈值
0 >插入
<500 >500修改
>500 500+20%基数修改

　　3、自动异步更新统计

　　如果自动异步更新开启，SQL Server中的基本统计行为没有根本的变化。当一组统计被标记为过时而一个查询使用这些统计时，统计更新不会像一般情况那样打断查询。相反，查询使用旧的统计完成执行。一旦查询完成，统计将进行异步更新。这种做法具有吸引力的原因是，当统计被更新时，过程缓存中的查询计划被删除，必须编译所运行的查询。所以，与其使查询等待统计更新和过程重编译，不如让查询完成。下次调用相同查询时，它将具有更新过的统计，只需要重编译它。

 　　尽管这一功能能够使重编译更快速一些，但是由于时间差，也可能会使某些查询使用旧的执行计划。在开启这项功能之前要进行小心地测试。

二、人工维护

 　　以下情况需要干预统计的自动维护：

  • SQL Server版本升级，当从以前的版本升级到SQL Server 2008时，因为SQL Server2008的统计维护已经升级，应该在升级之后马上手工更新整个数据库的统计，而不是等待SQL Server利用自动统计来更新它。
  • 执行一组将不会再次执行的特殊SQL活动时：在这种情况下，必须决定是否愿意花费自动统计维护的开销来获得一次更好的执行计划，而影响其他SQL Server活动的性能。所以，一般情况下，不需要关心这样的一次性动作。
  • 自动统计维护遇到一个问题，此时唯一的解决方案是关闭自动统计维护时：即使在这些情况下，也可以为某些遇到困难的数据库表而不是整个数据库关闭该特性。
  • 在分析查询性能时，你意识到一些查询引用的数据库对象的统计丢失了：这可以从图形化和XML执行计划中评估。
  • 分析统计有效性时，你意识到它们不精确：这可以在从应该较好的索引中创建了低执行的执行计划时确定。

　　1、管理统计设置

　　关于统计的操作到如下地址：http://www.cnblogs.com/kissdodog/p/3413893.html

　　也可以用UPDATE STATISTICS命令的WITH NORECOMPUTE选项，来为所有当前数据库中的一个表上的所有或单独索引禁用这一设置。SP_createstats存储过程也有NORECOMPUTE选项。NORECOMPUTE选项是创建或更新统计时的参数，在创建统计时指定该参数相当于指定该统计不能被更新。

　　除非通过测试确认这会带来性能上的好处，否则避免禁用自动统计特性。如果自动更新统计特性被禁用，应该手工确认并在没有索引的列上创建丢失的统计，然后保持现有统计最新。

　　2、生成统计

　　要手工创建统计，使用以下选项之一：

  • CREATE STATISTICS:可以在表或索引视图的一个或多个列上创建统计。与CREATE INDEX命令不同，CREATE STATISTICS默认使用采样。
  • sp_createstats:使用这个存储过程为当前表的所有用户表上的所有符合条件的列创建单列统计。计算列、ntext、text、geometry、geograohy或image数据类型的列除外，稀疏列以及已经有统计或索引的第一列之外的所有列。
  • UPDATE STATISTICS:可以使用这个选项更新一个表或一个索引视图的单独或所有索引键和非索引列的统计。
  • sp_updatestats:使用这个存储过程更新当前数据库中所有用户表的统计。

　　你可能发现允许自动更新统计不太适合你的系统。安排数据库在空闲时间进行UPDATE STATISTICS是处理这个问题可接受的方法。UPDATE STATISTICS是首选机制，因为它提供更好的灵活性和控制。因为插入数据的类型，有可能因其较为快速而采用的手机统计的采样方法不能收集正确的数据。在这些情况下，可以强制FULLSCAN(全扫描)，这样所有数据被用于更新统计，就像统计第一次创建时那样。这可能是开销非常大的操作，所以最好仔细选择处理的索引和时机。

　　3、统计维护状态

　　可以使用以下方法检查自动统计的当前设置。

  • DATABASEPROPERTYEX
  • sp_autostats

　　(1)、自动创建统计的状态

　　可以运行对sys.database系统表的查询来检查自动创建统计的当前设置，如下所示：

SELECT is_auto_create_stats_on
FROM sys.databases
WHERE [name] = ‘Test‘

　　返回值1表示启用，0表示禁用：

　　技术分享

　　检查统计的自动更新情况：

SELECT is_auto_update_stats_on    --检查自动更新
FROM sys.databases
WHERE [name] = ‘Test‘

 　　也可以使用sp_autostats系统存储过程来检查这个特性的状态，应用表名到这个存储过程将在全局统计设置的Output部分一下，提供当前数据库的自动创建统计配置值。

EXEC sp_autostats ‘Test‘    --查询数据库开启统计的状态

　　返回值ON表示启用，OFF表示禁用。

 　　技术分享

　　(2)、自动更新统计状态

　　可以用于自动创建统计相似方法，检查自动更新统计和自动异步更新统计的当前状态。

　　下面使用DATABASEEPROPERTYEX函数进行这项工作得方法:

SELECT DATABASEPROPERTYEX(‘Test‘,‘IsAutoUpdateStatistics‘)

统计维护<第四篇>


联系
我们