SQL Server – Query Plans with DesiredMemory

 

Background

I have a machine that is bingeing on Tempdb and so trying to figure out which Queries are asking for memory.

Code

Versioning

Depending on the Version of SQL Server that we are using the places to look and the details will be different.

Version 2016

For v2016, Amit Banerjee has a good write -up on how sys.dm_exec_query_stats has been extended to expose memory details on each Query.

That post is available here.

 

v2012, v2014

If you find yourself on v2012 or v2014, I will say dig into the XML Query plans using XPATH and tear apart the MemoryGrantInfo element.

Code




use master
go

if object_id('dbo.sp_ReviewCachedQueryPlansIdentifySerialMemory') is null
begin

	exec('create procedure dbo.sp_ReviewCachedQueryPlansIdentifySerialMemory as ')

end
go


alter procedure [dbo].[sp_ReviewCachedQueryPlansIdentifySerialMemory]
(

	@iNumberofRecordToReturn int = 500

)
as

begin

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


	;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	, ctePlan
	(
		[plan_handle]
	)
	as
	(
		select 
				[plan_handle]
					= cp.plan_handle
				  
		from   sys.dm_exec_cached_plans cp

		CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

		WHERE qp.query_plan.exist( '//MemoryGrantInfo[@SerialDesiredMemory[. > 0]]' ) =1

		and   qp.query_plan.exist('//ColumnReference[@Schema!="[sys]"]') = 1

	)
	, cteMetric
	(
		  [dbName]
		, [object]

		, [queryText]
		, [statementText]

		, [queryPlan]
		, [nodeMemoryInfo]

		, [usecounts]

		, [refcounts]

		, [SerialRequiredMemory]
		, [SerialDesiredMemory]

		, [RequiredMemory]
		, [DesiredMemory]
		, [DifferentialMemory]

		, [existSort]
		, [existWarning]
		, [existMissingIndexes]


	)
	as
	(
		select 

			 [dbName]
				= case [ep].[dbid]
					when 32767 then 'Resource DB'
					else db_name([ep].[dbid])
				  end

			 , [object]
				= object_schema_name(qt.objectid, qt.[dbid])
				  + '.'
				  + object_name(qt.objectid, qt.[dbid])

			 , [queryText]
				= qt.[text]

			
			 , [statementText] 
				=
					SUBSTRING
					(
						st.[text]
						, (qs.statement_start_offset/2)+1,   
						  ((CASE qs.statement_end_offset  
								WHEN -1 THEN DATALENGTH(st.text)  
								ELSE qs.statement_end_offset  
							END - qs.statement_start_offset)/2) + 1
					)

			
			 , ep.query_plan

			 , c.query('.')

			 , cp.usecounts

			 , cp.refcounts

			, [SerialRequiredMemory]
				= c.value
					(
						 '@SerialRequiredMemory'
						,'bigint'
					) 

			, [SerialDesiredMemory]
				= c.value
					(
						 '@SerialDesiredMemory'
						,'bigint'
					) 

			, [RequiredMemory]
				= coalesce
					(
						c.value
						(
							 '@RequiredMemory'
							,'bigint'
						) 
						, 0
					)

			, [DesiredMemory]
				=  coalesce
					(
						c.value
						(
							 '@DesiredMemory'
							,'bigint'
						) 
						, 0
					)

			, [DifferentialMemory]
				= coalesce
					(
						c.value
						(
							'@DesiredMemory'
							,'bigint'
						) 
						- c.value
						(
						 '@RequiredMemory'
						,'bigint'
						) 
						, 0
					)

				, [existSort]
					= ep.query_plan.exist('//Sort')

				, [existWarning]
					= ep.query_plan.exist('//Warning')

				, [existMissingIndexes]
					= ep.query_plan.exist('//MissingIndexes')


		from   ctePlan cteP

		inner join sys.dm_exec_cached_plans cp

				on cteP.[plan_handle] = [cp].[plan_handle]

		inner join sys.dm_exec_query_stats qs
				on cteP.[plan_handle] = qs.[plan_handle] 

		CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as qt

		CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) as st
 
		CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) as ep

		CROSS APPLY ep.query_plan.nodes ( '//MemoryGrantInfo[@SerialDesiredMemory[. > 0]]' ) qpMGI(c)

	)

	select 

			TOP ( @iNumberofRecordToReturn )
			
				cteMetric.*	

	from   cteMetric

	order by

			 [SerialDesiredMemory] desc


end

go


Output

 

Sample Output – 1

self-20161202-0537pm

 

Sample Output – Assist – Dev – 1

assist-dev-20161202-0540pm

 

References

  1. Amit Banerjee
    • SQL Server 2016 Public Preview (CTP2) – sys.dm_exec_query_stats
      Link
  2. Pedro Lopez
    • SQL Swiss Army Knife #13 – Exploring the plan cache – Part 2
      Link
  3. Joe Sack
    • Memory Grant Execution Plan Statistics
      Link
    • Fixing Gatekeeper Row Cardinality Estimate Issues
      Link
  4. Ed Pollack
    • Searching the SQL Server query plan cache
      Link
  5. CSS SQL Server Engineers
    • pssql
      • I think I am getting duplicate query plan entries in SQL Server’s procedure cache
        Link
  6. Sangeetha Shekar
    • 2.0 Sql_Handle and Plan_Handle Explained
      Link
    • SQL Programmability & API Development Team Blog
      Link

 

One thought on “SQL Server – Query Plans with DesiredMemory

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s