SQL Server - Single use execution plans

Get execution plans that were used only once. SQL Server from its nature caches all execution plans, even for queries that are executed once. This behavior can be suppressed by configuration called Optimize For Ad-Hoc Workloads. You can use this query to check if there are some execution plans stored in your plan cache but not used repeatedly. If so many of them such situation can be on of causes for memory bloat problem.

SELECT 
	query_hash,
	COUNT(*) as number_of_entries
FROM sys.dm_exec_query_stats
WHERE execution_count = 1
GROUP BY query_hash
HAVING COUNT(*) > 1
ORDER BY number_of_entries DESC
	
SELECT 
	TOP 10 stext.text,
	splan.query_plan
FROM sys.dm_exec_query_stats as stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as stext
CROSS APPLY sys.dm_exec_query_plan(stat.plan_handle) as splan
WHERE stat.query_hash = -- put hash from previous query here

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
query_hashbinary(8)Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
number_of_entitiesintCount of cached plans for given query.
---
textnvarchar(max)Text of the SQL query. Is NULL for encrypted objects.
query_planxmlContains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. The Showplan is in XML format. One plan is generated for each batch that contains, for example ad hoc Transact-SQL statements, stored procedure calls, and user-defined function calls. Column is nullable.

Related posts

Leave a Reply

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