使用数据库维护计划SQL Server更新统计信息

This article explores SQL Server Update Statistics using the database maintenance plan.

本文探索使用数据库维护计划SQL Server更新统计信息。

介绍 (Introduction)

SQL Server query optimizer uses statistics to build an optimized execution plan. These statistics contain the histogram that has information about data distribution, number of rows, data density. SQL Server automatically creates and updates the statistics based on a predefined threshold. The out of date statistics results in bad execution plans impacting query performance and higher resource utilization. In the article, SQL Server Statistics and how to perform Update Statistics in SQL, we explored the following topics.

SQL Server查询优化器使用统计信息来构建优化的执行计划。 这些统计信息包含具有有关数据分布，行数和数据密度的信息的直方图。 SQL Server根据预定义的阈值自动创建和更新统计信息。 过时的统计信息会导致执行计划不佳，从而影响查询性能和更高的资源利用率。 在“ SQL Server统计信息以及如何在SQL中执行更新统计信息”一文中 ，我们探讨了以下主题。

• Auto Create Statistics
  自动创建统计
• Auto Create Incremental Statistics
  自动创建增量统计
• Auto-update statistics
  自动更新统计
• Auto Update Statistics Asynchronously
  异步自动更新统计信息
• Manually Update Statistics
  手动更新统计信息

In this article, we will explore SQL Server Maintenance plan options in detail for SQL Server Update Statistics.

在本文中，我们将详细探讨SQL Server维护计划选项，以了解SQL Server更新统计信息。

SQL Server中的统计信息概述 (Overview of Statistics in SQL Server)

Let’s have a quick overview of viewing statistics. Expand a table, and you see different folders for Indexes and statistics. In the following screenshot, we see two types of statistics.

让我们快速查看统计信息。 展开一个表，您会看到索引和统计信息的不同文件夹。 在以下屏幕截图中，我们看到两种类型的统计信息。

• Index statistics: For each index, we have a corresponding statistic. Its name is also similar to the index name 索引统计信息：对于每个索引，我们都有一个相应的统计信息。 其名称也类似于索引名称
• Auto Created statistics: SQL Server automatically creates the statistics based on the columns in query predicate such as where clause. These statistics always starts with _WA. It is a two-letter code for Washington in the US 自动创建统计信息： SQL Server根据查询谓词中的列（例如where子句）自动创建统计信息。 这些统计信息始终以_WA开头。 这是美国华盛顿的两个字母的代码

You can also retrieve this information from the dynamic management view sys.dm_db_stats_properties and system catalog view sys.stats.

您还可以从动态管理视图sys.dm_db_stats_properties和系统目录视图sys.stats检索此信息。

SELECT
    name,
    last_updated, 
    rows, 
    rows_sampled,
    CASE AUTO_CREATED
        WHEN 0
        THEN 'Index Statistics'
        WHEN 1
        THEN 'Auto Created Statistics'
    END AS 'Auto Created Statistics',
    USER_CREATED AS 'User Created Statistics'
FROM sys.stats AS stat
  CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('[Sales].[Customer]')
ORDER BY AUTO_CREATED DESC;

In the query output, we can clearly differentiate index and auto created statistics.

在查询输出中，我们可以清楚地区分索引和自动创建的统计信息。

We can further join the above query with sys.stats_columns and sys.columns to know the column for which stats is created.

我们可以进一步将上述查询与sys.stats_columns和sys.columns结合起来，以了解为其创建统计信息的列。

SELECT
    stat.name,
    last_updated, 
    rows, 
    rows_sampled,
    CASE AUTO_CREATED
        WHEN 0
        THEN 'Index Statistics'
        WHEN 1
        THEN 'Auto Created Statistics'
    END AS 'Auto Created Statistics',
    --    AUTO_CREATED AS 'Auto Created Statistisc', 
    USER_CREATED AS 'User Created Statistics',
    c.name ColumnName
FROM sys.stats AS stat
  CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
  INNER JOIN sys.stats_columns sc ON stat.object_id = sc.object_id
                                     AND stat.stats_id = sc.stats_id
  INNER JOIN sys.columns c ON sc.object_id = c.object_id
                              AND sc.column_id = c.column_id
WHERE stat.object_id = OBJECT_ID('[Sales].[Customer]')
ORDER BY AUTO_CREATED DESC;

You can also use the sp_helpstats system procedure to check the statistics column. It is an old command and shows statistics created by Index or user.

您还可以使用sp_helpstats系统过程来检查统计信息列。 这是一个旧命令，显示由索引或用户创建的统计信息。

EXEC sp_helpstats
'[Sales].[Customer]';

创建一个新的用户统计 (Create a new user statistic)

