SQL Server statistics play a crucial role in optimizing query performance and generating efficient execution plans. These statistics provide information about the distribution of column values, helping the query optimizer estimate the number of rows and choose the most cost-effective operators.
Viewing SQL Server Statistics
To view SQL Server statistics for a specific object, you can use either SQL Server Management Studio (SSMS) or T-SQL.
In SSMS, connect to a SQL Server instance, expand the desired database, and navigate to the object of interest. Under the “STATISTICS” tab, you can see all available statistics and their details, including distribution of values and frequency of occurrence.
Alternatively, you can use the T-SQL query below to view statistics properties:
SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee');
Methods to Update SQL Server Statistics
SQL Server provides several methods to update statistics:
Auto Create Statistics
SQL Server automatically creates statistics on individual columns to improve cardinality estimates and execution plans. These auto-created statistics have names starting with “_WA”.
To identify auto-created statistics, you can use the following query:
SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee') AND name LIKE '_WA%';
Auto Create Incremental Statistics
Starting from SQL Server 2014, you can enable the “Auto-Create Incremental Statistics” option. This feature allows you to update only the partition that needs updating, reducing the overhead of scanning the entire table.
Auto Update Statistics
By default, SQL Server automatically updates statistics when they become out-of-date due to data modifications. The update frequency depends on the number of modifications and the size of the table. In SQL Server 2016 and above, the update threshold is dynamically calculated based on the table cardinality.
Auto Update Statistics Asynchronously
Enabling the “Auto Update Statistics Asynchronously” option allows SQL Server to update statistics in parallel with query execution, improving overall performance.
Manually Update Statistics
In addition to automatic updates, you can manually update statistics using the “UPDATE STATISTICS” command or the “sp_updatestats” stored procedure.
Here are some examples of manual updates:
-- Update all statistics in an object
UPDATE STATISTICS HumanResources.Employee;
-- Update specific statistics
UPDATE STATISTICS HumanResources.Employee IX_Employee_OrganizationNode;
-- Update statistics with FULL SCAN
UPDATE STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH FULLSCAN;
-- Update statistics with a sample
UPDATE STATISTICS HumanResources.Employee IX_Employee_OrganizationNode WITH SAMPLE 10 PERCENT;
Updating Statistics with SQL Server Maintenance Plan
You can also configure a SQL Server maintenance plan to update statistics regularly. This allows you to automate the process and ensure statistics are always up-to-date.
Conclusion
In this article, we have explored the importance of SQL Server statistics and various methods to update them. Regularly monitoring and updating statistics is crucial for maintaining optimal query performance and efficient execution plans. By understanding these concepts and utilizing the available methods, you can improve the overall performance of your SQL Server database.