Post reply

DBCC Reindex Question

  • April 24, 2007 at 10:33 am

    #62648

    Hi,

    Can anyone let me know if DBCC Reidex update stats?

    Thanks

     

  • April 24, 2007 at 11:24 am

    #702216

    It doesn't do anything to your statistics, you'll have to run UPDATE STATISTICS or CREATE STATISTICS commands.

  • April 24, 2007 at 11:41 am

    #702217

    The dbcc dbreindex always updates the statistics.  You can confirm this yourself by running this simple test in the pubs database, and looking at the statistics date before and after the dbcc dbreindex

    use pubs
select [Stats_Updated_Date] = stats_date(object_id('dbo.authors'),1)
dbcc dbreindex ('dbo.authors','',0)
select [Stats_Updated_Date] = stats_date(object_id('dbo.authors'),1)

     

  • April 24, 2007 at 11:57 am

    #702220

    Yes the DBCC DBREINDEX updates the statistics automatically. I have tested this.

    Thanks Michael for your help

  • April 25, 2007 at 7:10 am

    #702361

    Yes it will update stats....

  • April 26, 2007 at 9:24 am

    #702642

    what is difference between sql server 2000 and 2005 architecture

    send ans siva.meti@gmail.com plz

  • April 26, 2007 at 11:53 am

    #702726

    If you reindex do not forget the execute sp_recompile for the table(s) affected and sp_refreshview for the view(s) affected. otherwise you will have up to date statistics and stored procedure plans compiled with out of date statistics potentially causing you performance degredation.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • April 27, 2007 at 8:40 am

    #702947

    Right now I run Reindex on all tables and then update stats with fullscan?  Should I be running sp_recompile and sp_refreshview between those two jobs?  This is on sql server 2005.

  • April 27, 2007 at 9:13 am

    #702967

    Not totally sure on 2K5 but on 2K I can answer. That depends on the method of execution. If you do all table REINDEXES followed by all table UPDATE STATS you need to do this after each step. This is because you have a windows (database size and exeuction time are the variables) when the execution plans are not optimal.

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • April 27, 2007 at 9:36 am

    #702979

    if you are using SQL 2005 better you use ALTER INDEX than DBCC DBREINDEX as it would be removed from future version, more over ALTER INDEX does provide online index rebuild, row & page lock options etc..etc.. check http://msdn2.microsoft.com/en-us/library/ms188388.aspx

      

    Regards
    Shrikant Kulkarni

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply