In one of the recent projects, I found out that despite putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled. Let us learn about how to Enable Automatic Statistic Update on Database.
Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situations like this, please do the following:
1) Enable Auto Creation of Statistics
2) Enable Auto Update of Statistics
3) Update Statistics for whole database
USE AdventureWorks; GO /* Enable Stats and Auto Update */ -- Enable Auto Create of Statistics ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON; -- Enable Auto Update of Statistics ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON; GO -- Update Statistics for whole database EXEC sp_updatestats GO
You may not believe but this feature is one of the most efficient feature for the performance tuning. I strongly suggest that you watch my video on this URL where I have demonstrated by example how this feature can help us improve the performance of the system.
I also cover this featured in depth in my SQL Server Performance Tuning Practical Workshop (3-4 Hours).
Reference: Pinal Dave (https://blog.sqlauthority.com)
