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

• How to find the percentage of row modifications since last statistics update
• How to find table and index statistics in sql server

Rate Your Experience

: 89 : 1