I have problems understanding the following query plan: https://www.brentozar.com/pastetheplan/?id=SyoD_PosP

First there is the bottom right index seek. The estimation says over 22 million rows but the actual data is less than 2 million rows. My guess is, the estimation is based on just the rows matching the Tag column and a look at the index's histogram seems to corroborate that. Am I right in thinking that no assumption is made about the result of matching those rows to the predicate of the join and that this is what results in the differnce between the estimation and the actual result?

Second and more importantly, the Hash Match estimates that it will return 140k rows when in actuality it is about twelve times that. This leads to the sort operator spilling into tempdb a lot, if I understand what is happening correctly. Why is the estimate so low? And is there any way to improve it?

Update: Coincidentally, the values of Record.Tag are exactly [10399, 10438]. In principle there is no guarantee that it is a continous interval, tho. I tried J.D.'s suggestion and split the query into four queries and UNION ALL'ed them together. No improvement there. Next I tried filling a temporary table with the Record rows of those Tags:

SELECT * INTO #tmprecord
FROM Record 
WHERE (Record.Tag>=10399 AND Record.Tag<=10438) 

SELECT DateAndTime, r.Reading, r.Tag 
FROM RecordSet 
INNER JOIN #tmprecord AS r 
ON RecordSet.RecordSetId = r.RecordSet 
WHERE RecordSet.Collector=10090
AND RecordSet.DateAndTime >= '20201101 00:00:00' 
AND RecordSet.DateAndTime <= '20210101 00:00:00'
ORDER BY r.RecordId

DROP TABLE #tmprecord

The estimation here seems better (four times instead of twelve times), but the spill takes about the same number of tempdb pages (76xx) and even more memory.

  • Does your table have 2m rows? Could you share your index and table and SQL Server version? Also, you can check your statistics which are out of date. – Yunus UYANIK Dec 7 '20 at 11:43
  • @YunusUYANIK One table has 660m rows, the other 250m. Version is SQL Server 2016 SP2 with CU15. I used UPDATE STATISTICS on both involved indexs before running the query. – Florian Dec 7 '20 at 12:53

I believe this is due to all the ORs in your WHERE predicate on the Record.Tag field. It's causing a bad cardinality estimate. That is a rather long predicate and set of ORs, could you try breaking it into multiple smaller queries for different sets of Tags and the. UNION ALL the results of each query back together? It may help with the cardinality estimate.

You could also then change the ORs for a range when you break it up to make it less verbose at least, such as:

WHERE Record.Tag >= 10400 AND < 14006
  • Interesting, but I just tried rewriting it with UNION ALL (for two Tag values) and it gives me exactly the same erroneous estimation as with OR. Sadly I cannot change the query to a range, as it is produced by a program that I cannot modify. – Florian Dec 7 '20 at 12:47
  • Two Tag values might be hard to see any meaningful change when there's about 40 in your predicate. If you're able to test all of them and changing to a range (since 40 UNION ALLs is probably overly complex of a query for its own good too) even just for more clues on what the issue is, it may be helpful even if you can't change the program that generates the query. – J.D. Dec 7 '20 at 12:54
  • Also not sure if possible, still worth a test, is getting all your Record rows for those specific Tags into a temp table first, that way when you join the temp table to RecordSet you don't need to mix in that large predicate. This might simplify the query enough as well. – J.D. Dec 7 '20 at 12:56
  • 1
    In this case it is 20670 <= Record.Tag <= 20709. Record.Tag>=20670 AND Record.Tag<=20709 seems to get an even worse estimation than the original query with 39 ORs. Currently I am trying different number of UNION ALLs and will write it up when done. I will try the version with a temp table afterwards. – Florian Dec 7 '20 at 13:10
  • This might be getting into the nitty-gritty and I could be misremembering, but it might also be more performant (in edge cases) to use > and < instead of >= and <= since >= is similar to an OR in itself. So you may want to also try with: Record.Tag > 20669 AND Record.Tag < 20710. – J.D. Dec 7 '20 at 14:54

You should update your statistics like below, not just indexes use for the whole table. These might take time.


I think you need a better index. Look at the selectivity on your columns and create indexes if you don't have;

CREATE INDEX Tag_Reading_RecordSet_RecordId 
ON Record (Tag,Reading,RecordSet,RecordId)

CREATE INDEX Collector_DateAndTime_RecordSetId
ON RecordSet (Collector,DateAndTime,RecordSetId)


CREATE INDEX DateAndTime_Collector_RecordSetId
ON RecordSet (DateAndTime,Collector,RecordSetId)

You can use the below query but doesn't affect the performance. Just better to read.

FROM RecordSet
  ON RecordSet.RecordSetId = Record.RecordSet
WHERE Record.Tag IN (10400,10406,10401,10402,10404,10399,10405,10403,10408
AND RecordSet.Collector = 10090
AND RecordSet.DateAndTime >= '20201101 00:00:00'
AND RecordSet.DateAndTime <= '20201201 00:00:00'
AND Record.Reading IS NOT NULL
ORDER BY Record.RecordId
  • After just updating the statistics of the two tables the estimations even got a bit worse. I will look into the indexes tomorrow. – Florian Dec 7 '20 at 15:16

In case you're not aware, a significant part of the time spent in this query's execution is spent waiting on the client to consume the results. In your case it might be SSMS, as you're testing changes. Take a look at Elapsed Time vs the ASYNC_NETWORK_IO waits:

  <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="9" WaitCount="16222" />
  <Wait WaitType="IO_COMPLETION" WaitTimeMs="282" WaitCount="509" />
  <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="525" WaitCount="403" />
  <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="3935" WaitCount="144" />
  <Wait WaitType="CXPACKET" WaitTimeMs="24167" WaitCount="12741" />
<QueryTimeStats CpuTime="6117" ElapsedTime="6702" />

The query runs for 6.7 seconds, but 3.9 seconds are spent waiting for the client to consume the results. So really the query runs for less than 3 seconds without that bottleneck. That might still be unacceptable performance, but I thought it was worth pointing out.

Ideally you could remove the ORDER BY, which eliminates the problem by removing the need for the sort in the plan. Even if the estimates are correct, and you eliminate the spill, the sort is not going to be super fast. The spill is only ~60 MB, which shouldn't add a ton to the query's runtime if you have fairly quick storage.

You can get an idea of how much the spill is slowing you down by adding OPTION (MIN_GRANT_PERCENT = X) to the end of the query. Increase X (it needs to be a number between 0 and 100) until the spill goes away.

You mentioned in a comment that you can't change the query, though.

As a last resort, you could try adding a "dummy" nonclustered columnstore index to one of the tables involved, just to get batch mode. It might get you out of the spill as well:

ON dbo.RecordSet (DateAndTime);
  • Thank you, I was indeed not aware that the WaitStats informations were available. Maybe this should be a seperate question but your reply got me thinking about the sorting. Is there a way to preserve an index ordering through the Hash Match? After the repartition I would have expected the output of the Hash Match is ordered by RecordSetId but if I change the query to ORDER BY RecordSet.RecordSetId there still is a Sort in the end. – Florian Dec 9 '20 at 12:24
  • @Florian As far as I know, there's no way to preserve order through a hash join from the optimizer's point of view, so it will introduce a sort to handle that order by. In theory, the order of the lower (probe) input is preserved, but the hash spills can mess that up, so it can't be depended on. MERGE JOIN can preserve order, but that would be tough to get in this case I think (without introducing even more sorts). – Josh Darnell Dec 9 '20 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.