UPDATE STATISTICS (Transact-SQL)UPDATE STATISTICS (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

更新表或索引视图的查询优化统计信息。Updates query optimization statistics on a table or indexed view. 默认情况下,查询优化器已根据需要更新统计信息以改进查询计划;但在某些情况下,可以通过使用 UPDATE STATISTICS 或存储过程 sp_updatestats 来比默认更新更频繁地更新统计信息,提高查询性能。By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

更新统计信息可确保查询使用最新的统计信息进行编译。Updating statistics ensures that queries compile with up-to-date statistics. 不过,更新统计信息会导致查询重新编译。However, updating statistics causes queries to recompile. 我们建议不要太频繁地更新统计信息,因为需要在改进查询计划和重新编译查询所用时间之间权衡性能。We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. 具体的折衷方案取决于你的应用程序。The specific tradeoffs depend on your application. UPDATE STATISTICS 可以使用 tempdb 对行样本进行排序以生成统计信息。UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | SAMPLE number { PERCENT | ROWS }   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | RESAMPLE   
              [ ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]  
            | <update_stats_stream_option> [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ] 
        [ [ , ] MAXDOP = max_degree_of_parallelism ] 
    ] ;  
  
<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name | index_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
            | RESAMPLE   
        }  
    ]  
[;]  

参数Arguments

table_or_indexed_view_name table_or_indexed_view_name
包含统计信息对象的表或索引视图的名称。Is the name of the table or indexed view that contains the statistics object.

index_or_statistics_name index_or_statistics_name
要更新其统计信息的索引的名称,或要更新的统计信息的名称。Is the name of the index to update statistics on or name of the statistics to update. 如果不指定 index_or_statistics_name,则查询优化器将更新表或索引视图的所有统计信息 。If index_or_statistics_name is not specified, the query optimizer updates all statistics for the table or indexed view. 这包括使用 CREATE STATISTICS 语句创建的统计信息、在 AUTO_CREATE_STATISTICS 为 ON 时创建的单列统计信息以及为索引创建的统计信息。This includes statistics created using the CREATE STATISTICS statement, single-column statistics created when AUTO_CREATE_STATISTICS is on, and statistics created for indexes.

有关 AUTO_CREATE_STATISTICS 的详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For more information about AUTO_CREATE_STATISTICS, see ALTER DATABASE SET Options (Transact-SQL). 若要查看某一表或视图的所有索引,可以使用 sp_helpindexTo view all indexes for a table or view, you can use sp_helpindex.

FULLSCANFULLSCAN
通过扫描表或索引视图中的所有行来计算统计信息。Compute statistics by scanning all rows in the table or indexed view. FULLSCAN 和 SAMPLE 100 PERCENT 的结果相同。FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN 不能与 SAMPLE 选项一起使用。FULLSCAN cannot be used with the SAMPLE option.

SAMPLE number { PERCENT | ROWS } SAMPLE number { PERCENT | ROWS }
指定当查询优化器更新统计信息时要为其使用的表或索引视图中近似的百分比或行数。Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. 对于 PERCENT,number 可以介于 0 到 100 之间,对于 ROWS,number 可以介于 0 到总行数之间 。For PERCENT, number can be from 0 through 100 and for ROWS, number can be from 0 to the total number of rows. 查询优化器抽样的实际行百分比或行数可能与指定的行百分比或行数不匹配。The actual percentage or number of rows the query optimizer samples might not match the percentage or number specified. 例如,查询优化器扫描数据页上的所有行。For example, the query optimizer scans all rows on a data page.

对于基于默认抽样的查询计划并非最佳的特殊情况,SAMPLE 非常有用。SAMPLE is useful for special cases in which the query plan, based on default sampling, is not optimal. 在大多数情况下,不必指定 SAMPLE,这是因为在默认情况下,查询优化器根据需要采用抽样,并以统计方式确定大量样本的大小,以便创建高质量的查询计划。In most situations, it is not necessary to specify SAMPLE because the query optimizer uses sampling and determines the statistically significant sample size by default, as required to create high-quality query plans.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,在使用兼容性级别 130 时,并行执行数据采样以生成统计信息,从而提高统计信息收集的性能。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), sampling of data to build statistics is done in parallel, when using compatibility level 130, to improve the performance of statistics collection. 只要表大小超过某个阈值,查询优化器就会使用并行采样统计信息。The query optimizer will use parallel sample statistics, whenever a table size exceeds a certain threshold.

