Tags: MSSQL

SQL Server maintains cache for execution plan. This cache maintained to optimize query performance. Each query has its own execution plan stored in memory. It will lead to memory overhead for SQL Server. It will also be suppression to find exact execution. We need to cache only those queries which executed multiple times.

/* Get memory used for execution plan cache */
SELECT SUM(size_in_bytes)/1024.0/1024.0 AS [Total MB used]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

To understand the real time example lets turn off "optimize for ad hoc workloads" option and clear all execution plan from cache.

EXECUTE sp_configure 'optimize for ad hoc workloads', 0
RECONFIGURE
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

Now execute simple select query on database and generate execution plan cache.

SELECT * FROM HumanResources.Employee

To check query execution plan cache execute below query.

SELECT usecounts, size_in_bytes, cacheobjtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

Explanation
When you disable/turn off "optimize for ad hoc workloads" option, cache will stores whole execution plan regardless query will be used in future or not. This will create memory overhead. Lets see the counter part of this option.

---

Now clear all execution plan cache and enable/turn on "optimize for ad hoc workloads" option.

EXECUTE sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

Execute same query and check the execution plan cache. Execute both the query separately.

SELECT * FROM HumanResources.Employee

SELECT usecounts, size_in_bytes, cacheobjtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

Explanation
When you enable/turn on "optimize for ad hoc workloads" option, SQL server only store Compiled Plan Stub of query which executed once. If you fire same query again then SQL Server get to know that this query already executed once so it will remove Compiled Plan Stub from memory and replace it with Compiled Plan.

Tags: MSSQL