Selects That Cause Writes

Answer Time


While answering a question on dba.se, I got to thinking about if there would be a good way to detect SELECT queries that cause writes.

In newer versions of SQL Server, sys.dm_exec_query_stats has columns that show you spills.

That’s a pretty good start, but what about other kinds of writes, like the ones outlined in the Q&A I linked to?

So uh, I wrote this script to find them.

Downsides


The downsides here are that it’s looking at the plan cache, so I can’t show you which operator is spilling. You’ll have to figure that out on your own.

The source of the writes may be something else, too. It could be a spool, or a stats update, etc. That’s why I tried to set the spill size (1024.) kind of high, to not detect trivial writes.

You may be able to loosely correlate spills to IO_COMPLETION waits.

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS x )
, writes
    AS
     (
         SELECT   TOP ( 100 )
                  deqs.statement_start_offset,
                  deqs.statement_end_offset,
                  deqs.plan_handle,
                  deqs.creation_time,
                  deqs.last_execution_time,
                  deqs.total_logical_writes,
                  deqs.last_logical_writes,
                  deqs.min_logical_writes,
                  deqs.max_logical_writes,
                  deqs.query_hash,
                  deqs.query_plan_hash
         FROM     sys.dm_exec_query_stats AS deqs
         WHERE    deqs.min_logical_writes > 1024.
         ORDER BY deqs.min_logical_writes DESC
     ),
  plans
    AS
     (
         SELECT      DISTINCT
                     w.plan_handle,
                     w.statement_start_offset,
                     w.statement_end_offset,
                     w.creation_time,
                     w.last_execution_time,
                     w.total_logical_writes,
                     w.last_logical_writes,
                     w.min_logical_writes,
                     w.max_logical_writes
         FROM        writes AS w
         CROSS APPLY sys.dm_exec_query_plan(w.plan_handle) AS deqp
         CROSS APPLY deqp.query_plan.nodes('//x:StmtSimple') AS s(c)
         WHERE       deqp.dbid > 4
         AND         s.c.value('@StatementType', 'VARCHAR(100)') = 'SELECT'
         AND         NOT EXISTS (   SELECT      1/0 --If nothing comes up, quote out the NOT EXISTS. 
                                    FROM        writes AS w2
                                    CROSS APPLY deqp.query_plan.nodes('//x:StmtSimple') AS s2(c)
                                    WHERE       w2.query_hash = w.query_hash
                                    AND         w2.query_plan_hash = w.query_plan_hash
                                    AND         s2.c.value('@StatementType', 'VARCHAR(100)') <> 'SELECT' )
     )
SELECT      p.creation_time,
            p.last_execution_time,
            p.total_logical_writes,
            p.last_logical_writes,
            p.min_logical_writes,
            p.max_logical_writes,
            SUBSTRING(
                dest.text, ( p.statement_start_offset / 2 ) + 1,
                (( CASE p.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text) ELSE p.statement_end_offset END
                   - p.statement_start_offset ) / 2 ) + 1) AS text,
            deqp.query_plan
FROM        plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS deqp
ORDER BY    p.min_logical_writes DESC
OPTION ( RECOMPILE );

Thanks for reading!



4 thoughts on “Selects That Cause Writes

Leave a Reply

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