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

總結

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樹)

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 鎖的併發事務。



所有查詢（包括那些帶有 READUNCOMMITTED 和 NOLOCK 提示的查詢）都會在編譯和執行過程中獲取 Sch-S（架構穩定性）鎖。 因此,當併發事務持有表的 Sch-M（架構修改）鎖時,將阻塞查詢。 例如,資料定義語言 (DDL) 操作在修改表的架構資訊之前獲取 Sch-M 鎖。 所有併發查詢（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示執行的查詢）都會在嘗試獲取 Sch-S 鎖時被阻塞。 相反,持有 Sch-S 鎖的查詢將阻塞嘗試獲取 Sch-M 鎖的併發事務。



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拿著沒有釋放