June 6, 2005 at 8:17 am
I run these commands nightly on a database that had a high amount of inserts and needs to be optimized nightly. Here is the code that I run nightly, but it is starting to take too long to run. Which commands are redundant?
DBCC DBREINDEX (tablename)
UPDATE STATISTICS tablename WITH FULLSCAN, ALL
DBCC UPDATEUSAGE(database, tablename) WITH COUNT_ROWS
DBCC CHECKTABLE(tablename)
DBCC CHECKDB(database)
DBCC CHECKALLOC (database)
Thanks in advance to any responses to this post.
June 6, 2005 at 12:23 pm
DBCC CHECKTABLE(tablename) is not needed since DBCC CheckDB checks every table in the database already
A.J.
DBA with an attitude
June 6, 2005 at 2:47 pm
Actually IIRC, the checkalloc can be dropped as well if this is SQL Server 2000. I believe the checktable and checkalloc functions are both incorporated in checkDB with 2000. This would not apply to SQL Server 7.
My hovercraft is full of eels.
June 7, 2005 at 2:08 am
Also you dont have to reindex and update the statistics for all your tables daily.
You should check at the end of the days the fragmentation status of each of your tables (before reindexing them of cours) for let's say a week.
So you can see which are the tables requiring reindexing. If your tables are not highly fragmented, then in your place I wouldn't do all those task daily, but weekly and even weekly only for the tables requiring maintenance.
In addition as A.J and sswords have told you while you are doing a checkdb you don't have to do a chacktable and checkalloc because those are included whithin checkdb.
Bye
Gabor
June 7, 2005 at 6:45 am
Hi,
one thing the guys from microsoft told us was to use "sp_updatestats", and to additionally use the stored procedure "sp_createstats". Seems that sql server 2000 does not allways create statistics, even if the checkbox in database properties is turned on.
regards karl
Best regards
karl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply