sp_help用法_sp_updatestats概述和用法

sp_help用法

In this article, we will learn usage details of the sp_updatestats built-in store procedure which helps to update all statistics in a SQL Server database. First of all, we will take a glance at the statistics concept in SQL Server.

在本文中，我们将学习sp_updatestats内置存储过程的用法详细信息，该过程有助于更新SQL Server数据库中的所有统计信息。 首先，我们将看一下SQL Server中的统计概念。

了解SQL Server中的统计信息 (Understanding the statistics in SQL Server)

Statistics are database objects which involve the detailed statistical distribution of the column values for the tables or indexed views. The query optimizer uses this information to estimate how many rows will be returned from a query. If the statistics are up-to-date, the query optimizer makes more accurate estimates and as a result, it generates more optimized execution plans. The statistics information can go out-of-date when we perform modification operations in the tables. In this case, the query optimizer does not find out the best-optimized execution plan and may lead to performance issues. Therefore, keeping statistics up-to-date is the recommended performance practice.

统计是数据库对象，涉及表或索引视图的列值的详细统计分布。 查询优化器使用此信息来估计查询将返回多少行。 如果统计信息是最新的，则查询优化器将做出更准确的估计，因此，它会生成更优化的执行计划。 当我们在表中执行修改操作时，统计信息可能会过时。 在这种情况下，查询优化器不会找出最佳优化的执行计划，并可能导致性能问题。 因此，建议使统计信息保持最新。

句法 (Syntax)

sp_updatestats runs UPDATE STATISTICS command for whole user-defined and internal tables in the current database so that the statistics will be up-to-date. The following shows the syntax of this procedure.

sp_updatestats对当前数据库中的整个用户定义表和内部表运行UPDATE STATISTICS命令，以便统计信息是最新的。 下面显示了此过程的语法。

sp_updatestats [ [ @resample = ] ‘resample’]

sp_updatestats [[@resample =]'resample']

With the help of the following query, we can update all tables statistics in the Adventureworks database.

借助以下查询，我们可以更新Adventureworks数据库中的所有表统计信息。

USE AdventureWorks;  
    GO  
    EXEC sp_updatestats;

The output message notices an “update is not necessary” for some statistics. This is because, even if solely one row was modified in the table since the last statistics update, the statistics will be updated by this procedure. Otherwise, the statistics would not be updated. The aforementioned tables in the above image haven’t shown any modification since the last statistics update, for this reason, these tables are excluded from the statistics update operation.

对于某些统计信息，输出消息会指出“ 不需要更新 ”。 这是因为，即使自上次统计信息更新以来表中仅修改了一行，统计信息也将通过此过程进行更新。 否则，将不会更新统计信息。 自上次统计信息更新以来，上图中的上述表未显示任何修改，因此，这些表已从统计信息更新操作中排除。

Now, we will prove this concept. sys.dm_db_stats_properties is a DMV which helps to monitor statistics details of the particular table or indexed view. Through the following query, we will find out the statistics details of the Production.Product table.

现在，我们将证明这个概念。 sys.dm_db_stats_properties是DMV，可帮助监视特定表或索引视图的统计信息。 通过以下查询，我们将找到Production.Product表的统计信息。

SELECT sp.stats_id, 
        name, 
        filter_definition, 
        last_updated, 
        rows, 
        rows_sampled, 
        steps, 
        unfiltered_rows, 
        modification_counter
 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('Production.Product');

WHERE stat.object_id = OBJECT_ID(‘Production.Product’);

在哪里stat.object_id = OBJECT_ID（'Production.Product'）;

Now, we will update only one row in the Production.Product table with the help of the following query and execute the statistics monitoring query again.

现在，在以下查询的帮助下，我们将只更新Production.Product表中的一行，并再次执行统计信息监视查询。

UPDATE Production.Product
        SET 
            Name = 'Black Tire'
      WHERE ProductID = 1
      GO
      SELECT sp.stats_id, 
             name, 
             filter_definition, 
             last_updated, 
             rows, 
             rows_sampled, 
             steps, 
             unfiltered_rows, 
             modification_counter
      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('Production.Product')

As you can see, a value has changed in the modification_counter column, this column is specifying how many modifications occurred since the last statistics update. Now, we will run the sp_updatestats and examine the output of it.

如您所见， modification_counter列中的值已更改，该列指定自上次统计信息更新以来发生了多少次修改。 现在，我们将运行sp_updatestats并检查其输出。

USE AdventureWorks;  
        GO  
        EXEC sp_updatestats;

The above image illustrates that a statistic has been updated whose name is AK_Product_ProductNumber. Obviously, one-row modification is enough to update the statistics. Now, reconsider the statistic properties of the Product.Product table.

上图显示了名称为AK_Product_ProductNumber的统计信息已更新。 显然，单行修改足以更新统计信息。 现在，重新考虑Product.Product表的统计属性。

SELECT sp.stats_id, 
        name, 
        filter_definition, 
        last_updated, 
        rows, 
        rows_sampled, 
        steps, 
        unfiltered_rows, 
        modification_counter
     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('Production.Product');

The last_updated column value has been updated to the last statistics performing date and modification_counter re-updated to 0 due to the update process after the last statistics there has not been any modification performed in the table. In this working mechanism, we should take one thing into account. Suppose that we have a huge table and only one row is modified in this table. Under this circumstance, updating the statistics for only one modification might be redundant and will waste the resources of the SQL Server. For this reason, if you decide to use sp_updatestats, consider this issue:

