SQL SERVER – AdHoc Queries and Optimize for Adhoc Workloads

Today, we are going to discuss a very simple topic which I had discussed with my client during the Comprehensive Database Performance Health Check.  The topic is AdHoc Queries and Optimize for Adhoc Workloads.

My client had a very dynamic application that continuously changed the nature of the queries. Sometimes the query structure was changed and most often the data which was passed to the queries was changing. It was extremely common for the application to use any query for a single-use most of the time.

High CPU with AdHoc Queries

They had an issue with the high CPU usage almost all the time. We had done lots of changes to the server and configuration however, still, the CPU usage was high. After few tests, we decided to turn on the setting Optimize for Adhoc Workloads for the server, and the CPU suddenly came down to 60% from running all the way from 100%.

Here is the setting which I am mentioning in SSMS.

Here is another blog post that will help you determine if you are running lots of ad-hoc queries or not.

In summary, if your database is running lots of Adhoc queries it is a good idea to change the settings for Optimize for Adhoc Workloads and test your system performance.

Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)