December 3, 2008 at 5:19 pm
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
December 4, 2008 at 5:31 am
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
December 4, 2008 at 8:02 am
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?
December 4, 2008 at 8:11 am
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
March 18, 2010 at 11:03 am
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.
March 19, 2010 at 6:06 am
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
March 19, 2010 at 6:10 am
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
March 19, 2010 at 6:18 am
GilaMonster (3/19/2010)
Resurrected two year old thread.
Ha. Never noticed that! :laugh: :blink: :ermm: :blush:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply