Lonny Niederstadt shows that the -k startup option throttles two separate things:
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.
For further reference, here is his preliminary research into the -k option.