July 30, 2007 at 2:50 pm
I am relatively new to SQL 2005. I have used SQL 2000 and various incarnations of Oracle. I have run the Database Tuning Advisor. It is recommending that I create statistics on different columns or combinations of columns. I already have indexes created for individual fields, so I assume that it needs this information so it can make best use of the indexes.
My questions are:
1) Is there a way to have SQL 2005 do this statistic computation automatically without having to create specific statistic (objects? I am assuming that a statistic is an object or some physical structure since you create it)?
2) What is the advantage of a statistic object vs. an Index?
3) Are statistics portable between databases?
Thanks for your help.
Brian
July 31, 2007 at 3:21 am
Statistics are used by SQL as a way of knowing what is in a column, set of columns or an index.
When you create a table and query it, providing you have auto update stats on, they will be created as SQL sees fit.
They are used by the optimiser as a way to determine which index to use and how.
They are not a replacement for indexes.
1) Yes, auto update will create stats as well as update them.
2) You cannot compare stats to an index.
3) No, the static DDL is scriptable but it's information is not.
There are some good articles on this site detailing more.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 31, 2007 at 5:57 am
Thanks for the information!
So the statistics not being setup yet by the optimizer probably means it hasn't had enough time or data to do so, or the auto update stats is not set to on.
Thanks again!
Brian
July 31, 2007 at 7:01 am
Yes but more likely that it has not needed them.
On a very large and or very busy system, it's a good idea to run a job monthly / weekly to update them.
We had a 6TB DB which if we did not update every night, ran like a dog the following day. Also on occasion, had to create new ones as SQL complained that they were missing yet did not create them.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 8, 2007 at 3:15 pm
Setting Auto Update Statistics to True doesn't create new Statistics, Setting Auto Create Statistics to True does. Make sure you set both options to true to get the full use out of auto generated statistics in 2005.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply