ITPub博客

首页 > 数据库 > SQL Server > INDEX REBUILD和INDEX REORGANIZE和UPDATE STATISTICS是否涉及Sch-M的案例分析

INDEX REBUILD和INDEX REORGANIZE和UPDATE STATISTICS是否涉及Sch-M的案例分析

原创 SQL Server 作者:lusklusklusk 时间:2021-01-12 13:32:13 0 删除 编辑

总结

1、等待SCH_M的操作一般是涉及DDL的语句
2、等待SCH_S的操作一定是它前面的操作还在执行DDL并持有SCH_M还没有结束
3、 重新生成索引online on有三个阶段,开始阶段占用表的一个S锁,索引操作的主要阶段占用表的一个意向共享 (IS)锁,结束阶段占用表本身的一个Sch-M锁
4、重新生成索引online on和删除索引它们的快结束阶段,如果这个快结束阶段的过程比较长,则会一直持有表对应的SCH_M锁,此时对该表执行WITH (NOLOCK)查询也被会阻塞。
5、ALTER INDEX REORGANIZE重新组织索引使用的系统资源最少,并且是联机操作。也就是说,不保留长期阻塞性表锁,且对基础表的查询或更新可以在ALTER INDEX REORGANIZE事务处理期间继续进行。 不更新表或索引的统计信息,只对叶子级别的索引进行碎片整理,不对树干级别的索引进行碎片整理
6、ALTER INDEX REBUILD重新生成索引 会删除并重新创建索引。这可以联机完成,也可以脱机完成,重新生成索引联机执行(ON),则索引操作期间可以用此表中的数据进行查询和修改数据。默认为OFF。 更新索引本身的统计信息但是不更新表的统计信息,对整个索引进行碎片整理
7、ALTER INDEX REORGANIZE不会占用表本身的架构修改锁(SCH_M)
8、UPDATE STATISTICS会占用架构修改锁(SCH_M),但是占用的是表对应的MD的SCH_M而不是表本身的SCH_M
9、一旦某表的S锁被P1占用,则其后的会话P2如果需要占用表的SCH_M锁,则P2被P1堵塞,再其后的会话P3如果需要占用表本身的SCH_S比如select table,P3不堵塞,再其后的会话P4如果需要占用表本身的SCH_M或表对应MD的SCH_M,则P4被P2堵塞,这样就是P4被P2堵塞,P2被P1堵塞
10、一表正在index rebuild online操作时,无法同时对表执行index reorganize操作,否则会报错The indexes on table "XXX" cannot be reorganized because there is already an online index build or rebuild in progress on the table.
11、 sys.dm_tran_locks的request_mode、resource_associated_entity_id、request_status、resource_type四个字段可以看到一个会话需要请求的锁类型、需要请求的对象的ID、该请求的当前状态、需要请求的对象的资源类型(OBJECT表本身、METADATA表的元数据、DATABASE表对应的数据库名称、FILE表对应的文件、PAGE表对应的页、HOBT表对应的堆或B树)





https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15
SQL Server 数据库引擎在表数据定义语言 (DDL) 操作(例如添加列或删除表)的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间,Sch-M 锁将阻止对表进行并发访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。
某些数据操作语言 (DML) 操作(例如表截断)使用 Sch-M 锁阻止并发操作访问受影响的表。
SQL Server 数据库引擎在编译和执行查询时使用架构稳定性 (Sch-S) 锁。 Sch-S 锁不会阻止某些事务锁,其中包括排他 (X) 锁。 因此,在编译查询的过程中,其他事务(包括那些针对表使用 X 锁的事务)将继续运行。 但是,无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。
所有查询,包括在基于行版本控制的隔离级别下运行的事务,都在编译和执行期间获取 Sch-S(架构稳定性)锁。 因此,当并发事务持有表的 Sch-M(架构修改)锁时,将阻塞查询。 例如,数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。 查询事务,包括在基于行版本控制的隔离级别下运行的事务,都会在尝试获取 Sch-S 锁时被阻塞。 相反,持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。


https://docs.microsoft.com/zh-cn/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
所有查询(包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询)都会在编译和执行过程中获取 Sch-S(架构稳定性)锁。 因此,当并发事务持有表的 Sch-M(架构修改)锁时,将阻塞查询。 例如,数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。 所有并发查询(包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询)都会在尝试获取 Sch-S 锁时被阻塞。 相反,持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。


https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15
ON | OFF } <as applies to rebuild_index_option>
指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。 默认为 OFF。

