How to optimize SQL Server for Ad-hoc workloads

I'm not going to get into the debate of whether Stored procedures are better than ad-hoc queries.  There have been plenty of well written articles supporting each concept.  I will stop short and simply say that they both work well under certain conditions. 

I want to dive into the affects of using ad-hoc queries on SQL server.  These aren't necessarily ill affects, but I want to go over how the SQL Server cache is impacted with ad-hoc queries and how you can tune SQL Server if your workload is primary ad-hoc in nature.

I said I wouldn't get into debate of whether Stored procedures are better than ad-hoc queries, but I think it's important to discuss common circumstances a SQL server would be under an ad-hoc load.
 
1. There are several reporting engines in Enterprise applications that allow users to choose an infinite amount of columns, groupings, and custom calculated columns.  It would be difficult for stored procedures to handle this abstraction.
2. ORM Frameworks such as Hibernate and LINQ generate allow you to generate SQL within the Object-oriented layer.  The goal of these types of frameworks are to allow the object layer to generate the sql necessary to interface with the database.
3. Analytical and Investigative queries handling one-off questions related to support and or debugging.

Now that you have a background on why ad-hoc queries might be used, I will share a couple of sql statements that will inform you if your SQL Server is under an ad-hoc load.
First let's take a look at the dynamic management view, sys.dm_exec_cached_plans.  This DMV returns information about each cached plan, how many times it was executed, the plan size and so forth ...

select * FROM sys.dm_exec_cached_plans where objtype = 'Adhoc'

 

Take special note of the size of your adhoc plans.  I will reference the size of plans later in this post.

Below are the types of objects that can have cached plans.

Proc = Stored procedure, Prepared = Prepared statement, Adhoc = Ad ho
c query,ReplProc = Replication filter procedure, Trigger,View, Default, UsrTab = User table, SysTab = System table, Check = CHECK constraint and Rule

You will notice in the sample output from my query, that the use counts are relatively low for the cached plans on my environment.  This is an important concept to think about when planning for ad-hoc queries.  Again, there is nothing wrong with ad-hoc queries, I am simply suggesting that ah-hoc queries have low plan re-usage when compared to Stored Procedures, and this can affect cache storage in SQL Server.

You may be asking yourself, my database server has plenty of memory, so what's the big deal?
If you are experiencing large amounts of one time ad-hoc query statements, the procedure cache will start to fill with the full plans and for ad-hoc statements that may never be run again.  This is wasteful.  If the cache is more than 25% full of these one time ad-hoc plans, then you may be able to take advantage of a newer feature enabled in SQL Server 2008, "Optimize for Ad hoc workloads".

Below is a SQL statement that will determine your current cache size.

select SUM (CONVERT(decimal(14,4),size_in_bytes)) / 1048576  as  CurrentCacheSize_MB
FROM sys.dm_exec_cached_plans


This next statement will help you determine the amount of single use ad-hoc plans

select SUM(CONVERT(decimal(14,4), size_in_bytes)) / 1048576 as CurrentAdHocSize_MB
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND objtype = 'adhoc'

If your ratio is more than 25 %, then you should consider enabling the "Optimize for Ad hoc workloads" feature.

The Optimize for Ad hoc workloads feature allows for just the stubs of the query plans to be cached, instead of the full plan.  The stub is small, around 18 bytes, and this allows for all plans to remain in the cache for a longer periods of time.  The goal is to keep plans who have higher re-use counts in the cache longer.  Creating execution plans is a very expensive operation for SQL Server, so the server's goal is to re-use existing plans whenever possible.

Look at my query results again, you will notice the difference in size of the plan that is being stored.

select * FROM sys.dm_exec_cached_plans where objtype = 'Adhoc'

A quick recap. 

Think about the types of applications that are using your database.  Try looking at the sys.dm_exec_cached_plans to understand if the query plans in your SQL Server cache are being re-used and what type of plans are in the cache.  You may be surprised to learn how your database is being utilized by the SQL statements that are run against it.

Enjoy