Hi Friends,

There is a very good option in SQL Server 2008 for Performance Optimization, i.e. “Optimize for Adhoc Workloads”. By using this option you can maximize the use of SQL Server Plan cache. When your workload contains single use adhoc queries or batches then this option will provide you good performance improvement in terms of plan cache. Compiled plan storage depends on Parameterization option set for database which are

1- Parameterization Set Simple: This is the default option for SQL Server.

2- Parameterization Set Force.

For the demo, I am having a database named as INDEXAN which is having a table as xttest and Parameterization is set to SIMPLE which is default. Let’s run a query:

The output of above query is:


Now if we enable this workload option as shown below:

You can also set this option by Using SQL Server Management Studio. Go to the properties of SQL Server Instance and set Optimize For Ad hoc Workloads to True.


Now again run the same query:

Now the output of above query is:


Here rather than storing compiled plan sql server store Compiled Plan Stub in plan cache. This will take less space in plan cache so that other queries can use plan cache to store their plans. Here the size of Compiled Plan Stub is only 320 bytes.

Now again run the same query but without DBCC commands:

Now the output of above query is:


Here stub is replaced by compiled plan means if query is running once then SQL Server will store Compiled Plan Stub, But if the query will be running again then stub will be replace by compiled plan.

That means SQL Server will store compiled plan to only those queries which are running more than one time.



Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook