SQL SERVER – How to Drop All the Hypothetical Indexes of Database?

Earlier I wrote a blog post SQL SERVER – Say No To Database Engine Tuning Advisor and I got an email from my existing client where I frequently engage for Comprehensive Database Performance Health Check. They wanted me to help them remove all the negative impact of the Database Engine Tuning Advisor (DTA). I was able to help them achieve this by removing the Hypothetical Indexes they had added using the DTA.

Read More

SQL SERVER – Update Table Statistics in Parallel with FULLSCAN

SQL Server Performance Tuning is a much simpler job if you know exactly where to look for and how to tune the configurations. Here is the real world scenario which I encountered during the recent Comprehensive Database Performance Health Check. During the performance tuning exercise, we realized that for one of the table we needed to update the statistics with the full scan as the table was a very critical table for their business. However, every time whenever we were running the update statistics job, the query was running very slow and almost never finish.

Read More

SQL SERVER – Enabling Older Legacy Cardinality Estimation

Yesterday, I had a very interesting situation, one of our Comprehensive Database Performance Health Check client immediately called me on Skype with his entire server going continuously stopping due to a very toxic poison wait type of Resource Semaphore. Though, we had a good idea how to fix the wait type the real issue was the server was not staying up long enough to put us a patch in the system. It was extremely frustrating for us and finally, we were able to deploy the patch Enabling Older Legacy Cardinality Estimation.

Read More

SQL SERVER – Say No To Database Engine Tuning Advisor

I have been using SQL Server for over 20 years and have been working on the focused area of SQL Server Performance Tuning for over 12 years. In recent time I have been very busy doing Comprehensive Database Performance Health Check, and the client, I enjoy working the most is the one who has used in the past Database Engine Tuning Advisor.

Read More