1

I have a table with a field that has a very large variance on a particular field that is often searched. The number of records associated with any value in the field highly differs.

My estimated number of rows are always off, before refreshing statistics it's highly overestimated after refreshing it's highly underestimated. What can I do to improve it?

I update the statistics with full scan. The code is usually called from within a procedure but now I tested it outside.

Improve this question
2
  • Take a look here if you haven't yet. Throwing it out there because I'm not sure how you're testing the code outside the proc: brentozar.com/archive/2014/06/…
    – Erik Darling
    Jul 13, 2017 at 18:15
  • How many rows/columns do you typically return from the table? How many times per day does the stored procedure run? Would an estimate based on density be acceptable (guessing not)?
    – Joe Obbish
    Jul 14, 2017 at 0:21

1 Answer 1

Reset to default
1

You could create additional statistics for the data. Include a WHERE clause so each new statistics object references only a subset of the table. By bucketing the rows appropriately (big, medium, small?) the filtered statistics will be less bad than the whole-table ones, leading to better plans.

The predicate must match that used in queries. Depending on your settings there may be more system activity for stats maintenance. Of course this assumes the table contains (or could change to contain) some combination of columns on which rows can be meaningfully aggregated.

Improve this answer
1
  • I've had some rather mixed results with filtered statistics and stored procedures. Similar to filtered indexes.
    – Erik Darling
    Jul 14, 2017 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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