ON
在索引操作期间不持有长期表锁。 在索引操作的主要阶段,源表上只使用意向共享 (IS) 锁。 这样,即可继续对基础表和索引进行查询或更新。 操作开始时,将对源对象保持极短时间的共享 (S) 锁。 操作结束时,如果创建非聚集索引,将对源持有极短时间的 S 锁;当联机创建或删除聚集索引时,或者重新生成聚集或非聚集索引时,将获取 SCH-M(架构修改)锁。 对本地临时表创建索引时,ONLINE 不能设置为 ON。
在索引操作的主要阶段,源表上只使用意向共享 (IS) 锁。 索引重新生成开始时表上需要一个 S 锁,联机重新生成索引结束时表上需要一个 Sch-M 锁。 不过两个锁都是短的元数据锁,特别是 Sch-M 锁必须等待所有阻塞事务完成。 在等待期间,Sch-M 锁在访问同一表时阻止在此锁后等待的所有其他事务

OFF
在索引操作期间应用表锁。 创建、重新生成或删除聚集索引、空间索引或 XML 索引或者重新生成或删除非聚集索引的脱机索引操作将获得对表的架构修改 (Sch-M) 锁。 这样可以防止所有用户在操作期间访问基础表。 创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。 这样可以防止更新基础表,但允许读操作(如 SELECT 语句)。


重新组织索引

使用最少系统资源重新组织索引。 通过对叶级页以物理方式重新排序,使之与叶节点的从左到右的逻辑顺序相匹配,进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值。




手工执行alter index all on dbo.secmaster rebuild with (online on)的spid是713,被548堵塞,548只是一个select操作但是存放在一个事务里,该事务没有释放,该事务持有表本身的sch_s,713执行index rebuild online on的结束阶段需要表本身的sch_m,所以713被堵塞
DBCC INPUTBUFFER (713)
EventType    Parameters    EventInfo
Language Event    0    alter index all on dbo.secmaster rebuild with (online on)

DBCC INPUTBUFFER (548)
EventType    Parameters    EventInfo
Language Event    0    exec  [dbo].[Alerts_GetSymbolData]

select nt_username,hostname,program_name,cmd,spid,blocked,lastwaittype,waitresource from sys.sysprocesses where spid in (713,548)
nt_username    hostname    program_name    cmd    spid    blocked    lastwaittype    waitresource
wondawebprocess PANALERTKSTG1     jTDS               SELECT  548    0    ASYNC_NETWORK_IO   
lukes       WONCNFORIDB     SSMS - Query    ALTER INDEX    713    548    LCK_M_SCH_M     TAB: 22:1771153355:0



取消713上面alter index rebuild 的操作,再手工执行alter index all on dbo.secmaster reorganize spid仍是713,没有堵塞,可以正常执行完毕,548事务没有释放,该事务持有表本身的sch_s,713执行index reorganize不需要表本身的sch_m,所以713不被堵塞
DBCC INPUTBUFFER (713)
EventType    Parameters    EventInfo
Language Event    0    alter index all on dbo.secmaster reorganize

DBCC INPUTBUFFER (548)
EventType    Parameters    EventInfo
Language Event    0    exec  [dbo].[Alerts_GetSymbolData]

select nt_username,hostname,program_name,cmd,spid,blocked,lastwaittype,waitresource from sys.sysprocesses where spid in (713,548)
nt_username    hostname    program_name    cmd    spid    blocked    lastwaittype    waitresource
wondawebprocess PANALERTKSTG1   jTDS            SELECT      548    0       ASYNC_NETWORK_IO
lukes           WONCNFORIDB     SSMS - Query    DBCC   713  0  WRITELOG



713上面alter index reorganize的操作完毕后,再手工执行update statistics dbo.secmaster仍是713,没有堵塞,可以正常执行完毕,548事务没有释放,该事务持有表本身的sch_s,713执行update statistics不需要表本身的sch_m,只需要表对应的MD的sch_m,所以713不被堵塞
DBCC INPUTBUFFER (713)
EventType    Parameters    EventInfo
Language Event    0      update statistics dbo.secmaster

DBCC INPUTBUFFER (548)
EventType    Parameters    EventInfo
Language Event    0    exec  [dbo].[Alerts_GetSymbolData]

select nt_username,hostname,program_name,cmd,spid,blocked,lastwaittype,waitresource from sys.sysprocesses where spid in (713,548)
nt_username    hostname    program_name     cmd    spid    blocked    lastwaittype    waitresource
wondawebprocess PANALERTKSTG1   jTDS            SELECT  548    0       ASYNC_NETWORK_IO
lukes           WONCNFORIDB     SSMS - Query    UPDATE STATISTIC    713  0    RESERVED_MEMORY_ALLOCATION_EXT






