Optimize for ad hoc workloads – SQL 2008

SQL Server 2008 had introduced a new feature that helps optimizing ad hoc workloads. SQL server generates an execution plan for each and every statement that’s executed. The reason is that the SQL server would be able to re-use the execution plan when the statement run next time.

However, there are lot of scenario where we execute some statements only once and never again especially when troubleshooting some issues. The plans created for the one time queries occupy the memory and other resources which could be avoided.

Lets see how the SQL Server behaves when adhoc optimization is not enabled.

I have a sample query here that uses the AdventureWorks database, remember *NOT* to run these queries on a production systems. This will clear out the cache what could affect your systems performance.

DBCC freeproccache;
DBCC dropcleanbuffers;
go
SELECT * FROM sales.salesorderheader
go
SELECT CP.usecounts,
       CP.cacheobjtype,
       CP.objtype,
       PT.text
FROM sys.dm_exec_cached_plans CP
CROSS apply sys.dm_exec_sql_text(plan_handle) PT
WHERE PT.text LIKE '%select * from Sales.SalesOrderHeader%'
  AND pt.text NOT LIKE
    '%SELECT CP.usecounts, CP.cacheobjtype, CP.objtype, PT.TEXT%'; 

Though the query is executed only once, the plan is cached by the SQL Server for future use. Also look at the size in bytes for a simple one line query, as the query becomes bigger the plan size also would increase.

SQL Server introduced an option to optimize your SQL server for adhoc workloads. You can enable this option using the sp_configure

sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE

Now run the earlier queries again and see if the plan cache is still getting stored.

DBCC freeproccache;
DBCC dropcleanbuffers;
go
SELECT * FROM sales.salesorderheader
go
SELECT CP.usecounts,
       CP.cacheobjtype,
       CP.objtype,
       PT.text
FROM sys.dm_exec_cached_plans CP
CROSS apply sys.dm_exec_sql_text(plan_handle) PT
WHERE PT.text LIKE '%select * from Sales.SalesOrderHeader%'
  AND pt.text NOT LIKE
     '%SELECT CP.usecounts, CP.cacheobjtype, CP.objtype, PT.TEXT%'; 

You will notice that cacheobjecttype is Compiled Plan Stub and the size has reduced considerably. The stubs are not actual plans, you can consider them as a simple skeleton of the execution plan and stubs consume very less size, so your cache wasted in storing these plans will be very less.

We will run the queries once again, but lets run it multiple times(3 times here), and then we will look at the plan size and cacheobjecttype.

DBCC freeproccache;
DBCC dropcleanbuffers;
go
SELECT * FROM sales.salesorderheader
go 3

SELECT CP.usecounts,
       CP.size_in_bytes,
       CP.cacheobjtype,
       CP.objtype,
       PT.text
FROM sys.dm_exec_cached_plans CP
CROSS apply sys.dm_exec_sql_text(plan_handle) PT
WHERE  PT.text LIKE '%select * from Sales.SalesOrderHeader%'
AND pt.text NOT LIKE
'%SELECT CP.usecounts, CP.cacheobjtype, CP.objtype, PT.TEXT%';
go

When we executed the query multiple times, the SQL Server assumed that that it can reuse the plan and then decided to store the execution plan.

References
http://technet.microsoft.com/en-us/library/cc645587.aspx
http://blogs.technet.com/b/josebda/archive/2009/03/19/optimize-for-ad-hoc-workloads-in-sql-server-2008.aspx