MSDN Documentation

Statistics Maintenance

Effective statistics maintenance is crucial for query performance in SQL Server. Statistics provide the query optimizer with information about the distribution of data in tables and indexed views, enabling it to create efficient execution plans.

Why is Statistics Maintenance Important?

Over time, data in your tables changes due to inserts, updates, and deletes. Without updated statistics, the query optimizer might make suboptimal decisions, leading to:

• Poorly performing queries.
• Increased CPU usage.
• Increased I/O operations.
• Longer execution times.

Types of Statistics

SQL Server maintains two main types of statistics:

• Column Statistics: Provide information about the distribution of values in a single column.
• Index Statistics: Gathered for indexes and provide information about the distribution of data across the index key columns.

How Statistics are Updated

SQL Server can update statistics automatically or manually:

• Automatic Updates: Enabled by default, SQL Server automatically updates statistics when a sufficient number of rows have changed. The threshold for this is dynamic and depends on the size of the table.
• Manual Updates: You can explicitly update statistics using the UPDATE STATISTICS command or the sp_updatestats stored procedure.

Manual Statistics Update Methods

Using UPDATE STATISTICS

This Transact-SQL statement allows for granular control over statistics updates.

-- Update statistics for a specific table
UPDATE STATISTICS YourTableName;

-- Update statistics for a specific index
UPDATE STATISTICS YourTableName YourIndexName;

-- Update statistics for a specific column
UPDATE STATISTICS YourTableName YourColumnName WITH FULLSCAN;

-- Update all statistics for a table
UPDATE STATISTICS YourTableName WITH FULLSCAN;
                    

Key options for UPDATE STATISTICS:

• WITH FULLSCAN: Collects statistics by scanning all rows in the table or indexed view. This is more accurate but can be slower.
• WITH SAMPLE n PERCENT: Collects statistics by sampling a specified percentage of rows.
• WITH RESAMPLE: Collects statistics by using the last sample rate used when the statistics were created or last updated.
• WITH INCREMENTAL = ON: Updates statistics for only the data that has changed since the last update (for partitioned tables).

Using sp_updatestats

This stored procedure updates all statistics for all user tables in the current database that require updating.

EXEC sp_updatestats;
                    

Note that sp_updatestats uses the default sampling rates and does not support options like FULLSCAN.

Disabling and Enabling Auto-Update Statistics

While automatic updates are generally beneficial, there might be scenarios where you need to disable them. You can do this at the database level or for individual tables/indexes.

Database Level

-- Disable auto-update statistics for the current database
ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS OFF;

-- Enable auto-update statistics for the current database
ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON;
                    

Table/Index Level

-- Disable auto-update statistics for a specific table
ALTER TABLE YourTableName SET (AUTO_UPDATE_STATISTICS = OFF);

-- Enable auto-update statistics for a specific table
ALTER TABLE YourTableName SET (AUTO_UPDATE_STATISTICS = ON);

-- Disable auto-update statistics for a specific index (affects index statistics)
ALTER INDEX YourIndexName ON YourTableName SET (AUTO_UPDATE_STATISTICS = OFF);

-- Enable auto-update statistics for a specific index
ALTER INDEX YourIndexName ON YourTableName SET (AUTO_UPDATE_STATISTICS = ON);
                    

Creating Statistics

If statistics do not exist for a column or index, you may need to create them manually.

-- Create statistics on a column
CREATE STATISTICS Stats_YourColumn
ON YourTableName (YourColumnName);

-- Create statistics on multiple columns
CREATE STATISTICS Stats_MultiColumn
ON YourTableName (Column1, Column2);

-- Create statistics on an index (usually created automatically with the index)
-- CREATE STATISTICS Stats_YourIndex ON YourTableName (IndexKeyColumn1, IndexKeyColumn2);
                    

Monitoring Statistics

You can query system catalog views to check the last updated time and row modification counts for statistics.

SELECT
    s.name AS StatisticsName,
    OBJECT_NAME(s.object_id) AS TableName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.steps,
    sp.modification_counter
FROM
    sys.stats AS s
JOIN
    sys.stats_properties AS sp ON s.stats_id = sp.stats_id AND s.object_id = sp.object_id
WHERE
    OBJECT_NAME(s.object_id) = 'YourTableName'
ORDER BY
    s.name;