Let’s create a user statistic on Customer table on [AccountNumber ]and [ModifiedTable] columns. To create, right-click on Statistics and New Statistics.

让我们在[AccountNumber]和[ModifiedTable]列上的Customer表上创建用户统计信息。 要创建，请右键单击“ 统计信息”和“ 新统计信息”。

Give a name to the user statistic and add the statistics columns using the Add button. Here, we added both [AccountNumber ]and [ModifiedTable] columns.

为用户统计信息命名，然后使用“添加”按钮添加统计信息列。 在这里，我们同时添加了[AccountNumber]和[ModifiedTable]列。

In the case of multiple columns, we can also change the column order using the “Move Up” and “Move Down” buttons. Click Ok, and it creates the user statistics for you.

如果是多列，我们还可以使用“上移”和“下移”按钮更改列顺序。 单击确定，它将为您创建用户统计信息。

Let’s execute the previous query and check the statistics. Now we can see user statistics as well. Here, you see total rows and rows sampled counts are the same that shows a full scan is performed for these statistics.

让我们执行上一个查询并检查统计信息。 现在，我们还可以查看用户统计信息。 在这里，您会看到总行数和行采样数相同，表明对这些统计信息执行了完整扫描。

Apart from the auto-update statistics, we should also update them regularly to avoid any performance issues due to out of date statistics. We can use either SQL Scripts to Management plan to update them regularly based on your requirements.

除了自动更新统计信息之外，我们还应定期更新它们，以避免由于过时的统计信息而导致任何性能问题。 我们可以使用两种SQL脚本进行管理计划，根据您的要求定期更新它们。

为SQL Server更新统计信息创建维护计划 (Create a maintenance plan for SQL Server Update Statistics)

Let’s explore how to create a maintenance plan for SQL Server update statistics along with various configurations.

让我们探讨如何为SQL Server更新统计信息以及各种配置创建维护计划。

Connect to SQL instance in SSMS, expand Management and right-click on Maintenance Plans.

连接到SSMS中SQL实例，展开“ 管理”，然后右键单击“ 维护计划” 。

Launch Maintenance Plan Wizard. In the launch page, it gives a brief introduction of routine database administration tasks available with a maintenance plan. If you want to skip this introductory page, we can put a check on – Do not show this starting page again.

启动维护计划向导。 在启动页面中，它简要介绍了维护计划中可用的常规数据库管理任务。 如果您想跳过此介绍性页面，我们可以选中– 不要再次显示该起始页面。

Click Next and select maintenance plan properties. On this page, provide the following information.

单击下一步，然后选择维护计划属性。 在此页面上，提供以下信息。

• Name: Give a name for this maintenance plan. You should give a proper name to identify it quickly in case you have multiple maintenance plans 名称：为此维护计划命名。 如果您有多个维护计划，则应提供一个适当的名称以快速识别它
• Description: It is an optional field. You can add a brief description of the maintenance plan for reference to other DBA’s 说明：这是一个可选字段。 您可以添加维护计划的简短描述，以供其他DBA参考。

• Run as: It is the service account under the context of which SQL Server runs the maintenance plan. By default, it is the SQL Server Agent Service account. You can also create a proxy account for executing an agent job. For this article, let’s stick with the default agent service account

  运行方式：这是SQL Server运行维护计划的上下文中的服务帐户。 默认情况下，它是SQL Server代理服务帐户。 您也可以创建用于执行代理作业的代理帐户。 对于本文，我们继续使用默认的代理服务帐户

  

• Schedule: We might combine multiple tasks in a single maintenance plan. In this case, we might not want a single schedule for all maintenance. For example, we do not want the full database backup job to start at the same time as the index maintenance task. In this case, you can select separate schedules for each task

  时间表：我们可能将多个任务组合在一个维护计划中。 在这种情况下，我们可能不希望为所有维护安排一个时间表。 例如，我们不希望完整的数据库备份作业与索引维护任务同时开始。 在这种情况下，您可以为每个任务选择单独的时间表

  In this article, we focus on SQL Server Update Statistics task so we can go with option – Single schedule for the entire plan

  在本文中，我们专注于SQL Server Update Statistics任务，因此我们可以选择– 整个计划的单个计划

  Currently, we see Schedule as Not Scheduled (On-Demand). Click on Change and select a job schedule as per your requirement

  当前，我们将“计划”视为“ 未计划（按需）”。 单击更改，然后根据您的要求选择工作计划

  

  You can view the summary of the schedule as shown below

  您可以查看计划摘要，如下所示

  

  In the next step, select the maintenance task. Once you select this, you get a brief description that allows the query optimizer to make better judgements about data access strategies

  在下一步中，选择维护任务。 选择此选项后，您将获得简短描述，以使查询优化器可以更好地判断数据访问策略。

  

  In the next step, we can select the maintenance task order. We have only one task in this maintenance plan so Move Up and Move Down options are disabled

  在下一步中，我们可以选择维护任务顺序。 在此维护计划中，我们只有一项任务，因此上移和下移选项被禁用

  

  Click Next and define Update Statistics task options

  单击下一步，然后定义更新统计信息任务选项

  

