SQL Server Performance

Index Rebuilding and Table Locking

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by KrelianXgs, Aug 21, 2006.

  1. KrelianXgs New Member

    I'm aware that if you drop and create a Clustered Index that there will be a lock put on the table. This makes sense. I've also read that when you use DBCC DBREINDEX that Shared locks are put on the table during the rebuild of the Nonclustered Indexes.

    My question is... would the following also put a shared lock on a table? Keep in mind that the original index was also Nonclustered.

    CREATE NONCLUSTERED INDEX MYINFO
    ON TRANSLOG (T_MYINFO ASC)
    WITH
    FILLFACTOR = 75,PAD_INDEX,DROP_EXISTING
  2. waqar Member

    Hi,

    If i am not wrong for your case, you need to explicitly specify ONLINE while performing indexing.


    ________________________________________________
    Be great in act, as you have been in thought.
  3. Haywood New Member

    IIRC, the OFFLINE/ONLINE switches are available only in Enterprise edition...

Share This Page