When and How to Update Statistics in Azure SQL Database
Accurate statistics are a cornerstone of good query performance in Azure SQL Database. While the platform automatically manages statistics in most scenarios, there are real‑world cases where manual intervention is not only recommended—but essential. This article explains when, why, and how to update statistics in Azure SQL Database, with practical samples and real customer scenarios drawn from production support cases. Microsoft Learn reference (overview): https://learn.microsoft.com/sql/relational-databases/statistics/statistics Why Statistics Matter SQL Server’s query optimizer relies on statistics to estimate row counts, choose join strategies, allocate memory grants, and decide whether to run operations in parallel. When statistics are stale or inaccurate, even well‑indexed queries can suddenly degrade. In Azure SQL Database: AUTO_CREATE_STATISTICS is enabled and managed by the platform AUTO_UPDATE_STATISTICS runs asynchronously Because updates are async, queries may continue running with outdated cardinality estimates until statistics refresh completes. https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide When Manual Statistics Updates Are Required 1. After Large Data Changes (ETL / Batch Operations) Customer scenario A nightly ETL job bulk‑inserts millions of rows into a fact table. The following morning, reports time out and logical reads spike. Why it happens Auto‑update thresholds are based on row‑count changes and may not trigger immediately—especially for append‑only or skewed data. Recommended action UPDATE STATISTICS dbo.FactSales; Target only the critical statistic if known: UPDATE STATISTICS dbo.FactSales (IX_FactSales_CreatedDate); 2. Query Plan Regression Without Schema Changes Customer scenario A stable query suddenly switches from a Nested Loops join to a Hash Join, increasing CPU usage and BUFFERIO waits. Root cause Statistics no longer reflect current data distribution. Recommended action UPDATE STATISTICS dbo.Customer WITH FULLSCAN; Learn more: https://learn.microsoft.com/sql/relational-databases/statistics/update-statistics 3. After Restore Operations (PITR / Geo‑Restore / Database Copy) Customer scenario After a Point‑in‑Time Restore (PITR) on a Hyperscale database, queries run slower despite healthy platform telemetry. Why it happens Statistics are restored as‑is, but workload patterns often change after the restore point. Auto‑update statistics may lag behind. Recommended action EXEC sp_updatestats; Prioritize heavily accessed tables first on large databases. Learn more: https://learn.microsoft.com/azure/azure-sql/database/recovery-using-backups Query Store Comparison: Before vs After Updating Statistics One of the most effective ways to validate the impact of statistics updates is Query Store. Before update (typical signs): Sudden plan change for the same query text Increased logical reads and CPU time Change in join strategy or memory grant After statistics update: Optimizer selects a more efficient plan Logical reads reduced CPU and duration stabilize Example workflow -- Capture runtime stats SELECT * FROM sys.query_store_runtime_stats WHERE query_id = <QueryID>; -- Update statistics UPDATE STATISTICS dbo.Orders; -- Force recompilation EXEC sp_recompile 'dbo.Orders'; Query Store reference: https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store Decision Flow: When Should I Update Statistics? Performance regression observed? | v Query plan changed without schema change? | Yes | v Recent data change / restore / ETL? | Yes | v Update targeted statistics If NO at any step, rely on automatic statistics and continue monitoring. What NOT to Do ❌ Do not run blanket WITH FULLSCAN on all tables FULLSCAN is CPU and IO expensive, especially on large or Hyperscale databases. ❌ Do not schedule frequent database‑wide sp_updatestats jobs This can introduce unnecessary workload and plan churn. ❌ Do not update statistics blindly without investigation Always validate plan regression or stale estimates using Query Store or execution plans. Checking Statistics Freshness SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name, sp.last_updated, sp.rows, sp.rows_sampled FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp ORDER BY sp.last_updated; DMV reference: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-properties-transact-sql Best Practices Summary ✅ Prefer targeted statistics updates ✅ Update stats after bulk data changes or restores ✅ Validate results using Query Store ✅ Avoid unnecessary FULLSCAN operations ✅ Use stats updates as a diagnostic and remediation step, not routine maintenance Conclusion Although Azure SQL Database manages statistics automatically, asynchronous updates and changing workload patterns can result in sub‑optimal query plans. Manually updating statistics after significant data changes, restore operations, or observed plan regressions is a safe and effective best practice to restore optimal query performance.QTip: Maintenance plan for performance issues demo
1-Requirements 2-Considerations 3-Update statistics 4-Rebuild indexes 5-Missing indexes 6-More information Requirements Azure SQL Database SQL Server Management Studio (SSMS) Considerations Is not recommended to execute in business hours In update statistics and rebuild indexes there is not status of the process it means you will not see % of the process you will see an update when process is finished. Is important to consider number of lines to execute according to size of the tables because SQL is going to execute everything at the same time and may impact performance of the database by instance you can select 5 lines for small tables but 1 line in huge tables 1-Update statistics with fullscan (mandatory, apply to all tables) *When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute -- Update ALL Statistics WITH FULLSCAN -- ONLY GENERATES COMMANDS DOES NOT EXECUTE -- This will update all the statistics on all the tables in your database. SET NOCOUNT ON GO DECLARE updatestats CURSOR FOR SELECT table_schema, table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' OPEN updatestats DECLARE @tableSchema NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM updatestats INTO @tableSchema, @tableName WHILE (@@FETCH_STATUS = 0) BEGIN SET @Statement = 'UPDATE STATISTICS ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' WITH FULLSCAN' PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below. -- Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server --EXEC sp_executesql @Statement FETCH NEXT FROM updatestats INTO @tableSchema, @tableName END CLOSE updatestats DEALLOCATE updatestats GO SET NOCOUNT OFF GO -- end of script After execution you will see results Now you can select number of rows and copy in a new query window 2-Rebuild indexes (mandatory, apply to all tables) *When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute -- Rebuild indexes -- ONLY GENERATES COMMANDS DOES NOT EXECUTE -- This will rebuild all the indexes on all the tables in your database. SET NOCOUNT ON GO DECLARE rebuildindexes CURSOR FOR SELECT table_schema, table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' OPEN rebuildindexes DECLARE @tableSchema NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName WHILE (@@FETCH_STATUS = 0) BEGIN SET @Statement = 'ALTER INDEX ALL ON ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' REBUILD' PRINT @Statement -- Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server --EXEC sp_executesql @Statement FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName END CLOSE rebuildindexes DEALLOCATE rebuildindexes GO SET NOCOUNT OFF GO -- end of script After execution you will see results Now you can select number of rows and copy in a new query window 3- Missing indexes (optional review with your development team) SELECT CONVERT (varchar, getdate(), 126) AS runtime, migs.avg_user_impact, CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement, 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.avg_total_user_cost,migs.user_seeks,migs.user_scans, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC Based on improvement or avg_user_impact you may decide indexes to create keeping in mind that needs to be analyzed remember adding index increase work with Insert / update / delete operations Copy create_index text of desired commands and copy in a new window to execute Now you are ready to start troubleshooting good luck! More Information How to maintain Azure SQL Indexes and Statistics https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787 AUTO_CREATE_STATISTICS Option https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15#AutoUpdateStats Statistics https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16 Query execution times are slow https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#query-execution-times-are-slow Statistics options https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16&redirectedfrom=MSDN#statistics-options When to update statistics https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#when-to-update-statistics Index maintenance strategy https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#index-maintenance-strategy Reorganize an index https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#reorganize-an-index Rebuild an index https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#rebuild-an-index SQL Server In depth: What can Cause Index to be Still Fragmented After Rebuild https://learn.microsoft.com/en-us/archive/technet-wiki/28182.sql-server-in-depth-what-can-cause-index-to-be-still-fragmented-after-rebuild
