A Little About Sort Spills And IO_COMPLETION waits In SQL Server

Become a member! / @erikdarlingdata If you like what you see here, you'll love my advanced performance tuning training:
training.erikd...
Paul White AKA Pablo Blanco's post about Batch Mode sort spills: www.sql.kiwi/2...
SQL Server feedback issue about fixing them: feedback.azure... Click here for 50% off a health check: training.erikd...

[@artbart9080]

Very informative video.Thank you!

[@WahooNo2]

Great video!

[@madzebra5491]

+1

[@brainsniffer]

Sometime, it’d be interesting to see an experts take on doing something like making the engine work out what columns it needs for joins and stuff - virtualise the tables, pull and sort the crucial columns and map them back to the unsorted data and then do final lookups on final output. Surely it’d be faster with more memory available these days than sorting each and every step, especially with parallel collection sorts etc. Thanks for the background and stuff. Also, Built to Spill is great.

[@ErikDarlingData]

I think the main issue with schemes like that is ensuring consistent data. For example, Key Lookups require object-level Shared locks (which are incompatible with many other types of locks) to prevent data from changing while N number of lookups are occurring between the nonclustered and clustered indexes.
Outside of a snapshot isolation level, that is quite difficult to achieve without a blocking nightmare.

[@Lee-vl2wx]

Neat, don't stop making these.

[@ErikDarlingData]

I'm about to record one!

[@douglascoats7081]

What kind of mood do you have to be in to listen to Bela Lugosi Is Dead? good stuff as always

[@ErikDarlingData]

🍄

[@Rad343]

You go Coco!

[@ErikDarlingData]

😘