optimize for ad hoc workloads - Configuration Option

Normally, SQL Server compile, generate and stores the Plan for reuse. The process continues for all the following object types  
  • Proc
  • Prepared
  • Adhoc
  • ReplProc
  • Trigger
  • View
  • Default
  • UsrTab
  • SysTab
  • Check
  • Rule
For the every type of queries the process generates and stores the plan and every plan consume some Size.

Here, we going to see about the Transact-SQL statement also referred to as "Adhoc" query.

USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'




The plan consumes 72 KB (73728 Bytes) and the query uses 1st time. But, not sure whether the same plan going to be re-used next time. So, do we need to store the entire plan with consumes 72 KB at first time itself ?

No.

So, We have an option "optimize for ad hoc workloads" is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc queries/batches.

SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE WITH OVERRIDE

Done. 

As long as the above option is ON. All the Adhoc queries' entire plan are not going to be stored in Plan cache. But, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time.

To Remove the specific plan from the cache
DBCC FREEPROCCACHE(0x06000A004230791470B5CC340100000001000000000000000000000000000000000000000000000000000000)
GO

Run the query at first time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'




Yes. Now, we got only 352 Bytes (small compiled plan stub) for the plan at first compile/use.

When run the same query again second time

Run the query at second time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

Check whether the plan re-generated and stored entirely in Plan Cache
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'




Now, New plan generated (See the use count column as 1) and stored the entire plan in plan cache (See the Plan Size(Bytes)).

No comments:

Post a Comment