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.

Thanks for reading!

4 thoughts on “Selects That Cause Writes”

Leave a Reply

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