May 14, 2009 at 5:44 am
I have one question regarding SQL Server 2005 Update Statistics............We have Auto Update Statistics =True is by default in SQL 2005 then is there any use of scheduling a seperate job for update statistics??? One more thing is that how can we check that our update statistics actually done........bcoz when we update statistics by script or manually, it'll just show command completed successfully???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 14, 2009 at 6:51 am
The the vast and overwhelming majority of systems, you should do both.
The algorythm that decides whether or not to update statistics is pretty good, but it's not perfect. Most people run a statistics update process on a regular basis. Defining "regular" for any given system could vary. Most of my production OLTP systems are getting updated stats once a week, and that works just fine for them. A couple of my systems are getting their stats updated once a day. A few tables on a couple of systems are getting targted statistics updates twice a day. It just depends on how badly the statisitcs age and whether or not the automated processes take care of them well enough.
You can check the age of the statistics by looking at the STATS_DATE function. You can show all the information about statistics using DBCC SHOW_STATISTICS. You can also query sys.stats & sys.stats_columns.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2009 at 7:00 am
I use this script from Job Web
http://weblogs.sqlteam.com/joew/archive/2007/08/31/60316.aspx
It shows you ALL indexes and the date/time they were updated.
Rgds
JL
May 14, 2009 at 9:36 pm
Thanks a lot for reply. Query is really gud at least to see update statistics history.
See sometimes what we usually do on servers, we have schedulded the jobs for Update Statistics....ryt...Now my question is that why we have scheduled this Update Statistics Job while we have this facility (Auto Update Statistics = TRUE) inbuilt in SQL Server 2005.. Now I think my first question should be more clear................. Now is it valid point to schedule our job for Update Statistics or let SQL Server to do it automatically...
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply