UPDATE STATISTICS (Transact-SQL)

Άρθρο
02/13/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

Updates query optimization statistics on a table or indexed view. 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. Updating statistics via any process may cause query plans to recompile automatically. We recommend not updating statistics too frequently because there's a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

Note

For more information on statistics in Microsoft Fabric, see Statistics in Microsoft Fabric.

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 ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;  
  
<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_constant ]

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name | index_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
            | RESAMPLE   
        }  
    ]  
[;]

-- Syntax for Microsoft Fabric

UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
        }  
    ]  
[;]

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Arguments

table_or_indexed_view_name

Is the name of the table or indexed view that contains the statistics object.

index_or_statistics_name or statistics_name | index_name or statistics_name

Is the name of the index to update statistics on or name of the statistics to update. If index_or_statistics_name or statistics_name isn't specified, the query optimizer updates all statistics for the table or indexed view. This includes statistics created using the CREATE STATISTICS statement, single-column statistics created when AUTO_CREATE_STATISTICS is on, and statistics created for indexes.

For more information about AUTO_CREATE_STATISTICS, see ALTER DATABASE SET Options. To view all indexes for a table or view, you can use sp_helpindex.

FULLSCAN

Compute statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN can't be used with the SAMPLE option.

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. 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 is useful for special cases in which the query plan, based on default sampling, isn't optimal. In most situations, it isn't 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.

Note

In SQL Server 2016 (13.x) when using database compatibility level 130, sampling of data to build statistics is done in parallel to improve the performance of statistics collection. The query optimizer will use parallel sample statistics whenever a table size exceeds a certain threshold. Starting with SQL Server 2017 (14.x), regardless of database compatibility level, the behavior was changed back to using a serial scan in order to avoid potential performance issues with excessive LATCH waits. The rest of the query plan while updating statistics will maintain parallel execution if qualified.

SAMPLE can't be used with the FULLSCAN option. When neither SAMPLE nor FULLSCAN is specified, the query optimizer uses sampled data and computes the sample size by default.

We recommend against specifying 0 PERCENT or 0 ROWS. When 0 PERCENT or ROWS is specified, the statistics object is updated but doesn't contain statistics data.

For most workloads, a full scan isn't 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. While estimates may become more accurate with a full scan than a sampled scan, complex plans may not substantially benefit.

For more information, see Components and concepts of statistics.

RESAMPLE

Update each statistic using its most recent sample rate.

Using RESAMPLE can result in a full-table scan. For example, statistics for indexes use a full-table scan for their sample rate. When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimizer samples the data and computes the sample size by default.

In Warehouse in Microsoft Fabric, RESAMPLE is not supported.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Applies to: SQL Server 2016 (13.x) Service Pack 1 CU4, SQL Server 2017 (14.x) Service Pack 1, or SQL Server 2019 (15.x) and later versions, Azure SQL Database, Azure SQL Managed Instance

When ON, the statistics will retain the set sampling percentage for subsequent updates that don't explicitly specify a sampling percentage. When OFF, statistics sampling percentage will get reset to default sampling in subsequent updates that don't explicitly specify a sampling percentage. The default is OFF.

DBCC SHOW_STATISTICS and sys.dm_db_stats_properties expose the persisted sample percent value for the selected statistic.

If AUTO_UPDATE_STATISTICS is executed, it uses the persisted sampling percentage if available, or use default sampling percentage if not. RESAMPLE behavior isn't affected by this option.

If the table is truncated, all statistics built on the truncated heap or B-tree (HoBT) will revert to using the default sampling percentage.

Note

In SQL Server, when rebuilding an index which previously had statistics updated with PERSIST_SAMPLE_PERCENT, the persisted sample percent is reset back to default. Starting with SQL Server 2016 (13.x) SP2 CU17, SQL Server 2017 (14.x) CU26, and SQL Server 2019 (15.x) CU10, the persisted sample percent is kept even when rebuilding an index.

ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]

Applies to: SQL Server 2014 (12.x) and later

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 is required because partition statistics built with different sample rates can't be merged together.

ALL | COLUMNS | INDEX

Update all existing statistics, statistics created on one or more columns, or statistics created for indexes. If none of the options are specified, the UPDATE STATISTICS statement updates all statistics on the table or indexed view.

NORECOMPUTE

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.

To re-enable the AUTO_UPDATE_STATISTICS option behavior, run UPDATE STATISTICS again without the NORECOMPUTE option or run sp_autostats.

Warning

Using this option can produce suboptimal query plans. We recommend using this option sparingly, and then only by a qualified system administrator.

For more information about the AUTO_STATISTICS_UPDATE option, see ALTER DATABASE SET Options.

INCREMENTAL = { ON | OFF }

Applies to: SQL Server 2014 (12.x) and later

When ON, the statistics are recreated as per partition statistics. When OFF, the statistics tree is dropped and SQL Server re-computes the statistics. The default is OFF.

If per partition statistics aren't supported an error is generated. Incremental stats aren't supported for following statistics types:

