Clumsy
-
I couldn't remember if I included these in [my original answer][1], 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.

[![NUTS][2]][2]

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

Index Spool
-
[![NUTS][3]][3]

Table Spool
-
[![NUTS][4]][4]

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](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-2017).

    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;

[![NUTS][5]][5]

[![NUTS][6]][6]

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


  [1]: https://dba.stackexchange.com/a/191833/22336
  [2]: https://i.stack.imgur.com/Scexv.png
  [3]: https://i.stack.imgur.com/Xu2e4.png
  [4]: https://i.stack.imgur.com/0ska3.png
  [5]: https://i.stack.imgur.com/GPerd.png
  [6]: https://i.stack.imgur.com/Ox3FB.png

There's another time when this may happen, and that's with an automatic stats update.

Here's the XE session we'll be looking at:

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

Then we'll use this to collect information:

    USE tempdb
    
    DROP TABLE IF EXISTS dbo.SkewedUp
    
    CREATE TABLE dbo.SkewedUp (Id INT NOT NULL, INDEX cx_su CLUSTERED (Id))
    
    INSERT dbo.SkewedUp WITH ( TABLOCK ) ( Id )
    SELECT CASE WHEN x.r % 15 = 0 THEN 1
                WHEN x.r % 5 = 0 THEN 1000
                WHEN x.r % 3 = 0 THEN 10000
                ELSE 100000
           END AS Id
    FROM   (   SELECT     TOP 1000000 ROW_NUMBER() OVER ( ORDER BY @@DBTS ) AS r
               FROM       sys.messages AS m
               CROSS JOIN sys.messages AS m2 ) AS x;
    
    
    ALTER EVENT SESSION [batches_and_stats] ON SERVER STATE = START
    
    SELECT su.Id, COUNT(*) AS records
    FROM dbo.SkewedUp AS su
    WHERE su.Id > 0
    GROUP BY su.Id
    
    ALTER EVENT SESSION [batches_and_stats] ON SERVER STATE = STOP

Some of the interesting results from the XE Session:

[![NUTS][1]][1]

The auto stats update doesn't show any writes, but the query shows one write immediately after the stats update.


  [1]: https://i.stack.imgur.com/3Oa22.jpg

In some cases Query Store can cause writes to occur as an effect of a select statement, and in the same session.

This can be reproduced as follows:

    USE master;
    GO
    CREATE DATABASE [Foo];
    ALTER DATABASE [Foo] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, 
      CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
      DATA_FLUSH_INTERVAL_SECONDS = 900, 
      INTERVAL_LENGTH_MINUTES = 60, 
      MAX_STORAGE_SIZE_MB = 100, 
      QUERY_CAPTURE_MODE = ALL, 
      SIZE_BASED_CLEANUP_MODE = AUTO);
    USE Foo;
    CREATE TABLE Test (a int, b nvarchar(max));
    INSERT INTO Test SELECT 1, 'string';

Create an Extended Events session for monitoring:

    CREATE EVENT SESSION [Foo] ON SERVER 
    ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1)
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
        WHERE ([writes]>(0))),
    ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
        WHERE ([writes]>(0)))
    ADD TARGET package0.event_file(SET filename=N'C:\temp\FooActivity2016.xel',max_file_size=(11),max_rollover_files=(999999))
    WITH (MAX_MEMORY=32768 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);


Next run the following:

    WHILE @@TRANCOUNT > 0 COMMIT
    SET IMPLICIT_TRANSACTIONS ON;
    SET NOCOUNT ON;
    GO
    DECLARE @b nvarchar(max);
    SELECT @b = b FROM dbo.Test WHERE a = 1;
    WAITFOR DELAY '00:00:01.000';
    GO 86400

An implicit transaction may or may not be necessary to reproduce this.

By default, at the top of the next hour Query Store's statistics collection job will write out data. This appears to (sometimes?) occur as part of the first user query executed during the hour. The Extended Events session will show something similar to the following:

[![enter image description here][1]][1]

The transaction log shows the writes that have occurred:

    USE Foo;
    SELECT [Transaction ID], [Begin Time], SPID, Operation, 
      [Description], [Page ID], [Slot ID], [Parent Transaction ID] 
    FROM sys.fn_dblog(null,null) 
    /* Adjust based on contents of your transaction log */
    WHERE [Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
    OR [Parent Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
    ORDER BY [Current LSN];

[![enter image description here][2]][2]

Inspecting the page with `DBCC PAGE` shows that the writes are to `sys.plan_persist_runtime_stats_interval`.

    USE Foo;
    DBCC TRACEON(3604); 
    DBCC PAGE(5,1,344,1); SELECT
    OBJECT_NAME(229575856);

Note that the log entries show three nested transactions but only two commit records. In a similar situation in production, this led to an arguably faulty client library that used implicit transactions unexpectedly starting a write transaction, preventing the transaction log from clearing. The library was written to only issue a commit after running an update, insert, or delete statement, so it never issued a commit command and left a write transaction open.

  [1]: https://i.stack.imgur.com/1v4lR.png
  [2]: https://i.stack.imgur.com/ldvj0.png