During exam preparation I
was asked to answer a question that there are a lots of execution plans
generated during querying a database and used only once. How can I reduce that
and I got following answer
Server Configuration Option
(optimize for ad hoc workloads).
The optimize
for ad hoc workloads option is used to improve the efficiency of the plan cache
for workloads that contain many single use ad hoc batches. When option is
enabled only regularly used queries are cached that are used more than once. This
helps to relieve memory pressure by not allowing the plan cache to become
filled with compiled plans that are not reused.
Let's understand how it
works. Run the following text to enable advance option
sp_CONFIGURE 'show advanced options',1RECONFIGUREGO
sp_CONFIGURE ‘optimize for ad hoc workloads’,1RECONFIGUREGO
Now run the following SQL
/* Clean Cache and Buffers */DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS
GO/* Run Adhoc Query two Time */SELECT * FROM HumanResources.Shift
GO 5
GO/* Run Adhoc Query two Time */SELECT * FROM HumanResources.Shift
GO 5
/* Check if Adhoc query is cached.
It will return result with Adhoc Query ran two times*/SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plansCROSS APPLY sys.dm_exec_sql_text(plan_handle)WHERE usecounts > 0 ANDTEXT LIKE '%[your sql]%'ORDER BY usecounts DESC;GO
It will return result with Adhoc Query ran two times*/SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plansCROSS APPLY sys.dm_exec_sql_text(plan_handle)WHERE usecounts > 0 ANDTEXT LIKE '%[your sql]%'ORDER BY usecounts DESC;GO
Instead
of show 5 different execution plan. It will comes up with only one plan and
user count is 5. It means when this option is enabled same plan will execute
again and for rarely used queries that are only used for one time will not be
cached.
This option is very
good for memory utilization. Microsoft mentioned that only advanced options should be changed only by an
experienced database administrator or certified SQL Server technician.
No comments:
Post a Comment