reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt

Bad Estimate

I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, and AccountNumber [in that order]. Most of the estimates look decent except this Merge Join operator that's between two Compute Scalars. This particular operator returns ~53,000% of the estimated rows. The operators that follow that (Concat and the Table Insert) also have poor estimates but not quite as bad. Still, this insert takes roughly a minute or two on it's own, and this isn't dealing with nearly as many rows as it could in production.

I'm a little stumped as to why that particular operator is wildly underestimating the number of rows. Is that first bad estimate causing the following estimates to be way off as well? Is that slowing the insert down (this is a select into; an insert into a table + with(tablock) with a clustered index has performed just as poorly)? Why is that estimate so bad, and how can I improve it? I suspect that it has something to do with the ordering of the key columns in the clustered index but I feel like I'm just missing something because I'm basically stumped. Any advice is much appreciated. Thank you.

1 Answer 1


Doesn't look like it. The time stats for that operator don't show it running for long,

<RunTimeCountersPerThread Thread="0" ActualRows="2302947" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5417" ActualCPUms="5406" />

and the bulk of the time is spent inserting 2,302,947 rows into the temp table.

Do you expect that many rows? The query in the plan is truncated, so we can only see the first two queries in the UNION ALL.

  • Thanks, David. Apologies for the truncated query; not sure why that happened, maybe a char limit. The third/last query is nearly the same as the second w/slightly different filter. Anyway, I do think that we might be expecting to see that many rows being inserted here. I have a feeling that if it isn't the estimate (as you seem to indicate) then it's simply the large number of rows being inserted. I would like to ensure that the logic I was given (this is a refactor of an inherited query) is correct given the large result set; reducing that would be great.
    – scarr030
    Sep 29, 2021 at 18:09
  • Execution plans have a limit on the embedded SQL query length. Sep 29, 2021 at 18:26

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.