SAMPLE 不能与 FULLSCAN 选项一起使用。SAMPLE cannot be used with the FULLSCAN option. 如果未指定 SAMPLE 和 FULLSCAN,查询优化器则默认使用抽样数据并计算样本大小。When neither SAMPLE nor FULLSCAN is specified, the query optimizer uses sampled data and computes the sample size by default.

我们建议不指定 0 PERCENT 或 0 ROWS。We recommend against specifying 0 PERCENT or 0 ROWS. 如果指定 0 PERCENT 或 0 ROWS,则将更新统计信息对象,但该对象不包含任何统计信息数据。When 0 PERCENT or ROWS is specified, the statistics object is updated but does not contain statistics data.

对于大多数工作负载,不需要完全扫描,默认采样已经足够。For most workloads, a full scan is not required, and default sampling is adequate.
但是,对广泛变化的数据分布敏感的某些工作负载可能需要增加样本大小,或者甚至需要完全扫描。However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan.
有关详细信息,请参阅 CSS SQL 升级服务博客For more information, see the CSS SQL Escalation Services blog.

RESAMPLERESAMPLE
使用最近的采样速率更新每个统计信息。Update each statistic using its most recent sample rate.

使用 RESAMPLE 会导致全表扫描。Using RESAMPLE can result in a full-table scan. 例如,索引的统计信息使用全表扫描来获取其采样速率。For example, statistics for indexes use a full-table scan for their sample rate. 如果未指定采样选项(SAMPLE、FULLSCAN、RESAMPLE),则查询优化器默认将对数据进行抽样并计算样本大小。When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimizer samples the data and computes the sample size by default.

PERSIST_SAMPLE_PERCENT = { ON | OFF }PERSIST_SAMPLE_PERCENT = { ON | OFF }
为 ON 时,统计信息将保留设定的采样百分比,以用于未明确指定采样百分比的后续更新 。When ON, the statistics will retain the set sampling percentage for subsequent updates that do not explicitly specify a sampling percentage. 为 OFF 时,在未明确指定采样百分比的后续更新中,统计信息采样百分比将重置为默认采样 。When OFF, statistics sampling percentage will get reset to default sampling in subsequent updates that do not explicitly specify a sampling percentage. 默认为 OFFThe default is OFF.

备注

如果执行 AUTO_UPDATE_STATISTICS,则在可用情况下使用持久采样百分比,否则使用默认采样百分比。If AUTO_UPDATE_STATISTICS is executed, it uses the persisted sampling percentage if available, or use default sampling percentage if not. 此选项不影响 RESAMPLE 行为。RESAMPLE behavior is not affected by this option.

备注

如果该表被截断,则截断的 HoBT 上生成的所有统计信息将恢复为使用默认采样百分比。If the table is truncated, all statistics built on the truncated HoBT will revert to using the default sampling percentage.

提示

DBCC SHOW_STATISTICSsys.dm_db_stats_properties 公开选定统计信息的持久样本百分比值。DBCC SHOW_STATISTICS and sys.dm_db_stats_properties expose the persisted sample percent value for the selected statistic.

适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x)(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4 开始)及更高版本(从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU1 开始)。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4) and later (starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU1).

ON PARTITIONS ( { <partition_number> | <range> } [, …n] ) ] 强制重新计算涵盖在 ON PARTITIONS 子句中指定的分区的叶级统计信息,然后合并它们以生成全局统计信息。ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ] Forces the leaf-level statistics covering the partitions specified in the ON PARTITIONS clause to be recomputed, and then merged to build the global statistics. 需要 WITH RESAMPLE,因为使用不同抽样率生成的分区统计信息不能合并在一起。WITH RESAMPLE is required because partition statistics built with different sample rates cannot be merged together.

适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later

ALL | COLUMNS | INDEXALL | COLUMNS | INDEX
更新所有现有统计信息、在一列或多列上创建的统计信息或为索引创建的统计信息。Update all existing statistics, statistics created on one or more columns, or statistics created for indexes. 如果未指定上述任何选项,则 UPDATE STATISTICS 语句将更新表或索引视图上的所有统计信息。If none of the options are specified, the UPDATE STATISTICS statement updates all statistics on the table or indexed view.

NORECOMPUTENORECOMPUTE
为指定统计信息禁用自动统计信息更新选项 AUTO_UPDATE_STATISTICS。Disable the automatic statistics update option, AUTO_UPDATE_STATISTICS, for the specified statistics. 如果指定此选项,则查询优化器将完成此统计信息更新并禁用将来的更新。If this option is specified, the query optimizer completes this statistics update and disables future updates.

