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
what will happen in case of non clusterd index.?