Diagnosing and Fixing tempdb Contention from Spills in SQL Server
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
On recent non-VMs… use of NVMe for tempdb shiuld minimize this potential, right?
Well, this is a VM, but it’s a very local VM working on direct attached NVME. So you are free to draw conclusions from that.
Would you mind telling us why you use “Sort_In_tempdb = on” ?
“A friend of mine” always told tought that meant double the writes.
I use it to avoid unnecessary user database file activity. It’s difficult to figure out the intent of your second sentence, so I’ll leave it be.
hi Erik,
I always thought that sorting in tempdb meant writing things twice.
But when running
create index ix_reputation on dbo.users (reputation) with (sort_in_tempdb=on, data_compression=page, drop_existing=on)
create index ix_reputation on dbo.users (reputation) with (sort_in_tempdb=off, data_compression=page, drop_existing=on)
my server has everything in RAM, so no difference.
After setting MaxMem to 1GB, and running
drop index ix_title on dbo.Posts
create index ix_title on dbo.Posts (title) with (sort_in_tempdb=off, data_compression=page)
drop index ix_title on dbo.Posts
create index ix_title on dbo.Posts (title) with (sort_in_tempdb=on, data_compression=page)
I was able to lower the writes on mydb 10x, and overall writes by 10%
It looks as if I need to copy your “sort_in_tempdb=on”.
Thank you.
Well, congratulations.
Using Microsoft SQL Server 2019 (RTM-CU20), I was unable to produce a tempdb spill, even before creating the initial index, until I changed the database compat level to 140 (SQL Server 2017). After doing that, I was able to see the spill to tempdb in the execution plan with no index and with the first version of the index created. Upon creating the second version of the index, with the descending sort on Reputation, I no longer got the tempdb spill with compat. = 140.
Forgot to say this, but cool demo!