Updating Database Statistics in SQL Server
Introduction
Parameters for Updating Database Statistics
Examples of Updating Database Statistics
Benefits of Updating Database Statistics
Conclusion
Introduction
Database statistics play a crucial role in query optimization and overall database performance. Regularly updating these statistics ensures that the SQL Server
query optimizer can generate optimal execution plans. In this blog post, we'll explore the parameters for updating database statistics, explore the
benefits of doing so, and provide examples to illustrate the process effectively.
Parameters for Updating Database Statistics
When updating database statistics in SQL Server, administrators can specify various parameters to customize the process according to their specific requirements.
Some essential parameters include
- FULLSCAN: Forces a full scan of the table or index to update statistics.
- SAMPLE: Specifies the percentage of rows to sample when updating statistics, reducing the impact on system resources.
- RESAMPLE: Enables SQL Server to reevaluate the sampling rate dynamically during statistics updates. Update each statistic using its latest sample rate.
Utilizing RESAMPLE might trigger a full-table scan. For instance, statistics for indexes utilize a full-table scan as their sample rate. In cases where none
of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimizer automatically samples the data and calculates the sample size by
default.
- INCREMENTAL: Updates only the rows that have changed since the last statistics update, reducing processing time.
Examples of Updating Database Statistics
Let's consider a couple of examples to illustrate the process of updating database statistics:
Example 1: Updating Database Statistics with sp_updatestats
You can use
sp_updatestats to update all the statistics in the database. This procedure goes through each object's
statistics and updates them as needed. However, for large databases, this process might take a long time and use a lot of system resources because it
checks every statistic on every object.
-- Update statistics for the entire database using the default method
EXEC sp_updatestats;
You may refer to the
How to find table and index statistics
in sql server article to check statistics on all the tables and indexes.
Example 2: Updating Table Statistics with FULLSCAN
The following query performs a full scan and updates the statistics for specific statistics in the specified object. In the following example, you can
see that the number of
rows and
rows_sampled are the same when using FULLSCAN.
-- Table Statistics
UPDATE STATISTICS TableName WITH FULLSCAN;
--Example
UPDATE STATISTICS AuditLog WITH FULLSCAN;
--T-SQL to Check Table Statistics
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('AuditLog');
Example 3: Updating Index Statistics with FULLSCAN
The following query performs a full scan and updates the index statistics in the specified object.
-- Index Statistics
UPDATE STATISTICS TableName IndexName WITH FULLSCAN;
Example 4: Updating Database Statistics with FULLSCAN
The following query updates the statistics for all tables in the current database by performing a full scan on each table. This ensures that the
statistics are highly accurate, which can improve query performance by helping the SQL Server query optimizer make better decisions. However,
the process can be resource-intensive and time-consuming, especially for large databases.
-- Update statistics for the entire database using FULLSCAN
EXEC sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';
You may refer to the
How to find table and index statistics
in sql server article to check statistics on all the tables and indexes.
Example 5: Updating Database Statistics with SAMPLE SIZE
The following query updates the statistics for all tables in the current database, using a sample size of 50% of the rows for each table.
This approach aims to balance accuracy and performance by sampling half of the table's data, which can improve the query optimizer's decisions
while being less resource-intensive than a full scan.
-- Update Database statistics with a specified sample size
EXEC sp_msforeachtable 'UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT';
Example 6: Updating Database Statistics using SQL Server Maintenance Plan
Open SQL Server Management Studio (SSMS)
Launch SSMS and connect to the SQL Server instance where you want to create the Maintenance Plan.
Navigate to Management
Expand the Management node in the
Object Explorer and
Right-click on the Maintenance Plans node and select
"Maintenance Plan Wizard".
Maintenance Plan Wizard
The Maintenance Plan Wizard will open. Click
Next to begin.
Select Maintenance Plan Tasks
Choose "Maintenance Plan Tasks" from the available options and click
Next.
Select Maintenance Tasks
In the list of available maintenance tasks, select
Update Statistics Task and click
Next.
Specify Task Options
Configure the options for the
Update Statistics Task, including the databases for which you want to update statistics and any
additional settings such as whether to perform a full scan or sample the data. Click
Next when done.
Configure Schedule
Set up the schedule for when the Maintenance Plan should run. You can choose to run it immediately, on a recurring schedule, or according to specific conditions. Click
Next to proceed.
Configure Report Options (Optional)
If you want to generate a report after the Maintenance Plan runs, configure the report options. Otherwise, skip this step by clicking
Next.
Complete the Wizard
Review the summary of the Maintenance Plan settings and click
Finish to create the plan.
Execute Maintenance Plan
Once the Maintenance Plan is created, you can execute it immediately by right-clicking on the plan and selecting
Execute, or you can wait for the scheduled time if you specified one.
Monitor Execution
Monitor the execution of the Maintenance Plan to ensure that the database statistics are updated successfully. You can view the execution history and any generated reports to verify the outcome.
Benefits of Updating Database Statistics
Regularly updating database statistics offers several benefits, including:
- Improved Query Performance: Up-to-date statistics enable the query optimizer to generate accurate and efficient execution plans, leading to faster query processing.
- Optimal Resource Utilization: By providing accurate cardinality estimates, updated statistics help SQL Server allocate resources more effectively, preventing resource contention.
- Enhanced Stability: Updated statistics reduce the likelihood of query performance regressions or unexpected plan changes, ensuring database stability and reliability.
- Better Index Maintenance: Updated statistics facilitate better index maintenance decisions, such as index rebuilds or reorganizations, leading to improved index performance.
- Support for Real-Time Analytics: Timely statistics updates are crucial for databases supporting real-time analytics or rapidly changing data environments, ensuring accurate query results.
Conclusion
Updating database statistics is essential for maintaining optimal performance, stability, and reliability in SQL Server environments. By understanding the
parameters, benefits, and examples of updating statistics provided in this blog post, administrators can effectively manage and optimize their databases
to ensure peak performance and responsiveness. Regularly updating database statistics should be an integral part of any database maintenance strategy to ensure
efficient query processing and consistent performance.
Related content