Boosting SQL Server Efficiency: Why You Should Update Statistics Manually

While SQL Server provides automatic statistics management, manually updating statistics allows for greater control over database performance. Regularly monitoring the state of your statistics and updating them as necessary can lead to more efficient query execution, especially in environments with dynamic data and high transaction volumes. By implementing a proactive strategy for managing statistics—whether through scheduled jobs, maintenance plans, or manual updates—you can ensure that your SQL Server instance continues to perform optimally over time.

Shouldn’t SQL Server Update Statistics Automatically?

Yes, SQL Server does automatically update statistics when the number of rows modified exceeds a threshold typically defined as:

• If the total number of rows exceeds 500, the threshold for updating statistics is: 20% of the rows in the table plus 500 additional rows. This means that SQL Server updates the statistics when the number of modified rows exceeds (20% of the total rows) + 500. 
• If the total number of rows is less than 500 rows exist, any change will trigger an update.
  

However, automatic updates may not always be timely or sufficient due to:

1. Threshold Limits
   • Automatic Update Threshold: The thresholds for automatic updates may not align with your specific workload needs. If the data changes frequently but does not hit the threshold, you could end up with stale statistics.
2. Performance Considerations
   • Impact on Query Performance: Automatic updates may occur during query execution, which can lead to performance degradation during peak times. Scheduling manual updates during off-peak hours allows you to avoid impacting users.
3. Query Plan Stability
   • Unpredictable Execution Plans: If statistics are updated too frequently, especially in systems with high variability, it can lead to unpredictable execution plans. Manual updates allow for more control over when statistics are refreshed, leading to greater stability in execution plans.
4. Specific Query Needs
   • Optimising Specific Queries: Certain queries might have unique performance characteristics based on the data distribution. Manually updating statistics can be targeted towards specific tables or queries, allowing for optimisation tailored to your application’s needs.

So Why Do You Have to Update Statistics Manually?

While SQL Server automatically manages statistics to some extent, there are important reasons to perform manual updates:

1. Frequency of Changes
   • High Transaction Environments: In environments with frequent data modifications, such as e-commerce or high-volume transaction systems, relying solely on automatic updates may result in outdated statistics. Manually updating statistics after significant data modifications ensures that the optimiser has the most accurate data distribution information.
2. Large Data Sets
   • Diverse Data Patterns: Large tables can have diverse data patterns. If you frequently query with different criteria, such as ranges or specific values, manual updates help tailor the statistics to these usage patterns.
3. Optimisation
   • Query Performance: The SQL Server query optimiser makes decisions based on statistics. If the statistics are stale or missing, the optimiser may generate inefficient execution plans, leading to poor performance. By keeping statistics up-to-date, you can help ensure that queries run efficiently.
4. Control Over Updates
   • Maintenance Scheduling: You may want to control when statistics are updated to avoid performance overhead during peak hours or to synchronise updates with other maintenance tasks. This can be particularly useful in environments where data changes are predictable and can be scheduled accordingly.   

How to Manually Update Statistics in SQL Server

In SQL Server, you can manually update statistics using the following methods:

1. Using the UPDATE STATISTICS Command

This command allows you to specify which table or index you want to update statistics for. Here’s how it works:

UPDATE STATISTICS table_name;

If you want to update statistics for a specific index:

UPDATE STATISTICS table_name index_name;

You can also specify the sample size to be used in the statistics update:

UPDATE STATISTICS table_name WITH FULLSCAN;

This option ensures that all rows are sampled for the most accurate statistics.

2. Using the sp_updatestats Stored Procedure

This procedure is useful for updating statistics for all user-defined and internal tables in the current database. It’s particularly handy when you want to ensure that all statistics are up-to-date:

EXEC sp_updatestats;

3. Using SQL Server Management Studio (SSMS)

To update statistics using SSMS:

• Right-click on the table in Object Explorer.
• Navigate to Statistics and then select Update Statistics. This graphical interface allows you to specify options such as the sampling method.

4. Using Maintenance Plans

You can include statistics updates in your regular maintenance plans. This approach allows you to automate the process and schedule it for off-peak hours to minimise impact on performance.

That's all for now. Thanks.