Will NoLock Hint allow you to query a table which undergoing offline reindexing?

| 1 Comment

 

Recently I was delivering a workshop and this question came from one of the participants

Will NoLock Hint allow me to query a table which is undergoing offline reindexing?

My instant answer to that was NO, we cannot query a table with or without NOLOCK hint while we are performing Offline reindexing but I wasn’t sure so I performed this test which confirmed my instincts

Note: Online Reindexing is the option available starting SQL 2005 Enterprise Edition which would be ideal way for reindexing since locks are taken momentarily and during the reindexing operation, table can be accessed normally without NOLOCK hint. We are talking offline reindexing in this case which might be used by many customers for whom online reindexing is not feasible.

CREATE TABLE DUMMY
(
a int,
b varchar(25)
)

CREATE CLUSTERED INDEX IDX_A on DUMMY(A)

DECLARE @i int = 0
WHILE (@i < 1000000)
BEGIN
insert into DUMMY values(@i,’Testing’)
insert into DUMMY values(1000000-@i,’Testing’)
set @i=@i+1
END

Next we started the Reindexing of the Clustered Index from one of the connections in order to remove the fragmentation.

WHILE(1=1) — Using Reindexing in While Loop to repro the issue

ALTER INDEX IDX_A ON DUMMY REBUILD

While the Reindexing was on, I opened a new connection and tried to query the table using NOLOCK hint as shown

SELECT * FROM DUMMY WITH (NOLOCK)

And As expected found the above query to be blocked. To further investigate what it was waiting on. I used the following query and found that select query was waiting on LCK_M_SCH_S which was acquired by Reindexing session

select * from sys.dm_os_waiting_tasks
where session_id > 50

session_id exec_context_id wait_duration_ms     wait_type      blocking_session_id  resource_description

———- ————— ——————– ————– ——————-  —————————————————————————————————-

54         0               784                  LCK_M_SCH_S       52               objectlock lockPartition=0 objid=2059154381 subresource=FULL dbid=7    id=lock93f0800 mode=Sch-M

52         0               783                  CXPACKET              52                   exchangeEvent id=Pipe113bc2c0 WaitType=e_waitPipeGetRow nodeId=0

52         0               783                  CXPACKET              52                   exchangeEvent id=Pipe113bc2c0 WaitType=e_waitPipeGetRow nodeId=0

 

So as we see session_id 54 is blocked by session_id 52 which is performing the reindexing. The Lock on which session id 54 is waiting on is Schema Modication lock (LCK_M_SCH_S) which is acquired by Reindexing Job.

To further confirm this I used sys.dm_tran_locks to identify the lock acquired by session 52

select * from sys.dm_tran_locks
where request_session_id = 52

 

resource_type    resource_subtype         resource_database_id  request_mode  request_type  request_status  request_reference_count

—————————————– ——————–  ——————————————————————-

OBJECT                                                                                     7                     IX            LOCK          GRANT           1                      

DATABASE         DDL                                                              7                     S             LOCK          GRANT           1                      

DATABASE                                                                               7                     S             LOCK          GRANT           1                      

OBJECT                                                                                     7                     IX            LOCK          GRANT           1                      

OBJECT                                                                                     7                     IX            LOCK          GRANT           1                      

DATABASE         ENCRYPTION_SCAN                                   7                     S             LOCK          GRANT           1                      

DATABASE         ENCRYPTION_SCAN                                   7                     S             LOCK          GRANT           1                      

DATABASE         ENCRYPTION_SCAN                                   7                     S             LOCK          GRANT           1                      

METADATA         DATA_SPACE                                             7                     Sch-S         LOCK          GRANT           5                      

KEY                                                                                            7                     X             LOCK          GRANT           0                      

HOBT                                                                                        7                     Sch-M         LOCK          GRANT           1                      

ALLOCATION_UNIT  BULK_OPERATION_PAGE                 7                     S             LOCK          GRANT           1                      

KEY                                                                                            7                     X             LOCK          GRANT           0                      

PAGE                                                                                         7                     X             LOCK          GRANT           0                      

KEY                                                                                            7                     X             LOCK          GRANT           0                      

KEY                                                                                            7                     X             LOCK          GRANT           0                      

METADATA         INDEXSTATS                                               7                     Sch-S         LOCK          GRANT           4                      

METADATA         STATS                                                          7                     Sch-S         LOCK          GRANT           2                      

OBJECT                                                                                     7                     S             LOCK          GRANT           2                      

OBJECT                                                                                     7                     S             LOCK          GRANT           2                      

OBJECT                                                                                     7                     Sch-M         LOCK          GRANT           1                      

OBJECT           INDEX_OPERATION                                       7                     Sch-M         LOCK          GRANT           1                      

KEY                                                                                           7                     X             LOCK          GRANT           0      

 

This confirms our hypothesis and hence proved that we will not be able to query a table using NOLOCK hint when it undergoing offline reindexing

 

Parikshit Savjani
Premier Field Engineer, Microsoft

One Comment

  1. what will happen in case of non clusterd index.?

Leave a Reply

Required fields are marked *.