【google翻译】使用Ola Hallengren的脚本更新统计数据

使用Ola Hallengren的脚本更新统计数据

作者：艾琳斯特拉托                     发表于：2018年6月22日上午7:49

作为定期维护的一部分，我是更新统计数据的巨大粉丝。事实上，如果您不知道您是否有定期更新统计数据的步骤或工作，请立即检查！当你回来时，这篇文章仍然会在这里

无论如何，长期以来，更新统计数据的默认选项几乎都是大锤。在维护计划选项中，“更新统计信息任务”仅提供更新索引统计信息，列统计信息或两者的选项。您还可以指定它是完整扫描还是更新样本，但这是关于它的：

更新统计任务（维护计划）

我不喜欢这个选项，因为这意味着将更新几乎没有变化的统计数据。我可以有一个1000万行表，只有1000行更改，但该表的统计信息将更新。这是浪费资源。对于小型数据库或不是24×7的系统，这不是什么大问题。但是在拥有多个1000万行表的数据库中，这是一个大问题。

sp_updatestats命令也不是我的最爱。我在这里写过，所以我不会重新哈希。

如果您已经使用Ola Hallengren的脚本进行维护，那么您希望知道它还将使用@UpdateStatistics参数更新统计信息。默认值为NULL，这意味着不更新统计信息。要明确的是，如果您放入Ola的脚本并让它为您创建作业，然后您开始运行“IndexOptimize - USER_DATABASES”作业，默认情况下您不会更新统计信息。IndexOptimize - USER_DATABASES作业的代码默认为：

1 2 3

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'

如果您希望该作业也更新统计信息，您需要：

1 2 3 4

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @UpdateStatistics = 'ALL', @LogToTable = 'Y'

通过这种变化，我们正在更新索引和列统计信息，这很好。但是......无论是否需要，我们都在更新它们。没有修改行的统计信息？更新它。修改了10行的统计数据？更新它。

一直有一个选项可以只更新已更改的统计信息，这是@OnlyModifiedStatistics选项，这使我们的行为就像sp_updatestats一样。

1 2 3 4 五

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y'

使用此选项，如果没有更改行，则不会更新统计信息。如果一行或多行已更改，则统计信息将更新。

自2012年发布SP1以来，这是我对Ola脚本的唯一挑战。在SQL Server 2008R2 SP2和SQL Server 2012 SP1中，他们引入了sys.dm_db_stats_properties DMV，它跟踪每个统计信息的修改。我编写了自定义脚本来使用这些信息来确定是否应该更新统计信息，我在这里已经讨论过了。Jonathan还为一些客户修改了Ola的脚本，以查看sys.dm_db_stats_properties以确定是否有足够的数据更改为更新统计数据，很久以前我们通过电子邮件向Ola询问是否可以包含一个设置阈值的选项。  好消息，这个选项现在存在！

使用Ola的脚本根据更改阈值更新统计信息

使用IndexOptimize存储过程，  Ola现在包含@StatisticsModificationLevel选项。您可以使用它来设置修改阈值，以便仅更新具有特定更改量的统计信息。例如，如果我希望在5％的数据发生更改时更新统计信息，请使用：

1 2 3 4 五

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @UpdateStatistics = 'ALL', @StatisticsModificationLevel= '5', @LogToTable = 'Y'

请注意：此处未包含选项@OnlyModifiedStatistics选项...您不能同时使用这两个选项，它必须是一个或另一个。

这很棒！我可以进一步为不同的表定制。考虑一个具有非常易变的表的数据库，可能是dbo.OrderStatus，其中自动更新可能会或可能不会在白天启动，所以我想确保统计数据每晚更新：

1 2 3 4 五 6

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @Indexes = 'ALL_INDEXES, -SalesDB.dbo.OrderStatus', @UpdateStatistics = 'ALL', @StatisticsModificationLevel= '10', @LogToTable = 'Y'

这将解决SalesDB数据库中除  dbo.OrderStatus 之外的所有表的碎片和更新统计信息，并且如果10％或更多行已更改，它将更新统计信息。

然后，我将有第二份工作来解决OrderStatus的碎片和统计信息：

1 2 3 4 五 6

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @Indexes = 'SalesDB.dbo.OrderStatus', @UpdateStatistics = 'ALL', @StatisticsModificationLevel= '1', @LogToTable = 'Y'

对于dbo.OrderStatus表，只有1％的数据发生更改时，统计信息才会更新。

我喜欢这提供的灵活性！

您可能想知道为什么我选择1％...仔细看看Ola文档中包含的这个重要说明：

当修改的行数达到递减的动态阈值SQRT（行数* 1000）时，统计信息也会更新

这对于理解是至关重要的，因为如果我为@StatisticsModificationLevel设置的阈值最终具有比上述公式更高的行数，则统计信息将比我预期的更快地更新。

例如，如果我在表中有100万行并且我有@StatisticsModificationLevel = 10，则必须更改10％的行或100,000，以便更新统计信息。但是，如果您将100万插入SQRT（1,000,000 * 1000），则会获得31,623，这意味着Ola的脚本将在31,623行更改后更新统计信息...远在100,000之前。

这对于你们中的一些人来说可能很重要，因此我将这些信息放入表中以便更容易理解（至少对我来说更容易！）。

统计信息更新的阈值（百分比和SQRT算法）

使用我的原始示例，如果dbo.OrderStatus有大约一百万行，然后以1％作为阈值，则在更新统计信息之前只需要更改10,000行。如果使用SQRT算法，则在更新统计数据之前需要更改超过30,000行，并且取决于数据偏差，这可能太高。

了解随着表变大，在达到设置的百分比值之前可能会更新统计信息，因为SQRT算法的阈值较低。（是的，我正在把这一点推到家里。）考虑一个有1000万行的表。如果我将阈值设置为5％，我预计统计数据会在500,000次修改后更新，但实际上它们会在100,000次之后更新。

如果您想知道SQRT算法的来源，请查看Microsoft的统计文档。该阈值最初与跟踪标志2371一起引入，以降低自动更新的阈值。它在默认情况下在使用兼容级别130时在SQL Server 2016中启动。我的假设是Ola确定这是一个很好的阈值，可以用作脚本的故障安全/全能，并且我认为这是明智之举他的一部分。一般来说，我宁愿经常更新统计信息，而不是经常更新。但是，使用新的@StatisticsModificationLevel选项可以让我们比以前更好地控制，除非我们编写自定义脚本（这仍然是一个选项......做最适合你的事情！）。