Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: dbreindex vs index defrag question

    Does anyone know if dbreindex and index defrag ideally perform the same function? I have been told that index defrag does not hold locks on a table when executed and dbreindex does. Other than this is there any difference between the two functions? My understanding was that dbreindex reindexes the data stored in a table for faster reads and index defrag removes purged data. Am I correct? I am currently running both functions on my SQL server and was advised that I really only need to run the index defrag job. Is this advise correct?

  2. #2
    Join Date
    Jan 2007
    Posts
    56
    http://www.mssqlcity.com/Articles/Ad...gmentation.htm

    You can reduce fragmentation and improve read-ahead performance by using one of the following:

    Dropping and re-creating an index
    =================================
    Best performance, but places an exclusive table lock on the table, preventing any table access by users and shared table lock on the table, preventing all
    but SELECT operations to be performed on it.

    OR

    Rebuilding an index by using the DBCC DBREINDEX statement
    ================================================== =======
    Faster than dropping and re-creating, but during rebuilding a clustered index, an exclusive table lock is put on the table, preventing any table access by
    users. And during rebuilding a nonclustered index a shared table lock is put on the table, preventing all but SELECT operations to be performed on it

    OR

    Defragmenting an index by using the DBCC INDEXDEFRAG statement
    ================================================== ============
    It does not hold locks (or only for very shot time) [i.e. online operation], but takes longer time - works little by little. It is not suggested to use for
    very fragmented indexes

    Hope it helps ...

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    Thanks for the info. So basically running both index defrag and dbreindex is redundant because they perform the same function. I will disable my dbreindex job.


    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

    Remember these are deprecated in 2005. It is also not correct to say that they perform the same function - they perform similar functions.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by pootle flump
    http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

    Remember these are deprecated in 2005. It is also not correct to say that they perform the same function - they perform similar functions.

    HTH
    Wow Pootie...thanks! I read this just in time for it to help me solve the sqlservercentral Question Of The Day!!!
    Quote Originally Posted by sqlcervercentral
    Question: You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?

    Correct Answer: ALTER INDEX with the REORGANIZE option

    You Answered: ALTER INDEX with the REORGANIZE option

    Total Participants: 466

    Total Correct Answers: 196 or 42.1% of participants


    Explanation:
    You should use the ALTER INDEX with the REORGANIZE option because the DBCC commands have been deprecated.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Wow - you are in the top 42.1% of respondants. Congratulations
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2007
    Location
    UK
    Posts
    1
    Hi dsmbwoy,

    DBCC DBREINDEX and DBCC INDEXDEFRAG are not one and the same. DBREINDEX sorts the both internal and external fragmantation, whereas INDEXDEFRAG only assists with internal fragmentation. You might want to take a look at the following link, which explains the differences: http://www.microsoft.com/technet/pro...p.mspx?pf=true

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •