August 11, 2016 at 8:50 am
Recently I noticed some of my code has the warning "Operator used tempdb to spill data during execution with spill level 1." I have a few questions on this.
1) What is with the different spill levels? I've seen level 1, 2 and (I think) 3. Is one worse than the other? I can't find a definition for any of this in Google.
I did find Brent Ozar's article on spills[/url]. Which is leading me to a other links, including a discussion on how to fix Hash Match spills[/url] (by fixing the JOIN clauses). Unless I'm missing something, though, it doesn't tell me how to identify which join is causing the problem. When I hover over the Hash Match in execution plan, it gives me an Output List, the Warning and Build Residual.
2) Which one of the above is where I should first focus my efforts?
I also have a spill on a Sort operator but I'm not using an ORDER BY or ROW_NUMBER(). I'm not even using a GROUP BY. When I look at the sort, it's calling out an ORDER BY on a column that I'm joining on, but not ordering on.
3) Am I right in thinking that an INNER JOIN statement where an unindexed column is being used on the left side of the ON against a clustered PK column on the right side would force the first table (with the unindexed column) to sort before it can SELECT?
August 11, 2016 at 9:23 am
recommend reading Joe Sack's write up on this
August 11, 2016 at 12:36 pm
Brandie Tarvin (8/11/2016)
3) Am I right in thinking that an INNER JOIN statement where an unindexed column is being used on the left side of the ON against a clustered PK column on the right side would force the first table (with the unindexed column) to sort before it can SELECT?
No.
Of the three join types that SQL has, only one needs the data sorted on the join columns (merge join). The QO typically won't pick a merge join if it needs to sort a lot of data. That you're getting a spill on that sort suggest that your row estimations are off, the QO thinks it has fewer rows than it does, so it picks a merge join with sort and requests too small a memory grant, so the sort spills to disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2016 at 2:19 pm
Brandie Tarvin (8/11/2016)
Recently I noticed some of my code has the warning "Operator used tempdb to spill data during execution with spill level 1." I have a few questions on this.1) What is with the different spill levels? I've seen level 1, 2 and (I think) 3. Is one worse than the other? I can't find a definition for any of this in Google.
According to Sort Warnings Event Class
1 = Single pass. When the sort table was written to disk, only a single additional pass over the data was required to obtain sorted output.
2 = Multiple pass. When the sort table was written to disk, multiple passes over the data were required to obtain sorted output.
I also know for a fact that I have seen spill level's higher than 2. My understand has always been that any number higher than 1 represents the number of passes over the data. The link prvmine posted shows spill level 8.
-- Itzik Ben-Gan 2001
August 15, 2016 at 8:59 am
I don't think I've ever seen a spill warning before I moved to SQL 2012. Now I seem to be spotting them all over the place. I really wish I knew how to fix this.
Then again, I'm not entirely sure the spill warnings are my entire problem. So I am going to have to dig deeper into this.
Thanks for your input.
August 15, 2016 at 9:06 am
GilaMonster (8/11/2016)
Brandie Tarvin (8/11/2016)
3) Am I right in thinking that an INNER JOIN statement where an unindexed column is being used on the left side of the ON against a clustered PK column on the right side would force the first table (with the unindexed column) to sort before it can SELECT?No.
Of the three join types that SQL has, only one needs the data sorted on the join columns (merge join). The QO typically won't pick a merge join if it needs to sort a lot of data. That you're getting a spill on that sort suggest that your row estimations are off, the QO thinks it has fewer rows than it does, so it picks a merge join with sort and requests too small a memory grant, so the sort spills to disk.
Hrmmm. I looked at one of my many queries getting spill warnings and this is spot on. Estimated rows are smaller than actual rows. Kendra Little links to an article for updating stats and other things to fix the issue. So I'm going to try that.
Thanks, Gail.
August 15, 2016 at 10:03 am
GilaMonster (8/11/2016)
Brandie Tarvin (8/11/2016)
3) Am I right in thinking that an INNER JOIN statement where an unindexed column is being used on the left side of the ON against a clustered PK column on the right side would force the first table (with the unindexed column) to sort before it can SELECT?No.
Of the three join types that SQL has, only one needs the data sorted on the join columns (merge join). The QO typically won't pick a merge join if it needs to sort a lot of data. That you're getting a spill on that sort suggest that your row estimations are off, the QO thinks it has fewer rows than it does, so it picks a merge join with sort and requests too small a memory grant, so the sort spills to disk.
There are circumstances where it'll still get it wrong even if the estimates and other calculations are right. There's a fix in a CU. https://support.microsoft.com/en-us/kb/3088480
Thomas Rushton
blog: https://thelonedba.wordpress.com
August 15, 2016 at 12:46 pm
ThomasRushton (8/15/2016)
GilaMonster (8/11/2016)
Brandie Tarvin (8/11/2016)
3) Am I right in thinking that an INNER JOIN statement where an unindexed column is being used on the left side of the ON against a clustered PK column on the right side would force the first table (with the unindexed column) to sort before it can SELECT?No.
Of the three join types that SQL has, only one needs the data sorted on the join columns (merge join). The QO typically won't pick a merge join if it needs to sort a lot of data. That you're getting a spill on that sort suggest that your row estimations are off, the QO thinks it has fewer rows than it does, so it picks a merge join with sort and requests too small a memory grant, so the sort spills to disk.
There are circumstances where it'll still get it wrong even if the estimates and other calculations are right. There's a fix in a CU. https://support.microsoft.com/en-us/kb/3088480
Thank you for that link. I will look it up.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply