據(jù)庫(kù)巡檢命令清單：會(huì)話、索引碎片與死鎖排查實(shí)戰(zhàn))
前陣子我集中對(duì)一套業(yè)務(wù)系統(tǒng)做了幾輪數(shù)據(jù)庫(kù)巡檢翻筆記本時(shí)發(fā)現(xiàn)當(dāng)天用到的SQL Server命令其實(shí)可以組成一份相當(dāng)完整的排查清單。從會(huì)話狀態(tài)到索引碎片從等待統(tǒng)計(jì)到死鎖現(xiàn)場(chǎng)基本覆蓋了日常運(yùn)維里最常遇到的那幾類問(wèn)題。這篇就把當(dāng)天真正動(dòng)手敲過(guò)、并且驗(yàn)證有效的命令逐條寫出來(lái)每條附帶我當(dāng)時(shí)的判斷邏輯和踩過(guò)的坑想直接拿去用的朋友照著復(fù)制即可。1. 這批命令的整理背景慢查詢和阻塞告警逼出來(lái)的清單那段時(shí)間生產(chǎn)環(huán)境頻繁報(bào)出兩類問(wèn)題一是某些頁(yè)面接口響應(yīng)突然變慢二是早上九點(diǎn)半左右偶發(fā)超時(shí)告警。我最初也是靠經(jīng)驗(yàn)逐個(gè)排查執(zhí)行計(jì)劃、索引、鎖挨個(gè)看效率不高。后面實(shí)在覺(jué)得不行干脆把高頻用到的排查命令統(tǒng)一整理成一份腳本文件命名成“巡檢腳本.sql”塞進(jìn)常用工具目錄里。之后只要系統(tǒng)再報(bào)警我第一件事就是跑一遍這套命令從全局指標(biāo)到細(xì)節(jié)語(yǔ)句一條龍過(guò)完基本十分鐘內(nèi)能定位到方向。這套命令挑選的標(biāo)準(zhǔn)很簡(jiǎn)單一條命令對(duì)應(yīng)一類常見(jiàn)問(wèn)題。比如連接數(shù)異常對(duì)應(yīng)會(huì)話視圖慢查詢對(duì)應(yīng)語(yǔ)句統(tǒng)計(jì)鎖等待對(duì)應(yīng)阻塞查詢空間緊張對(duì)應(yīng)數(shù)據(jù)庫(kù)文件大小。每一條都要能直接給出可判斷的指標(biāo)而不是給一堆原始數(shù)據(jù)讓讀者自己猜。適用版本方面我本地的環(huán)境是SQL Server 2019和2022下面寫的系統(tǒng)視圖和命令在2016及以上版本都可以跑老版本2008 R2部分視圖字段略有差異使用時(shí)要留意。另外提一句我整理的這些命令多數(shù)是只讀查詢不會(huì)修改數(shù)據(jù)跑起來(lái)相對(duì)安全。但涉及DBCC、重建索引和備份那幾條必須在維護(hù)窗口或低峰期操作千萬(wàn)別在業(yè)務(wù)高峰期直接對(duì)生產(chǎn)庫(kù)執(zhí)行重建索引這個(gè)后面細(xì)說(shuō)。2. 數(shù)據(jù)庫(kù)健康巡檢命令先看整體狀態(tài)再追細(xì)節(jié)很多朋友排查問(wèn)題時(shí)習(xí)慣直接揪著某條慢SQL去優(yōu)化我的習(xí)慣是反過(guò)來(lái)——先看一眼服務(wù)器和數(shù)據(jù)庫(kù)整體狀態(tài)。因?yàn)楹芏鄷r(shí)候單個(gè)語(yǔ)句慢只是表象背后是連接數(shù)堆積或日志文件膨脹在拖累全局。開場(chǎng)先跑這幾條相當(dāng)于給數(shù)據(jù)庫(kù)做個(gè)體檢。2.1 會(huì)話與連接狀態(tài)快速判斷當(dāng)前有沒(méi)有“堵車”判斷數(shù)據(jù)庫(kù)“堵不堵”第一條命令是查當(dāng)前會(huì)話和連接數(shù)SELECT session_id, status, login_name, host_name, program_name, login_time, last_request_start_time, last_request_end_time FROM sys.dm_exec_sessions WHERE is_user_process 1 ORDER BY last_request_start_time;這條命令會(huì)把所有用戶會(huì)話列出來(lái)我主要看兩件事一是有沒(méi)有大量會(huì)話長(zhǎng)時(shí)間處于running或sleeping狀態(tài)二是last_request_start_time和last_request_end_time間隔是不是異常大。如果某個(gè)會(huì)話的開始時(shí)間和結(jié)束時(shí)間差了好幾秒甚至幾十秒大概率這個(gè)會(huì)話對(duì)應(yīng)的請(qǐng)求出現(xiàn)了阻塞或者執(zhí)行計(jì)劃有問(wèn)題。再配合查詢具體請(qǐng)求狀態(tài)SELECT session_id, start_time, status, command, wait_type, wait_time, blocking_session_id FROM sys.dm_exec_requests WHERE session_id 50;wait_type是重點(diǎn)常見(jiàn)的有LCK_M_S共享鎖等待、LCK_M_X排他鎖等待、PAGEIOLATCH_SH磁盤IO等待、CXPACKET并行查詢等待?？吹絯ait_type是鎖等待時(shí)就得繼續(xù)查阻塞鏈這個(gè)我放到后面專門講?？吹絇AGEIOLATCH_SH這類IO等待占大頭就要考慮是不是內(nèi)存不足或磁盤性能瓶頸而不是盲目加索引。2.2 數(shù)據(jù)庫(kù)文件和日志狀態(tài)預(yù)防磁盤被寫滿巡檢時(shí)必須看的還有數(shù)據(jù)庫(kù)文件大小和剩余空間。生產(chǎn)環(huán)境最常見(jiàn)的事故之一就是日志文件瘋狂增長(zhǎng)把磁盤塞滿導(dǎo)致數(shù)據(jù)庫(kù)進(jìn)入只讀狀態(tài)。我用的語(yǔ)句是SELECT db.name AS database_name, mf.type_desc AS file_type, mf.name AS logical_name, mf.physical_name, mf.size * 8 / 1024 AS size_mb, mf.growth * 8 / 1024 AS growth_mb, mf.is_percent_growth, CAST(mf.size * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS size_gb, CAST(FILEPROPERTY(mf.name, SpaceUsed) * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS used_gb FROM sys.databases db JOIN sys.master_files mf ON db.database_id mf.database_id WHERE db.database_id 4 ORDER BY size_gb DESC;這里算出每個(gè)文件的邏輯名、物理路徑、總大小、已用空間一眼看到剩余空間。有個(gè)容易忽略的點(diǎn)growth字段如果是按百分比增長(zhǎng)大型數(shù)據(jù)庫(kù)擴(kuò)容時(shí)可能瞬間分配很大空間引起IO抖動(dòng)。建議核心業(yè)務(wù)庫(kù)的數(shù)據(jù)文件和日志文件都改成固定大小增長(zhǎng)比如每次增長(zhǎng)2GB或4GB避免頻繁自動(dòng)擴(kuò)張帶來(lái)的性能波動(dòng)。2.3 活動(dòng)事務(wù)檢查清理日志失敗的常見(jiàn)原因還有一條我經(jīng)常用的DBCC命令DBCC OPENTRAN(數(shù)據(jù)庫(kù)名);這條的作用是查看當(dāng)前數(shù)據(jù)庫(kù)中最早的活動(dòng)事務(wù)。如果日志備份一直失敗或者日志文件無(wú)法收縮多半是有長(zhǎng)事務(wù)沒(méi)有提交。跑完這條如果看到類似“最早的活動(dòng)事務(wù)”信息就能定位到具體的事務(wù)開始時(shí)間和SPID。記得有一次某張業(yè)務(wù)表被一條未提交的UPDATE鎖住外部程序一直在等鎖日志也越漲越大就是靠DBCC OPENTRAN找到事務(wù)源頭然后和業(yè)務(wù)側(cè)確認(rèn)后手動(dòng)回滾才解除的。3. 性能排查命令從等待統(tǒng)計(jì)中揪出真正的瓶頸數(shù)據(jù)庫(kù)變慢往往不是單一語(yǔ)句的問(wèn)題而是某個(gè)環(huán)節(jié)成為了瓶頸。我比較依賴等待統(tǒng)計(jì)來(lái)定位因?yàn)榈却y(tǒng)計(jì)是SQL Server自己記錄的“卡在哪”的數(shù)據(jù)比我們猜要準(zhǔn)確得多。有個(gè)很形象的類比醫(yī)院急診室護(hù)士會(huì)記錄每個(gè)病人的等待原因和等待時(shí)間等待統(tǒng)計(jì)就是數(shù)據(jù)庫(kù)的“候診記錄表”。3.1 等待類型匯總先看整體等待分布SELECT wait_type, wait_time_ms / 1000.0 AS wait_time_sec, waiting_tasks_count, signal_wait_time_ms / 1000.0 AS signal_wait_sec, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec, wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS wait_percent FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( BROKER_EVENTHANDLER, BROKER_RECEIVE_WAITFOR, BROKER_TASK_STOP, BROKER_TO_FLUSH, BROKER_TRANSMITTER, CHECKPOINT_QUEUE, CHKPT, CLR_AUTO_EVENT, CLR_MANUAL_EVENT, CLR_SEMAPHORE, DBMIRROR_DBM_EVENT, DBMIRROR_EVENTS_QUEUE, DBMIRROR_WORKER_QUEUE, DBMIRRORING_CMD, DIRTY_PAGE_SCAN, DISPATCHER_QUEUE_SEMAPHORE, EXECSYNC, FSAGENT, FT_IFTS_SCHEDULER_IDLE_WAIT, FT_IFTSHC_MUTEX, HADR_CLUSAPI_CALL, HADR_NOTIFICATION_DEQUEUE, HADR_TIMER_TASK, HADR_WORK_QUEUE, KSOURCE_WAKEUP, LAZYWRITER_SLEEP, LOGMGR_QUEUE, MEMORY_ALLOCATION_EXT, ON_TASK_WAIT, PREEMPTIVE_OS_LIBRARYOPS, PREEMPTIVE_OS_COUNTOPS, PREEMPTIVE_OS_CRYPTOPS, PREEMPTIVE_OS_QUERYMDN, PREEMPTIVE_OS_TXITPROC, REQUEST_FOR_DEADLOCK_SEARCH, RESOURCE_QUEUE, SLEEP_BPOOL, SLEEP_BUFFERPOOL, SLEEP_DBSTARTUP, SLEEP_DCOMSTARTUP, SLEEP_MASTERDBREADY, SLEEP_MASTERMDREADY, SLEEP_MASTERUPGRADED, SLEEP_MSDBSTARTUP, SLEEP_SYSTEMTASK, SLEEP_TASK, SLEEP_TEMPDBSTARTUP, SNI_HTTP_ACCEPT, SP_SERVER_DIAGNOSTICS_SLEEP, SQLTRACE_BUFFER_FLUSH, SQLTRACE_INCREMENTAL_FLUSH, SQLTRACE_WAIT_ENTRIES, WAIT_FOR_RESULTS, WAITFOR, WAITFOR_TASKSHUTDOWN, WHILE_TEMP, XE_BUFFERMGR_ALLOC, XE_DISPATCHER_WAIT, XE_DISPATCHER_JOIN, XE_MODULEMGR_CALLNOTIFICATION, XE_LOGGER_TICK, XE_TIMER_EVENT ) AND waiting_tasks_count 0 ORDER BY wait_time_ms DESC;這里我排除了大量自循環(huán)等待類型——比如SLEEP_TASK、WAITFOR這類屬于空閑等待如果不過(guò)濾掉它們會(huì)占掉大部分統(tǒng)計(jì)容易誤導(dǎo)判斷?？唇Y(jié)果時(shí)我主要關(guān)注三塊排名靠前的是PAGEIOLATCH_*說(shuō)明磁盤IO壓力大LCK_*說(shuō)明存在鎖阻塞CXPACKET說(shuō)明并行查詢等待較多。每種等待的應(yīng)對(duì)思路完全不同不能盲目加索引或加內(nèi)存。3.2 最耗時(shí)查詢排行直接找出“罪魁禍?zhǔn)住钡却y(tǒng)計(jì)確認(rèn)了方向后下一步就是把最慢的語(yǔ)句找出來(lái)。我用的是執(zhí)行計(jì)劃緩存中的統(tǒng)計(jì)信息SELECT TOP 10 qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms, qs.total_worker_time / qs.execution_count AS avg_cpu_ms, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.execution_count, qs.total_elapsed_time, qs.total_logical_reads, qs.creation_time, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) 1) AS statement_text, qs.plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY avg_elapsed_ms DESC;這條能列出平均耗時(shí)最長(zhǎng)、平均CPU最多、邏輯讀取最多的語(yǔ)句。我通常按平均邏輯讀取排序再看耗時(shí)因?yàn)檫壿嬜x太高的語(yǔ)句往往意味著全表掃描或索引缺失。得到plan_handle之后還可以進(jìn)一步查看該語(yǔ)句的完整執(zhí)行計(jì)劃SELECT query_plan FROM sys.dm_exec_query_plan(0x06000600...); -- 填入實(shí)際的 plan_handle注意query_plan返回的是XML格式在SSMS中點(diǎn)擊即可看到圖形化執(zhí)行計(jì)劃。這里有個(gè)隱藏坑執(zhí)行計(jì)劃緩存是動(dòng)態(tài)的實(shí)例重啟或內(nèi)存壓力大時(shí)舊的統(tǒng)計(jì)會(huì)被清掉。所以如果系統(tǒng)已經(jīng)重啟過(guò)或者跑了很長(zhǎng)時(shí)間sys.dm_exec_query_stats里只能看到緩存中的語(yǔ)句。想完整記錄歷史語(yǔ)句還是得提前開擴(kuò)展事件或第三方監(jiān)控不能只靠這一個(gè)視圖。3.3 缺失索引提示參考但不要無(wú)腦加SQL Server提供一個(gè)很實(shí)用的動(dòng)態(tài)管理視圖可以直接告訴你“它覺(jué)得哪里缺索引”SELECT migs.avg_total_user_cost * migs.avg_user_impact AS improvement_score, migs.avg_user_impact, migs.avg_total_user_cost, mid.database_id, mid.object_id, mid.statement AS table_name, mid.equality_columns, mid.inequality_columns, mid.included_columns, CREATE INDEX [IX_ OBJECT_NAME(mid.object_id, mid.database_id) _ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ), , , _), [, ), ], ) CASE WHEN mid.inequality_columns IS NOT NULL THEN _ REPLACE(REPLACE(REPLACE(mid.inequality_columns, , , _), [, ), ], ) ELSE END ] ON mid.statement ( ISNULL(mid.equality_columns, ) CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN , ELSE END ISNULL(mid.inequality_columns, ) ) CASE WHEN mid.included_columns IS NOT NULL THEN INCLUDE ( mid.included_columns ) ELSE END ; AS create_index_statement FROM sys.dm_db_missing_index_details mid JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle mid.index_group_handle WHERE mid.database_id DB_ID() ORDER BY improvement_score DESC;這個(gè)視圖會(huì)直接拼出建議的CREATE INDEX語(yǔ)句看起來(lái)非常方便但我要提醒一句缺失索引建議只是基于成本評(píng)估的“建議”不是“必須執(zhí)行”。我在實(shí)際中見(jiàn)過(guò)不少案例按提示加索引后單條查詢快了不少但整個(gè)表的寫入變慢甚至因?yàn)樗饕^(guò)多導(dǎo)致日志增長(zhǎng)和存儲(chǔ)膨脹。我的習(xí)慣是先人工判斷這個(gè)缺失索引對(duì)應(yīng)的查詢是否高頻涉及的列是否在WHERE和JOIN條件中出現(xiàn)選擇性是否夠好再加一個(gè)INCLUDE的列是否能讓查詢覆蓋。一條查詢?nèi)绻慌芤淮尉退泐A(yù)測(cè)收益99%也不值得加索引。4. 索引維護(hù)命令碎片檢測(cè)與重建/重組的完整決策鏈索引維護(hù)是SQL Server日常運(yùn)維里繞不開的話題但也是被誤解較多的。很多人只知道定期重建索引卻不問(wèn)為什么重建、碎片到什么程度才需要重建。這里把完整的命令決策鏈寫出來(lái)。4.1 碎片詳情查詢重建之前先看看數(shù)據(jù)執(zhí)行如下語(yǔ)句查看某個(gè)數(shù)據(jù)庫(kù)所有索引的碎片情況SELECT OBJECT_NAME(ps.object_id) AS table_name, i.name AS index_name, ps.index_type_desc, ps.page_count, ps.avg_fragmentation_in_percent, ps.avg_page_space_used_in_percent, ps.record_count, ps.ghost_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, SAMPLED) ps JOIN sys.indexes i ON ps.object_id i.object_id AND ps.index_id i.index_id WHERE ps.avg_fragmentation_in_percent 5 AND ps.page_count 100 AND ps.index_type_desc HEAP ORDER BY ps.avg_fragmentation_in_percent DESC;重點(diǎn)看兩列avg_fragmentation_in_percent代表碎片率avg_page_space_used_in_percent代表頁(yè)面填充率。碎片率只是表面指標(biāo)頁(yè)面填充率過(guò)低說(shuō)明頁(yè)內(nèi)空間浪費(fèi)嚴(yán)重即使碎片率不高也需要關(guān)注。這里我踩過(guò)一個(gè)大坑SAMPLED模式只采樣部分頁(yè)面掃描大表很快但結(jié)果可能不夠準(zhǔn)確。小表或者碎片極不均勻的表用SAMPLED可能漏掉某些碎片區(qū)。更準(zhǔn)確的是DETAILED模式但DETAILED會(huì)掃描全部頁(yè)面大表耗時(shí)很長(zhǎng)必須在維護(hù)窗口執(zhí)行。所以我的做法是日常巡檢用SAMPLED快速看維護(hù)日再針對(duì)重點(diǎn)大表用DETAILED做最終確認(rèn)。4.2 重建與重組的選擇按碎片率分檔處理查到碎片后按以下規(guī)則處理比較穩(wěn)妥碎片率低于5%不需要處理哪怕索引會(huì)繼續(xù)膨脹。碎片率在5%到30%之間執(zhí)行重組ALTER INDEX ... REORGANIZE這個(gè)是邏輯整理不重建物理結(jié)構(gòu)不會(huì)產(chǎn)生大量日志。碎片率大于30%執(zhí)行重建ALTER INDEX ... REBUILD物理上重新分配頁(yè)產(chǎn)生日志較多。對(duì)應(yīng)命令分別是-- 重組單個(gè)索引 ALTER INDEX [IX_表名_列名] ON [dbo].[表名] REORGANIZE; -- 重建單個(gè)索引 ALTER INDEX [IX_表名_列名] ON [dbo].[表名] REBUILD; -- 重建表上所有索引 ALTER INDEX ALL ON [dbo].[表名] REBUILD WITH (FILLFACTOR 90, ONLINE ON);如果生產(chǎn)環(huán)境是企業(yè)版重建索引時(shí)可以帶ONLINE ON這樣表在重建期間仍然可讀寫業(yè)務(wù)影響小很多標(biāo)準(zhǔn)版不支持在線重建通常只能選擇在維護(hù)窗口操作。另外重建大表索引前一定要預(yù)估好日志空間。曾經(jīng)有人在幾小時(shí)內(nèi)跑完重建結(jié)果日志文件把磁盤塞爆最后數(shù)據(jù)庫(kù)進(jìn)入只讀。更穩(wěn)妥的做法是對(duì)核心大表在重建前先備份數(shù)據(jù)庫(kù)或者把數(shù)據(jù)庫(kù)恢復(fù)模式臨時(shí)改成簡(jiǎn)單模式僅限于非關(guān)鍵庫(kù)并且要評(píng)估數(shù)據(jù)安全。4.3 填充因子與其他參數(shù)別讓索引維護(hù)白忙活FILLFACTOR是索引頁(yè)的填充百分比它直接影響后續(xù)碎片產(chǎn)生的速度。默認(rèn)值是0等價(jià)于100%即索引頁(yè)盡可能填滿。對(duì)于頻繁插入的表建議設(shè)置成90左右預(yù)留一部分空間給后續(xù)插入避免頁(yè)分裂導(dǎo)致碎片快速增加對(duì)于基本只讀的表可以保持默認(rèn)這樣索引更緊湊查詢IO更少。還有一個(gè)點(diǎn)容易被忽略——重建索引之后對(duì)應(yīng)的統(tǒng)計(jì)信息也會(huì)自動(dòng)更新。所以通常不需要再單獨(dú)執(zhí)行UPDATE STATISTICS。但如果某個(gè)查詢的執(zhí)行計(jì)劃一直不穩(wěn)定另當(dāng)別論。5. 阻塞與死鎖排查命令還原現(xiàn)場(chǎng)而不是只看到結(jié)果數(shù)據(jù)庫(kù)阻塞和死鎖是運(yùn)維中最頭疼的問(wèn)題之一。難點(diǎn)在于死鎖發(fā)生那一刻就結(jié)束了等你去查的時(shí)候現(xiàn)場(chǎng)已經(jīng)沒(méi)了。所以阻塞查詢要“實(shí)時(shí)”跑死鎖追蹤要“提前開”。以下是我排查這類問(wèn)題時(shí)實(shí)際用到的命令組合。5.1 實(shí)時(shí)阻塞鏈查詢從等待者找到源頭sp_who2是老一代DBA常用命令勝在簡(jiǎn)單EXEC sp_who2;但sp_who2返回的信息不夠結(jié)構(gòu)化也不方便過(guò)濾。我現(xiàn)在更常用這樣一條查詢能直接看到阻塞者和被阻塞者的關(guān)系SELECT r.session_id AS blocked_session_id, r.blocking_session_id AS blocking_session_id, r.wait_type, r.wait_time / 1000.0 AS wait_time_sec, r.status, t.text AS blocked_sql_text, b.text AS blocking_sql_text, es.host_name, es.login_name, es.program_name FROM sys.dm_exec_requests r LEFT JOIN sys.dm_exec_connections rc ON r.session_id rc.session_id LEFT JOIN sys.dm_exec_connections bc ON r.blocking_session_id bc.session_id CROSS APPLY sys.dm_exec_sql_text(rc.most_recent_sql_handle) t CROSS APPLY sys.dm_exec_sql_text(bc.most_recent_sql_handle) b JOIN sys.dm_exec_sessions es ON r.session_id es.session_id WHERE r.blocking_session_id 0 AND r.blocking_session_id IS NOT NULL;這條查詢把阻塞者和被阻塞者兩邊的SQL文本都列出來(lái)一眼就能看出是哪條語(yǔ)句鎖住了什么資源。有一次排查超時(shí)告警跑完后發(fā)現(xiàn)阻塞源頭是一個(gè)長(zhǎng)事務(wù)里的UPDATE語(yǔ)句一直在更新某大表的同一批記錄。往下一查發(fā)現(xiàn)更新條件字段上沒(méi)有索引每次更新都在全表掃描持有大量鎖不釋放后面所有涉及該表的操作全被擋在門外。后續(xù)在該字段上補(bǔ)了索引再配合業(yè)務(wù)側(cè)縮短事務(wù)時(shí)間問(wèn)題才徹底解決。5.2 歷史死鎖追蹤提前開啟跟蹤標(biāo)記死鎖不像阻塞可以現(xiàn)場(chǎng)看到更多時(shí)候需要我們翻歷史記錄。SQL Server默認(rèn)會(huì)記錄死鎖相關(guān)信息但信息比較簡(jiǎn)略。我建議在實(shí)例層面開啟跟蹤標(biāo)記1222和1204這樣死鎖發(fā)生時(shí)會(huì)把完整的資源和語(yǔ)句寫到錯(cuò)誤日志里DBCC TRACEON(1222, -1); DBCC TRACEON(1204, -1);開啟后查看錯(cuò)誤日志中的死鎖信息EXEC sp_readerrorlog 0, 1, deadlock;日志里會(huì)顯示類似“deadlock victim”的字樣以及涉及的資源、進(jìn)程ID和SQL語(yǔ)句。如果還嫌不夠直觀可以開擴(kuò)展事件來(lái)捕獲xml_deadlock_report事件保存死鎖圖形文件用SSMS打開直接看環(huán)形依賴關(guān)系。我個(gè)人在生產(chǎn)環(huán)境更推薦擴(kuò)展事件方式因?yàn)樗鼙４嫱暾乃梨i圖XML排查效率比看文本日志高很多。5.3 鎖粒度和隔離級(jí)別理解比命令本身更重要排查鎖問(wèn)題到深處必須理解隔離級(jí)別的影響。很多時(shí)候不是語(yǔ)句寫錯(cuò)了而是隔離級(jí)別設(shè)置不合理導(dǎo)致鎖持有時(shí)間過(guò)長(zhǎng)。比如默認(rèn)的READ COMMITTED下讀操作只加共享鎖讀完即釋放但如果改成REPEATABLE READ或SERIALIZABLE讀鎖會(huì)一直持有到事務(wù)結(jié)束極易造成阻塞放大。-- 查看當(dāng)前會(huì)話隔離級(jí)別 SELECT CASE transaction_isolation_level WHEN 0 THEN Unspecified WHEN 1 THEN READ UNCOMMITTED WHEN 2 THEN READ COMMITTED WHEN 3 THEN REPEATABLE READ WHEN 4 THEN SERIALIZABLE WHEN 5 THEN SNAPSHOT ELSE UNKNOWN END AS isolation_level FROM sys.dm_exec_sessions WHERE session_id SPID;針對(duì)某些報(bào)表類查詢?nèi)绻试S臟讀可以按語(yǔ)句級(jí)別加WITH (NOLOCK)提示把讀鎖徹底去掉但這要業(yè)務(wù)側(cè)接受可能出現(xiàn)的中途被修改的數(shù)據(jù)。對(duì)核心交易邏輯不建議用NOLOCK來(lái)繞過(guò)鎖問(wèn)題正確做法是優(yōu)化事務(wù)粒度、減少鎖持有時(shí)間或者開啟READ COMMITTED SNAPSHOTRCSI讓讀操作走行版本快照。6. 備份與完整性校驗(yàn)命令出事后才做的操作其實(shí)最需要提前演練備份算是SQL Server命令里最基礎(chǔ)也最不能出錯(cuò)的部分。我發(fā)現(xiàn)很多團(tuán)隊(duì)的備份腳本是“能跑就行”但從沒(méi)做過(guò)恢復(fù)演練真到出問(wèn)題時(shí)才發(fā)現(xiàn)備份文件損壞或恢復(fù)鏈斷裂。這里分享的是我實(shí)際使用的備份與校驗(yàn)命令組合。6.1 全量備份與差異備份參數(shù)不是隨便填的全量備份最常用的一條BACKUP DATABASE [數(shù)據(jù)庫(kù)名] TO DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120.bak WITH INIT, NAME N數(shù)據(jù)庫(kù)名-全量備份, COMPRESSION, CHECKSUM, STATS 10;幾個(gè)關(guān)鍵參數(shù)說(shuō)明一下。INIT表示覆蓋同名文件如果不用INIT而且目標(biāo)文件已存在備份會(huì)追加到文件里容易造成文件字節(jié)數(shù)增長(zhǎng)且不可讀。COMPRESSION啟用備份壓縮能顯著減小備份體積但也更消耗CPU資源——如果服務(wù)器CPU本身已經(jīng)長(zhǎng)期高位可以改成MAXTRANSFERSIZE調(diào)優(yōu)而不是啟用壓縮。CHECKSUM在校驗(yàn)備份時(shí)非常關(guān)鍵它會(huì)在備份過(guò)程中計(jì)算校驗(yàn)和RESTORE VERIFYONLY時(shí)就能檢查數(shù)據(jù)頁(yè)是否有損壞。差異備份的命令類似BACKUP DATABASE [數(shù)據(jù)庫(kù)名] TO DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120_diff.bak WITH DIFFERENTIAL, INIT, NAME N數(shù)據(jù)庫(kù)名-差異備份, CHECKSUM;需要強(qiáng)調(diào)差異備份是基于上一次全量備份的增量。如果全量備份被覆蓋或刪除中間所有差異備份都失效。所以備份文件的保留策略必須嚴(yán)格比如保留最近7天每日全量再加上當(dāng)天所有差異備份不要手動(dòng)去刪中間文件。6.2 事務(wù)日志備份恢復(fù)時(shí)間點(diǎn)的關(guān)鍵對(duì)恢復(fù)模式為FULL的數(shù)據(jù)庫(kù)必須定期備份事務(wù)日志BACKUP LOG [數(shù)據(jù)庫(kù)名] TO DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120_log.trn WITH INIT, CHECKSUM;日志備份做得好才能把數(shù)據(jù)庫(kù)恢復(fù)到任意時(shí)間點(diǎn)?，F(xiàn)實(shí)中我看到不少情況是全量備份和差異備份都有但日志備份頻率過(guò)低甚至一天才一次。如果數(shù)據(jù)庫(kù)在下午兩點(diǎn)宕機(jī)日志備份最后一次是早上六點(diǎn)中間的數(shù)據(jù)就只能丟。核心交易庫(kù)建議日志備份間隔控制在15分鐘以內(nèi)備份文件不大成本主要是IO開銷和時(shí)間精度。6.3 恢復(fù)校驗(yàn)備份的真正考驗(yàn)是能不能恢復(fù)備份完不等于萬(wàn)事大吉我用一條命令驗(yàn)證備份文件完整性RESTORE VERIFYONLY FROM DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120.bak WITH CHECKSUM;這條只校驗(yàn)備份集完整性不實(shí)際恢復(fù)數(shù)據(jù)速度快可作為日常監(jiān)控任務(wù)的一部分。但需要說(shuō)明VERIFYONLY不能完全代替真正的恢復(fù)演練。我自己的做法是每季度至少做一次完整的RESTORE DATABASE到隔離環(huán)境的演練確認(rèn)備份文件能正?；謴?fù)并且應(yīng)用日志鏈后能得到期望時(shí)間點(diǎn)。偶爾遇到備份文件在校驗(yàn)時(shí)報(bào)CHECKSUM錯(cuò)誤的情況就是因?yàn)閭浞葸^(guò)程中磁盤IO異常或文件受損這種時(shí)候必須盡快定位物理磁盤問(wèn)題重新做備份不能繼續(xù)往這個(gè)文件上追加任何數(shù)據(jù)。6.4 數(shù)據(jù)庫(kù)完整性檢查DBCC CHECKDB的正確打開方式除了備份我還把DBCC CHECKDB納入月度維護(hù)計(jì)劃。它檢查數(shù)據(jù)庫(kù)邏輯和物理完整性能發(fā)現(xiàn)頁(yè)損壞、分配錯(cuò)誤、邏輯一致性等問(wèn)題DBCC CHECKDB([數(shù)據(jù)庫(kù)名]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY;大多數(shù)情況下生產(chǎn)庫(kù)我建議用PHYSICAL_ONLY模式因?yàn)樗粰z查物理結(jié)構(gòu)不檢查所有邏輯約束耗時(shí)比完整檢查短很多。維護(hù)窗口充足時(shí)再跑不帶PHYSICAL_ONLY的完整檢查。還有一個(gè)實(shí)際建議CHECKDB也是CPU和IO密集操作盡量不要和索引重建、全量備份安排在同一時(shí)間段執(zhí)行免得互相搶資源。7. 把命令整理成自己的巡檢清單多跑幾遍你也有手感最后分享一個(gè)我自己堅(jiān)持的習(xí)慣。上面這些命令不是每次都需要全部跑一遍我的做法是分為日常巡檢、周度巡檢、月度維護(hù)三檔對(duì)應(yīng)不同頻率和深度。日常巡檢每天早上一遍跑會(huì)話狀態(tài)查詢、數(shù)據(jù)庫(kù)文件大小、DBCC OPENTRAN、備份是否成功。十分鐘內(nèi)完成腳本里加好時(shí)間戳和狀態(tài)高亮異常時(shí)直接標(biāo)紅。周度巡檢加跑等待類型匯總、碎片率抽樣、最耗時(shí)查詢排行用于發(fā)現(xiàn)性能退化趨勢(shì)。月度維護(hù)跑CHECKDB、索引重組或重建、季度恢復(fù)演練、完整死鎖日志分析。把這些命令組合到一起后你會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)問(wèn)題大多是漸變的——日志文件在悄悄變大某條語(yǔ)句的邏輯讀在逐步上升等待類型占比在慢慢偏移。只看某一天的快照很難發(fā)現(xiàn)問(wèn)題持續(xù)按固定頻率采集并對(duì)比才能抓住趨勢(shì)。關(guān)于這些命令本身我再補(bǔ)充一個(gè)建議新建一個(gè)專門的查詢腳本文件把每條命令都寫好注釋和排序條件別到用的時(shí)候臨時(shí)打開SSMS手敲。人總是會(huì)記憶模糊的尤其凌晨被叫起來(lái)處理故障時(shí)一份固化的巡檢腳本比什么都可靠。我?guī)状瘟璩颗挪閱?wèn)題能十分鐘定位靠的就是這份提前寫好的腳本以及平時(shí)養(yǎng)成的固定執(zhí)行節(jié)奏。 SQL Server數(shù)據(jù)庫(kù)巡檢命令清單：會(huì)話、索引碎片與死鎖排查實(shí)戰(zhàn)
前陣子我集中對(duì)一套業(yè)務(wù)系統(tǒng)做了幾輪數(shù)據(jù)庫(kù)巡檢翻筆記本時(shí)發(fā)現(xiàn)當(dāng)天用到的SQL Server命令其實(shí)可以組成一份相當(dāng)完整的排查清單。從會(huì)話狀態(tài)到索引碎片從等待統(tǒng)計(jì)到死鎖現(xiàn)場(chǎng)基本覆蓋了日常運(yùn)維里最常遇到的那幾類問(wèn)題。這篇就把當(dāng)天真正動(dòng)手敲過(guò)、并且驗(yàn)證有效的命令逐條寫出來(lái)每條附帶我當(dāng)時(shí)的判斷邏輯和踩過(guò)的坑想直接拿去用的朋友照著復(fù)制即可。1. 這批命令的整理背景慢查詢和阻塞告警逼出來(lái)的清單那段時(shí)間生產(chǎn)環(huán)境頻繁報(bào)出兩類問(wèn)題一是某些頁(yè)面接口響應(yīng)突然變慢二是早上九點(diǎn)半左右偶發(fā)超時(shí)告警。我最初也是靠經(jīng)驗(yàn)逐個(gè)排查執(zhí)行計(jì)劃、索引、鎖挨個(gè)看效率不高。后面實(shí)在覺(jué)得不行干脆把高頻用到的排查命令統(tǒng)一整理成一份腳本文件命名成“巡檢腳本.sql”塞進(jìn)常用工具目錄里。之后只要系統(tǒng)再報(bào)警我第一件事就是跑一遍這套命令從全局指標(biāo)到細(xì)節(jié)語(yǔ)句一條龍過(guò)完基本十分鐘內(nèi)能定位到方向。這套命令挑選的標(biāo)準(zhǔn)很簡(jiǎn)單一條命令對(duì)應(yīng)一類常見(jiàn)問(wèn)題。比如連接數(shù)異常對(duì)應(yīng)會(huì)話視圖慢查詢對(duì)應(yīng)語(yǔ)句統(tǒng)計(jì)鎖等待對(duì)應(yīng)阻塞查詢空間緊張對(duì)應(yīng)數(shù)據(jù)庫(kù)文件大小。每一條都要能直接給出可判斷的指標(biāo)而不是給一堆原始數(shù)據(jù)讓讀者自己猜。適用版本方面我本地的環(huán)境是SQL Server 2019和2022下面寫的系統(tǒng)視圖和命令在2016及以上版本都可以跑老版本2008 R2部分視圖字段略有差異使用時(shí)要留意。另外提一句我整理的這些命令多數(shù)是只讀查詢不會(huì)修改數(shù)據(jù)跑起來(lái)相對(duì)安全。但涉及DBCC、重建索引和備份那幾條必須在維護(hù)窗口或低峰期操作千萬(wàn)別在業(yè)務(wù)高峰期直接對(duì)生產(chǎn)庫(kù)執(zhí)行重建索引這個(gè)后面細(xì)說(shuō)。2. 數(shù)據(jù)庫(kù)健康巡檢命令先看整體狀態(tài)再追細(xì)節(jié)很多朋友排查問(wèn)題時(shí)習(xí)慣直接揪著某條慢SQL去優(yōu)化我的習(xí)慣是反過(guò)來(lái)——先看一眼服務(wù)器和數(shù)據(jù)庫(kù)整體狀態(tài)。因?yàn)楹芏鄷r(shí)候單個(gè)語(yǔ)句慢只是表象背后是連接數(shù)堆積或日志文件膨脹在拖累全局。開場(chǎng)先跑這幾條相當(dāng)于給數(shù)據(jù)庫(kù)做個(gè)體檢。2.1 會(huì)話與連接狀態(tài)快速判斷當(dāng)前有沒(méi)有“堵車”判斷數(shù)據(jù)庫(kù)“堵不堵”第一條命令是查當(dāng)前會(huì)話和連接數(shù)SELECT session_id, status, login_name, host_name, program_name, login_time, last_request_start_time, last_request_end_time FROM sys.dm_exec_sessions WHERE is_user_process 1 ORDER BY last_request_start_time;這條命令會(huì)把所有用戶會(huì)話列出來(lái)我主要看兩件事一是有沒(méi)有大量會(huì)話長(zhǎng)時(shí)間處于running或sleeping狀態(tài)二是last_request_start_time和last_request_end_time間隔是不是異常大。如果某個(gè)會(huì)話的開始時(shí)間和結(jié)束時(shí)間差了好幾秒甚至幾十秒大概率這個(gè)會(huì)話對(duì)應(yīng)的請(qǐng)求出現(xiàn)了阻塞或者執(zhí)行計(jì)劃有問(wèn)題。再配合查詢具體請(qǐng)求狀態(tài)SELECT session_id, start_time, status, command, wait_type, wait_time, blocking_session_id FROM sys.dm_exec_requests WHERE session_id 50;wait_type是重點(diǎn)常見(jiàn)的有LCK_M_S共享鎖等待、LCK_M_X排他鎖等待、PAGEIOLATCH_SH磁盤IO等待、CXPACKET并行查詢等待?？吹絯ait_type是鎖等待時(shí)就得繼續(xù)查阻塞鏈這個(gè)我放到后面專門講?？吹絇AGEIOLATCH_SH這類IO等待占大頭就要考慮是不是內(nèi)存不足或磁盤性能瓶頸而不是盲目加索引。2.2 數(shù)據(jù)庫(kù)文件和日志狀態(tài)預(yù)防磁盤被寫滿巡檢時(shí)必須看的還有數(shù)據(jù)庫(kù)文件大小和剩余空間。生產(chǎn)環(huán)境最常見(jiàn)的事故之一就是日志文件瘋狂增長(zhǎng)把磁盤塞滿導(dǎo)致數(shù)據(jù)庫(kù)進(jìn)入只讀狀態(tài)。我用的語(yǔ)句是SELECT db.name AS database_name, mf.type_desc AS file_type, mf.name AS logical_name, mf.physical_name, mf.size * 8 / 1024 AS size_mb, mf.growth * 8 / 1024 AS growth_mb, mf.is_percent_growth, CAST(mf.size * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS size_gb, CAST(FILEPROPERTY(mf.name, SpaceUsed) * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS used_gb FROM sys.databases db JOIN sys.master_files mf ON db.database_id mf.database_id WHERE db.database_id 4 ORDER BY size_gb DESC;這里算出每個(gè)文件的邏輯名、物理路徑、總大小、已用空間一眼看到剩余空間。有個(gè)容易忽略的點(diǎn)growth字段如果是按百分比增長(zhǎng)大型數(shù)據(jù)庫(kù)擴(kuò)容時(shí)可能瞬間分配很大空間引起IO抖動(dòng)。建議核心業(yè)務(wù)庫(kù)的數(shù)據(jù)文件和日志文件都改成固定大小增長(zhǎng)比如每次增長(zhǎng)2GB或4GB避免頻繁自動(dòng)擴(kuò)張帶來(lái)的性能波動(dòng)。2.3 活動(dòng)事務(wù)檢查清理日志失敗的常見(jiàn)原因還有一條我經(jīng)常用的DBCC命令DBCC OPENTRAN(數(shù)據(jù)庫(kù)名);這條的作用是查看當(dāng)前數(shù)據(jù)庫(kù)中最早的活動(dòng)事務(wù)。如果日志備份一直失敗或者日志文件無(wú)法收縮多半是有長(zhǎng)事務(wù)沒(méi)有提交。跑完這條如果看到類似“最早的活動(dòng)事務(wù)”信息就能定位到具體的事務(wù)開始時(shí)間和SPID。記得有一次某張業(yè)務(wù)表被一條未提交的UPDATE鎖住外部程序一直在等鎖日志也越漲越大就是靠DBCC OPENTRAN找到事務(wù)源頭然后和業(yè)務(wù)側(cè)確認(rèn)后手動(dòng)回滾才解除的。3. 性能排查命令從等待統(tǒng)計(jì)中揪出真正的瓶頸數(shù)據(jù)庫(kù)變慢往往不是單一語(yǔ)句的問(wèn)題而是某個(gè)環(huán)節(jié)成為了瓶頸。我比較依賴等待統(tǒng)計(jì)來(lái)定位因?yàn)榈却y(tǒng)計(jì)是SQL Server自己記錄的“卡在哪”的數(shù)據(jù)比我們猜要準(zhǔn)確得多。有個(gè)很形象的類比醫(yī)院急診室護(hù)士會(huì)記錄每個(gè)病人的等待原因和等待時(shí)間等待統(tǒng)計(jì)就是數(shù)據(jù)庫(kù)的“候診記錄表”。3.1 等待類型匯總先看整體等待分布SELECT wait_type, wait_time_ms / 1000.0 AS wait_time_sec, waiting_tasks_count, signal_wait_time_ms / 1000.0 AS signal_wait_sec, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec, wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS wait_percent FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( BROKER_EVENTHANDLER, BROKER_RECEIVE_WAITFOR, BROKER_TASK_STOP, BROKER_TO_FLUSH, BROKER_TRANSMITTER, CHECKPOINT_QUEUE, CHKPT, CLR_AUTO_EVENT, CLR_MANUAL_EVENT, CLR_SEMAPHORE, DBMIRROR_DBM_EVENT, DBMIRROR_EVENTS_QUEUE, DBMIRROR_WORKER_QUEUE, DBMIRRORING_CMD, DIRTY_PAGE_SCAN, DISPATCHER_QUEUE_SEMAPHORE, EXECSYNC, FSAGENT, FT_IFTS_SCHEDULER_IDLE_WAIT, FT_IFTSHC_MUTEX, HADR_CLUSAPI_CALL, HADR_NOTIFICATION_DEQUEUE, HADR_TIMER_TASK, HADR_WORK_QUEUE, KSOURCE_WAKEUP, LAZYWRITER_SLEEP, LOGMGR_QUEUE, MEMORY_ALLOCATION_EXT, ON_TASK_WAIT, PREEMPTIVE_OS_LIBRARYOPS, PREEMPTIVE_OS_COUNTOPS, PREEMPTIVE_OS_CRYPTOPS, PREEMPTIVE_OS_QUERYMDN, PREEMPTIVE_OS_TXITPROC, REQUEST_FOR_DEADLOCK_SEARCH, RESOURCE_QUEUE, SLEEP_BPOOL, SLEEP_BUFFERPOOL, SLEEP_DBSTARTUP, SLEEP_DCOMSTARTUP, SLEEP_MASTERDBREADY, SLEEP_MASTERMDREADY, SLEEP_MASTERUPGRADED, SLEEP_MSDBSTARTUP, SLEEP_SYSTEMTASK, SLEEP_TASK, SLEEP_TEMPDBSTARTUP, SNI_HTTP_ACCEPT, SP_SERVER_DIAGNOSTICS_SLEEP, SQLTRACE_BUFFER_FLUSH, SQLTRACE_INCREMENTAL_FLUSH, SQLTRACE_WAIT_ENTRIES, WAIT_FOR_RESULTS, WAITFOR, WAITFOR_TASKSHUTDOWN, WHILE_TEMP, XE_BUFFERMGR_ALLOC, XE_DISPATCHER_WAIT, XE_DISPATCHER_JOIN, XE_MODULEMGR_CALLNOTIFICATION, XE_LOGGER_TICK, XE_TIMER_EVENT ) AND waiting_tasks_count 0 ORDER BY wait_time_ms DESC;這里我排除了大量自循環(huán)等待類型——比如SLEEP_TASK、WAITFOR這類屬于空閑等待如果不過(guò)濾掉它們會(huì)占掉大部分統(tǒng)計(jì)容易誤導(dǎo)判斷?？唇Y(jié)果時(shí)我主要關(guān)注三塊排名靠前的是PAGEIOLATCH_*說(shuō)明磁盤IO壓力大LCK_*說(shuō)明存在鎖阻塞CXPACKET說(shuō)明并行查詢等待較多。每種等待的應(yīng)對(duì)思路完全不同不能盲目加索引或加內(nèi)存。3.2 最耗時(shí)查詢排行直接找出“罪魁禍?zhǔn)住钡却y(tǒng)計(jì)確認(rèn)了方向后下一步就是把最慢的語(yǔ)句找出來(lái)。我用的是執(zhí)行計(jì)劃緩存中的統(tǒng)計(jì)信息SELECT TOP 10 qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms, qs.total_worker_time / qs.execution_count AS avg_cpu_ms, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.execution_count, qs.total_elapsed_time, qs.total_logical_reads, qs.creation_time, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) 1) AS statement_text, qs.plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY avg_elapsed_ms DESC;這條能列出平均耗時(shí)最長(zhǎng)、平均CPU最多、邏輯讀取最多的語(yǔ)句。我通常按平均邏輯讀取排序再看耗時(shí)因?yàn)檫壿嬜x太高的語(yǔ)句往往意味著全表掃描或索引缺失。得到plan_handle之后還可以進(jìn)一步查看該語(yǔ)句的完整執(zhí)行計(jì)劃SELECT query_plan FROM sys.dm_exec_query_plan(0x06000600...); -- 填入實(shí)際的 plan_handle注意query_plan返回的是XML格式在SSMS中點(diǎn)擊即可看到圖形化執(zhí)行計(jì)劃。這里有個(gè)隱藏坑執(zhí)行計(jì)劃緩存是動(dòng)態(tài)的實(shí)例重啟或內(nèi)存壓力大時(shí)舊的統(tǒng)計(jì)會(huì)被清掉。所以如果系統(tǒng)已經(jīng)重啟過(guò)或者跑了很長(zhǎng)時(shí)間sys.dm_exec_query_stats里只能看到緩存中的語(yǔ)句。想完整記錄歷史語(yǔ)句還是得提前開擴(kuò)展事件或第三方監(jiān)控不能只靠這一個(gè)視圖。3.3 缺失索引提示參考但不要無(wú)腦加SQL Server提供一個(gè)很實(shí)用的動(dòng)態(tài)管理視圖可以直接告訴你“它覺(jué)得哪里缺索引”SELECT migs.avg_total_user_cost * migs.avg_user_impact AS improvement_score, migs.avg_user_impact, migs.avg_total_user_cost, mid.database_id, mid.object_id, mid.statement AS table_name, mid.equality_columns, mid.inequality_columns, mid.included_columns, CREATE INDEX [IX_ OBJECT_NAME(mid.object_id, mid.database_id) _ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ), , , _), [, ), ], ) CASE WHEN mid.inequality_columns IS NOT NULL THEN _ REPLACE(REPLACE(REPLACE(mid.inequality_columns, , , _), [, ), ], ) ELSE END ] ON mid.statement ( ISNULL(mid.equality_columns, ) CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN , ELSE END ISNULL(mid.inequality_columns, ) ) CASE WHEN mid.included_columns IS NOT NULL THEN INCLUDE ( mid.included_columns ) ELSE END ; AS create_index_statement FROM sys.dm_db_missing_index_details mid JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle mid.index_group_handle WHERE mid.database_id DB_ID() ORDER BY improvement_score DESC;這個(gè)視圖會(huì)直接拼出建議的CREATE INDEX語(yǔ)句看起來(lái)非常方便但我要提醒一句缺失索引建議只是基于成本評(píng)估的“建議”不是“必須執(zhí)行”。我在實(shí)際中見(jiàn)過(guò)不少案例按提示加索引后單條查詢快了不少但整個(gè)表的寫入變慢甚至因?yàn)樗饕^(guò)多導(dǎo)致日志增長(zhǎng)和存儲(chǔ)膨脹。我的習(xí)慣是先人工判斷這個(gè)缺失索引對(duì)應(yīng)的查詢是否高頻涉及的列是否在WHERE和JOIN條件中出現(xiàn)選擇性是否夠好再加一個(gè)INCLUDE的列是否能讓查詢覆蓋。一條查詢?nèi)绻慌芤淮尉退泐A(yù)測(cè)收益99%也不值得加索引。4. 索引維護(hù)命令碎片檢測(cè)與重建/重組的完整決策鏈索引維護(hù)是SQL Server日常運(yùn)維里繞不開的話題但也是被誤解較多的。很多人只知道定期重建索引卻不問(wèn)為什么重建、碎片到什么程度才需要重建。這里把完整的命令決策鏈寫出來(lái)。4.1 碎片詳情查詢重建之前先看看數(shù)據(jù)執(zhí)行如下語(yǔ)句查看某個(gè)數(shù)據(jù)庫(kù)所有索引的碎片情況SELECT OBJECT_NAME(ps.object_id) AS table_name, i.name AS index_name, ps.index_type_desc, ps.page_count, ps.avg_fragmentation_in_percent, ps.avg_page_space_used_in_percent, ps.record_count, ps.ghost_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, SAMPLED) ps JOIN sys.indexes i ON ps.object_id i.object_id AND ps.index_id i.index_id WHERE ps.avg_fragmentation_in_percent 5 AND ps.page_count 100 AND ps.index_type_desc HEAP ORDER BY ps.avg_fragmentation_in_percent DESC;重點(diǎn)看兩列avg_fragmentation_in_percent代表碎片率avg_page_space_used_in_percent代表頁(yè)面填充率。碎片率只是表面指標(biāo)頁(yè)面填充率過(guò)低說(shuō)明頁(yè)內(nèi)空間浪費(fèi)嚴(yán)重即使碎片率不高也需要關(guān)注。這里我踩過(guò)一個(gè)大坑SAMPLED模式只采樣部分頁(yè)面掃描大表很快但結(jié)果可能不夠準(zhǔn)確。小表或者碎片極不均勻的表用SAMPLED可能漏掉某些碎片區(qū)。更準(zhǔn)確的是DETAILED模式但DETAILED會(huì)掃描全部頁(yè)面大表耗時(shí)很長(zhǎng)必須在維護(hù)窗口執(zhí)行。所以我的做法是日常巡檢用SAMPLED快速看維護(hù)日再針對(duì)重點(diǎn)大表用DETAILED做最終確認(rèn)。4.2 重建與重組的選擇按碎片率分檔處理查到碎片后按以下規(guī)則處理比較穩(wěn)妥碎片率低于5%不需要處理哪怕索引會(huì)繼續(xù)膨脹。碎片率在5%到30%之間執(zhí)行重組ALTER INDEX ... REORGANIZE這個(gè)是邏輯整理不重建物理結(jié)構(gòu)不會(huì)產(chǎn)生大量日志。碎片率大于30%執(zhí)行重建ALTER INDEX ... REBUILD物理上重新分配頁(yè)產(chǎn)生日志較多。對(duì)應(yīng)命令分別是-- 重組單個(gè)索引 ALTER INDEX [IX_表名_列名] ON [dbo].[表名] REORGANIZE; -- 重建單個(gè)索引 ALTER INDEX [IX_表名_列名] ON [dbo].[表名] REBUILD; -- 重建表上所有索引 ALTER INDEX ALL ON [dbo].[表名] REBUILD WITH (FILLFACTOR 90, ONLINE ON);如果生產(chǎn)環(huán)境是企業(yè)版重建索引時(shí)可以帶ONLINE ON這樣表在重建期間仍然可讀寫業(yè)務(wù)影響小很多標(biāo)準(zhǔn)版不支持在線重建通常只能選擇在維護(hù)窗口操作。另外重建大表索引前一定要預(yù)估好日志空間。曾經(jīng)有人在幾小時(shí)內(nèi)跑完重建結(jié)果日志文件把磁盤塞爆最后數(shù)據(jù)庫(kù)進(jìn)入只讀。更穩(wěn)妥的做法是對(duì)核心大表在重建前先備份數(shù)據(jù)庫(kù)或者把數(shù)據(jù)庫(kù)恢復(fù)模式臨時(shí)改成簡(jiǎn)單模式僅限于非關(guān)鍵庫(kù)并且要評(píng)估數(shù)據(jù)安全。4.3 填充因子與其他參數(shù)別讓索引維護(hù)白忙活FILLFACTOR是索引頁(yè)的填充百分比它直接影響后續(xù)碎片產(chǎn)生的速度。默認(rèn)值是0等價(jià)于100%即索引頁(yè)盡可能填滿。對(duì)于頻繁插入的表建議設(shè)置成90左右預(yù)留一部分空間給后續(xù)插入避免頁(yè)分裂導(dǎo)致碎片快速增加對(duì)于基本只讀的表可以保持默認(rèn)這樣索引更緊湊查詢IO更少。還有一個(gè)點(diǎn)容易被忽略——重建索引之后對(duì)應(yīng)的統(tǒng)計(jì)信息也會(huì)自動(dòng)更新。所以通常不需要再單獨(dú)執(zhí)行UPDATE STATISTICS。但如果某個(gè)查詢的執(zhí)行計(jì)劃一直不穩(wěn)定另當(dāng)別論。5. 阻塞與死鎖排查命令還原現(xiàn)場(chǎng)而不是只看到結(jié)果數(shù)據(jù)庫(kù)阻塞和死鎖是運(yùn)維中最頭疼的問(wèn)題之一。難點(diǎn)在于死鎖發(fā)生那一刻就結(jié)束了等你去查的時(shí)候現(xiàn)場(chǎng)已經(jīng)沒(méi)了。所以阻塞查詢要“實(shí)時(shí)”跑死鎖追蹤要“提前開”。以下是我排查這類問(wèn)題時(shí)實(shí)際用到的命令組合。5.1 實(shí)時(shí)阻塞鏈查詢從等待者找到源頭sp_who2是老一代DBA常用命令勝在簡(jiǎn)單EXEC sp_who2;但sp_who2返回的信息不夠結(jié)構(gòu)化也不方便過(guò)濾。我現(xiàn)在更常用這樣一條查詢能直接看到阻塞者和被阻塞者的關(guān)系SELECT r.session_id AS blocked_session_id, r.blocking_session_id AS blocking_session_id, r.wait_type, r.wait_time / 1000.0 AS wait_time_sec, r.status, t.text AS blocked_sql_text, b.text AS blocking_sql_text, es.host_name, es.login_name, es.program_name FROM sys.dm_exec_requests r LEFT JOIN sys.dm_exec_connections rc ON r.session_id rc.session_id LEFT JOIN sys.dm_exec_connections bc ON r.blocking_session_id bc.session_id CROSS APPLY sys.dm_exec_sql_text(rc.most_recent_sql_handle) t CROSS APPLY sys.dm_exec_sql_text(bc.most_recent_sql_handle) b JOIN sys.dm_exec_sessions es ON r.session_id es.session_id WHERE r.blocking_session_id 0 AND r.blocking_session_id IS NOT NULL;這條查詢把阻塞者和被阻塞者兩邊的SQL文本都列出來(lái)一眼就能看出是哪條語(yǔ)句鎖住了什么資源。有一次排查超時(shí)告警跑完后發(fā)現(xiàn)阻塞源頭是一個(gè)長(zhǎng)事務(wù)里的UPDATE語(yǔ)句一直在更新某大表的同一批記錄。往下一查發(fā)現(xiàn)更新條件字段上沒(méi)有索引每次更新都在全表掃描持有大量鎖不釋放后面所有涉及該表的操作全被擋在門外。后續(xù)在該字段上補(bǔ)了索引再配合業(yè)務(wù)側(cè)縮短事務(wù)時(shí)間問(wèn)題才徹底解決。5.2 歷史死鎖追蹤提前開啟跟蹤標(biāo)記死鎖不像阻塞可以現(xiàn)場(chǎng)看到更多時(shí)候需要我們翻歷史記錄。SQL Server默認(rèn)會(huì)記錄死鎖相關(guān)信息但信息比較簡(jiǎn)略。我建議在實(shí)例層面開啟跟蹤標(biāo)記1222和1204這樣死鎖發(fā)生時(shí)會(huì)把完整的資源和語(yǔ)句寫到錯(cuò)誤日志里DBCC TRACEON(1222, -1); DBCC TRACEON(1204, -1);開啟后查看錯(cuò)誤日志中的死鎖信息EXEC sp_readerrorlog 0, 1, deadlock;日志里會(huì)顯示類似“deadlock victim”的字樣以及涉及的資源、進(jìn)程ID和SQL語(yǔ)句。如果還嫌不夠直觀可以開擴(kuò)展事件來(lái)捕獲xml_deadlock_report事件保存死鎖圖形文件用SSMS打開直接看環(huán)形依賴關(guān)系。我個(gè)人在生產(chǎn)環(huán)境更推薦擴(kuò)展事件方式因?yàn)樗鼙４嫱暾乃梨i圖XML排查效率比看文本日志高很多。5.3 鎖粒度和隔離級(jí)別理解比命令本身更重要排查鎖問(wèn)題到深處必須理解隔離級(jí)別的影響。很多時(shí)候不是語(yǔ)句寫錯(cuò)了而是隔離級(jí)別設(shè)置不合理導(dǎo)致鎖持有時(shí)間過(guò)長(zhǎng)。比如默認(rèn)的READ COMMITTED下讀操作只加共享鎖讀完即釋放但如果改成REPEATABLE READ或SERIALIZABLE讀鎖會(huì)一直持有到事務(wù)結(jié)束極易造成阻塞放大。-- 查看當(dāng)前會(huì)話隔離級(jí)別 SELECT CASE transaction_isolation_level WHEN 0 THEN Unspecified WHEN 1 THEN READ UNCOMMITTED WHEN 2 THEN READ COMMITTED WHEN 3 THEN REPEATABLE READ WHEN 4 THEN SERIALIZABLE WHEN 5 THEN SNAPSHOT ELSE UNKNOWN END AS isolation_level FROM sys.dm_exec_sessions WHERE session_id SPID;針對(duì)某些報(bào)表類查詢?nèi)绻试S臟讀可以按語(yǔ)句級(jí)別加WITH (NOLOCK)提示把讀鎖徹底去掉但這要業(yè)務(wù)側(cè)接受可能出現(xiàn)的中途被修改的數(shù)據(jù)。對(duì)核心交易邏輯不建議用NOLOCK來(lái)繞過(guò)鎖問(wèn)題正確做法是優(yōu)化事務(wù)粒度、減少鎖持有時(shí)間或者開啟READ COMMITTED SNAPSHOTRCSI讓讀操作走行版本快照。6. 備份與完整性校驗(yàn)命令出事后才做的操作其實(shí)最需要提前演練備份算是SQL Server命令里最基礎(chǔ)也最不能出錯(cuò)的部分。我發(fā)現(xiàn)很多團(tuán)隊(duì)的備份腳本是“能跑就行”但從沒(méi)做過(guò)恢復(fù)演練真到出問(wèn)題時(shí)才發(fā)現(xiàn)備份文件損壞或恢復(fù)鏈斷裂。這里分享的是我實(shí)際使用的備份與校驗(yàn)命令組合。6.1 全量備份與差異備份參數(shù)不是隨便填的全量備份最常用的一條BACKUP DATABASE [數(shù)據(jù)庫(kù)名] TO DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120.bak WITH INIT, NAME N數(shù)據(jù)庫(kù)名-全量備份, COMPRESSION, CHECKSUM, STATS 10;幾個(gè)關(guān)鍵參數(shù)說(shuō)明一下。INIT表示覆蓋同名文件如果不用INIT而且目標(biāo)文件已存在備份會(huì)追加到文件里容易造成文件字節(jié)數(shù)增長(zhǎng)且不可讀。COMPRESSION啟用備份壓縮能顯著減小備份體積但也更消耗CPU資源——如果服務(wù)器CPU本身已經(jīng)長(zhǎng)期高位可以改成MAXTRANSFERSIZE調(diào)優(yōu)而不是啟用壓縮。CHECKSUM在校驗(yàn)備份時(shí)非常關(guān)鍵它會(huì)在備份過(guò)程中計(jì)算校驗(yàn)和RESTORE VERIFYONLY時(shí)就能檢查數(shù)據(jù)頁(yè)是否有損壞。差異備份的命令類似BACKUP DATABASE [數(shù)據(jù)庫(kù)名] TO DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120_diff.bak WITH DIFFERENTIAL, INIT, NAME N數(shù)據(jù)庫(kù)名-差異備份, CHECKSUM;需要強(qiáng)調(diào)差異備份是基于上一次全量備份的增量。如果全量備份被覆蓋或刪除中間所有差異備份都失效。所以備份文件的保留策略必須嚴(yán)格比如保留最近7天每日全量再加上當(dāng)天所有差異備份不要手動(dòng)去刪中間文件。6.2 事務(wù)日志備份恢復(fù)時(shí)間點(diǎn)的關(guān)鍵對(duì)恢復(fù)模式為FULL的數(shù)據(jù)庫(kù)必須定期備份事務(wù)日志BACKUP LOG [數(shù)據(jù)庫(kù)名] TO DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120_log.trn WITH INIT, CHECKSUM;日志備份做得好才能把數(shù)據(jù)庫(kù)恢復(fù)到任意時(shí)間點(diǎn)?，F(xiàn)實(shí)中我看到不少情況是全量備份和差異備份都有但日志備份頻率過(guò)低甚至一天才一次。如果數(shù)據(jù)庫(kù)在下午兩點(diǎn)宕機(jī)日志備份最后一次是早上六點(diǎn)中間的數(shù)據(jù)就只能丟。核心交易庫(kù)建議日志備份間隔控制在15分鐘以內(nèi)備份文件不大成本主要是IO開銷和時(shí)間精度。6.3 恢復(fù)校驗(yàn)備份的真正考驗(yàn)是能不能恢復(fù)備份完不等于萬(wàn)事大吉我用一條命令驗(yàn)證備份文件完整性RESTORE VERIFYONLY FROM DISK ND:\Backup\數(shù)據(jù)庫(kù)名_20260120.bak WITH CHECKSUM;這條只校驗(yàn)備份集完整性不實(shí)際恢復(fù)數(shù)據(jù)速度快可作為日常監(jiān)控任務(wù)的一部分。但需要說(shuō)明VERIFYONLY不能完全代替真正的恢復(fù)演練。我自己的做法是每季度至少做一次完整的RESTORE DATABASE到隔離環(huán)境的演練確認(rèn)備份文件能正?；謴?fù)并且應(yīng)用日志鏈后能得到期望時(shí)間點(diǎn)。偶爾遇到備份文件在校驗(yàn)時(shí)報(bào)CHECKSUM錯(cuò)誤的情況就是因?yàn)閭浞葸^(guò)程中磁盤IO異常或文件受損這種時(shí)候必須盡快定位物理磁盤問(wèn)題重新做備份不能繼續(xù)往這個(gè)文件上追加任何數(shù)據(jù)。6.4 數(shù)據(jù)庫(kù)完整性檢查DBCC CHECKDB的正確打開方式除了備份我還把DBCC CHECKDB納入月度維護(hù)計(jì)劃。它檢查數(shù)據(jù)庫(kù)邏輯和物理完整性能發(fā)現(xiàn)頁(yè)損壞、分配錯(cuò)誤、邏輯一致性等問(wèn)題DBCC CHECKDB([數(shù)據(jù)庫(kù)名]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY;大多數(shù)情況下生產(chǎn)庫(kù)我建議用PHYSICAL_ONLY模式因?yàn)樗粰z查物理結(jié)構(gòu)不檢查所有邏輯約束耗時(shí)比完整檢查短很多。維護(hù)窗口充足時(shí)再跑不帶PHYSICAL_ONLY的完整檢查。還有一個(gè)實(shí)際建議CHECKDB也是CPU和IO密集操作盡量不要和索引重建、全量備份安排在同一時(shí)間段執(zhí)行免得互相搶資源。7. 把命令整理成自己的巡檢清單多跑幾遍你也有手感最后分享一個(gè)我自己堅(jiān)持的習(xí)慣。上面這些命令不是每次都需要全部跑一遍我的做法是分為日常巡檢、周度巡檢、月度維護(hù)三檔對(duì)應(yīng)不同頻率和深度。日常巡檢每天早上一遍跑會(huì)話狀態(tài)查詢、數(shù)據(jù)庫(kù)文件大小、DBCC OPENTRAN、備份是否成功。十分鐘內(nèi)完成腳本里加好時(shí)間戳和狀態(tài)高亮異常時(shí)直接標(biāo)紅。周度巡檢加跑等待類型匯總、碎片率抽樣、最耗時(shí)查詢排行用于發(fā)現(xiàn)性能退化趨勢(shì)。月度維護(hù)跑CHECKDB、索引重組或重建、季度恢復(fù)演練、完整死鎖日志分析。把這些命令組合到一起后你會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)問(wèn)題大多是漸變的——日志文件在悄悄變大某條語(yǔ)句的邏輯讀在逐步上升等待類型占比在慢慢偏移。只看某一天的快照很難發(fā)現(xiàn)問(wèn)題持續(xù)按固定頻率采集并對(duì)比才能抓住趨勢(shì)。關(guān)于這些命令本身我再補(bǔ)充一個(gè)建議新建一個(gè)專門的查詢腳本文件把每條命令都寫好注釋和排序條件別到用的時(shí)候臨時(shí)打開SSMS手敲。人總是會(huì)記憶模糊的尤其凌晨被叫起來(lái)處理故障時(shí)一份固化的巡檢腳本比什么都可靠。我?guī)状瘟璩颗挪閱?wèn)題能十分鐘定位靠的就是這份提前寫好的腳本以及平時(shí)養(yǎng)成的固定執(zhí)行節(jié)奏。