Take the 2-minute tour ×
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.

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the optimizer to make good estimates - in other words, is it no longer "selective" enough? With this particular customer's database/table, my query plan estimations are way off compared to others.

The particular statistic that I am concerned with comes from the table's PK/CLUSTERED INDEX. It is a multi-column statistic containing an int (ParentId) and a smalldatetime (TimeStamp).

When I issue a DBCC SHOW_STATISTICS('SomeTable', 'PK_SomeTable'), I get the following output (histogram omitted - but I can post it if it will help):

Name                    Updated              Rows       Rows Sampled    Steps   Density         Average key length  String Index    Filter Expression   Unfiltered Rows
PK_SomeTable            Jan 31 2014 10:59AM  181170887  181170887       200     2.022617E-05    8                   NO              NULL                181170887

All density     Average Length  Columns
0.0004892368    4               ParentId
5.519651E-09    8               ParentId, TimeStamp

Most of my queries are performed using the combination of both these columns (ParentId and TimeStamp). The small all density value shows the selectivity of this pair - obviously since it's the PK.

(1) The histogram appears to only show the ParentId column. Am I missing something here? Are both columns being accounted for?

(2) If I take 200,000,000 rows / 200 steps, I essentially have 1,000,000 rows defined in each histogram step. This seems large enough that it could cause estimation issues, right? What about sort spills to tempdb and things like that?

(3) Would manually created statistics/filtered statistics be an avenue to explore? How does one go about deciding what type of filter to apply?

share|improve this question
3  
A histogram is built on only the leading column. The only thing multi-column statistics add is the extra level of average density information. –  Paul White Feb 1 at 8:59
    
Did you have any luck with this? I see below that you had some interaction away from the thread. –  GaTechThomas Mar 24 at 21:02
    
I have not reached a conclusion on this. In this particular case, the misestimation is causing a sort to spill to tempdb. It was suggested that I look into filtered statistics, but I have not pursued that avenue. –  John Russell Mar 25 at 13:06

2 Answers 2

With a table that large I would consider partitioning. Unfortunately I can't answer your specific questions (1-3) but in general one of the benefits of using partitioned views (instead of native partitioning) is that the individual tables within the partitioned view are considered separate objects and have their own statistics each with 200 steps. Here is a post where Kimberly Tripp of SQLSkills recommends for large tables that you consider not only partitioning or partitioned views but combining the two.

In case you don't know a partitioned view is one where you have multiple tables each holding a portion of the data and a view on top with UNION ALLs to combine the tables together.

Here is another of Kimberly's blogs on statistics in case you are interested. It should help you answer some of your other questions.

Here's an article of Connor Cunningham regarding statistics: Statistics, Damned Lies, and Statistics – What is Statman?

share|improve this answer

I've got clients with tables that are over 2B rows and the statistics are able to be used to generate good execution plans. Tables with just a few hundred million rows should be no problem.

If you really need more statistics on the table you could put filtered statistics on the table, filtered by year or month depending on what's needed so that you can get more data for the optimized. But this requires that you manually update the statistics.

For your large statistic that you have now I'd recommend having trace flag 2371 turned on. This way the statistic updates more often. You'll also want to turn on async stats updates.

Are you seeing specific performance problems with queries using this statistic?

share|improve this answer
    
I have a stored procedure that queries this table (clustered index seek) and on this client's database, the execution plan shows that the estimated rows are significantly less than the actual rows. As a result, I think the underestimation is affecting a sort operator down the line and causing it to spill to tempdb. I thought the statistics might be the culprit behind the misestimation. As mentioned above, the statistics have been updated WITH FULLSCAN. –  John Russell Feb 2 at 15:39
    
Thanks for the tips on flag 2371 and async stat updates. I think those will certainly help our larger tables. However, I think there may still be an issue with this particular query... –  John Russell Feb 2 at 15:46
    
How signigicantly less? Can you post the plan you are looking at (or email it to me if you can't post it)? –  mrdenny Feb 28 at 19:35
    
mrdenny, sorry for the delayed response. I sent you the execution plan via email to your dcac.co account. Let me know via email if you have any additional questions. Any recommendations would be appreciated. Thanks! –  John Russell Mar 4 at 12:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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