SQL Server Consulting, Education, and Training
I was helping a client troubleshoot a scalability issue recently, where during one point in their workload tempdb went out of control.
What was puzzling them was that nothing in the stored procedure did anything with temporary objects, cursors, …
Lots of people say these are rare — and perhaps they are becoming more so — but I still see them with some frequency. That’s not a terribly strong indictment of SQL Server, but that’s not the point of …
Hash spills are, as we’ll see, sometimes identified by a different wait than sort spills. In small quantities, spills are often not worth bothering with. But when they pile up, they can really cause some severe performance issues.…
Over the next few days, I’m going to look at the wait stats that show up during different types of spills of different sizes. For no reason whatsoever, I’m going to start with sort spills. I’ll also cover …
This is a somewhat complicated topic. There’s not a great TL;DR here, but I don’t want anyone to walk away from this post thinking that parallelism or indexes are “bad”.
What I do want to show …
For some background on Exchange Spills, check out this Great Post™ by Joe.
The root of this demo was trying to show people silly things about CTEs, how TOP can fence things off…
If you’ve made it this far, you’ve learned a few things:
Hash spills are nothing like sort spills, in that even with (very fast) disks, there’s no immediate benefit to breaking the hash down into pieces via a spill.
In fact, there are many downsides, especially when memory is …
In yesterday’s post, we looked at a funny situation where a query that spilled was about 5 seconds faster than one that didn’t.
Here’s what the query looked like:
SELECT x.PostId
FROM (
SELECT v.PostId,
ROW_NUMBER() OVER ( … Every post this week is going to be about spills. No crazy in-depth, technical, debugger type stuff.
Just some general observations about when they seem to matter more for performance, and when you might be chasing nothing by fixing …