簡(jiǎn)介圍繞 SQL Server 中一個(gè)看似反常的 Deadlock 案例這份資源面向數(shù)據(jù)庫開發(fā)、運(yùn)維與性能優(yōu)化人員系統(tǒng)梳理了死鎖的產(chǎn)生條件與完整分析路徑。文檔從一張同時(shí)帶有聚集索引和兩個(gè)非聚集索引的示例表出發(fā)先復(fù)現(xiàn) update 語句在 rowlock 下的互相阻塞再對(duì)比取消非聚集索引中的 include(d) 字段、將 d 列由 varchar(max) 改為 varchar(200) 兩種調(diào)整從而解釋索引鍵長(zhǎng)度、鎖粒度與死鎖觸發(fā)之間的關(guān)系。分析環(huán)節(jié)覆蓋 DBCC TRACEON(1222) 開關(guān)、SQL Profiler 按 SPID 篩選并捕獲 Locks/TSQL 事件、sp_readerrorlog 讀取死鎖報(bào)告以及通過執(zhí)行堆棧定位等待資源等典型方法能讓讀者形成一套可復(fù)用的死鎖排查思路。壓縮包內(nèi)為 1 個(gè) docx 文檔容量約 694KB內(nèi)容緊湊便于對(duì)照學(xué)習(xí)。該資源已有 326 人學(xué)習(xí)下載適合想深入理解 SQL Server 索引、鎖等待與死鎖機(jī)制的從業(yè)者參考。1. 這個(gè)死鎖怪在哪兒兩條一模一樣的 UPDATE鎖死了自己人先說結(jié)論SQL Server 里兩條完全相同的 UPDATE 語句在并發(fā)執(zhí)行時(shí)發(fā)生了死鎖。更詭異的是把索引里的include(d)去掉或者把d從varchar(max)改成varchar(200)死鎖就消失了。這聽起來像是索引或者數(shù)據(jù)類型的“玄學(xué)”但背后是 SQL Server 執(zhí)行計(jì)劃對(duì)鎖申請(qǐng)順序的決定性影響。這個(gè)案例是我見過最典型的“死鎖不是等行鎖而是等執(zhí)行計(jì)劃里那幾步鎖”的教材。本文從復(fù)現(xiàn)步驟開始完整走一遍 1222 跟蹤、SQL Profiler 鎖事件分析、hobtid 定位索引、執(zhí)行計(jì)劃對(duì)比這幾板斧最終解釋清楚死鎖形成的完整鏈路。適合正在處理 SQL Server 死鎖、或者被“同樣的語句為什么這里鎖那里不鎖”困擾的 DBA 和開發(fā)人員。2. 復(fù)現(xiàn)這個(gè)死鎖從表結(jié)構(gòu)到并發(fā)觸發(fā)腳本2.1 三張索引的物理布局為什么它是死鎖的溫床原案例在 SQL 2008 上復(fù)現(xiàn)我這邊在 SQL Server 2019 兼容級(jí)別 100 的庫上也跑通了。核心在于這張表的結(jié)構(gòu)——它刻意制造了一個(gè)“更新一條記錄但索引鏈條很長(zhǎng)”的場(chǎng)景create table tt( id int identity primary key, a char(36), b char(36), d varchar(max) ) go create index ix_a_bc on tt(a) include(d) create index ix_b_cd on tt(b) include(d) go這里有兩個(gè)關(guān)鍵設(shè)計(jì)。第一id上的主鍵是 clustered index所以表本身按id物理排序更新d字段時(shí)基礎(chǔ)數(shù)據(jù)行的位置是確定的。第二ix_a_bc和ix_b_cd是 non-clustered index而且都在葉子節(jié)點(diǎn)里include(d)了。這就意味著當(dāng)UPDATE修改d時(shí)SQL Server 不僅要改 clustered index 里的數(shù)據(jù)行還得改這兩個(gè) non-clustered index 的葉子頁——因?yàn)樗鼈兊娜~子節(jié)點(diǎn)里存了這個(gè)字段的副本。varchar(max)的存在也很微妙。max 類型在大對(duì)象處理上有特殊的存儲(chǔ)和鎖行為它不會(huì)老老實(shí)實(shí)地把所有數(shù)據(jù)塞進(jìn)索引頁而是可能采用單獨(dú)的大對(duì)象分配單元。這一步給后面的問題埋了個(gè)大雷。所以在動(dòng)手復(fù)現(xiàn)之前先確認(rèn)自己的環(huán)境里表和索引的實(shí)際物理布局而不是看邏輯結(jié)構(gòu)想當(dāng)然。2.2 插入一萬行并定位一條“幸運(yùn)”記錄insert into tt select NEWID(),bbb,ddd go 10000這個(gè)go 10000在 SSMS 里表示執(zhí)行 10000 次插入用NEWID()生成a列的隨機(jī)值。a列是char(36)數(shù)據(jù)庫會(huì)用空格補(bǔ)齊尾部的差異但查詢時(shí)自動(dòng)截?cái)嗨圆挥绊懫ヅ洹２迦胪瓿珊笕〉?10 條記錄的a值作為后續(xù) UPDATE 的定位條件select * from tt where id 10這里有個(gè)值得說明的點(diǎn)這一萬條數(shù)據(jù)中a列的值全部通過NEWID()生成是唯一的而且因?yàn)殡S機(jī)分布ix_a_bc索引的鍵值排序會(huì)比較分散。這種離散性直接導(dǎo)致后續(xù) UPDATE 語句在索引上尋址時(shí)會(huì)經(jīng)歷 B-tree 的逐層定位而不是聚集掃描。我一般會(huì)順手跑一下DBCC SHOW_STATISTICS看看直方圖確保a列的分布能讓優(yōu)化器選擇 Index Seek否則整個(gè)復(fù)現(xiàn)路徑就變了。2.3 兩條死鎖循環(huán)rowlock 與“平均分配”的假象-- 連接 1 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3 -- 連接 2 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3兩個(gè)連接各自開一個(gè)查詢窗口同時(shí)執(zhí)行這段循環(huán)死鎖在幾秒內(nèi)必然出現(xiàn)。with(rowlock)是刻意加上的它限定 SQL Server 只在行級(jí)加鎖避免鎖升級(jí)到頁鎖或表鎖后掩蓋掉真正的問題。while 1 1讓 UPDATE 無限循環(huán)重復(fù)執(zhí)行同一行的更新。在實(shí)際復(fù)現(xiàn)時(shí)不要兩個(gè)窗口都手動(dòng)點(diǎn)執(zhí)行然后碰運(yùn)氣。更穩(wěn)妥的做法是先用一個(gè)連接把循環(huán)跑起來確認(rèn)單連接不會(huì)自鎖再啟動(dòng)第二個(gè)連接。批量測(cè)試時(shí)我會(huì)用兩個(gè)sqlcmd會(huì)話加上START時(shí)間對(duì)齊盡量模擬同一時(shí)刻的并發(fā)提交。這個(gè)場(chǎng)景下循環(huán)體沒有事務(wù)包裹每條 UPDATE 是隱式事務(wù)但死鎖檢測(cè)器依然能捕捉到鎖等待環(huán)。2.4 對(duì)比實(shí)驗(yàn)改動(dòng)索引或類型死鎖為什么消失原案例里另外兩個(gè)測(cè)試構(gòu)成了對(duì)照。測(cè)試二把兩個(gè) non-clustered index 里的include(d)去掉測(cè)試三把d改為varchar(200)。兩者都不再死鎖。在執(zhí)行計(jì)劃層面這兩次改動(dòng)的共同點(diǎn)是UPDATE 不再需要對(duì)三個(gè)索引分別做 X 鎖申請(qǐng)SQL Server 只更新 clustered index 主數(shù)據(jù)行。提示include(d)不是“附帶一個(gè)字段”這么簡(jiǎn)單它讓索引葉子節(jié)點(diǎn)物理包含這個(gè)字段的副本。UPDATE 修改該字段時(shí)所有包含該字段的索引對(duì)應(yīng)的葉子頁都要維護(hù)。維護(hù)的索引越多鎖的生命周期越長(zhǎng)死鎖概率越高。把varchar(max)換成varchar(200)不再死鎖的原因稍微不同后續(xù)章節(jié)會(huì)詳細(xì)展開執(zhí)行計(jì)劃的差異。復(fù)現(xiàn)階段把這個(gè)對(duì)照組跑一遍能很快讓新手體會(huì)到“死鎖不是靠猜是靠執(zhí)行計(jì)劃鎖序”。3. 收集死鎖證據(jù)打開 1222 開關(guān)與 SQL Trace 抓鎖事件3.1 開啟 1222 跟蹤死鎖報(bào)告的“黑匣子”SQL Server 默認(rèn)把死鎖信息寫到錯(cuò)誤日志但只記錄受害者會(huì)話的部分信息。開啟 1222 跟蹤后日志里會(huì)輸出完整的死鎖圖包括兩個(gè)參與進(jìn)程的鎖等待鏈、資源模式、SQL 文本和事務(wù)時(shí)間線。操作如下dbcc traceon (1222, -1)-1表示全局開啟對(duì)所有會(huì)話生效而不是只對(duì)當(dāng)前會(huì)話。開啟后死鎖一旦發(fā)生錯(cuò)誤日志會(huì)記錄一段以deadlock-list開頭的文本。關(guān)閉跟蹤用dbcc traceoff (1222, -1)。注意生產(chǎn)環(huán)境開啟 1222 會(huì)有輕微性能開銷建議只在復(fù)現(xiàn)窗口期內(nèi)開啟用完即關(guān)。sp_readerrorlog是讀錯(cuò)誤日志最快的入口sp_readerrorlog 0, 1, deadlock-list第三個(gè)參數(shù)可以過濾關(guān)鍵字。實(shí)際輸出里的死鎖報(bào)告關(guān)鍵部分長(zhǎng)這樣子deadlock victimprocess5e27708 process idprocess5e27708 ... lockModeX ... spid60 process idprocess5e09dc8 ... lockModeU ... spid54 resource-list keylock hobtid72057594066108416 ... indexnameix_a_bc ... modeU owner idprocess5e09dc8 modeU waiter idprocess5e27708 modeX keylock hobtid72057594065518592 ... indexnamePK__tt__3213E83F10E07F16 ... modeX owner idprocess5e27708 modeX waiter idprocess5e09dc8 modeU這段內(nèi)容能直接看到鎖等待方向spid 60 持有主鍵上的 X 鎖等待ix_a_bc上的 X 鎖spid 54 持有ix_a_bc上的 U 鎖等待主鍵上的 U 鎖。這就是一個(gè)典型的環(huán)狀等待。但 1222 不會(huì)告訴你鎖的申請(qǐng)順序——為什么兩個(gè)進(jìn)程各自持有第一個(gè)鎖之后會(huì)去申請(qǐng)對(duì)方的鎖3.2 通過 SPID 過濾 SQL Profiler 的 Locks 事件要還原鎖的申請(qǐng)順序SQL Trace 是更好的工具。SQL Profiler 新建一個(gè) Trace在事件選擇里勾上 Show all events 和 Show all columns然后從 Locks 類別選Lock:Acquired、Lock:Released、Lock:Deadlock等事件從 TSQL 類別選SQL:BatchStarting和SQL:BatchCompleted。Column Filters 里按 SPID 過濾只保留死鎖涉及的兩個(gè)連接外加后臺(tái)進(jìn)程的 SPID。select spid在死鎖發(fā)生前分別到兩個(gè)連接里執(zhí)行這條語句拿到各自 SPID。原案例里一個(gè)是 54一個(gè)是 60。過濾時(shí)只選這兩個(gè) SPID 加系統(tǒng)進(jìn)程的 ID。Trace 輸出里L(fēng)ock:Acquired 和 Lock:Released 的 Mode、ObjectID、ObjectID2 是判斷鎖類型的入口。ObjectID 是索引對(duì)象 IDObjectID2 是 hobtid也就是 1222 報(bào)告里的那個(gè) 15 位數(shù)字。3.3 hobtid 到索引名的精確映射1222 輸出的keylock hobtid72057594066108416一串?dāng)?shù)字難以直接看出是哪個(gè)索引。常規(guī)做法是查sys.indexes、sys.objects和sys.partitions三張系統(tǒng)視圖的關(guān)聯(lián)select o.name as table_name, i.name as index_name, i.type_desc, p.partition_id from sys.indexes i inner join sys.objects o on i.object_id o.object_id inner join sys.partitions p on p.index_id i.index_id and p.object_id i.object_id where p.partition_id in ( 72057594065518592, 72057594066108416, 72057594066173952 )這條查詢把跟蹤日志里的hobtid轉(zhuǎn)成表名和索引名。partition_id就是 hobtid每個(gè)索引的每個(gè)分區(qū)有獨(dú)立值。注意 SQL Server 2008 以后一個(gè)索引對(duì)應(yīng)一個(gè)分區(qū)時(shí)hobtid和partition_id一一對(duì)應(yīng)如果表做了分區(qū)同一個(gè)索引會(huì)有多個(gè)hobtid排查時(shí)需要結(jié)合partition_number去辨別實(shí)際命中了哪個(gè)分區(qū)。提示把這條查詢里的partition_id換成object_id也適用于sys.dm_db_index_operational_stats這類 DMV 的排查場(chǎng)景能直接看到每個(gè)索引上的等待統(tǒng)計(jì)。3.4 鎖申請(qǐng)時(shí)間線誰先 U 鎖誰后 X 鎖從 Profiler 拉出來的 Lock:Acquired 和 Lock:Released 記錄按時(shí)間排序后一次成功的 UPDATE 的鎖生命周期清晰可見表 3-1一次成功 UPDATE 的鎖申請(qǐng)順序時(shí)間序索引鎖類型會(huì)話動(dòng)作1ix_a_bcUAcquired2PKclusteredUAcquired3PKclusteredXAcquired4ix_a_bcUReleased5ix_a_bcXAcquired6ix_b_cdXAcquired7ix_b_cdXReleased8ix_a_bcXReleased9PKclusteredXReleased從這張時(shí)間線能看到兩個(gè)關(guān)鍵階段。第一階段UPDATE 語句通過ix_a_bc的 Index Seek 定位到符合條件的記錄期間對(duì)ix_a_bc加 U 鎖確認(rèn)記錄存在后再對(duì) clustered index 的主鍵行加 U 鎖然后升級(jí)成 X 鎖執(zhí)行更新。第二階段SQL Server 發(fā)現(xiàn)d字段被兩個(gè) non-clustered index 的葉子頁包含于是回過頭來為兩個(gè)索引補(bǔ) X 鎖維護(hù)索引數(shù)據(jù)。這個(gè)“先更新主表再回來更新索引”的二次尋路過程就是死鎖的溫床。4. 死鎖形成鏈路執(zhí)行計(jì)劃里的 Index Update 與兩步走的陷阱4.1 鎖環(huán)怎么套上的連接 A 等主鍵、連接 B 等輔助索引結(jié)合 1222 報(bào)告和上表死鎖的直接因果關(guān)系能描述得很具體連接 Aspid 54完成第一階段在ix_a_bc上持 U 鎖做了 Index Seek然后準(zhǔn)備對(duì) clustered index 上的目標(biāo)行申請(qǐng) U 鎖時(shí)發(fā)現(xiàn)該行已被連接 B 加了 X 鎖。連接 Bspid 60恰好走完第一階段正在第二階段對(duì)ix_a_bc補(bǔ) X 鎖但ix_a_bc上有連接 A 的 U 鎖。于是連接 A 等主鍵連接 B 等輔助索引形成完整環(huán)形等待SQL Server 死鎖檢測(cè)器挑一個(gè)犧牲品回滾。這里有個(gè)耐人尋味的細(xì)節(jié)連接 B 在第一階段結(jié)束時(shí)釋放了ix_a_bc的 U 鎖連接 A 才申請(qǐng)到 U 鎖然后連接 B 在第二階段又回來搶 X 鎖。也就是說同一個(gè)索引、同一個(gè)鍵的鎖在同一個(gè)事務(wù)里被釋放后又被不同模式重新申請(qǐng)。這個(gè)釋放后重申請(qǐng)的窗口恰恰是另一個(gè)連接插入的時(shí)機(jī)。4.2 鎖申請(qǐng)與釋放的“兩步走”Index Update 與 Lazy Index Maintenanceset statistics profile on go update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3set statistics profile on輸出文字形式的執(zhí)行計(jì)劃每行代表一個(gè)物理操作。測(cè)試一中能看到三個(gè)Index Update它們的父節(jié)點(diǎn)是同一個(gè)Update說明 SQL Server 將主鍵更新、ix_a_bc更新、ix_b_cd更新拆成了三個(gè)獨(dú)立的物理操作。測(cè)試二中輔助索引不包含d字段所以只有一個(gè)Index Update鎖申請(qǐng)鏈路縮短死鎖消失。測(cè)試三中d為varchar(200)時(shí)執(zhí)行計(jì)劃里只有一個(gè) Update 節(jié)點(diǎn)但它的 child 節(jié)點(diǎn)同時(shí)包含三個(gè)對(duì)象的操作說明引擎在一步內(nèi)完成了三處索引維護(hù)。測(cè)試一這種“先更新主表、再逐個(gè)更新二級(jí)索引”的行為在內(nèi)部叫作 Lazy Index Maintenance它把索引維護(hù)推遲到了主表更新之后帶來的代價(jià)就是多階段鎖申請(qǐng)。varchar(200)之所以能避免死鎖本質(zhì)是優(yōu)化器計(jì)算出大對(duì)象不再需要單獨(dú)的行外存儲(chǔ)索引葉子頁可以容納該字段于是選擇了 eager index maintenance——在一個(gè)算子內(nèi)完成全部更新鎖生命周期被壓縮到極小。4.3 為什么 varchar(max) 放進(jìn) include 會(huì)催化這個(gè)鎖環(huán)varchar(max)與普通varchar在存儲(chǔ)上有本質(zhì)區(qū)別max 類型默認(rèn)使用 large object 分配單元數(shù)據(jù)可能存儲(chǔ)在行外。當(dāng)它被include進(jìn)非聚集索引后索引維護(hù)算子需要處理行外數(shù)據(jù)的定位和搬遷這一步比普通定長(zhǎng)字符串的原地更新昂貴得多。優(yōu)化器在代價(jià)估算中發(fā)現(xiàn)行外存儲(chǔ)的更新不值得交付給單個(gè)算子做于是把輔助索引的更新拆成獨(dú)立算子形成了那三個(gè) Index Update。注意不要只關(guān)注死鎖本身。把varchar(max)放進(jìn)索引的葉子節(jié)點(diǎn)本身就意味著每一次UPDATE都要維護(hù) LOB 指針寫入放大效應(yīng)明顯。死鎖只是暴露了這個(gè)問題真正的改進(jìn)方向是索引設(shè)計(jì)不合理。4.4 執(zhí)行計(jì)劃對(duì)比三個(gè)方案的鎖數(shù)量與死鎖概率把三次測(cè)試的執(zhí)行計(jì)劃并排看差異非常直觀表 4-1三次測(cè)試的執(zhí)行計(jì)劃與鎖行為對(duì)比場(chǎng)景d 列類型索引是否 include(d)執(zhí)行計(jì)劃中的 Update 節(jié)點(diǎn)死鎖是否發(fā)生測(cè)試一varchar(max)是1 個(gè) Update 3 個(gè) Index Update是測(cè)試二varchar(max)否1 個(gè) Update 1 個(gè) Index Update否測(cè)試三varchar(200)是1 個(gè) Update內(nèi)部合并更新三個(gè)索引否測(cè)試二的鎖少是因?yàn)檩o助索引里沒有dSQL Server 根本不需要碰它們。測(cè)試三的鎖數(shù)量和測(cè)試一相同但申請(qǐng)順序從“兩步走”變成了“一步到位”鎖等待窗口消失。這個(gè)對(duì)比印證了一件事死鎖的關(guān)鍵變量不是鎖的數(shù)量而是鎖的申請(qǐng)持續(xù)時(shí)間和順序。5. 避坑指南分析死鎖時(shí)最容易翻車的五個(gè)細(xì)節(jié)5.1 只看 1222 報(bào)告就下結(jié)論漏掉鎖申請(qǐng)順序是最大的坑現(xiàn)象1222 報(bào)告清楚顯示了兩個(gè)進(jìn)程各自持有什么鎖、等待什么鎖但看完了還是不知道為什么持有這些鎖也沒法解釋死鎖成因。原因1222 是死鎖發(fā)生瞬間的靜態(tài)快照它不包含鎖的申請(qǐng)時(shí)間線。解決必須配合 SQL Profiler 的 Lock:Acquired 和 Lock:Released 事件按時(shí)間排序還原鎖生命周期才能真正理解死鎖形成鏈路。我從這案例里學(xué)到的習(xí)慣是所有死鎖分析都至少保留一份鎖事件跟蹤否則只能停留在“看到環(huán)”的層面。5.2 用 spid 過濾 Trace 時(shí)漏掉系統(tǒng)進(jìn)程現(xiàn)象Trace 里只看到兩個(gè)死鎖會(huì)話的鎖事件但關(guān)鍵的中間環(huán)節(jié)缺失鎖的時(shí)序斷檔。原因SQL Server 的鎖管理和死鎖檢測(cè)還涉及多個(gè)后臺(tái)系統(tǒng)會(huì)話比如 LOCK_MONITOR 等這些進(jìn)程的鎖事件也應(yīng)該被捕獲。解決在原案例中過濾條件里除了 54 和 60還應(yīng)該加上 6 和 20 這類關(guān)鍵系統(tǒng) SPID。一般做法是先啟用 Locks 事件并不過濾發(fā)生死鎖后用小范圍的重放來定位避免第一次 Trace 就漏數(shù)據(jù)。5.3 用 ObjectID 而不是 ObjectID2 關(guān)聯(lián)索引現(xiàn)象Trace 里 ObjectID 是索引的 object_id去看 SysIndexes 時(shí)匹配到的是表而不是具體索引定位錯(cuò)誤。原因SQL Server 在鎖事件里的 ObjectID 指的是對(duì)象 ID而 ObjectID2 才是 hobtid也就是 1222 報(bào)告里 keylock 后面那串?dāng)?shù)字。解決直接用 ObjectID2 去關(guān)聯(lián) sys.partitions.partition_id。這是我做死鎖排查時(shí)用的固定腳本每次 Trace 之后立即執(zhí)行避免靠記憶硬編碼。5.4 把 rowlock 提示當(dāng)成解決死鎖的銀彈現(xiàn)象給 UPDATE 加上 with(rowlock) 后死鎖依然發(fā)生。原因rowlock 只是告訴優(yōu)化器行級(jí)鎖是允許的它不改變執(zhí)行計(jì)劃的形狀也不改變鎖申請(qǐng)順序。解決用它復(fù)現(xiàn)問題時(shí)是為了防止鎖升級(jí)掩蓋掉真實(shí)沖突解決死鎖還是得靠執(zhí)行計(jì)劃調(diào)優(yōu)、索引調(diào)整或事務(wù)邊界改造。不要在產(chǎn)品代碼里依賴鎖提示來“修死鎖”后續(xù)維護(hù)是噩夢(mèng)。5.5 測(cè)試“去掉 include(d)” 后順手刪了索引破壞了原有查詢性能現(xiàn)象去掉 include(d) 后死鎖消失但原有查詢變慢甚至從 Index Seek 變成 RID Lookup。原因include 列本來是為了覆蓋常用查詢移除后 leaf 頁不包含 d 字段查詢定位到索引條目后還要回表。解決這個(gè)場(chǎng)景下更穩(wěn)妥的改法是把varchar(max)改成varchar(8000)或者直接改成varchar(200)保留 include 的結(jié)構(gòu)同時(shí)消除大對(duì)象的行外存儲(chǔ)問題。要記住死鎖是索引設(shè)計(jì)不合理的最外層癥狀不要只治癥狀。6. 從執(zhí)行計(jì)劃預(yù)判死鎖用 Include/Exclude 索引反向檢查鎖序這個(gè)案例給了我一個(gè)很實(shí)用的方法論在代碼評(píng)審階段用set statistics profile on或set statistics xml on看 UPDATE 語句的輸出數(shù)一下Index Update算子數(shù)量。一個(gè) UPDATE 如果對(duì)應(yīng)多個(gè)Index Update就意味著多個(gè)物理操作分散在事務(wù)時(shí)間軸上鎖的持有時(shí)間會(huì)被拉長(zhǎng)死鎖風(fēng)險(xiǎn)隨之上升。這個(gè)方法在表結(jié)構(gòu)變更前就能發(fā)現(xiàn)問題不需要等死鎖真的發(fā)生。具體的操作流程是先拿到目標(biāo) UPDATE 語句在測(cè)試庫上執(zhí)行set statistics profile on把輸出保存下來。然后手工統(tǒng)計(jì) Update 節(jié)點(diǎn)下的 Index Update 數(shù)量以及涉及的索引名。如果數(shù)量大于等于 2就要警惕。下一步結(jié)合索引定義檢查看這些索引的葉子節(jié)點(diǎn)是否包含了 UPDATE 會(huì)修改的字段。如果包含就得評(píng)估這些索引是否可以去掉或者把大字段從 include 里移除。這里可以套用我在這個(gè)案例里沉淀下的檢查清單數(shù) Index Update 算子數(shù)量超過 2 個(gè)標(biāo)黃。檢查涉及更新的索引里有沒有 include 正在被修改的列。檢查被修改列的數(shù)據(jù)類型varchar(max)、nvarchar(max)優(yōu)先標(biāo)紅。模擬兩個(gè)并發(fā)會(huì)話用循環(huán) UPDATE 驗(yàn)證是否存在鎖等待不要只測(cè)單會(huì)話執(zhí)行。把varchar(max)從索引里移除或者改成nvarchar(4000)這類帶長(zhǎng)度限制的類型通常是最直接的優(yōu)化。如果業(yè)務(wù)需要長(zhǎng)文本字段的覆蓋索引考慮改用nvarchar(4000)加上全文索引或者把長(zhǎng)文本拆表存放。那以后我每次做表結(jié)構(gòu)評(píng)審都會(huì)把“這張表有哪些索引包含了大對(duì)象列”當(dāng)成必查項(xiàng)目。這個(gè)案例也讓我養(yǎng)成了一個(gè)習(xí)慣所有新增索引的腳本都先跑一次set statistics profile on驗(yàn)證 UPDATE 和 DELETE 的執(zhí)行計(jì)劃形狀確認(rèn)沒有多算子索引維護(hù)再進(jìn)入發(fā)布流程。對(duì)于死鎖這種問題事后排查的能力很重要但從執(zhí)行計(jì)劃里提前看見風(fēng)險(xiǎn)才是更省時(shí)間的做法。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取