SQL Server Plan Cache Optimization Using Optimization For Ad hoc Workloads

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:

dbcc freeproccache --Do Not Run this DBCC Command on production Server
dbcc dropcleanbuffers --Do Not Run this DBCC Command on production Server
go
select name from xttest where city='moradabad'
go
select b.TEXT, a.usecounts, a.size_in_bytes, a.cacheobjtype 
from sys.dm_exec_cached_plans as a
cross apply sys.dm_exec_sql_text(plan_handle) as b
where text NOT like '%sys.dm%'

The output of above query is:

Now if we enable this workload option as shown below:

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
sp_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO

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:

dbcc freeproccache --Do Not Run this DBCC Command on production Server
dbcc dropcleanbuffers --Do Not Run this DBCC Command on production Server
go
select name from xttest where city='moradabad'
go
select b.TEXT, a.usecounts, a.size_in_bytes, a.cacheobjtype 
from sys.dm_exec_cached_plans as a
cross apply sys.dm_exec_sql_text(plan_handle) as b
where text NOT like '%sys.dm%'

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:

select name from xttest where city='moradabad'
go
select b.TEXT, a.usecounts, a.size_in_bytes, a.cacheobjtype 
from sys.dm_exec_cached_plans as a
cross apply sys.dm_exec_sql_text(plan_handle) as b
where text NOT like '%sys.dm%'

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.

Regards

Prince Rastogi

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

Follow me on Twitter |  Follow me on FaceBook