若要重新启用 AUTO_UPDATE_STATISTICS 选项行为,请不使用 NORECOMPUTE 选项再次运行 UPDATE STATISTICS,或运行 sp_autostats 。To re-enable the AUTO_UPDATE_STATISTICS option behavior, run UPDATE STATISTICS again without the NORECOMPUTE option or run sp_autostats.

警告

使用此选项可能会产生并非最佳的查询计划。Using this option can produce suboptimal query plans. 建议您尽量少用此选项,并且此选项只能由有资格的系统管理员使用。We recommend using this option sparingly, and then only by a qualified system administrator.

有关 AUTO_STATISTICS_UPDATE 选项的详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For more information about the AUTO_STATISTICS_UPDATE option, see ALTER DATABASE SET Options (Transact-SQL).

INCREMENTAL = { ON | OFF }INCREMENTAL = { ON | OFF }
为 ON 时,根据分区统计信息重新创建统计信息 。When ON, the statistics are recreated as per partition statistics. 为 OFF 时,删除统计信息树并且 SQL ServerSQL Server 重新计算统计信息 。When OFF, the statistics tree is dropped and SQL ServerSQL Server re-computes the statistics. 默认为 OFFThe default is OFF.

如果不支持每个分区统计信息,将生成错误。If per partition statistics are not supported an error is generated. 对于以下统计信息类型,不支持增量统计信息:Incremental stats are not supported for following statistics types:

  • 使用未与基表的分区对齐的索引创建的统计信息。Statistics created with indexes that are not partition-aligned with the base table.
  • 对 Always On 可读辅助数据库创建的统计信息。Statistics created on Always On readable secondary databases.
  • 对只读数据库创建的统计信息。Statistics created on read-only databases.
  • 对筛选的索引创建的统计信息。Statistics created on filtered indexes.
  • 对视图创建的统计信息。Statistics created on views.
  • 对内部表创建的统计信息。Statistics created on internal tables.
  • 使用空间索引或 XML 索引创建的统计信息。Statistics created with spatial indexes or XML indexes.

适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism
适用对象SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 开始)。Applies to: SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3).

在统计信息操作期间替代最大并行度配置选项 。Overrides the max degree of parallelism configuration option for the duration of the statistic operation. 有关详细信息,请参阅 配置 max degree of parallelism 服务器配置选项For more information, see Configure the max degree of parallelism Server Configuration Option. 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大数量为 64 个处理器。The maximum is 64 processors.

max_degree_of_parallelism 可以是 :max_degree_of_parallelism can be:

11
取消生成并行计划。Suppresses parallel plan generation.

>1>1
基于当前系统工作负载,将并行统计信息操作中使用的最大处理器数限制为指定数量或更少。Restricts the maximum number of processors used in a parallel statistic operation to the specified number or fewer based on the current system workload.

0(默认值)0 (default)
根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。Uses the actual number of processors or fewer based on the current system workload.

<update_stats_stream_option> 标识为仅供参考。Identified for informational purposes only. 不提供支持。Not supported. 不保证以后的兼容性。Future compatibility is not guaranteed.<update_stats_stream_option> 标识为仅供参考。Identified for informational purposes only. 不提供支持。Not supported. 不保证以后的兼容性。Future compatibility is not guaranteed.

RemarksRemarks

何时使用 UPDATE STATISTICSWhen to Use UPDATE STATISTICS

有关何时使用 UPDATE STATISTICS 的详细信息,请参阅统计信息For more information about when to use UPDATE STATISTICS, see Statistics.

限制和局限Limitations and Restrictions

  • 外部表格中不支持更新统计信息。Updating statistics is not supported on external tables. 若要更新外部表格中的统计信息,请删除并重新创建统计信息。To update statistics on an external table, drop and re-create the statistics.
  • MAXDOP 选项与 STATS_STREAMROWCOUNTPAGECOUNT 选项不兼容。The MAXDOP option is not compatible with STATS_STREAM, ROWCOUNT and PAGECOUNT options.
  • 如果使用的话,MAXDOP 选项会受资源调控器工作负载组 MAX_DOP 设置的限制。The MAXDOP option is limited by the Resource Governor workload group MAX_DOP setting, if used.

使用 sp_updatestats 更新所有统计信息Updating All Statistics with sp_updatestats

