Does DBCC REINDEX "LOCK" the table

[btturner]

In SQL Server 2000 (sp3), does DBCC REINDEX physically LOCK the table?

One DBA tells me it does, the other DBA tells me you can still execute queries against a table being REINDEXed.

 

[dbomrrsm]

not a full answer but leads you to believe that DBCC REINDEX does seem to hold long term locks but does seem to suggest that querys can still be run (although without use of the index !

What is the difference between DBCC INDEXDEFRAG and DBCC REINDEX?

Answer:

Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.

[blue]DBomrrsm[/blue]

 

[SQLBill]

Here's an answer from experience.

I ran DBCC DBREINDEX on a 200+GB database. Not a single user could use the database until the reindex was complete. PERIOD.

-SQLBill

Posting advice: FAQ481-4875

 

[dbomrrsm]

that seems pretty conclusive Bill

[blue]DBomrrsm[/blue]

 

[SQLBill]

Yeah. And it was a real pain. Luckily, I prewarned everyone it might happen. They were down for 6 1/2 hours. But we had to do it.

-SQLBill

Posting advice: FAQ481-4875