Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Statistics Explained Expand / Collapse
Author
Message
Posted Tuesday, May 29, 2007 8:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229

Without going out and buying yet another book on SQL Server 2005 by Kalen Delaney, can any one provide me with a good explaination of statistics and how the work and are used by the query engine without going into a lot of detail?

Thanks




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #369536
Posted Tuesday, May 29, 2007 9:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:24 PM
Points: 1,006, Visits: 557

Here's my brief answer... 

Statistics are simply data SQL Server collects about a column's data selectivity,  uniqueness and counts of particular values in that column.  Stats exist on every index, however stats can also exist on columns that are not indexed.





A.J.
DBA with an attitude
Post #369564
Posted Tuesday, May 29, 2007 10:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
Adam has a good explanation, but I'll add mine:

Statistics are a way of guessing what the distribution of data is in a column. So if you have age, SQL "samples" the data and gets some idea of how many people are < 10, 11-20, 21-30, etc. Note these are my numbers, not really what SQL would use. It would just have an "idea" of the distribution. That way it can "guess" whether an index will help the query run quicker or if it's better to just scan the table.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #369582
Posted Tuesday, May 29, 2007 10:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229

Along these lines, how much effort should I put in to understand statistics?  Would getting Inside Microsoft SQL Server 2005: Query Tuning and Optimization be a worthwhile investment?  Just curious as I have invested a lot in a variety of SQL Server 2005 books over the last few months.

Thanks.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #369589
Posted Wednesday, May 30, 2007 6:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

If you plan to be a DBA for a significant portion of time, invest the time in learning about query tuning.  I would recommend you spare the week to take a class if you can get your company to pay for it.

It will do two things that are really important.  First, it will teach you how to performance tune and troubleshoot slow queries in your databases.  Second, it will give you a much better understanding of what SQL Server is doing with the query you have passed it so you can more intelligently make decisions on what to do and what not to do.

You may never specifically use performance tuning and query optimization, but understanding how SQL creates execution plans will be a great help in database design if you have to build anything new.

Post #369859
Posted Wednesday, May 30, 2007 7:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229

I think I have a pretty good understanding about building and tuning queries, and the use and creating of indexes.  What I am not sure about is statistics.  In working on slow performance of our PeopleSoft Finance system after upgrading to SQL Server 2005, I ran an hour long trace and used that with DTA.  It recommended we build 35 indexes and a lot more multi-column statistics.  We have implemented those recommendations and have seen a significant improvement in performance.  I can understand how the indexes help, but how do the statistics?  If you are doing this manually, how do you decide if a statistic is more appropriate than index?  Without going into a 3 to 30 page explaination, how are statistics used to improve query performance?

Or should I invest in the book I mentioned earlier by Karen Delaney?

Also, I work for a school district, training funds are very sparse.

 




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #369868
Posted Wednesday, May 30, 2007 8:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 6:34 PM
Points: 750, Visits: 3,159

In my limited understanding : statistics are used by the optimiser to determine the most efficient query plan, including which indexes to use and how.  Using an index to identify rows might be extremely efficient if it's a unique index on a large table, but extremely inefficient if there is only a small number of distinct values in that index.  On non-indexed fields the stats presumably help the optimiser determine what kinds of join are appropriate and which order to process each element in the plan.

If you haven't done so already, take a look at the properties for some of the stats in your tables - it helps to visualise the kind of information they represent.

I think statistics are definitely one of the least-understood aspects of performance tuning.  Not sure about the book as I haven't read it, but I would expect there to be some blogs about stats somewhere. 

 

Post #369921
Posted Wednesday, May 30, 2007 10:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229

Based on that, would you say it is worth learning more about statistics?  I am just curious how much time and effort I should put into some of this or if I should just "put my faith in Microsoft" and that DTA knows what's best.

Thanks




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #369975
Posted Wednesday, May 30, 2007 12:44 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 6:34 PM
Points: 750, Visits: 3,159

I think it's handy to understand if you have the time and inclination, but for the most part you don't need to know it in detail - just knowing that they are beneficial is probably sufficient.  Having said that, it's another tool to use in performance tuning and I have occasionally made queries run faster due to that knowledge.  If you just want to get your toes wet, knowing when statistics become out of date is useful. 

Here's a document I found:

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

 

Post #370026
Posted Thursday, May 31, 2007 12:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 43,010, Visits: 36,169

 I am just curious how much time and effort I should put into some of this or if I should just "put my faith in Microsoft" and that DTA knows what's best.

The DTA does not always know best. I've found that it tends to go overboard.

When I use it (which isn't often) what I'll generally do is implement its suggestions 1 by 1 and test the performance improvement, then I implement the ones that have the most impact.




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 #370168
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse