Clumsy

I couldn't remember if I included these in my original answermy original answer, so here's another couple.

Spools!

SQL Server has lots of different spools, which are temporary data structures stored off in tempdb. Two examples are Table and Index spools.

When they occur in a query plan, the writes to those spools will be associated with the query.

These will also be registered as writes in DMVs, profiler, XE, etc.

Index Spool

Table Spool

The amount of writes performed will go up with the size of the data spooled, obviously.

Spills

When SQL Server doesn't get enough memory for certain operators, it may spill some pages to disk. This primarily happens with sorts and hashes. You can see this in actual execution plans, and in newer versions of SQL server, spills are also tracked in dm_exec_query_stats.

SELECT deqs.sql_handle,
       deqs.total_spills,
       deqs.last_spills,
       deqs.min_spills,
       deqs.max_spills
FROM sys.dm_exec_query_stats AS deqs
WHERE deqs.min_spills > 0;

Tracking

You can use a similar XE session as the one I used above to see these in your own demos.

CREATE EVENT SESSION spools_and_spills
    ON SERVER
    ADD EVENT sqlserver.sql_batch_completed
    ( ACTION ( sqlserver.sql_text ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\spools_and_spills' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 1 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO