Statistics

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Statistics

Statistics

Rate Topic

Display Mode

Topic Options

Author

Message

David Betteridge

Posted Wednesday, December 03, 2008 5:19 PM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 739, Visits: 1,566

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

Post #613303

Grant Fritchey

Posted Thursday, December 04, 2008 5:31 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #613572

David Betteridge

Posted Thursday, December 04, 2008 8:02 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 739, Visits: 1,566

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?

Post #613713

Grant Fritchey

Posted Thursday, December 04, 2008 8:11 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #613721

roger.price-1150775

Posted Thursday, March 18, 2010 11:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 28, 2010 11:46 AM
Points: 7, Visits: 64

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.

Post #885750

Paul White

Posted Friday, March 19, 2010 6:06 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:43 PM
Points: 10,989, Visits: 10,533

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #886270

GilaMonster

Posted Friday, March 19, 2010 6:10 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

Resurrected two year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #886275