Memory Spill in dmvs

Question

In sql server 2014, can we find the queries which spilled into tempdb using dmvs ?

This sounds like an interview question, and not a very good one at that. — Sean Lange, May 15 '18 at 13:54
what have u tried, did u checked with obtaining `db_id from sys.dm_exec_query_stats` — Ven, May 15 '18 at 14:04

score 0 · Accepted Answer · edited Mar 17 '19 at 12:10

Sure, easy just query against the XML generated by the execution plan. The following query shows the top 50 worst performing queries with warnings in the execution plan.

Spills are shown as warnings in the execution plan just like conversion errors, you can filter down the items returned by making the count more restrictive where you are not just looking for the word warning. you could look for table scans as well. Have a play with it. Here is the query:

SELECT  [TSQL]= st.text,
        qp.query_plan
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";count(//p:Warnings)', 'int') > 0

Part2 Your question

Having access to your execution plans from the DMV's allows you to find anything that is shown in the execution plan, like queries that generate tables scans and ignore the indexes:

DECLARE @Table_Name sysname = N'Orders';

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
 [TSQL]=st.text
,Schema_Name=operators.value('(TableScan/Object/@Schema)[1]','sysname')
,[Table]=operators.value('(TableScan/Object/@Table)[1]','sysname')
,[Index]=operators.value('(TableScan/Object/@Index)[1]','sysname')
,[Physical]= operators.value('@PhysicalOp','nvarchar(50)')
,[Occurance]= cp.usecounts
,qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY query_plan.nodes('//RelOp') rel(operators)
WHERE operators.value('@PhysicalOp','nvarchar(60)') IN ('Table Scan')
AND operators.value('(TableScan/Object/@Table)[1]','sysname') = QUOTENAME(@Table_Name,'[');

Pulled the original from SQLTips some time ago and will work with most versions just make sure that the execution plan contains the data you're looking for.

Perhaps share your plan and SQL version you are using.

Thanks for the reply. It is good way to find warnings. But it doesn't solve my problem. For testing, I wrote a select query with order by. Its execution plan has sort operator spilling over to tempdb but the select operator has no warning. Now , when i use the above query to get the plan then its doesn't get returned as its filtered out by where clause. When I commented the where clause in the above query i got the plan from cache but now this plan has no spills in any operator. Strange? — jshikha, May 16 '18 at 10:27
Interesting spils should generate a warning, hence you should see them. You can look for any data in your execution plan.... WHERE operators.value('@PhysicalOp','nvarchar(60)') IN ('Table Scan') 15. AND operators.value('(TableScan/Object/@Table)[1]','sysname') = QUOTENAME(@Table_Name,'['); perhaps share your execution plan so I can update the answer — Walter Verhoeven, May 16 '18 at 12:12
Update the where to include "where you're interested in" in the 2nd query — Walter Verhoeven, May 16 '18 at 12:25

Memory Spill in dmvs

1 Answers1

Part2 Your question