Maintenance Question
-
Hi,
I have a Question regarding the Maintenance Task of my Production Databases:-
Created a Job for doing the Maintenance activities on all of the production databases.The Job Performs the following activities on all the databases:-
1. DBCC CHECKDB
2. DBCC CHECKCATALOG
3. DBCC UPDATEUSAGE
4. sp_updatestats
5. Rebuilding all the Indexes.
I am a little bit confused whether to rebuild the Indexes first and then Update the statistics.
Production databases are Heavily used during the day time with Lots of Inserts/Updates/Deletes.
-
Actually, in this case I think updatestats doesn't matter as much as the updateusage, since this updates the information stored in the sysindexes table and is quite likely to change when you rebuild your indexes. If I were doing this all in the same job, I'd rebuild the indexes, update usage, then update statistics in that order.
I also prefer to schedule index rebuilds pretty close to a full DB backup in case something goes awry in the process so I can recover if needed. You may also want to build in failure points if either the CheckDB and CheckCatalog fail or report errors before going on to do your rebuilds and re-indexing. Just my thoughts.
My hovercraft is full of eels.
-
I thought about my reply and just wanted to add that how you use update statistics depends on how you rebuild your indexes. If you're using index defrag, you need to include a separate step to update the statistics as the DBCC doesn't do this on its own. I believe DBCC DBReindex updates the statistics when it executes, so including a separate update in your job schedule is probably unnecessary.
My hovercraft is full of eels.
-
I second him. If you update stats and then rebuild index your sysindexes will be with new set and update stats that you have done will have no effect. better plan it vice versa. but i dont think even that could help as indexes will be built.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
From BOL - DBCC UPDATEUSAGE
Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.
Only need to run ONCE. (Well we hope
 
DBCC DBREINDEX will take the tables offline (locked), so be aware of that fact.
I also believe that it will update the stats after it is done. And if it does, sp_updatestats will not bother to gather stats if it doesn't need to do so.
So I always run sp_updatestats AFTER rebuilding indexes.
My Daily plan is as follows:
1) check db, catalog
2) reorganize indexes
3) update stats
My Weekend Plan is:
1) Check DB/Catalog
2) Cleanup History
3) Rebuild Indexes
4) Shrink DB's (my own process, not MS)
5) Update Stats
Hope this helps
-
Forgot what forum I was in. If you are still using 2000.
I would put the DBCC UPDATEUSAGE AFTER the Rebuild of INDEXES
-
Just one update.....
If we reindex any table the statistics is also get updated.
do the follow. to ensure this
step 1)dbcc show_statistics(tablename,indexname)
observe the "updated" field (date and time)
step 2)reindex that table
now again execute the show statistics
step 3)dbcc show_statistics(tablename,indexname)
and again observe the "updated" field (date and time)
now this will show the updated statistics date and time(that is the time of reindexing)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply