1

Problem

I've been attempting to optimize a query generated from Entity Framework, and noticed that the execution plan shows wildly inaccurate estimations. After a little digging, I noticed that the statistics objects for some of the clustered indexes is very skewed. Below is a snip of the top operators for the query, ordered by actual rows:

snip of actual versus estimated rows in query
(source: imgh.us)

EDIT: Redacted the database name.

The third operator from the top (the one I have selected) is a clustered index seek, and when I look at the statistics object for that particular index I see an absurd amount of skew:

terribly skewed clustered index statistics
(source: imgh.us)

Is there a reason for this? I'm confused as to why SQL Server is only generating 3 steps for this histogram with a full scan. With 225,000+ rows in the table, I would expect to see a more steps with smaller ranges, but what I'm actually seeing is nearly every row included in one step. Rebuilding indexes and updating stats does not redistribute this.

In addition to that particular index, it appears many other clustered index statistics have a similar problem, and provide similarly bad estimations. Another operator in the above list is a clustered index scan on the WorkOrder table, and it also produces a relatively bad estimation. Though it appears this is the cardinality estimator picking 30% of the rows, so perhaps it is an issue with parameter sniffing (or something other than statistics).

Basically, my questions are as follows:

  • What should clustered index statistics look like?
  • Is the above distribution expected?
  • If it is expected, how do I work around these bad estimates?
  • If it is not expected, how do I correct these bad statistics?

EDIT2: Here is the XML Plan: https://www.brentozar.com/pastetheplan/?id=rJTjT4zze

  • What does the actual data distribution look like? What version of SQL Server is this? – Randolph West Nov 22 '16 at 20:50
  • @RandolphWest The clustering key is a unique, auto-incrementing integer, so I guess the distribution is perfectly even. – Garrett Bates Nov 22 '16 at 20:52
  • @RandolphWest Version: Microsoft SQL Server 2014 - 12.0.4213.0 Standard Edition (64-bit) on Windows NT 6.3 – Garrett Bates Nov 22 '16 at 20:53
  • 1
    Can you post the plan XML up to somewhere like brentozar.com/pastetheplan? – Martin Smith Nov 22 '16 at 21:43
4

Your statistics are fine.

It is telling you that you have

  • exactly one row with value 1
  • 1 row with value 213152
  • 1 row with value 213151
  • 213,144 distinct values that are > 1 and < 213,151 (so basically one row for each of the 213,149 possible integer values between that with 5 missing)
  • If the statistics are fine, what is causing the terrible estimates on clustered index seek? I'm really confused by this. – Garrett Bates Nov 22 '16 at 21:49
  • @GarrettBates - Not possible to say based on no query and no plan. – Martin Smith Nov 22 '16 at 21:51
  • Plan is now posted. – Garrett Bates Nov 22 '16 at 21:53
  • 1
    @GarrettBates - The estimated number of executions of that seek is 407.694 the actual is 168796 so that's out by a factor of 400 to begin with. That 168796 seeks produces 157760 rows. So an average of 0.93 rows per seek. There is a residual predicate on the seek that SQL Server apparently imagines is much more selective than it turns out to be in practice i.stack.imgur.com/wXbsb.png – Martin Smith Nov 22 '16 at 22:01

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.