Large query causing 140GB TEMPDB to run out of space. It appears that the hash match may be a problem. Without seeing the query, is there any advice?
Why is the sort after the hash match necessary? Can you create an index that supports it to remove the sort operator at all? How about the missing index SQL Server suggested for table1? Why is table 5 a heap? Can you add a clustered index, or expand the existing non-clustered index so a RID lookup is not required? Can't really get more specific than that without seeing the query (some of these things may just disappear with a rewrite).
Thank you Aaron, I'll look at your advice and return if needed.
A post-execution plan would be more helpful than the current estimated one.
Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.
Answers and Comments
query plan x691
plan explorer pro upload x166
hash match x7
asked: Aug 28, 2014 at 06:20 PM
Seen: 180 times
Last Updated: Aug 28, 2014 at 10:38 PM
How to avoid spill data to tempdb
Where should I start?
OUTER APPLY to external table costs 49% of the entire execution plan
How to remove Hash Match
Any ideas how to optimize this batch from a third party?
Why spill to TempDB
What kind of questions can I ask here, and how does the site work?
Running a query twice, the second one has less IO/cost?
DELETE using INNER JOIN vs EXISTS: which one is better for this scenario
Index not being used
© 2013 SQL Sentry, Inc. All rights reserved.