Post reply

Maintenance Question

  • July 16, 2007 at 7:45 am

    #68861

    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.

    Kindest Regards,

    Jeetendra

  • July 16, 2007 at 9:38 am

    #719717

    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.

  • July 16, 2007 at 9:58 am

    #719724

    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.

  • July 16, 2007 at 10:43 pm

    #719864

    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

  • July 17, 2007 at 7:17 am

    #720013

    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 &nbsp 

    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

  • July 17, 2007 at 7:25 am

    #720018

    Forgot what forum I was in.  If you are still using 2000. 

    I would put the DBCC UPDATEUSAGE AFTER the Rebuild of INDEXES

  • July 17, 2007 at 9:35 am

    #720090

    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