• Databases: Select the databases for which you want SQL Server Update Statistics

  数据库：选择要为其提供SQL Server更新统计信息的数据库

  

  You can select from the following options:

  您可以从以下选项中选择：

  • All databases
    所有数据库
  • System databases
    系统数据库
  • All user databases(excluding system databases)
    所有用户数据库（系统数据库除外）
  • Specific databases
    特定数据库
  
  
  

  If you select specific databases, it provides you options to further drill down and specify tables to update stats. We specified all databases, so it does not enable the object selection window

  如果选择特定的数据库，它将为您提供进一步细化和指定表以更新统计信息的选项。 我们指定了所有数据库，因此它不会启用对象选择窗口

• Update: Here, we can select update stats operations from the following values

  更新：在这里，我们可以从以下值中选择更新统计信息操作

  • All existing statistics
    所有现有统计
  • Column statistics only
    仅列统计
  • Index statistics only
    仅索引统计
  
  
  

  Usually, we update all existing statistics using the database maintenance task

  通常，我们使用数据库维护任务来更新所有现有统计信息

• Scan type: It is a vital configuration option. Here, we select either a full scan or sample percentage for SQL Server Update Statistics. We should perform full scan update regularly so that the query optimizer has accurate information about data distribution and prepare an optimized execution plan. It is a similar option of adding a FULL SCAN clause in the UPDATE STATISTICS command 扫描类型：这是至关重要的配置选项。 在这里，我们为SQL Server更新统计信息选择完全扫描或样本百分比。 我们应该定期执行完整扫描更新，以便查询优化器获得有关数据分布的准确信息并准备优化的执行计划。 这是在UPDATE STATISTICS命令中添加FULL SCAN子句的类似选项。

Here is my configuration for this article.

这是本文的配置。

On the next page, select the maintenance plan report delivery option. You should select at least one option to analyze maintenance plan logs, especially in case of a failure. If you select an email report, it shows the database email operator configured in the SQL instance. If you do not have any mail profile, you should configure it first using a reference article How to configure Database Mail in SQL Server.

在下一页上，选择维护计划报告传送选项。 您应该至少选择一个选项来分析维护计划日志，尤其是在发生故障的情况下。 如果选择电子邮件报告，它将显示在SQL实例中配置的数据库电子邮件操作员。 如果您没有任何邮件配置文件，则应首先使用参考文章如何在SQL Server中配置数据库邮件对其进行配置 。

• Write a report to a text file
  将报告写入文本文件
• Email report
  电邮报告

Click Next and review the maintenance plan configuration we completed so far. You can go back and change the configuration if required.

单击下一步，查看到目前为止我们完成的维护计划配置。 您可以返回并根据需要更改配置。

Click on Finish, and it shows the progress, status of each task.

单击完成，它会显示进度，每个任务的状态。

You can see the configured maintenance plan and SQL Server Agent job in the following screenshot.

您可以在以下屏幕截图中看到已配置的维护计划和SQL Server代理作业。

We can either execute the SQL Server agent job or execute the maintenance plan directly.

我们可以执行SQL Server代理作业，也可以直接执行维护计划。

It starts the SQL Server Update Statistics and shows success only completed.

它启动SQL Server更新统计信息并显示仅成功完成。

Let’s go to the maintenance plan log folder specified during configuration. Here, you see a text file as shown below.

让我们转到在配置期间指定的维护计划日志文件夹。 在这里，您将看到一个文本文件，如下所示。

Open this log file, and you see individual queries for performing SQL Server update statistics on the individual tables. In case the maintenance plan fails, you can come to this log file and view what went wrong and fix the issue before the job reruns.

打开此日志文件，您会看到用于在各个表上执行SQL Server更新统计信息的各个查询。 万一维护计划失败，您可以访问此日志文件并查看出现了什么问题并在重新运行作业之前解决该问题。

结论 (Conclusion)

In this article, we explored the process to perform SQL Server Update statistics using a database maintenance plan. You should create this maintenance plan as per your requirement and keep statistics updated and avoid any performance issues due to out of date statistics.

在本文中，我们探讨了使用数据库维护计划执行SQL Server Update统计信息的过程。 您应该根据需要创建此维护计划，并保持统计信息更新，并避免由于统计信息过时而导致任何性能问题。

翻译自: https://www.sqlshack.com/sql-server-update-statistics-using-database-maintenance-plans/