Statistics created with indexes that aren't partition-aligned with the base table.
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.
Statistics created with spatial indexes or XML indexes.

MAXDOP = max_degree_of_parallelism

Applies to: SQL Server (Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3).

Overrides the max degree of parallelism configuration option for the duration of the statistic operation. For more information, see Configure the max degree of parallelism Server Configuration Option. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.

max_degree_of_parallelism can be:

1
Suppresses parallel plan generation.

>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 (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.

AUTO_DROP = { ON | OFF }

Applies to: SQL Server 2022 (16.x) and later.

Currently, if statistics are created by a third party tool on a customer database, those statistics objects can block or interfere with schema changes the customer may desire.

(Starting with SQL Server 2022 (16.x))| This feature allows the creation of statistics objects in a mode such that a schema change will not be blocked by the statistics, but instead the statistics will be droppped. In this way, auto drop statistics behave like auto created statistics.

Note

Trying to set or unset the Auto_Drop property on auto created statistics may raise errors - auto created statistics always uses auto drop. Some backups, when restored, may have this property set incorrectly until the next time the statistics object is updated (manually or automatically). However, auto created statistics always behave like auto drop statistics.

Remarks

When to UPDATE STATISTICS

For more information about when to use UPDATE STATISTICS, see When to update statistics.

Limitations

Updating statistics isn't supported on external tables. To update statistics on an external table, drop and re-create the statistics.
The MAXDOP option isn't compatible with STATS_STREAM, ROWCOUNT and PAGECOUNT options.
The MAXDOP option is limited by the Resource Governor workload group MAX_DOP setting, if used.

Update all statistics with sp_updatestats

For information about how to update statistics for all user-defined and internal tables in the database, see the stored procedure 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

To determine when statistics were last updated, use the STATS_DATE function.

PDW / Azure Synapse Analytics

The following syntax isn't supported by Analytics Platform System (PDW) / Azure Synapse Analytics:

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

Requires ALTER permission on the table or view.

Examples

A. Update all statistics on a table

The following example updates all statistics on the SalesOrderDetail table.

USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO

B. Update the statistics for an index

The following example updates the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.

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

C. Update statistics by using 50 percent sampling

The following example creates and then updates the statistics for the Name and ProductNumber columns in the Product table.

USE AdventureWorks2022;
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. Update statistics by using FULLSCAN and NORECOMPUTE

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 AdventureWorks2022;  
GO  
UPDATE STATISTICS Production.Product(Products)  
    WITH FULLSCAN, NORECOMPUTE;  
GO

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Update statistics on a table

The following example updates the CustomerStats1 statistics on the Customer table.

UPDATE STATISTICS Customer (CustomerStats1);

F. Update statistics by using a full scan

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. Update all statistics on a table

The following example updates all statistics on the Customer table.

UPDATE STATISTICS Customer;

H. Using CREATE STATISTICS with AUTO_DROP

To use auto drop statistics, just add the following to the "WITH" clause of statistics create or update.

UPDATE STATISTICS Customer (CustomerStats1) WITH AUTO_DROP = ON

Κοινή χρήση μέσω

UPDATE STATISTICS (Transact-SQL)

Syntax

Arguments

table_or_indexed_view_name

index_or_statistics_name or statistics_name | index_name or statistics_name

FULLSCAN

SAMPLE number { PERCENT | ROWS }

RESAMPLE

PERSIST_SAMPLE_PERCENT = { ON | OFF }

ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]

ALL | COLUMNS | INDEX

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Remarks

When to UPDATE STATISTICS

Limitations

Update all statistics with sp_updatestats

Automatic index and statistics management

Determining the Last Statistics Update

PDW / Azure Synapse Analytics

Permissions

Examples

A. Update all statistics on a table

B. Update the statistics for an index

C. Update statistics by using 50 percent sampling

D. Update statistics by using FULLSCAN and NORECOMPUTE

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Update statistics on a table

F. Update statistics by using a full scan

G. Update all statistics on a table

H. Using CREATE STATISTICS with AUTO_DROP

Σχόλια

Πρόσθετοι πόροι

Κοινή χρήση μέσω

UPDATE STATISTICS (Transact-SQL)

Syntax

Arguments

table_or_indexed_view_name

index_or_statistics_name or statistics_name | index_name or statistics_name

FULLSCAN

SAMPLE number { PERCENT | ROWS }

RESAMPLE

PERSIST_SAMPLE_PERCENT = { ON | OFF }

ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]

ALL | COLUMNS | INDEX

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Remarks

When to UPDATE STATISTICS

Limitations

Update all statistics with sp_updatestats

Automatic index and statistics management

Determining the Last Statistics Update

PDW / Azure Synapse Analytics

Permissions

Examples

A. Update all statistics on a table

B. Update the statistics for an index

C. Update statistics by using 50 percent sampling

D. Update statistics by using FULLSCAN and NORECOMPUTE

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Update statistics on a table

F. Update statistics by using a full scan

G. Update all statistics on a table

H. Using CREATE STATISTICS with AUTO_DROP

Related content

Σχόλια

Πρόσθετοι πόροι