June 19, 2002 at 10:56 am
this question may sound silly, but I was keeping wondering....
what's the difference between 'update statistics', 'dbcc dbreindex', and 'drop and re-create the indexes manually'?
and which should be used under a specified situation?
Could someone here be patient to explain in details?
Thanks!
June 19, 2002 at 4:09 pm
DBCC DBREINDEX is the same thing as dropping and recreating the indexes except it does all on a single table at one time and you do not have to know the index structures.
update statistics is the statistical density of a table or a representation of the number of unique values. Periodically if you have auto update statitics turned on the db will update these which help the query engine determine the index that will require the least work to get the resultset based on the amount of unique values. Turning this on, on a small database is usually bennificial, but on large databases it may not do as often since at least 5% of the data must change for it to feel the need to run. Doing it manaually helps ensure that the indexes are represented with the more current statistical representation which means the query engine will have better decision making options. If these get too far out based on the number of changes then a more usefull index may not get used as opposed to another when the db grows.
I hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 4:48 pm
Books On Line has some good information on each of these. I'd read about it there.
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply