INDEX--建立索引和刪除索引時的SCH_M鎖

標籤：style   blog   http   color   使用   os   

最近有一個困惑，生產伺服器上有一表索引建得亂七八糟，經過整理後需要建立幾個索引，再刪除幾個索引，建立索引時使用聯機（ONLINE=ON）建立,查看下伺服器負載（磁碟和CPU壓力均比較低的情況）後就選擇業務時間建立，但是到刪除索引時卻遇到問題：阻塞，刪除索引需要架構修改鎖（SCH_M）,有阻塞很正常，雖然查詢使用NOLOCK提示降低了對其他會話的影響，但還是會在頁或表上產生一些意圖共用鎖（IS），這些意圖共用鎖與SCH_M無法相容，因此阻塞無可避免，悲催的是在該表上多個會話重複執行查詢且該查詢執行時間超過100秒，根本無法找到一個完美的時間空擋來執行刪除操作。想著白天業務高峰不成，我晚上來，為此好幾晚半夜爬起來做嘗試刪除操作，最後還是跟業務確認後使用KILL幹掉所有長時間阻塞會話才得以刪除成功。

囉囉嗦嗦一堆，問題來了：在聯機建立索引時，同樣需要架構修改鎖(SCH_M),為什麼這就不阻塞呢？

感謝群裡大神“一川晴雨”提醒，聯機建立索引和刪除索引雖然都使用架構修改鎖(SCH_M),但是作用的對象卻是不同的，因此影響也不同，那就讓我們來驗證下吧

首先準備測試資料

--=============================--建立測試資料庫CREATE DATABASE DB2GOUSE db2GO--建立測試表CREATE TABLE TB1004(    ID INT IDENTITY(1,1) PRIMARY KEY,    C1 BIGINT)GO--匯入資料，本次測試匯入100w資料INSERT INTO TB1004()SELECT OBJECT_ID FROM SYS.all_columnsgo 2000--查詢匯入的資料量SELECT  COUNT(1) FROM TB1004

接下來就是準備抓起鎖，我們使用XEVENT來完成

--建立擴充回話XE_LockMonitor--增加監控事件sqlserver.lock_acquired和sqlserver.lock_released--並按鎖類型和資料庫名來過濾資料CREATE EVENT SESSION [XE_LockMonitor] ON SERVER ADD EVENT sqlserver.lock_acquired(    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text)    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N‘DB2‘) AND [mode]=(2))),ADD EVENT sqlserver.lock_released(    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text)    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N‘DB2‘) AND [mode]=(2))) ADD TARGET package0.event_file(SET filename=N‘D:\DB\XE_LockMonitor.xel‘)WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)GO

建立完後查看該擴充事件屬性

有了擴充事件會話，然後我們啟用啟用它

--=============================================================--啟動回話ALTER EVENT SESSION [XE_LockMonitor] ON SERVERSTATE=START;

啟動擴充會話後，選擇“監視即時資料”，然後在彈出的視窗中，先配置要顯示的資料，在標題列右鍵選擇“選擇列”，選擇以下我們關心的列，並儲存。

 

準備好測試環境，是時候開測資料啦

--========================--離線建立索引--耗時5秒CREATE INDEX IDX_OBJECTIDON TB1004(ID)WITH(ONLINE=OFF,MAXDOP=1)GO--========================--刪除索引DROP INDEX IDX_OBJECTID ON TB1004--========================--聯機建立索引--耗時53秒CREATE INDEX IDX_OBJECTIDON TB1004(ID)WITH(ONLINE=ON,MAXDOP=1)GO--========================--刪除索引DROP INDEX IDX_OBJECTID ON TB1004

擴充會話捕獲到的資料：

使用SELECT OBJECT_NAME(1269579561)查看發現OBJECT對象為TB1004

由上面的資料我們不難發現這麼幾個結論：

1.無論聯機還是離線建立索引時，架構修改鎖的對象為HOBT和METADATA

2.刪除索引操作時，架構修改鎖的對象為OBJECT：TB1004

3.聯機索引建立耗時53秒，離線索引建立耗時53秒（在沒有外部資料操作情況下），離線索引建立耗時遠小於聯機索引建立

--============================================================

是時候揭曉謎底啦

我們開啟一個會話，執行下面SQL:

--使用NOLOCK訪問表SELECT * FROM TB1004 WITH(NOLOCK)

再另外開啟一個會話，執行下面SQL:

--====================================================--使用SP_LOCK來擷取鎖--尋找某個對象上的鎖DECLARE @T TABLE( SPID BIGINT, DataBaseID INT, OBJECTID BIGINT, IndexID BIGINT, LockType VARCHAR(20), LockResource NVARCHAR(200), LockMode NVARCHAR(20), LockStats NVARCHAR(200))INSERT INTO @TEXEC SP_LOCKSELECT SPID,DataBaseID,DB_NAME(DataBaseID) AS DataBaseName,OBJECTID,OBJECT_Name(OBJECTID,DataBaseID) ObjectName,IndexID,LockType,LockResource,LockMode,LockStatsFROM @TWHERE OBJECTID=OBJECT_ID(‘TB1004‘)

我們發現，即使使用NOLOCK提示，仍需要SCH_S鎖，這就是為什麼DROP INDEX時被阻塞的原因，因為在同一個資源（object_ID:1269579561）上有互斥的SCH_M鎖和SCH_S鎖。

而對於聯機索引建立，索引建立會話使用的SCH_M鎖的對象與NOLOCK查詢的使用的SCH_S鎖的對象不是同一個，因此不會阻塞。

相信諸位看官到此應該深深地明白WHY了吧。

--=====================================================================

再次感謝群友”一川晴雨“，妹子為你而上