SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Where have you been all my life?

Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills.

They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill to tempdb.

The tough part is that catching parameter sniffing in action is difficult. It’s sort of like looking for it makes it disappear, and some evidence of it still disappears.

This has improved both with the addition of Query Store in general, and memory grants in DMVs.

Now with tempdb spills added, your job gets even easier. Sort of.

The finding problems part is easier.

You still have to solve them. Or send an email to the Automation department.

They’ll get back to you in a jiffy.

Adds the following columns to the sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, and sys.dm_exec_trigger_stats DMVs.

Okay, cool, but… remember what I just said about Query Store?

These columns aren’t in there. Yet. Maybe?

Give it a name

This makes things a little awkward

Especially for someone building tooling for Query Store.

  • Do I need to hit DMVs outside of Query Store now to get spill info?
  • Should I wait to find out?

I don’t know!

Dock U Ment

So what’s the good part?

Well, tempdb spills aren’t available in cached plans.

If I have this procedure…

Without any helpful indexes we end up spilling to tempdb.

OUCH!

But if I’m digging through the plan cache, I won’t know that.

Donkey

Nothing in the plan warns me that a Sort spilled.

But now I can query the new DMVs to find spills! I’ll be adding this to sp_BlitzCache for the next release.

Weeee

spilt

Is there a downside?

Well… There are a few things that can spill; Sorts and Hashes being the most common.

If you’ve got multiple operators that can spill, you don’t know which ones did spill.

  • Did one spill?
  • Did more than one spill?
  • Did they all spill?
  • Which one spilled the most?

I only bring this up because Microsoft already knows it.

There are a number of XE sessions geared towards capturing spills, and many of them collect the node id in the query plan of the operator that spilled.

Guess who’s spilling their spaghetti?

Is this the end of the world?

No, not by a long shot. it’s just something for you to be aware of.

Are spills even a problem?

No, not always. But they’re a good thing to be aware of.

They tend to manifest in wait stats as IO_COMPLETION.

If you’ve got queries that are both spilling to disk and have long average waits on that wait type, it’s probably time to take a closer look.

Thanks, Microsoft

I’m very happy to see this stuff surfaced! Microsoft is slowly getting away from the “catch me if you can” approach to performance issues.

In many scenarios, you still really do have to catch problems while they’re happening to capture meaning information, but those scenarios are dwindling with every release.

I really hope this information comes to Query Store soon!

Thanks for reading!

Brent says: I am so loving the age of automated performance tuning. No, seriously, hear me out, this isn’t a joke. If you’re gonna build a robot to fix performance issues in Azure SQL DB, then the robot needs metrics just like humans do. Is this fix gonna be backported to 2016, 2014, or 2012? Who knows? Who cares? I’m at the point where I say if you need speed, you need to be on 2017 – not just for today’s speed, but to get the fastest releases of the fastest performance troubleshooting tools.

Previous Post
SQL Server 2017 CU3 adds CXCONSUMER waits, doesn’t mention it
Next Post
SQL Server 2017 CU3 adds optimizer row goal information in query plans

4 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}