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:

Transact-SQL

1

2

3

4

5

6

7

8

9

dbccfreeproccache--Do Not Run this DBCC Command on production Server

dbccdropcleanbuffers--Do Not Run this DBCC Command on production Server

go

selectnamefromxttestwherecity='moradabad'

go

selectb.TEXT,a.usecounts,a.size_in_bytes,a.cacheobjtype

fromsys.dm_exec_cached_plansasa

crossapplysys.dm_exec_sql_text(plan_handle)asb

wheretextNOTlike'%sys.dm%'

The output of above query is:

Now if we enable this workload option as shown below:

Transact-SQL

1

2

3

4

5

6

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:

Transact-SQL

1

2

3

4

5

6

7

8

9

dbccfreeproccache--Do Not Run this DBCC Command on production Server

dbccdropcleanbuffers--Do Not Run this DBCC Command on production Server

go

selectnamefromxttestwherecity='moradabad'

go

selectb.TEXT,a.usecounts,a.size_in_bytes,a.cacheobjtype

fromsys.dm_exec_cached_plansasa

crossapplysys.dm_exec_sql_text(plan_handle)asb

wheretextNOTlike'%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:

Transact-SQL

1

2

3

4

5

6

selectnamefromxttestwherecity='moradabad'

go

selectb.TEXT,a.usecounts,a.size_in_bytes,a.cacheobjtype

fromsys.dm_exec_cached_plansasa

crossapplysys.dm_exec_sql_text(plan_handle)asb

wheretextNOTlike'%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.

Share This Story, Choose Your Platform!

Prince Rastogi is working as Principle Database Administrator at Admiral Technologies. Prince started his career working on SQL Server since Yukon. Prince is having almost 7 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 at SQLServerGeeks.com.