Update Stats on Table SQL Server

by

Statistics are the single most critical component of query performance. They are the “map” that the SQL Server Query Optimizer uses to navigate your data. If the map is wrong, the engine gets lost, and your application performance tanks. In this guide, I will walk you through exactly how to update statistics on a table in SQL Server.

Update Stats on Table SQL Server

What Are SQL Server Statistics?

Before we run the commands, we need to understand what we are updating. We will move beyond the basic commands and dive into the strategies.

Statistics are binary large objects (BLOBs) that contain a histogram describing the distribution of values in one or more columns. When you submit a query, the optimizer checks these histograms to estimate how many rows will be returned (“Cardinality Estimation”).

  • Accurate Stats: The optimizer picks the right index and the right join type (e.g., Hash Match vs. Nested Loop).
  • Stale Stats: The optimizer guesses wrong, potentially causing massive memory grants or tempdb spills.

How to Check When Statistics Were Last Updated

Before you blindly run an update, you should verify if it is necessary. I use this query daily to audit tables in my environments. It tells you exactly how “fresh” your data map is.

SQL

SELECT 
    OBJECT_NAME(stat.object_id) AS TableName,
    stat.name AS StatisticsName,
    sp.last_updated AS LastUpdated,
    sp.rows AS TotalRows,
    sp.rows_sampled AS RowsSampled,
    sp.unfiltered_rows AS UnfilteredRows,
    sp.modification_counter AS RowsChanged
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE OBJECT_NAME(stat.object_id) = 'SalesOrderHeader'; -- Replace with your table

Key Columns to Watch

  • LastUpdated: If this date is from 2023 and you are reading this in 2025, you have a problem.
  • RowsChanged: This counts the number of modifications (INSERT/UPDATE/DELETE) since the last update.
  • RowsSampled: If you have 10 million rows but only 1% were sampled, your histogram might miss outliers.

Method 1: The UPDATE STATISTICS Command (Recommended)

It allows you to target a specific table or index with granular control. This is the method I recommend for 95% of production scenarios.

Basic Syntax

To update all statistics on a specific table using the default sampling rate:

SQL

UPDATE STATISTICS Sales;

After executing the query above, I received the expected output, as shown in the screenshot below.

Update Stats on Table SQL Server

Using FULLSCAN for Maximum Accuracy

If you have a table that is critical for reporting (e.g., a nightly batch process for a CEO dashboard), default sampling might not be enough. You can force SQL Server to read every single row to build the histogram.

Warning: This is resource-intensive. Do not run this on a 1TB table during business hours.

SQL

UPDATE STATISTICS Sales
WITH FULLSCAN;

After executing the query above, I received the expected output, as shown in the screenshot below.

update statistics table in sql server

Using SAMPLE for Large Tables

For massive tables where a full scan takes too long, you can define a specific percentage.

SQL

UPDATE STATISTICS Sales.LogisticsTracking 
WITH SAMPLE 20 PERCENT;

After executing the query above, I received the expected output, as shown in the screenshot below.

sql server update statistics on table

Method 2: sp_updatestats

You will often see tutorials recommending sp_updatestats. This stored procedure runs against the entire database.

My Professional Advice: Avoid using this in production unless you are doing a full weekend maintenance window.

  • Pros: It checks the rowmodctr (row modification counter) and only updates stats that have changed.
  • Cons: It uses the default sampling rate, which is often too low for large tables. It can also trigger massive recompilation storms, where every query plan in your cache is invalidated at once, causing a sudden CPU spike.

SQL

-- Updates statistics for all tables in the current database
EXEC sp_updatestats;

After executing the query above, I received the expected output, as shown in the screenshot below.

update statistics sql server all tables

Advanced Options: NORECOMPUTE and RESAMPL

To truly master statistics, you need to understand the optional flags.

NORECOMPUTE

By default, SQL Server has an “Auto Update Statistics” feature. When a certain threshold of data changes (usually 20% + 500 rows), the server automatically triggers an update.

Sometimes, you don’t want this. For example, if you just ran a manual FULLSCAN on a Saturday, you don’t want SQL Server to overwrite your perfect stats with a quick, low-quality sample on Monday morning just because you hit the threshold.

SQL

UPDATE STATISTICS Sales.SalesOrderHeader 
WITH FULLSCAN, NORECOMPUTE;

Note: This disables future auto-updates for this specific statistic. Use with caution.

After executing the query above, I received the expected output, as shown in the screenshot below.

update statistics on all tables sql server

RESAMPLE

This option tells SQL Server to update the statistic using the last known sampling rate. If you previously updated with FULLSCAN (100%), RESAMPLE will use 100% again. If you used 10%, it will use 10%.

SQL

UPDATE STATISTICS Sales.SalesOrderHeader 
WITH RESAMPLE;

After executing the query above, I received the expected output, as shown in the screenshot below.

update statistics in sql server with full scan

Comparison of Update Strategies

FeatureUPDATE STATISTICSsp_updatestatsAuto-Update (System)
ScopeSingle Table or IndexEntire DatabaseSingle Statistic (Triggered by query)
ControlHigh (Fullscan, Sample %)Low (Default only)None (System determined)
Performance ImpactVariable (You control it)High (Database-wide)Minimal (Background process)
Best Use CaseCritical tables, Nightly jobsDev/Test environmentsGeneral maintenance

When Should You Update Statistics

You do not need to update statistics every five minutes. Over-updating is a common mistake that wastes CPU and I/O. Here is my rule of thumb for US enterprise clients:

  • After Bulk Loads: If you just imported 500,000 rows into a table that had 100,000 rows, update stats immediately.
  • After Index Reorganization: Rebuilding an index (REBUILD) updates stats automatically with FULLSCAN. Reorganizing (REORGANIZE) does not. If you only reorg, you must manually update stats.
  • When Troubleshooting Performance: If a query suddenly goes slow on a Tuesday afternoon, checking/updating stats is step one in triage.
  • Weekly Maintenance: For most OLTP tables, a weekly update job (usually on Sunday at 2 AM EST) is sufficient.

Conclusion

Updating statistics is not just a maintenance task; it is a performance tuning strategy. By controlling when and how your statistics are calculated, you ensure that the SQL Server optimizer always has the best possible map of your data.

Stop relying on auto-updates for your critical tables. Script out targeted UPDATE STATISTICS commands for your high-volume tables, and watch your query execution times drop.

You may also like the following articles: