Statistics

Question

Statistics

David Betteridge

Hall of Fame

Points: 3264
More actions
December 3, 2008 at 5:19 pm

#196690

I have done quite a few experiments with indexes, and now I'm fairly happy with the way query plans change for different types of indexes.
I have now started looking at statistics and their impact, but with less success. It's my understanding that sqlserver will automatically create statistics on single columns if used in query. However, for multiple columns (e.g. price * qty) it won't and you have to create them manually.
Does anyone have a good example where SQLServer will pick a poor plan unless some additional statistics are created. I have had a few attempts but failed.
Thanks in advance
David

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Generally, rather than create statistics, I just create an index. It's a fairly rare circumstance that requires you to manually build and maintain statistics rather than simply have an index. I've never run across it, but one of the consultant types around here may have.

The main concern with statistics, usually, is to be sure the ones you have, especially on your indexes, not individual or compound columns, are maintained. By and large the automatic maintenance works fine, but you'll find that some statistics need to be refreshed more often than the automatic processes supply. Some statistics will need to be refreshed with a full scan, which is not done automatically. That's where my time is frequently spent when dealing with statistics.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

David Betteridge Hall of Fame Points: 3264 More actions · Answer 2

Thanks for the reply.

Out of interest, what is a good indicator that the statistics are out of date? Is it just that the estimated row counts are very wrong in the estimated query plans?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

I wish I knew.

You can look at the date that the statistics were last updated. If it seems to long ago (very scientific) then you can update it. You've got it right that you need to keep an eye on the disparity between the row counts. If, after updating the statistics you don't see a change in behavior of the optimizer, you may also need to do a full scan.

I've been looking and I haven't found a more systematic approach to this yet.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

roger.price-1150775 Default port Points: 1458 More actions · Answer 4

Statistics can play a big part in the execution path SQL chooses even where there is a non-clustered index. You will find a query does a table scan instead of using an index if it estimates (based on statistics) that its quicker to do a table scan than a non-clustered index seek and associated lookups.

Also statistics and indexes based on identity columns, dates or other incrementing values present problems if you don't keep statistics up to date. Consider a big table that has values and statistics up to 1/1/2010. Queries base their execution path on these statistics. If new row/ dates are added and statistics are not updated then the query may think there are no rows with these values and choose an execution plan based on that. I see this a lot especially with big tables where not enough rows get inserted or updated regular to trigger update on statistics.

Paul White SSC Guru Points: 150467 More actions · Answer 5

Complex topic. Rather than try to summarize it here, I will just link to a pretty definitive TechNet article by Eric N. Hanson and Lubor Kollar:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Resurrected two year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 7

GilaMonster (3/19/2010)
Resurrected two year old thread.

Ha. Never noticed that! :laugh: :blink: :ermm: :blush:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi