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.

SQL SERVER - AdHoc Queries and Optimize for Adhoc Workloads adhocqueries-800x459

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.

SQL SERVER - AdHoc Queries and Optimize for Adhoc Workloads optadhoc

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)

Ad Hoc Query, SQL CPU, SQL Scripts, SQL Server, SQL Server Configuration
Previous Post
Azure Free Courses at Pluralsight
Next Post
SQL SERVER – Change Password at the First Login

Related Posts

Leave a Reply