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)