Update statistics
-
Hello,
I have created a maintenance plan for Update Statistics in sql server 2005 production server. My question is What will be the recommended schedule for this job. Daily or Weekly?
Do we need to run this job Daily or Weekly? How you guys do?
I aslo Have Rebuild index task, which is running weekly.
-
As I learned from Gail Shaw, the issue of outdated statistics with sql2000 caused by the way your data is added/deleted/updated shouldn't be an issue with sql2005 anymore. Its statistics engine should be able to cope with the load.
So you can skip that task.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
thanks,
No need to run the update statistics job for ever or can we run weekly?
-
If you have the maintenance window to run it (with a full scan) every night, then there really is no harm in doing so. Most of the time, it's a matter of finding the time you can run it that's the problem.
So, yes - if you can fit it in every week then run it weekly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
The statistics are really important for the query optimiser. The rebuild index job will update them for your indices but the non-indexed fields can be important too. We've got one system with very badly skewed data and terrible indexing (vendor-supplied and contractually banned from changing them) and it is very sensitive to the stats on a couple of tables (queries that normally take 30-60 seconds take around 10 minutes, and we've confirmed that it's different execution plans), so we update stats on those tables every night.
While the stats are generally going to be OK this is one job that it doesn't hurt to run more often than necessary: nonblocking, doesn't change data, pretty low disk/CPU utilisation on default settings. For 99+% of systems running it weekly would be ample.
-
Yep, statistics are vital.
If you update them automatically, you might get a performance hit on the processes that force the update.
If you do it manually, you need to pick a quiet time.
Either way, they MUST be done!
.
-
klnsuddu (4/15/2009)
thanks,No need to run the update statistics job for ever or can we run weekly?
With sql2000 I used to run sp_updatestats and dbcc updateusage (0) with count_rows after the weekly index rebuilds.
After migrating a db to sql2005 , we perform all db maintenance, including an actual sp_updatestats and dbcc updateusage (0) with count_rows .
After that I only run sp_updatestats and dbcc updateusage (0) with count_rows a couple of times a year.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
It really depends on the system. We have a few tables on a couple of databases where, due to some issues, we're running update stats a couple of times a day. A lot of our other systems are once a week, or once a month.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
ALZDBA (4/15/2009)
As I learned from Gail Shaw, the issue of outdated statistics with sql2000 caused by the way your data is added/deleted/updated shouldn't be an issue with sql2005 anymore. Its statistics engine should be able to cope with the load.When did I say that?
DBCC UPDATEUSAGE isn't required on SQL 2005 any longer, as the 'bugs' around the space-used metadata is fixed.
Manually updating statistics may be required if the automatic updates aren't happening often enough, and that's as true on 2008 as it was on SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi Gail, it must have been on 2009-03-10.
That's the date I modified my rebuild proc.
My "my posts" history only goes back til 2009-03-11
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I know I've said that UPDATEUSAGE isn't required (in the discussion of the corruption article), but I don't recall ever saying that stats updates are never required.
If anyone knows where I said that please let me know so I can go back and change it. My posting history doesn't go much past the beginning of this month.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Certainly no blame Gail
Chances are I misinterpreted the advise and may have taken it to be applicable for as well dbcc updateusage as well as sp_updatestats.
Once again proves we're human after all
Strange SSC hasn't got a search feature in "my posts" on date or so.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply