首页 > 代码库 > INDEX--创建索引和删除索引时的SCH_M锁
INDEX--创建索引和删除索引时的SCH_M锁
最近有一个困惑，生产服务器上有一表索引建得乱七八糟，经过整理后需要新建几个索引，再删除几个索引，建立索引时使用联机（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了吧。
--=====================================================================
再次感谢群友”一川晴雨“，妹子为你而上