有关如何为数据库中的所有用户定义表和内部表更新统计信息的信息,请参阅存储过程 sp_updatestats (Transact-SQL)For information about how to update statistics for all user-defined and internal tables in the database, see the stored procedure sp_updatestats (Transact-SQL). 例如,以下命令调用 sp_updatestats 来更新数据库的所有统计信息。For example, the following command calls sp_updatestats to update all statistics for the database.

EXEC sp_updatestats;  

自动索引和统计信息管理Automatic index and statistics management

利用自适应索引碎片整理等解决方案,自动管理一个或多个数据库的索引碎片整理和统计信息更新。Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. 此过程根据碎片级别以及其他参数,自动选择是重新生成索引还是重新组织索引,并使用线性阈值更新统计信息。This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

确定最近的统计信息更新Determining the Last Statistics Update

若要确定最近一次更新统计信息的时间,请使用 STATS_DATE 函数。To determine when statistics were last updated, use the STATS_DATE function.

PDW/SQL 数据仓库PDW / SQL Data Warehouse

以下语法不受 PDW/SQL 数据仓库支持The following syntax is not supported by PDW / SQL Data Warehouse

update statistics t1 (a,b);   
update statistics t1 (a) with sample 10 rows;  
update statistics t1 (a) with NORECOMPUTE;  
update statistics t1 (a) with INCREMENTAL=ON;  
update statistics t1 (a) with stats_stream = 0x01;  

权限Permissions

要求具有对表或视图的 ALTER 权限。Requires ALTER permission on the table or view.

示例Examples

A.A. 更新表的所有统计信息Update all statistics on a table

以下示例更新 SalesOrderDetail 表上所有索引的统计信息。The following example updates the statistics for all indexes on the SalesOrderDetail table.

USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO  

B.B. 更新索引的统计信息Update the statistics for an index

以下示例更新 AK_SalesOrderDetail_rowguid 表的 SalesOrderDetail 索引的统计信息。The following example updates the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.

USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;  
GO  

C.C. 通过使用 50% 抽样更新统计信息Update statistics by using 50 percent sampling

以下示例将创建并更新 Name 表中的 ProductNumberProduct 列的统计信息。The following example creates and then updates the statistics for the Name and ProductNumber columns in the Product table.

USE AdventureWorks2012;  
GO  
CREATE STATISTICS Products  
    ON Production.Product ([Name], ProductNumber)  
    WITH SAMPLE 50 PERCENT  
-- Time passes. The UPDATE STATISTICS statement is then executed.  
UPDATE STATISTICS Production.Product(Products)   
    WITH SAMPLE 50 PERCENT;  

D.D. 通过使用 FULLSCAN 和 NORECOMPUTE 更新统计信息Update statistics by using FULLSCAN and NORECOMPUTE

以下示例更新 Products 表中的 Product 统计信息,强制对 Product 表中的所有行进行完全扫描,并关闭 Products 统计信息的自动统计信息功能。The following example updates the Products statistics in the Product table, forces a full scan of all rows in the Product table, and turns off automatic statistics for the Products statistics.

USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Production.Product(Products)  
    WITH FULLSCAN, NORECOMPUTE;  
GO  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

E.E. 更新表的统计信息Update statistics on a table

以下示例更新 Customer 表的 CustomerStats1 统计信息。The following example updates the CustomerStats1 statistics on the Customer table.

UPDATE STATISTICS Customer ( CustomerStats1 );  

F.F. 使用完全扫描更新统计信息Update statistics by using a full scan

以下示例基于扫描 Customer 表中的所有行来更新 CustomerStats1 统计信息。The following example updates the CustomerStats1 statistics, based on scanning all of the rows in the Customer table.

UPDATE STATISTICS Customer (CustomerStats1) WITH FULLSCAN;  

G.G. 更新表的所有统计信息Update all statistics on a table

以下示例更新 Customer 表的所有统计信息。The following example updates all statistics on the Customer table.

UPDATE STATISTICS Customer;  

另请参阅See Also

统计信息 Statistics
ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
CREATE STATISTICS (Transact-SQL) CREATE STATISTICS (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL) DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL) DROP STATISTICS (Transact-SQL)
sp_autostats (Transact-SQL) sp_autostats (Transact-SQL)
sp_updatestats (Transact-SQL) sp_updatestats (Transact-SQL)
STATS_DATE (Transact-SQL)STATS_DATE (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL) sys.dm_db_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (Transact-SQL)sys.dm_db_stats_histogram (Transact-SQL)