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:

1_SQL_Server_Plan_Cache_Optimization_Using_Optimization_For_Ad_hoc_Workloads

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.

2_SQL_Server_Plan_Cache_Optimization_Using_Optimization_For_Ad_hoc_Workloads

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:

3_SQL_Server_Plan_Cache_Optimization_Using_Optimization_For_Ad_hoc_Workloads

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:

4_SQL_Server_Plan_Cache_Optimization_Using_Optimization_For_Ad_hoc_Workloads

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 TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *