Things SQL Server vNext Should Address: Spools

Per Capita

---

There are two angles to this. One is that spools are just crappy temp tables. The mechanism used for loading data, especially into Eager Spools, is terribly inefficient, especially when compared to the various advances in tempdb efficiency over the years.

The second angle is that Eager Index Spools should provide feedback to users that there’s a potentially useful index that could be created.

There are checks for Eager Table and Index Spools in sp_BlitzCache. Heck, it’ll even unravel them and tell you which index to create in the case of Eager Index Spools.

It still bothers me that there’s no built-in tooling or warning about queries that rely on Spools. Not because the Spools themselves are necessarily bad, but because they’re usually a sign that something is deficient with the query or indexing. Spools in modification queries are often necessary and not as worthy of your scorn, but can often be substituted with manual phase separation.

You may have lots of very fast queries with small Spools in them. You may have them right now.

You may also have lots of very slow queries with Spools in them, and there’s nothing telling you about it.

Spool Building

---

In a perfect world, when spools are being built, they’d emit a specific wait stat. Having that information would help all of us who do performance tuning work know what to look for and focus in on during investigations.

Right now you can get part of the way there by looking at EXECSYNC waits, but even that’s unreliable. They show up in parallel plans with Eager Index Spools, but they show up from other things too. Usually, your best bet is to just look for top resource consuming queries and examine the plans for Spools.

Step 1: Give us better waits to identify Spools being built

Index Building

---

The optimizer will complain about missing indexes all the live-long day. Even in cases where an index would barely help.

I don’t know about you, but in general ~200ms isn’t a performance emergency.

But like, three minutes?

I’d wanna know about this. I’d wanna create an index to help this query.

Step 2: Give us missing index requests when Eager Index Spools get built.

Buy A Telescope

---

This kind of stuff is already super-important now, and will become even more important as Scalar Function Inlining becomes more widely used.

End users need better feedback when new features get turned on and performance gets worse.

Sure, if you read this blog and know what to look for, you can find and fix things quickly. But there are a whole lot of people who don’t have things that easy, and I get a lot of calls from them to fix this sort of issue.

Thanks for reading!

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 performance problems quickly.