December 12, 2007 at 5:28 am
Hi Experts,
I am sorry I sound dumb to myself but I have never worked in a environment where I ever needed to update statistics Primarily cause (i) I never knew I had to which was cause I never realized I had to (ii) I only knew they had to be updated but never knew when and what decides the reason to update them ?
But I wish to know what is the criteria to update statistics. Should they be updated each day cause even rebuild index is a costly operation ??? Any pointers please
December 12, 2007 at 5:59 am
Generally statistics are automatically updated by SQL server. The only time it's necessary to update them manually is when the auto update is disabled (which it should never be) or on larger tables where the threshold for an auto update is large.
I only update stats manually if I know that out of date stats on a particular table is causing a problem with a query. The most commonly affected are tables with clustered indexes of a dateinserted column and lots of range queries on that column.
You can see when a statistic was last updated with the Stats_date function.
SELECT OBJECT_NAME(object_id), name, STATS_DATE(object_id,index_id) FROM sys.indexes
Index rebuilds will update statistics. Index defragment won't
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply