Tempdb Spill level1

Question

Tempdb Spill level1

Martin Stephenson

SSCrazy

Points: 2950
More actions
October 19, 2015 at 9:14 am

#301515

I have a query where a sort operation is flagging a spill into tempdb in the execution plan. There are no Order by sort operations in the query. It does, or can pull lots of data but it spills even on a couple of thousand rows. This occurs after parallelism gathers the streams at the end of the query. The Query Optimiser is adding the sort in but I'm not sure why or where is the best place to start looking for the cause

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Martin Stephenson (10/19/2015)
I have a query where a sort operation is flagging a spill into tempdb in the execution plan. There are no Order by sort operations in the query. It does, or can pull lots of data but it spills even on a couple of thousand rows. This occurs after parallelism gathers the streams at the end of the query. The Query Optimiser is adding the sort in but I'm not sure why or where is the best place to start looking for the cause

Can you post up the plan? A sort generally spills when the number of actual rows exceeds the number of estimated rows.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Martin Stephenson SSCrazy Points: 2950 More actions · Answer 2

There is a mismatch in estimated count of 17 vs actual of 13k, this becomes visible after the join to the table with the suggested missing index. Not really in a position to create new indices on somebody else's database.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Martin Stephenson (10/19/2015)
There is a mismatch in estimated count of 17 vs actual of 13k, this becomes visible after the join to the table with the suggested missing index. Not really in a position to create new indices on somebody else's database.

I can't offhand see why the plan has a distinct sort operator - but the plan indicates an optimiser timeout (it gave up trying to find a good plan). You have loads of key lookups which hints that indexing might not be optimal. That's where I'd start. Decent indexes will at least give the optimiser better distinction between poor and good plans.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

As additional background, SQL estimates the memory it will need to do the sort ahead of time, based on its estimated row count and row size. If you end up needing more memory than that, then SQL will spill to tempdb.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".