last_updated列的值 已被更新到最后一个统计执行日期，由于在最后一个统计没有在表中执行任何修改之后的更新过程， modification_counter重新更新为0。 在这种工作机制中，我们应该考虑一件事。 假设我们有一个巨大的表，并且在此表中仅修改了一行。 在这种情况下，仅更新一次修改的统计信息可能是多余的，并且将浪费SQL Server的资源。 因此，如果决定使用sp_updatestats，请考虑以下问题：

参数用法 (Arguments usage)

sp_updatestats [ [ @resample = ] 'resample']

From the above syntax, we can clearly understand that we can use the procedure with the RESAMPLE option. If we use this option, the statistics are updated based on the latest sample rate. The following query shows the usage of the procedure with the RESAMPLE option.

通过上面的语法，我们可以清楚地了解到，可以将过程与RESAMPLE选项一起使用。 如果使用此选项，则统计信息将根据最新的采样率进行更新。 以下查询显示带有RESAMPLE选项的过程用法。

EXEC sp_updatestats @resample = 'resample'

If we don’t use the RESAMPLE option, the statistics will be updated with the default sampling rate. The default sampling rate is determined by the SQL Server automatically according to a total number of the rows in a table. The following query illustrates the default usage.

如果我们不使用RESAMPLE选项，则将使用默认采样率更新统计信息。 默认采样率由SQL Server根据表中的行总数自动确定。 以下查询说明了默认用法。

EXEC sp_updatestats

As a last, if we decide to use the RESAMPLE, we should take into account one issue. If the last statistics have been updated by scanning the entire rows in the table, using the sp_updatestats with RESAMPLE option will update the statistics by scanning all rows in the table. Now, we will learn the difference between default and RESAMPLE usage options. We will populate 10,00,000 data to Sales.SalesReason table in the Adventureworks database and monitor this table properties via statistics monitoring query.

最后，如果我们决定使用RESAMPLE，则应考虑一个问题。 如果最近的统计信息已通过扫描表中的整个行进行了更新，则使用带有RESAMPLE的sp_updatestats选项将通过扫描表中的所有行来更新统计信息。 现在，我们将学习默认用法和RESAMPLE使用选项之间的区别。 我们将向Adventureworks数据库中的Sales.SalesReason表填充10,00,000个数据，并通过统计信息监视查询监视该表属性。

SELECT sp.stats_id, 
        name, 
        filter_definition, 
        last_updated, 
        rows, 
        rows_sampled, 
        steps, 
        unfiltered_rows, 
        modification_counter
     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.salesreason');

PK_SalesReason_SalesReasonID statistics updated in 27.10.2017. On this date, the total row number of the table was 10 and the total number of the sampled rows was 10. Also, 1,000,000 rows have been modified since the latest statistics (27.10.2017) were updated. Now we will update statistics through the sp_updatestats procedure without any option.

PK_SalesReason_SalesReasonID统计信息已于2017年10月27日更新。 在此日期，表的总行数为10，采样行的总数为10。此外，自最新统计信息（27.10.2017）更新以来，已修改1,000,000行。 现在，我们将通过sp_updatestats过程更新统计信息，而没有任何选择。

EXEC sp_updatestats

The output message explains that PK_SalesReason_SalesReasonID named statistic has been updated. We will reanalyze the statistics details of the table.

输出消息说明名为statistic的PK_SalesReason_SalesReasonID已更新。 我们将重新分析该表的统计信息。

As we can see, rows_sampled value has been changed and the sampling rate is determined by SQL Server. Now, we will make an example for the RESAMPLE option so that we can find out the difference between these two options usage. At first, we restore the AdventureWorks database and then repeat the synthetic data creation. In this way, we obtain the same state of the PK_SalesReason_SalesReasonID statistic. The rows and rows_sampled column values are the same. It means that the previous update statistics operation is scanning all rows. Therefore, the RESAMPLE option forces the sp_updatestats procedure to behave the same.

如我们所见， rows_sampled值已更改，采样率由SQL Server确定。 现在，我们将以RESAMPLE选项为例，以便我们找出这两个选项用法之间的区别。 首先，我们还原AdventureWorks数据库，然后重复合成数据的创建。 这样，我们获得了PK_SalesReason_SalesReasonID统计信息的相同状态。 行和rows_sampled列的值相同。 这意味着先前的更新统计信息操作正在扫描所有行。 因此， RESAMPLE选项强制sp_updatestats过程的行为相同。

Run the following query in order to update statistics.

运行以下查询以更新统计信息。

EXEC sp_updatestats @resample = 'resample'

As we can see in the above illustration, the statistics update operation performed the scanning all rows in the table. This result proves our theorem. The lesson we need to learn in this example, if the previous statistics update was scan all table rows, the RESAMPLE will also be scan all table rows.

如上图所示，统计信息更新操作执行了扫描表中的所有行的操作。 这个结果证明了我们的定理。 在此示例中，我们需要学习的课程是，如果先前的统计信息更新是扫描所有表行，则RESAMPLE也将扫描所有表行。

结论 (Conclusion)

In this article, at first we mentioned about the statistics and performance impact, essentially up-to-date statistics provide performance improvements for queries. In the continuing sections of the article, we learned how to update statistics with sp_updatestats and detailed usage.

在本文中，首先我们提到了统计信息和性能影响，实质上，最新的统计信息可提高查询的性能。 在本文的后续部分中，我们学习了如何使用sp_updatestats和详细用法来更新统计信息。

翻译自: https://www.sqlshack.com/sp_updatestats-overview-and-usage/

sp_help用法