x

Statistics on the databases

Good Morning all,

I am actually trying to learn as to how and when to use the statistics in SQL SERVER 2008.

CREATE STATISTICS  MYSTATS 
ON HumanResources.Department(DepartmentID) 
WITH SAMPLE 5 PERCENT;
go

Once i do this part of the command, i am not sure where to look for the created statistics. Is there any special table in the sys.tables associated with the Statistics. And as far as I have done my research, i have seen having statistics created on a specific table or an index, Wondering if there is an option to do them, on a specific schema in the database and a specific database as a whole.

Then the next part of the statement would be to update this statistics, I have seen sample update statistics statements, but i am not sure how would i update my previously created statistics, presented in the above CREATE STATISTICS statement.
I want to learn the flow of process that is used in this concept of creation of statistics. The process namely including, create, update, use and drop the statistics.I understand that, this is a long question, at least directing me to any document also would be very helpful.

Thank you all once again.

more ▼

asked Apr 28, 2010 at 11:24 AM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The best practice is to set the database to auto create and auto update statistics. Unless you've hit a performance issue that is pretty extreme, this is the best way to manage statistics.

To see what statistics you have, use DBCC SHOW_STATISTICS. You can specify the object within a database that you want to look at.

You really should not try to manually manage statistics. The system does the vast majority of the work for you. You can, and should, schedule a manual update of the stats using sp_updatestats. That will update all the statistics, using sampling, within a given database. In some, rare, instances you may need to do targeted statistics updates with a full scan. You can do this with the UPDATE STATISTICS command.

more ▼

answered Apr 28, 2010 at 11:45 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

Thank you Grant, another aditional question to clarify my reason for asking this question.. so for a DB which would encounter about a quater a million transactions every day, should statistics be a concern, even when set to auto create and auto update?
Apr 28, 2010 at 11:58 AM Katie 1
Yes, but probably only in certain tables or certain indexes. For the most part the automatically created & maintained statistics will work fine.
Apr 28, 2010 at 01:54 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x985
x126
x33

asked: Apr 28, 2010 at 11:24 AM

Seen: 987 times

Last Updated: Apr 28, 2010 at 11:31 AM