Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'd like to understand a section of the query plan that seems to mess up my query.

enter image description here

The picture shows that there's a clustered index seek, followed by compute scalar, followed by sort.

The part that I do not understand, is if Compute Scalar's Estimated Rows are 327,561, than why is Sort's Estimated Number of Rows only 14? Does it assume Compute Scalar will only return 14 results even though it's input is estimated at 327,561 rows?

I tried running update statistics on the register_hdrs table WITH SAMPLE 100 PERCENT, it didn't seem to change behavior at all.

The suggested indexes improve performance of the clustered index seek, but the sort still estimates number of rows at 14, and so the query plan remains same.

What I need it to do is pick a different path, there's another table in query which when picked speeds up query by about 14 times (it goes from running in 14-15 seconds to 1 second). I believe the under-estimate on the number of rows in the sort is the problem, and is why the slow plan gets picked.

I'm not looking to change the query, because application generates it in a specific way, and it wouldn't be possible to change it at this point. I'm looking into how to force SQL Server to give a better estimate for the rows in the sort, which I believe would result in SQL Server picking a different execution plan.


migration rejected from Jul 21 '13 at 14:33

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Martin Smith, Paul White, Mark Storey-Smith, Aaron Bertrand Jul 21 '13 at 14:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

I agree with AndriyM. In the meantime, @Alex, can you edit the question and add the query that produces these plans and the table structure (CREATE TABLE statement and indexes)? – ypercubeᵀᴹ Jul 19 '13 at 9:42
Can you upload the entire *.sqlplan somewhere? What is to the left of the sort? I assume that it must be under an operator that can potentially stop requesting rows before the whole input is read (e.g. TOP / semi join / anti semi join). – Martin Smith Jul 19 '13 at 10:03
Voting to close anyway. It is impossible to answer whether you can get a better estimate on the rows that will be requested from the sort without knowing anything about the predicate to the left of the sort. I can't think why the predicate wouldn't occur before the sort anyway except if it is on e.g. ROW_NUMBER or something in which case getting the application to generate a better query may be the only option anyway. – Martin Smith Jul 20 '13 at 10:01
Please add the information being requested and we can look into re-opening the question. – Aaron Bertrand Jul 21 '13 at 14:33