Statistics

Question

Statistics

litu deb

SSC Eights!

Points: 818
More actions
August 5, 2008 at 10:38 am

#191986

Hi All,
I went through different website but not undersatanding what Statics means.what it does?
i am really confused what actually this is and why we need to update statistics.Can anyone help me..
Thanks,
Litu deb

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

This is probably overly simplified, but here are the basics. Statistics are a histogram of the values in an index or column that the query optimizer uses to determine the fasest access path to the data. If you do not keep statistics up to date you will not get the most efficient query plans.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Jack has a good explanation. Basically statistics are meta data about the indexes and data. They help ensure the query optimizer performs well.

GSquared SSC Guru Points: 260824 More actions · Answer 3

In it's simplest form, statistics is a list of what percentage of the rows are different from each other, in each column.

For example, if you have 100 rows, and 10 are A, 10 are B, 10 are C, and so on, then each entry is 10% of the column.

If you have 100 rows, and 15 are A and 5 are B, 15 are C and 5 are D, and so on, then it's still 10% average.

On the other hand, if you have 100 rows, and 50 are A, and 2 each are B through Z, then it's just about 4% average, but it's going to be a bit more complex because of those 50 rows that are A.

When SQL is trying to figure out what indexes to use, it looks at the statistics. It wants the lowest percentage it can get, because that means it's going to find it the fastest.

All of that is a bit of an over-simplification, but it gets you going in the right direction.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

litu deb SSC Eights! Points: 818 More actions · Answer 4

Thanks a lot everyone.

I got now some idea about statistics.when I run the following command i can see the last updated date.

DBCC SHOW_STATISTICS ( )

So how to know when i have to update any statistics for any particular table.

Thanks in advance..

Litu Deb.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Unless you are managing an extremely busy system and are noticing a negative impact I recommend using autoupdate statistics. SQL Server will update the stats when a certain percentage of rows have changed.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

litu deb SSC Eights! Points: 818 More actions · Answer 6

Thanks for your suggestion.

I would like to know is there any way to verify my tables statistics are updated.

Regds,

Litu Deb

GSquared SSC Guru Points: 260824 More actions · Answer 7

When you display the execution plan for a proc/script/whatever, it will warn you if any of the statistics are out-of-date.

Also, you can set up a maintenance task to run automatically to update the statistics on a schedule. Then you pretty much don't have to worry about it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 8

If you have the database option enabled for automatically updating the statistics, SQL Server will automatically update them UNLESS you've specified the STATISTICS NORECOMPUTE clause for a particular index.

If this is the case, you must manually update these statistics by issuing the UPDATE STATISTICS statement against that statistic OR rebuild that index. Rebuilding an index causes the stats for that index to be updated during the process. This is even true for rebuilding an index on a partitioned table. Reorganizing an index does NOT update the statistic, unfortunately.

The following whitepapers describe how indexes are created, structured, and maintained for SQL Server

SQL Server 2000

http://msdn.microsoft.com/en-us/library/aa902688(SQL.80).aspx

SQL Server 2005

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

Both those articles will give you more than you would ever want to know about statistics within SQL Server. However to sum up the articles and answer your question specifically, You can use the DBCC SHOW_STATISTICS command to see information on a particular stat.