select nt_username,hostname,program_name,cmd,* from sys.sysprocesses where spid in (517,384,608,498,816) or nt_username='lukes' and dbid=22
nt_username    hostname    program_name     cmd    spid    blocked    lastwaittype    waitresource    dbid
wondawebprocess  PANALERTKSTG1   jTDS          SELECT               384    0    ASYNC_NETWORK_IO                                                                                        6
SQLPROCESS       STAGINGDB11     SQLAgent - TSQL JobStep (Job XXAD : Step 2)  CREATE INDEX         498    517    LCK_M_SCH_S  TAB: 22:1771153355:0 22
SQLPROCESS       STAGINGDB11     SQLAgent - TSQL JobStep (Job YYA7 : Step 1)  ALTER INDEX          517    384    LCK_M_SCH_M  TAB: 22:1771153355:0 5
SQLPROCESS       STAGINGDB11     SQLAgent - TSQL JobStep (Job ZZ02 : Step 5)  INSERT               608    498    LCK_M_S              KEY: 22:844424932360192 (44a4d8a0238a) 22
lukes       WONCNFORIDB     SSMS - Query                             UPDATE STATISTIC    744    517    LCK_M_SCH_M          MD: database_id = 22 STATS(object_id = 1771153355, stats_id = 75), lockPartitionId = 0     22
lukes       WONCNFORIDB     SSMS - Transact-SQL IntelliSense         SELECT              769    498    LCK_M_S              KEY: 22:562949958270976 (73d3e88aa61a)   22
lukes       WONCNFORIDB     SSMS - Transact-SQL IntelliSense         SELECT              779    498    LCK_M_S              KEY: 22:1125899909070848 (056183c7bee9)  22
lukes       WONCNFORIDB     SSMS - Query                             ALTER INDEX         814    816    LCK_M_SCH_S          MD: database_id = 22 STATS(object_id = 1771153355, stats_id = 75), lockPartitionId = 0  22
SQLPROCESS       STAGINGDB11     SQLAgent - TSQL JobStep (Job WWCA : Step 1)  UPDATE              816    744    LCK_M_SCH_S          MD: database_id = 22 STATS(object_id = 1771153355, stats_id = 75), lockPartitionId = 0  22

备注:alter index rebuild with (online on)会话是814被堵塞,814等待816的update对应表object_id=1771153355的MD的LCK_M_SCH_S ,816等待744的UPDATE STATISTIC表object_id=1771153355的MD的LCK_M_SCH_S,744等待517表object_id=1771153355的MD的LCK_M_SCH_M ,517等待384表object_id=1771153355表本身的LCK_M_SCH_M ,384就是一个查询但是占用了表本身的SCH_S

select request_session_id,request_lifetime,request_type,request_mode,resource_associated_entity_id,request_status,resource_type,resource_subtype,resource_description
from sys.dm_tran_locks where resource_database_id=22 and request_session_id in  (517,384) order by 1
request_session_id    request_lifetime    request_type    request_mode    resource_associated_entity_id    request_status    resource_type    resource_subtype    resource_description
384    0    LOCK    Sch-S    35670239    GRANT    OBJECT
384    0    LOCK    Sch-S    1771153355    GRANT    OBJECT
517    536870912    LOCK    Sch-M    1771153355    WAIT    OBJECT

517    33554432    LOCK    Sch-M    1771153355    GRANT    OBJECT    INDEX_OPERATION
517    0    LOCK    S    0    GRANT    DATABASE    DDL
517    33554432    LOCK    Sch-S    0    GRANT    METADATA    DATA_SPACE    data_space_id = 3                                                                                                                                     
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 89
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 88
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 87
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 86
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 85
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 84
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 83
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 82
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 81
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 80
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 79
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 78
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 77
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 76
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 75
517    0    LOCK    Sch-S    0    GRANT    METADATA    STATS    object_id = 1771153355, stats_id = 75
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 74
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 2
517    0    LOCK    Sch-S    0    GRANT    METADATA    INDEXSTATS    object_id = 1771153355, index_id or stats_id = 1

备注:真正的堵塞是第三行517对应WAIT这一行,517等待的表1771153355本身的Sch-M,表1771153355本身的Sch-s被第二行384拿着没有释放

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2749291/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    430
  • 访问量
    752937