Muokkaa

Jaa

Update statistics

  • Artikkeli

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

You can update query optimization statistics on a table or indexed view in SQL Server by using SQL Server Management Studio or Transact-SQL. 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'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.

Permissions

If using UPDATE STATISTICS or making changes through SQL Server Management Studio, requires ALTER permission on the table or view. If using sp_updatestats, requires membership in the sysadmin fixed server role, or ownership of the database (dbo).

Use SQL Server Management Studio

Update a statistics object

  1. In Object Explorer, select the plus sign to expand the database in which you want to update the statistic.

  2. Select the plus sign to expand the Tables folder.

  3. Select the plus sign to expand the table in which you want to update the statistic.

  4. Select the plus sign to expand the Statistics folder.

  5. Right-click the statistics object you wish to update and select Properties.

  6. In the Statistics Properties -statistics_name dialog box, select the Update statistics for these columns check box and then select OK.

Use Transact-SQL

Update a specific statistics object

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    USE AdventureWorks2022;
GO
-- The following example updates the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO

Update all statistics in a table

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    USE AdventureWorks2022;
GO
-- The following example updates the statistics for all indexes on the SalesOrderDetail table.
UPDATE STATISTICS Sales.SalesOrderDetail;
GO

For more information, see UPDATE STATISTICS.

Update all statistics in a database

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    USE AdventureWorks2022;
GO
-- The following example updates the statistics for all tables in the database.
EXEC sp_updatestats;

Automatic index and statistics management

Use 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, among other parameters, and update statistics with a linear threshold.

Related content