Here's my plan I'm having trouble with: https://www.brentozar.com/pastetheplan/?id=r168tltbe

I'm betting this is something easy I'm not seeing. I have a query that is running very slowly because instead of the optimizer pulling in the 16 rows from the 'child' table, it says "pull all the rows from the child table between 26550557 and 28078855". I rebuilt the statistics on the index, which worked in one of my environments, but not another.

26461290        32767       1       16043               2.042449
26652512        65544       1       31523               2.079244
26868906        65535       1       31715               2.066372
26950626        32765       4       15484               2.116055
27097668        32762       6       16202               2.022096
27220249        32810       4       15978               2.053448
27301782        32770       1       15161               2.161467
27572159        32782       4       15406               2.127872
27758980        32793       4       16564               1.979775
27864880        32781       4       16356               2.004219
27922320        32771       4       16075               2.038631
28092976        32789       5       16462               1.991799

You can see from my DBCC SHOW_STATISTICS partial results above, there is more than 1 row in that range.

EDIT: Posted correct range of stats (per Martin) and a non-commented query. Thanks everyone for your responses!

EDIT2: Here's the stats_stream. Also, even though is says sorting is 44% of the effort, removing that sort speeds it up by a negligible amount. Because stats_stream is so large, here's a link to it: https://www.dropbox.com/s/fq6gu1jvb1q7tg8/Stats_Stream.csv?dl=0

EDIT3: DDL Script - https://www.dropbox.com/s/fq6gu1jvb1q7tg8/Stats_Stream.csv?dl=0

  • Can't see the whole query. What happens if you break the subquery out into a table variable, or temp table, then query that? – Nic Nov 15 '16 at 18:59
  • Please remove the commented lines and post another plan or provide the query text in your question. – Joe Obbish Nov 15 '16 at 19:02
  • 3
    the part of the histogram you have shown us ends at 26.2mn. Your query looks at a range starting at 26.5mn – Martin Smith Nov 15 '16 at 19:35
  • 3
    To add to what Martin has said, this is called an ascending key problem - the range you are searching is outside the bounds that the current stats knows about. Try updating stats again, perhaps using FULLSCAN, until your RANGE_HI_KEY bumps. – Aaron Bertrand Nov 15 '16 at 19:45
  • 1
    This is more complicated than I thought. I suspect that this is an issue with join cardinality estimates. It's not clear to me if there is a statistics issue on one or both tables. Can you provide the table and index DDL for both tables? Can you also provide the stats stream output for PK_parent_plan and IDX_child_plan_Search? I think that you already provided IDX_child_plan_Search but it's important to label it and to give the table DDL. Otherwise SQL Server will say that the STATS_STREAM is corrupt when I try to use it. – Joe Obbish Nov 16 '16 at 18:05

Your Answer

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

Browse other questions tagged or ask your own question.