Search code examples
sqlsql-servert-sqltempdbdmv

Memory Spill in dmvs

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

Solution

  • 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

    enter image description here

    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.