#sqlserver -k startup option: tempdb spill vs checkpoint writes

Looking over my last blog post, I realized that although I vaguely mentioned two separate throttling functions of the -k startup parameter, I never actually showed that they were separate functions :-)

The -k startup option can throttle checkpoint writes, and can throttle tempdb spills.
On my systems, I've never seen an overwhelming checkpoint but I've seen plenty of overwhelming spills to tempdb.  But are spill writes through the checkpoint mechanism?  If so, then -k would just be throttling checkpoint writes to persistent databases and to tempdb - same function in two contexts.

Let's take a look.  I'll look at the same test scenarios I used in my March 29, 2017 blog post: an "insert into #temptable select...", a "select... into #temptable", and an index create with sort_in_tempdb.

Now - this way of looking at tempdb activity may be helpful when you are trying to profile tempdb activity.  Without looking at the sys.dm_database_task_space_usage and sys.dm_database_session_space_usage DMVs, or the sys.dm_exec_query_memory_grant DMV, it can be tough to determine if writes to tempdb are from sort/hash spill (or index activity) or from DML to temp tables.  But looking at these perfmon counters might give a pretty good idea. :-) 

(Here's that blog post)
tempdb: "insert into... select" vs "select... into" vs index sort_in_tempdb write behavior
http://sql-sasquatch.blogspot.com/2017/03/tempdb-index-sortintempdb-vs-select.html

Today's blog post is just a quick jaunt, so here it is.  From perfmon in 1 second captures.

In the first activity, extents of 8 pages are being written to tempdb by the lazy writer.  But no checkpoint write activity is apparent.  In the second activity, each page is being written separately by the lazy writer. In the third activity - the index create I'm using as a proxy for sort/hash spill - no lazy writes, no checkpoint writes.  But plenty of page writes to tempdb :-)

And in my March 31, 2017 blog post, I showed how this sort activity is throttled by -k.  Doesn't show up as checkpoint activity, though.
SQL Server tempdb: sort_in_tempdb & -k startup option
http://sql-sasquatch.blogspot.com/2017/03/sql-server-tempdb-sortintempdb-k.html

So... two separate throttle functions of the -k startup option.