控的實用技巧)
我用了十多年SQL Server有很長一段時間都靠命令行和一個個SQL腳本“硬撐”直到把SQL Server Management Studio下面統(tǒng)一叫SSMS的一些高頻功能和隱藏技巧真正吃透之后才發(fā)現(xiàn)這工具不管對新手還是老手節(jié)省下來的時間都不是一星半點。說白了SSMS就是微軟家的SQL Server圖形化管理客戶端它能干的事遠不止“寫個查詢點點運行”這么簡單。建庫建表、權(quán)限分配、執(zhí)行計劃分析、性能監(jiān)控、數(shù)據(jù)備份恢復(fù)甚至把重復(fù)性操作自動生成腳本都能在這個窗口里完成。很多新手下的第一個客戶端就是它卻只用了10%的功能很多老手嘴上說“命令行效率高”實際排查死鎖和看IO統(tǒng)計的時候還是老老實實切回SSMS。這篇內(nèi)容沒有任何花哨的包裝我會直接按實際工作流來拆解連接實例、寫查詢、看執(zhí)行計劃、管理對象、監(jiān)控排查、日常避坑。每個環(huán)節(jié)都會給出我在真實環(huán)境里驗證過的操作方式和參數(shù)依據(jù)同時也把那些文檔里不怎么會寫、但是實際總會撞上的“坑”拎出來講清楚。1. 初識SSMS版本選擇、連接方式和基本布局1.1 為什么幾乎所有人都選SSMS作為主要管理工具先說明一個背景SQL Server本身是一個數(shù)據(jù)庫引擎像發(fā)動機一樣跑在機器上而SSMS就是駕駛艙。駕駛艙這個類比很貼切因為儀表盤上的各種窗口——對象資源管理器、查詢編輯器、活動監(jiān)視器本質(zhì)上就是把發(fā)動機狀態(tài)呈現(xiàn)給你看同時把你的操作指令翻譯給引擎執(zhí)行?？赡苡腥藭柤热籗QL Server不裝SSMS也能跑那是不是用其他第三方工具也行。答案是“行”但SSMS有幾個一直沒被替代的優(yōu)勢。第一官方發(fā)布和SQL Server的版本兼容性最好不會出現(xiàn)連不上的版本錯位問題。第二功能覆蓋最完整。早期版本的SQL Server還能靠命令行“裸奔”但到了較新的版本里很多新功能、新DMV動態(tài)管理視圖、新的圖形化監(jiān)控界面第三方工具要么落后一個版本要么做得不完整而SSMS永遠是同步的。第三資源占用適中內(nèi)存和CPU開銷控制得比一堆Web版管理面板要好至少不會因為開個管理工具就把開發(fā)機拖卡。SSMS本身是免費分發(fā)的不需要額外買授權(quán)只要是SQL Server的合法用戶就能直接從官方渠道下載。這一點在很多企業(yè)里非常關(guān)鍵因為數(shù)據(jù)庫軟件采購?fù)^好幾輪審批而SSMS作為客戶端工具零授權(quán)成本裝上就能用。1.2 下載版本與安裝時的關(guān)鍵選項很多新手下載SSMS的時候會犯一個錯直接在搜索引擎里搜“SQL Server Management Studio下載”結(jié)果下到一堆第三方打包的舊版本或者干脆下成了SQL Server Express的完整安裝包體積又大還一堆用不上的組件。正確做法是去微軟官方文檔頁面找“Download SQL Server Management Studio (SSMS)”入口認準版本號。SSMS是獨立于SQL Server的版本機制目前主流穩(wěn)定版本通常按年份和序號命名比如18.x、19.x、20.x這樣遞增。需要特別注意的是SSMS并不是必須和實例版本完全一致才能連SSMS高版本一般可以連低版本的SQL Server實例反過來低版本SSMS連高版本實例就可能出現(xiàn)不支持的情況所以原則上裝新不裝舊。安裝過程本身沒什么難度一路默認就可以。但有兩個選項值得關(guān)注。一個是安裝路徑默認裝在C盤如果你機器C盤空間緊張一定把它改到D盤或其他數(shù)據(jù)盤因為SSMS會隨版本更新頻繁升級舊版本殘留和新版本緩存加起來占用空間比你想象的大。另一個是“SQL SERVER MANAGEMENT SYSTEM”這類附帶功能默認全選就好里面包含了SQL Profiler、數(shù)據(jù)庫引擎優(yōu)化顧問等附屬工具這些都是排查問題時必備的。裝完之后第一次打開會看到連接對話框這里有幾個細節(jié)。服務(wù)器類型默認是“數(shù)據(jù)庫引擎”一般不用改。服務(wù)器名稱可以直接寫實例名也可以寫主機名加實例名比如“某服務(wù)器\SQLEXPRESS”。如果本機裝了默認實例寫一個點號“.”或者“(local)”就能連上本機。身份驗證選“Windows身份驗證”還是“SQL Server身份驗證”取決于實例的認證模式。如果選了后者需要確保實例啟用了混合認證模式否則登錄會被拒絕。1.3 連接實例后的界面習(xí)慣配置連上實例之后第一件事不是急著建庫而是把界面按自己的習(xí)慣調(diào)整好這一步非常影響后續(xù)效率。對象資源管理器默認在左側(cè)建議保持展開。它按“數(shù)據(jù)庫、安全性、服務(wù)器對象、復(fù)制、管理”幾個大節(jié)點組織你的絕大多數(shù)日常操作都能在這里找到入口。右鍵任意節(jié)點會出現(xiàn)上下文菜單菜單里的選項會隨節(jié)點類型變化比如右鍵“數(shù)據(jù)庫”節(jié)點會有“新建數(shù)據(jù)庫”右鍵某個表節(jié)點則有“設(shè)計”“選擇前1000行”“編輯前200行”這些快捷操作。查詢編輯器是核心工作區(qū)。默認情況下雙擊某個表不會彈查詢窗口需要手動點“新建查詢”或者按快捷鍵CtrlN。我個人強烈建議先到“工具 - 選項 - 環(huán)境 - 鍵盤”里把常用快捷鍵背下來。比如CtrlN新建查詢窗口F5執(zhí)行當前選中的腳本或者執(zhí)行整個窗口里所有腳本CtrlShfitR刷新智能感知緩存這個在新建了表或者改了結(jié)構(gòu)之后特別有用CtrlR顯示/隱藏結(jié)果面板CtrlShiftM在查詢編輯器中插入模板參數(shù)這個屬于進階技巧后面會細說還有一個容易被忽略的選項“工具 - 選項 - 查詢結(jié)果 - SQL Server - 將結(jié)果保存到文件”的默認編碼和結(jié)果格式。如果日常經(jīng)常要用SSMS導(dǎo)出查詢結(jié)果給業(yè)務(wù)同學(xué)建議把默認結(jié)果格式設(shè)置成CSV并且把“執(zhí)行查詢后自動保存”打開這樣每次查詢完可以直接落盤。默認那個網(wǎng)格結(jié)果粘貼到Excel里很容易出現(xiàn)格式錯亂尤其涉及超過15位的數(shù)字ID時會被Excel自動轉(zhuǎn)成科學(xué)計數(shù)法臉上笑嘻嘻心里已經(jīng)在罵人了。2. 查詢編輯器從寫腳本到調(diào)優(yōu)的完整鏈路2.1 智能感知IntelliSense的正確打開方式很多人吐槽SSMS的智能感知“不好用”“不提示”其實大部分時候是沒開啟或者緩存沒刷新。檢查方法是在查詢編輯器里輸入前幾個字母如果沒有任何代碼提示優(yōu)先確認“工具 - 選項 - 文本編輯器 - Transact-SQL - IntelliSense”下的“啟用IntelliSense”復(fù)選框是否勾選。勾選之后重啟查詢窗口一般就能生效。智能感知生效之后它有幾種很實用的表現(xiàn)輸入表名、視圖名、存儲過程名時會彈出候選列表。輸入表名后輸入空格或點號會列出這個表的列名。輸入SELECT后開始輸入列名能基于已選表補全提示。函數(shù)名、系統(tǒng)變量、關(guān)鍵字也都有提示。不過智能感知有一個歷史遺留問題它對剛創(chuàng)建的新表經(jīng)?！翱床灰姟?。比如你剛用腳本建了一張表然后在同一個查詢窗口里直接寫SELECT FROM這張表怎么敲都不提示。這時候按CtrlShiftR刷新IntelliSense緩存或者直接新開一個查詢窗口問題就消失了。實操層面還有一個習(xí)慣值得培養(yǎng)寫SELECT之前先把表名列出來不要一上來就寫“SELECT * FROM 某表”。智能感知在完整列出表結(jié)構(gòu)后對后續(xù)條件的補全準確率要高很多。雖然這主要是個人代碼習(xí)慣問題但配合智能感知使用會讓整個編碼體驗順滑不少。2.2 執(zhí)行與結(jié)果交互的關(guān)鍵設(shè)置查詢窗口寫好后F5執(zhí)行是標配但很多人沒認真看過執(zhí)行結(jié)果面板的類型。SSMS有三種結(jié)果模式。文本結(jié)果CtrlT結(jié)果以文本方式展示適合快速目測小結(jié)果集。網(wǎng)格結(jié)果CtrlD默認模式適合查看表格數(shù)據(jù)、復(fù)制到Excel。文件結(jié)果CtrlShiftF直接把結(jié)果輸出到文件。日常用得最多的是網(wǎng)格結(jié)果。但如果查詢結(jié)果很大比如幾十萬行直接拖回客戶端會非常慢而且內(nèi)存占用會飆升。這時候把結(jié)果改成文件輸出讓服務(wù)端把結(jié)果流式寫入文件更穩(wěn)妥。還有一個很多人沒意識到的問題SSMS默認在“結(jié)果”頁簽旁邊會有一個“消息”頁簽里面會顯示影響的行數(shù)和查詢時間。想看執(zhí)行計劃、客戶端統(tǒng)計信息、IO統(tǒng)計都需要先開啟對應(yīng)選項。顯示實際執(zhí)行計劃快捷鍵CtrlM開啟后再按F5查詢執(zhí)行完會自動多出一個“執(zhí)行計劃”頁簽這是分析SQL性能最重要的入口一定要養(yǎng)成習(xí)慣。包括實際的執(zhí)行計劃CtrlShiftM這個對應(yīng)的是預(yù)估執(zhí)行計劃常用在不用真正執(zhí)行、只檢查查詢寫法是否會走合適的索引時。包括客戶端統(tǒng)計信息在“查詢 - 查詢選項 - 高級”里勾選能查看等待時間、處理時間等指標。包括服務(wù)器端IO統(tǒng)計和時間統(tǒng)計對應(yīng)SET STATISTICS IO ON; 和SET STATISTICS TIME ON;這兩個命令可以直接寫在查詢窗口頂部特別適合對比不同寫法的性能差異。我在實際優(yōu)化慢查詢時固定套路就是先CtrlM打開實際執(zhí)行計劃執(zhí)行一次看IO統(tǒng)計和耗時然后改寫查詢再對比兩次IO統(tǒng)計里“邏輯讀取”和“掃描計數(shù)”的變化。邏輯讀數(shù)是判斷SQL好壞的核心指標之一因為它不依賴硬件環(huán)境差異是一致可比的對象。2.3 執(zhí)行計劃里最容易忽略的“反常識”細節(jié)很多人一開始看執(zhí)行計劃就盯著“缺失索引提示”總覺得只要按提示建了索引性能就上來了。但SSMS的缺失索引提示只是一個“基于當前查詢估算”的建議有時候它建議建的索引是一個中看不中用的巨型復(fù)合索引列順序不對或者包含列太多不僅消耗額外磁盤空間還會拖慢寫入。正確做法不是看到提示就建索引而是先看執(zhí)行計劃里運算子的物理操作類型。常見的幾個坑如下。表掃描Table Scan毫無索引可用整表遍歷小表無所謂大表必優(yōu)化。聚集索引掃描Clustered Index Scan有聚集索引但查詢條件沒法利用它定位本質(zhì)上也是掃全表只是代價比堆表掃描稍微可控一點。索引查找Index Seek這才是理想狀態(tài)表示通過索引B樹定位到了極少量數(shù)據(jù)。鍵查找Key Lookup非聚集索引命中了但還需要回到聚集索引去取其他列的數(shù)據(jù)。如果返回的列多鍵查找次數(shù)巨大反而比掃描更慢。新手最容易栽在“鍵查找”上。判斷基準很簡單當鍵查找的次數(shù)達到數(shù)萬甚至數(shù)十萬級別時優(yōu)化器可能已經(jīng)悄悄選擇表掃描了。解決方式是使用覆蓋索引把查詢需要返回的列都包含進索引的包含列中。執(zhí)行計劃里還有一個細節(jié)兩個表做Hash連接時如果右側(cè)輸入是大量數(shù)據(jù)而左側(cè)只有幾條時要注意觀察估計行數(shù)和實際行數(shù)是否嚴重不一致。這個不一致往往源于過期的統(tǒng)計信息。遇到這種情況最快的方法是對相關(guān)表執(zhí)行UPDATE STATISTICS而不是急著改SQL寫法。2.4 模板資源管理器被低估的腳本加速器SSMS里有一個“模板資源管理器”窗口默認可能沒打開可以在“視圖”菜單里找到。它內(nèi)置了大量模板建表、建索引、備份恢復(fù)、添加作業(yè)、創(chuàng)建登錄用戶等幾乎覆蓋所有常見DDL/DML操作。模板的價值不在于“內(nèi)容有多高級”而在于它的參數(shù)化機制。打開任何一個模板按CtrlShiftMSSMS會彈出“替換模板參數(shù)”對話框自動列出模板里的變量比如表名、架構(gòu)名、數(shù)據(jù)文件路徑這些。用戶只需要填一次值就能生成完整腳本。舉一個實際場景新接一個項目需要給三十張表逐一加一個統(tǒng)一命名規(guī)范的審計字段。如果手寫腳本每張表復(fù)制黏貼改表名半小時就沒了。從模板資源管理器里拉出“添加列”的模板用CtrlShiftM填充列名和數(shù)據(jù)類型然后批量替換表名五分鐘搞定。還有一個配套功能是“代碼片段”。在查詢編輯器里右鍵 - “插入代碼段”可以調(diào)出光標位置的SQL代碼模板比如創(chuàng)建存儲過程、創(chuàng)建臨時表、WHILE循環(huán)這些。自己常用的代碼塊也可以提前存成代碼片段放進SSMS的“我的代碼片段”目錄里下次插入時一鍵調(diào)出。3. 對象管理建庫建表、權(quán)限分配與自動化生成腳本3.1 圖形化建表時的默認值陷阱SSMS的圖形化建表界面很友好右擊“表”節(jié)點選擇“新建”就能彈出一個表格設(shè)計器可以直接鍵入列名、選擇數(shù)據(jù)類型、設(shè)置長度和允許Null。但越友好的界面越容易埋坑這里有幾個重點必須強調(diào)。第一SSMS在圖形化建表時默認可能會勾選“允許 Null 值”但業(yè)務(wù)上很多字段明明不該為空。在下線之前一定要根據(jù)業(yè)務(wù)仔細過一遍不然上線后的數(shù)據(jù)清洗和校驗工作會非常痛苦。第二decimal類型在建表時精度和小數(shù)位數(shù)如果不理解清楚隨便填一個比如decimal(10,2)事后要擴容不僅麻煩還可能造成阻塞。第三圖形化建表界面里默認值是直接寫在“默認值或綁定”那一列但如果你不寫這個字段就沒有默認值約束插入數(shù)據(jù)時漏了就會報錯或插Null。實際業(yè)務(wù)里創(chuàng)建時間和更新時間這類字段最好在建表時就設(shè)置默認值。再補充一個設(shè)計器的大坑在SSMS的“設(shè)計”視圖里修改表結(jié)構(gòu)比如改列的數(shù)據(jù)類型、加一個不允許Null的列SSMS默認是“阻止保存要求重新創(chuàng)建表的更改”。一旦這個設(shè)置開啟很多看似簡單的改動都無法直接保存而且當你去改列順序或者加非空列時SSMS會提示必須重建表。重建表意味著整表數(shù)據(jù)復(fù)制到臨時表、重新創(chuàng)建索引、重新設(shè)置權(quán)限一旦表數(shù)據(jù)量超過幾百萬行這個過程極容易造成長時間鎖表。所以有兩個建議如果只是加一個允許Null的新列直接ALTER TABLE ADD列是最快的。如果要修改列類型、刪除列、改約束應(yīng)該評估用腳本ALTER操作而不是在圖形界面里拖來拖去。3.2 數(shù)據(jù)庫屬性里的關(guān)鍵配置項恢復(fù)模式與自動收縮在數(shù)據(jù)庫上右鍵 - “屬性”能進入數(shù)據(jù)庫級別的配置面板。新手一般只看“常規(guī)”頁里的排序規(guī)則和數(shù)據(jù)庫名稱但有兩個配置項需要特別關(guān)注。一個是“選項”頁里的“恢復(fù)模式”。默認是“完整”模式。在完整模式下所有事務(wù)都會寫入事務(wù)日志數(shù)據(jù)備份時可以利用日志做時間點恢復(fù)但對初學(xué)者來說如果不配套進行定期日志備份事務(wù)日志會不斷膨脹很快就能撐爆磁盤。實際項目里只要不是核心交易庫完全可以用“簡單”恢復(fù)模式讓SQL Server自動回收失效日志空間省心很多。另一個是“自動收縮”選項默認是False。但有些版本的SSMS在新建數(shù)據(jù)庫時不會自動開啟而網(wǎng)上很多教程建議開自動收縮這是誤導(dǎo)。自動收縮開啟后文件會在每次釋放大量空間后收縮到很小但下一次大量數(shù)據(jù)寫入時又得重新增長和初始化文件產(chǎn)生大量的文件增長操作頻繁影響性能。我見過不少數(shù)據(jù)庫因為開著自動收縮每幾分鐘就發(fā)生一次文件自動擴展寫日志和IO都受到嚴重影響。正確做法是關(guān)閉自動收縮手動定期評估文件大小一次性回收。3.3 權(quán)限管理既別“一刀切”也別“裸奔”權(quán)限管理這塊SSMS有圖形化界面也有腳本方式。圖形化操作是在“安全性 - 登錄名”中新建登錄名然后在“用戶映射”里把登錄名映射到具體數(shù)據(jù)庫打勾分配數(shù)據(jù)庫角色。常見誤區(qū)是給業(yè)務(wù)賬號直接分配db_owner角色。db_owner在對應(yīng)數(shù)據(jù)庫內(nèi)幾乎擁有所有權(quán)限可以刪除表、修改結(jié)構(gòu)。很多外包項目為了省事全員db_owner等出問題時才發(fā)現(xiàn)連審計的痕跡都不好排查。更穩(wěn)妥的做法是建立一個“最小權(quán)限”賬號組只授予SELECT、INSERT、UPDATE、DELETE這類DML權(quán)限schema和DDL權(quán)限單獨把控。在SSMS圖形界面里不用細摳每一個權(quán)限可以在“用戶映射”頁面選擇“public”和“db_datareader”、“db_datawriter”這類預(yù)定義角色然后針對個別表通過“安全對象”頁面單獨授權(quán)。權(quán)限腳本化也很簡單右擊登錄名或用戶 - “編寫腳本為” - “CREATE 到” - “新查詢編輯器窗口”就能得到完整創(chuàng)建腳本。這個方法對所有對象都通用是SSMS里最能“復(fù)制黏貼”的自動化技巧。3.4 把重復(fù)操作做成腳本生成腳本與計劃任務(wù)日常運維中很多操作都是重復(fù)的每周備份一次數(shù)據(jù)庫、定期清理歷史表、把某張表的數(shù)據(jù)同步到分析庫。SSMS在做這類事情時最核心的價值不是“記住操作步驟”而是把所有操作變成一條可保存的腳本或作業(yè)。以備份為例右擊數(shù)據(jù)庫 - 任務(wù) - 備份在圖形界面上跑完一次后不必再手動做第二次。可以改成在“SQL Server代理” - “作業(yè)”里新建作業(yè)步驟里選擇“T-SQL腳本”把備份腳本填進去然后配置“計劃”選擇每周執(zhí)行一次。SQL Server代理的作業(yè)里還可以設(shè)置“步驟之間失敗轉(zhuǎn)移”。比如備份步驟失敗時自動執(zhí)行一個“發(fā)送郵件警告”的步驟這樣DBA不需要時刻盯著任務(wù)日志。配置數(shù)據(jù)庫郵件功能時也是在SSMS里操作在“管理 - 數(shù)據(jù)庫郵件”中配置SMTP服務(wù)器然后在“SQL Server代理 - 運算符”里綁定郵件通知。配置成功后作業(yè)執(zhí)行失敗會自動發(fā)警報到郵箱。不過SQL Server代理服務(wù)可能不是默認啟動的。新裝的實例中“SQL Server代理”服務(wù)狀態(tài)往往是“停止”需要在“配置管理器”里啟動并且設(shè)為自動啟動。很多人拍了半天桌子說“作業(yè)怎么不跑”十有八九就是代理服務(wù)根本沒啟動。4. 性能監(jiān)控與故障排查活動監(jiān)視器、死鎖和慢查詢4.1 活動監(jiān)視器怎么看活動監(jiān)視器是SSMS自帶的直觀性能儀表盤入口在“對象資源管理器”的根節(jié)點下方也可以在某個數(shù)據(jù)庫實例上右鍵 - “活動監(jiān)視器”打開。打開后默認有四個區(qū)塊概覽、等待任務(wù)、數(shù)據(jù)文件IO、最近耗費大量資源的查詢。概覽區(qū)域里能看到CPU時間百分比、等待時間百分比、數(shù)據(jù)庫IO百分比和批處理請求量。這里的“等待時間”不是指查詢執(zhí)行消耗的時間而是指SQL因為某些資源如鎖、IO、內(nèi)存而“卡住”的時間。如果等待時間長期偏高意味著系統(tǒng)有瓶頸。舉個例子某段時間“等待類型”大量是PAGEIOLATCH_SH說明正在經(jīng)歷大量磁盤頁讀取并等待磁盤IO完成這時候加內(nèi)存、換SSD、優(yōu)化索引都能緩解。而如果等待類型主要是LCK_M_X則表明大量會話在等待鎖這通常是邏輯問題比如事務(wù)過長、索引缺失導(dǎo)致鎖范圍過大單純加硬件沒用。最近耗費大量資源的查詢列表默認按CPU時間排序。它給出的只是“命令文本”和“運行中的總CPU時間”等指標。點開某條查詢可以看到對應(yīng)的執(zhí)行計劃按鈕點擊后能進一步看到這個查詢的詳細執(zhí)行計劃。這個功能對于快速定位“現(xiàn)在哪個查詢最占資源”非常方便尤其適合線上問題初步排查。4.2 死鎖與阻塞把所有線索同步到一條命令里死鎖排查方面SSMS里最常見的方式是開啟跟蹤標志1222或者1204把死鎖圖形事件抓出來。SSMS沒有直接為死鎖圖形提供實時界面需要借助擴展事件或者SQL Server Profiler來捕獲但這些工具在SSMS的“工具”菜單下都有入口。完整做法是這樣的打開SQL Server Profiler新建跟蹤。在“事件選擇”里勾選“Deadlock graph”事件跟蹤。當死鎖發(fā)生時跟蹤日志里會生成一個圖形化死鎖節(jié)點兩個進程、資源對象和鎖模式都一目了然。把捕獲到的死鎖圖導(dǎo)出成文件作為后續(xù)問題和業(yè)務(wù)方溝通的依據(jù)。死鎖的“圖形”看起來像兩個進程各握著一個資源然后都在等對方手里的另一個資源。圖形里的鎖模式通常顯示為“排他鎖”和“共享鎖”兩個箭頭交織成一個循環(huán)。依據(jù)這個圖能快速定位是哪兩張表的哪個索引導(dǎo)致的死鎖然后從業(yè)務(wù)角度統(tǒng)一訪問順序、縮小事務(wù)范圍而不是單純靠提高隔離級別來壓問題。如果是阻塞問題某一個會話卡住了另一個會話SSMS里最常見的排查方式是用系統(tǒng)視圖SELECT blocking.session_id AS 阻塞會話ID, blocking.blocking_session_id AS 被阻塞會話ID, blocking.wait_type, blocking.wait_time, blocking.wait_resource, sql_text.text AS 當前SQL FROM sys.dm_exec_requests AS blocking CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) AS sql_text WHERE blocking.blocking_session_id IS NOT NULL這條命令能在阻塞發(fā)生時把阻塞者和被阻塞者當前的SQL都帶出來。鎖定資源的具體描述比如“key: hash(某值)”可以配合DBCC SQLPERF或者專用查詢翻譯成具體的表名和索引鍵值。SSMS里支持把結(jié)果網(wǎng)格直接跳轉(zhuǎn)到對應(yīng)對象只要點擊資源名旁邊相關(guān)的項即可。4.3 慢查詢定位不只是看總耗時慢查詢的定位有很多維度。SSMS的“活動監(jiān)視器”只能看到當前正在運行的查詢過去已經(jīng)跑完的慢查詢需要借助DMV或者查詢存儲Query Store功能。查詢存儲是較新版本SQL Server自帶的功能默認是關(guān)閉狀態(tài)需要在數(shù)據(jù)庫屬性 - “選項” - “查詢存儲”里啟用。啟用后它會把所有查詢的編譯信息和執(zhí)行計劃實時記錄下來包括平均耗時、邏輯讀寫、物理讀寫、執(zhí)行次數(shù)等。遇到“昨天還正常今天突然某個報表頁變慢”的問題用查詢存儲可以直接查看某條查詢在過去幾天的執(zhí)行耗時曲線以及對應(yīng)的執(zhí)行計劃變化。這個功能比在日志里翻SQL文本高效得多因為問題往往不是SQL寫法變了而是統(tǒng)計信息更新或參數(shù)嗅探導(dǎo)致執(zhí)行計劃漂移查詢存儲里能直觀體現(xiàn)出“計劃變了性能退化”的因果鏈條。沒有開啟查詢存儲的老庫臨時分析時可以用以下DMVSELECT TOP 20 qs.total_worker_time / qs.execution_count AS 平均CPU時間, qs.total_logical_reads / qs.execution_count AS 平均邏輯讀, qs.total_elapsed_time / qs.execution_count AS 平均耗時, qs.execution_count AS 執(zhí)行次數(shù), 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 sql_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.total_worker_time DESC這條語句能快速找出累計CPU時間最高的前20條SQL。但要注意total_worker_time是“累計”而非“單次”如果一條SQL執(zhí)行了一百萬次單次開銷很小累計也很高。所以看數(shù)據(jù)時一定要結(jié)合平均耗時和平均邏輯讀來綜合判斷不能只看累計值。5. 備份恢復(fù)與日常操作避坑實錄5.1 備份腳本的正確寫法和標準實踐SSMS的圖形化備份功能非常簡單點幾下就能完成一次備份。但生產(chǎn)環(huán)境的備份不是“能備份就行”必須考慮恢復(fù)目標。我每次給新庫配置備份時都會寫一段類似這樣的完整備份腳本BACKUP DATABASE [你的數(shù)據(jù)庫] TO DISK ND:\Backup\你的數(shù)據(jù)庫_20250601.bak WITH INIT, NAME N你的數(shù)據(jù)庫-完整備份, COMPRESSION, CHECKSUM, STATS 10;這里面每一句話都有講究。WITH INIT表示覆蓋同名文件避免同一天的重復(fù)備份追加成多段備份導(dǎo)致文件越來越大。COMPRESSION啟用備份壓縮備份文件體積能減少一半以上而且SQL Server可以把壓縮卸載到備份過程中恢復(fù)時也能自動解壓。CHECKSUM開啟校驗和計算在備份時能檢測頁損壞避免靜默的壞頁一路帶到備份中。STATS 10表示每完成10%就輸出一次進度方便觀察大庫備份進度。事務(wù)日志備份則寫成BACKUP LOG完整恢復(fù)模式的數(shù)據(jù)庫建議開啟定期日志備份。如果從來不做日志備份完整恢復(fù)模式下的日志會一直累積到下一次完整備份才截斷時間一長磁盤就滿了。5.2 恢復(fù)操作中最常見的“假成功”備份恢復(fù)時SSMS圖形界面有一個默認選項“覆蓋現(xiàn)有數(shù)據(jù)庫”很多新手沒注意直接勾上恢復(fù)完卻發(fā)現(xiàn)數(shù)據(jù)庫還是舊的或者恢復(fù)失敗然后一臉懵。實際原因是一些恢復(fù)細節(jié)沒配好。正確做法如下。第一恢復(fù)時一定要指定“選項”頁里的“恢復(fù)到”狀態(tài)。如果選項是RESTORE WITH RECOVERY恢復(fù)完成后數(shù)據(jù)庫會處于在線狀態(tài)可以直接訪問如果選了RESTORE WITH NORECOVERY數(shù)據(jù)庫會處于“正在還原”狀態(tài)用戶可以繼續(xù)追加備份文件比如從差異備份或日志備份做增量還原這個模式適合搭建可用性組或做時間點恢復(fù)但在普通單機場景里如果最后沒有執(zhí)行RECOVERY數(shù)據(jù)庫會一直顯示“正在還原”看起來像假死了。第二默認恢復(fù)時數(shù)據(jù)文件路徑是備份文件記錄的原始路徑。如果源庫和目標庫的磁盤盤符不一致恢復(fù)會直接報錯“找不到文件”。解決方法是在“文件”頁里手動修改邏輯文件名對應(yīng)的物理路徑到目標位置。這個坑幾乎每個從一臺服務(wù)器遷移到另一臺服務(wù)器的同學(xué)都會踩一次。第三恢復(fù)前要確認備份文件的完整性?？梢杂靡韵旅顑H讀取備份信息不實際恢復(fù)RESTORE HEADERONLY FROM DISK ND:\Backup\你的數(shù)據(jù)庫.bak; RESTORE FILELISTONLY FROM DISK ND:\Backup\你的數(shù)據(jù)庫.bak;這兩條命令能快速查看備份集里包含了哪些文件、備份時間、是否包含日志等。在真正執(zhí)行恢復(fù)之前先跑這兩個命令是一種很穩(wěn)的低成本驗證方式。5.3 千萬別在生產(chǎn)庫上做的幾件“小事”有些操作在生產(chǎn)環(huán)境中一旦執(zhí)行后果可能比丟了數(shù)據(jù)還麻煩。第一件在SSMS里直接雙擊表然后“編輯前200行”手工改數(shù)據(jù)。圖形化編輯數(shù)據(jù)很容易因為誤操作把某一行改了而且沒有臨時保存的概念改動會跟著緩沖一起提交。如果一定要手工改先執(zhí)行SELECT … FOR UPDATE或者用事務(wù)包裹確認無誤后提交。更穩(wěn)妥的方式是寫UPDATE腳本放事務(wù)里執(zhí)行。第二件不用WHERE條件直接對全表執(zhí)行UPDATE或者DELETE。很多人會在開發(fā)環(huán)境寫順了切到生產(chǎn)窗口忘記把WHERE補上等回車了才發(fā)現(xiàn)“影響行數(shù)”是幾十萬。這類事故已經(jīng)成了數(shù)據(jù)庫事故排行榜里的?？?。習(xí)慣性做法是先把SELECT寫出來看行數(shù)再把SELECT改成UPDATE或DELETE并對影響行數(shù)做二次確認。第三件隨便重啟SQL Server服務(wù)。在SSMS里右擊實例選擇“重啟”會把當前所有連接斷開正在跑的長事務(wù)回滾。生產(chǎn)實例在業(yè)務(wù)高峰期重啟回滾操作本身就可能讓整個庫不可用很久。重啟前務(wù)必檢查是否有長時間運行的會話可以用活動監(jiān)視器或DMV查詢確認。5.4 斷連和“無法連接到服務(wù)器”的排查順序“無法連接到服務(wù)器”這個報錯是SSMS相關(guān)的提問里出現(xiàn)頻率最高的。我總結(jié)了一個排查順序基本能覆蓋90%的場景。第一步看服務(wù)是否啟動。在“SQL Server配置管理器”里確認SQL Server服務(wù)狀態(tài)是“正在運行”。如果服務(wù)沒有啟動SSMS當然連不上這時候連的是本機還是遠程都一樣。第二步看網(wǎng)絡(luò)和防火墻。遠程連接場景需要在防火墻里放行SQL Server使用的TCP端口默認是1433。可以用telnet或者Test-NetConnection去測端口是否開放。注意SQL Server可能有多個實例命名實例的端口不是默認1433而是動態(tài)分配的端口需要先通過SQL Server Browser服務(wù)或者客戶端別名來定位端口。第三步看身份驗證模式。如果實例沒有開啟“SQL Server和Windows身份驗證模式”創(chuàng)建了SQL賬號也登錄不了。在SSMS連接對話框里切換身份驗證方式時可以記住這個規(guī)則先確保你在Windows本機有管理員權(quán)限否則很多配置項無權(quán)更改。第四步數(shù)據(jù)庫實例名拼寫。遠程連接時服務(wù)器名稱要寫“IP地址\實例名”如果只寫了IP地址連的是默認實例。如果SQL Server以命名實例方式安裝這樣連當然會失敗。第五步登錄名是否被鎖定或者密碼過期。如果設(shè)置了密碼策略多次輸入錯誤密碼賬號會被鎖定。查看“安全性 - 登錄名”里的狀態(tài)即可確認。這個順序幾乎是一條鏈路走下來太多人上來就重裝SQL Server重裝完問題還在最后還是老老實實按這個順序排查出是防火墻端口沒放行。6. 進階操作把SSMS變成你的數(shù)據(jù)庫百寶箱6.1 使用“編寫腳本為”功能快速導(dǎo)出結(jié)構(gòu)在對象資源管理器里右擊任意對象選擇“編寫腳本為”能生成該對象的創(chuàng)建腳本。這個功能在很多場景下比圖形化管理更快也更適合版本控制。比如要把開發(fā)庫里的某張表結(jié)構(gòu)同步到測試庫最穩(wěn)妥的方式不是直接在測試庫里重新建一遍表而是右擊表 - “編寫腳本為” - “CREATE 到” - “新查詢編輯器窗口”然后把生成好的腳本拿到目標庫執(zhí)行。因為這段腳本包含了列定義、索引、約束和默認值不會像手工建表那樣漏掉細節(jié)。如果想把整個數(shù)據(jù)庫的所有對象都導(dǎo)出成腳本可以右擊數(shù)據(jù)庫 - “任務(wù)” - “生成腳本”。在“選擇對象”頁面可以選擇“整個數(shù)據(jù)庫及所有數(shù)據(jù)庫對象”在“高級”選項里可以設(shè)定是否包含索引、觸發(fā)器、統(tǒng)計信息等。整套腳本導(dǎo)出后基本就是一份數(shù)據(jù)庫結(jié)構(gòu)文檔放在版本控制里非常合適。6.2 自定義快捷鍵和代碼片段SSMS的編輯器擴展性雖然不如Visual Studio那么完善但支持自定義快捷鍵。在“工具 - 選項 - 環(huán)境 - 鍵盤”里可以搜索某個命令并分配新快捷鍵。比如默認的注釋和取消注釋快捷鍵不是所有人都習(xí)慣可以改成一個自己順手的組合。代碼片段這塊前面提到了實際使用中可以自定義。SSMS的代碼片段文件是XML格式存放在安裝路徑下的“Code Snippets”目錄里。按自己的常用邏輯寫一個模板文件下次寫復(fù)雜查詢時就省掉大量重復(fù)輸入。舉一個我自己的例子我常寫“分頁查詢”腳本就專門存了帶ROW_NUMBER、OFFSET-FETCH和包含列名的代碼片段每次套用后只需改表名和篩選條件。6.3 日常連接多個實例時的“已注冊服務(wù)器”很多DBA和開發(fā)手上不止一個SQL Server實例開發(fā)環(huán)境、測試環(huán)境、生產(chǎn)環(huán)境可能還有各類歷史環(huán)境。每次連接都輸入服務(wù)器地址和憑據(jù)重復(fù)且易出錯。SSMS里有“已注冊服務(wù)器”窗口視圖 - 已注冊服務(wù)器可以把所有實例分組保存配上顏色標記。比如開發(fā)庫標成綠色生產(chǎn)庫標成紅色。雙擊一條記錄就能直接連接不用每次手輸服務(wù)器名稱?！耙炎苑?wù)器”還有一個很實用的特性你可以在一個組上右鍵選擇“新建查詢”SSMS會同時為組內(nèi)的所有實例打開查詢窗口。運維批量執(zhí)行腳本時比如統(tǒng)一查所有實例的版本、統(tǒng)一收集基礎(chǔ)配置這種方法能節(jié)省大量切換時間。但生產(chǎn)環(huán)境批量執(zhí)行時需要注意事務(wù)一致性和執(zhí)行順序別把一批操作以不同順序跑在多個庫上。6.4 SQL Agent作業(yè)與日常自動化SQL Server代理是SSMS里最容易忽略但十分重要的模塊。日常運維中備份、索引維護、統(tǒng)計信息更新、歷史數(shù)據(jù)清理都能做成作業(yè)。新建作業(yè)時要注意“步驟”頁里的“類型”選擇“Transact-SQL 腳本(T-SQL)”然后寫執(zhí)行腳本。數(shù)據(jù)庫一欄要顯式指定數(shù)據(jù)庫名不要默認因為代理作業(yè)的默認上下文可能不是你預(yù)期的庫。腳本里如果涉及USE語句務(wù)必在開頭標明?！坝媱潯表撛O(shè)置頻率時常用“每周”、“每天”兩種。建議不要把所有作業(yè)都塞到凌晨02:00因為多個作業(yè)同時啟動時可能發(fā)生資源爭搶和實例負載波動?？梢园磳嵗撦d情況把備份作業(yè)放在22:30索引維護放在01:00統(tǒng)計更新放在03:00盡量錯峰?！巴ㄖ表摽梢栽O(shè)置“作業(yè)失敗時發(fā)送電子郵件”。配置數(shù)據(jù)庫郵件后在通知頁勾選“電子郵件”選項選擇操作員。這樣作業(yè)執(zhí)行失敗時能第一時間收到通知而不是等到業(yè)務(wù)反饋才發(fā)現(xiàn)備份一整個月都沒成功。7. 一些容易踩的版本兼容的坑7.1 SSMS版本和SQL Server版本的對應(yīng)關(guān)系SSMS雖是官方客戶端但并非所有版本都能連接所有SQL Server版本。之前提到高版本SSMS一般向下兼容低版本實例但低版本SSMS連接高版本實例時經(jīng)常遇到“對象資源管理器不支持此服務(wù)器版本”的提示。舉個例子老版本SSMS連新版本實例時新建數(shù)據(jù)庫向?qū)?、查詢存儲界面、部分DMV查看頁面可能直接報錯。為了省麻煩最好保持SSMS為最新穩(wěn)定版本。同時要注意SQL Server的某個分支版本比如早期版本甚至更老的2000/2005已經(jīng)不再受官方支持SSMS最新版也不一定兼容。這時候要么升級實例要么繼續(xù)使用對應(yīng)年代的舊版SSMS。但舊版SSMS在新操作系統(tǒng)上運行又容易出兼容性問題?？傊谏a(chǎn)環(huán)境長期使用時保持SQL Server和SSMS都在受支持的生命周期內(nèi)是很值得安排的一件事。7.2 連接Azure SQL Database時與本地SSMS的差異連接Azure SQL Database云數(shù)據(jù)庫時SSMS的基本操作習(xí)慣和本地實例差不多但有幾個重要差異。登錄名和用戶映射邏輯不一致。Azure SQL Database里沒有“登錄名映射到數(shù)據(jù)庫用戶”的傳統(tǒng)概念登錄用戶就是數(shù)據(jù)庫用戶在連接時要選擇正確數(shù)據(jù)庫不能像本地實例那樣先連實例再切換庫。某些本地DMV不可用。Azure SQL Database會屏蔽很多和操作系統(tǒng)、服務(wù)狀態(tài)相關(guān)的視圖遇到不支持的DMV時只能使用云數(shù)據(jù)庫特有的DMV。備份恢復(fù)方式是服務(wù)托管的SSMS里部分備份任務(wù)無法在本地實例上執(zhí)行需要通過Azure門戶或者自動化方式做。防火墻規(guī)則是以IP白名單方式配置的有時連接報錯的原因不是SQL賬號密碼而是客戶端的公網(wǎng)IP沒有加進白名單。很多一開始在本地跑SQL Server的人第一次連云端會有點不適但實際上只要理解了“實例邊界”這個概念大部分問題都能順著SSMS的報錯提示和文檔定位出來。8. 日常維護建議和長期習(xí)慣8.1 每月做一次索引和統(tǒng)計信息體檢不管業(yè)務(wù)量大小SQL Server用久了索引碎片和統(tǒng)計信息陳舊都是必然的。SSMS圖形界面里有現(xiàn)成的“維護計劃”向?qū)г凇肮芾?- 維護計劃”里可以圖形化配置索引重建、統(tǒng)計信息更新、數(shù)據(jù)庫一致性檢查等任務(wù)。實際操作時不建議對整庫所有索引一律重建。碎片率低于30%的索引重組比重建更合適因為重建會頻繁鎖表影響在線業(yè)務(wù)。而統(tǒng)計信息更新也不建議全部更新大表的完整掃描更新耗時很長。比較穩(wěn)妥的方式是分庫分表評估或使用采樣更新。8.2 定期檢查數(shù)據(jù)庫一致性SQL Server在正常關(guān)機或異常斷電后數(shù)據(jù)頁可能發(fā)生損壞SSMS的“DBCC CHECKDB”功能可以檢測這種頁損壞。在執(zhí)行DBCC CHECKDB時需要注意它可能會消耗大量IO和CPU。生產(chǎn)環(huán)境盡量安排在低峰期執(zhí)行。若檢查發(fā)現(xiàn)錯誤可以先查看錯誤日志了解錯誤級別。部分頁損壞可能可以通過備份恢復(fù)所以定期備份和定期一致性檢查要搭配執(zhí)行。8.3 善用SSMS自帶報表SSMS里每個數(shù)據(jù)庫節(jié)點下右鍵 - “報表” - “標準報表”有一堆監(jiān)控報表可以直接使用。磁盤使用情況、緩存命中率、索引使用統(tǒng)計、死鎖跟蹤等都能以圖形化方式生成。雖然這些報表和第三方監(jiān)控工具相比不算深入但勝在零部署、零成本在中小型環(huán)境里完全能看出趨勢。我平時會定期跑一份“索引使用情況”報表看看哪些索引幾乎從不被使用識別出低效索引后和業(yè)務(wù)確認再決定是否刪除。這種刪除無用索引的操作為數(shù)據(jù)庫寫入性能帶來的正向影響常常被不少團隊忽略。9. 最后再分享一點自己的經(jīng)驗用SSMS這么多年我最大的體會是它不是一個“打開了寫SQL”的連接工具而是一整套數(shù)據(jù)庫日常操作的中控臺。新手期會把它當查詢工具工作幾年后才會慢慢意識到SSMS的真正價值在于把備份、恢復(fù)、權(quán)限、監(jiān)控、調(diào)度全部收斂到一個圖形界面下同時保留了隨時生成腳本的能力。這種“圖形界面腳本化”的雙軌模式才是SQL Server日常管理最順手的方式。如果讓我給剛接觸的人一個具體建議我會說從今天開始所有在SSMS圖形界面里做完的操作馬上用“編寫腳本為”重新生成一份腳本并保存。堅持一個月你就有了一整套屬于自己環(huán)境的運維腳本集之后無論是遷移、快速搭建還是排查歷史操作都有據(jù)可查。這個習(xí)慣比背住任何一條快捷鍵都值錢。最后多提一句“工具 - 選項 - 環(huán)境 - 啟動時”可以設(shè)置“打開空環(huán)境”不讓SSMS每次啟動就自動連接上次的服務(wù)器。如果你經(jīng)常手滑連錯環(huán)境這個設(shè)置能幫你減少一次操作失誤的機會。別小看這些小配置日常管理數(shù)據(jù)庫最浪費時間的往往不是技術(shù)難題而是那些反復(fù)出現(xiàn)的小差錯。 SQL Server Management Studio高效指南：從查詢到性能監(jiān)控的實用技巧
我用了十多年SQL Server有很長一段時間都靠命令行和一個個SQL腳本“硬撐”直到把SQL Server Management Studio下面統(tǒng)一叫SSMS的一些高頻功能和隱藏技巧真正吃透之后才發(fā)現(xiàn)這工具不管對新手還是老手節(jié)省下來的時間都不是一星半點。說白了SSMS就是微軟家的SQL Server圖形化管理客戶端它能干的事遠不止“寫個查詢點點運行”這么簡單。建庫建表、權(quán)限分配、執(zhí)行計劃分析、性能監(jiān)控、數(shù)據(jù)備份恢復(fù)甚至把重復(fù)性操作自動生成腳本都能在這個窗口里完成。很多新手下的第一個客戶端就是它卻只用了10%的功能很多老手嘴上說“命令行效率高”實際排查死鎖和看IO統(tǒng)計的時候還是老老實實切回SSMS。這篇內(nèi)容沒有任何花哨的包裝我會直接按實際工作流來拆解連接實例、寫查詢、看執(zhí)行計劃、管理對象、監(jiān)控排查、日常避坑。每個環(huán)節(jié)都會給出我在真實環(huán)境里驗證過的操作方式和參數(shù)依據(jù)同時也把那些文檔里不怎么會寫、但是實際總會撞上的“坑”拎出來講清楚。1. 初識SSMS版本選擇、連接方式和基本布局1.1 為什么幾乎所有人都選SSMS作為主要管理工具先說明一個背景SQL Server本身是一個數(shù)據(jù)庫引擎像發(fā)動機一樣跑在機器上而SSMS就是駕駛艙。駕駛艙這個類比很貼切因為儀表盤上的各種窗口——對象資源管理器、查詢編輯器、活動監(jiān)視器本質(zhì)上就是把發(fā)動機狀態(tài)呈現(xiàn)給你看同時把你的操作指令翻譯給引擎執(zhí)行?？赡苡腥藭柤热籗QL Server不裝SSMS也能跑那是不是用其他第三方工具也行。答案是“行”但SSMS有幾個一直沒被替代的優(yōu)勢。第一官方發(fā)布和SQL Server的版本兼容性最好不會出現(xiàn)連不上的版本錯位問題。第二功能覆蓋最完整。早期版本的SQL Server還能靠命令行“裸奔”但到了較新的版本里很多新功能、新DMV動態(tài)管理視圖、新的圖形化監(jiān)控界面第三方工具要么落后一個版本要么做得不完整而SSMS永遠是同步的。第三資源占用適中內(nèi)存和CPU開銷控制得比一堆Web版管理面板要好至少不會因為開個管理工具就把開發(fā)機拖卡。SSMS本身是免費分發(fā)的不需要額外買授權(quán)只要是SQL Server的合法用戶就能直接從官方渠道下載。這一點在很多企業(yè)里非常關(guān)鍵因為數(shù)據(jù)庫軟件采購?fù)^好幾輪審批而SSMS作為客戶端工具零授權(quán)成本裝上就能用。1.2 下載版本與安裝時的關(guān)鍵選項很多新手下載SSMS的時候會犯一個錯直接在搜索引擎里搜“SQL Server Management Studio下載”結(jié)果下到一堆第三方打包的舊版本或者干脆下成了SQL Server Express的完整安裝包體積又大還一堆用不上的組件。正確做法是去微軟官方文檔頁面找“Download SQL Server Management Studio (SSMS)”入口認準版本號。SSMS是獨立于SQL Server的版本機制目前主流穩(wěn)定版本通常按年份和序號命名比如18.x、19.x、20.x這樣遞增。需要特別注意的是SSMS并不是必須和實例版本完全一致才能連SSMS高版本一般可以連低版本的SQL Server實例反過來低版本SSMS連高版本實例就可能出現(xiàn)不支持的情況所以原則上裝新不裝舊。安裝過程本身沒什么難度一路默認就可以。但有兩個選項值得關(guān)注。一個是安裝路徑默認裝在C盤如果你機器C盤空間緊張一定把它改到D盤或其他數(shù)據(jù)盤因為SSMS會隨版本更新頻繁升級舊版本殘留和新版本緩存加起來占用空間比你想象的大。另一個是“SQL SERVER MANAGEMENT SYSTEM”這類附帶功能默認全選就好里面包含了SQL Profiler、數(shù)據(jù)庫引擎優(yōu)化顧問等附屬工具這些都是排查問題時必備的。裝完之后第一次打開會看到連接對話框這里有幾個細節(jié)。服務(wù)器類型默認是“數(shù)據(jù)庫引擎”一般不用改。服務(wù)器名稱可以直接寫實例名也可以寫主機名加實例名比如“某服務(wù)器\SQLEXPRESS”。如果本機裝了默認實例寫一個點號“.”或者“(local)”就能連上本機。身份驗證選“Windows身份驗證”還是“SQL Server身份驗證”取決于實例的認證模式。如果選了后者需要確保實例啟用了混合認證模式否則登錄會被拒絕。1.3 連接實例后的界面習(xí)慣配置連上實例之后第一件事不是急著建庫而是把界面按自己的習(xí)慣調(diào)整好這一步非常影響后續(xù)效率。對象資源管理器默認在左側(cè)建議保持展開。它按“數(shù)據(jù)庫、安全性、服務(wù)器對象、復(fù)制、管理”幾個大節(jié)點組織你的絕大多數(shù)日常操作都能在這里找到入口。右鍵任意節(jié)點會出現(xiàn)上下文菜單菜單里的選項會隨節(jié)點類型變化比如右鍵“數(shù)據(jù)庫”節(jié)點會有“新建數(shù)據(jù)庫”右鍵某個表節(jié)點則有“設(shè)計”“選擇前1000行”“編輯前200行”這些快捷操作。查詢編輯器是核心工作區(qū)。默認情況下雙擊某個表不會彈查詢窗口需要手動點“新建查詢”或者按快捷鍵CtrlN。我個人強烈建議先到“工具 - 選項 - 環(huán)境 - 鍵盤”里把常用快捷鍵背下來。比如CtrlN新建查詢窗口F5執(zhí)行當前選中的腳本或者執(zhí)行整個窗口里所有腳本CtrlShfitR刷新智能感知緩存這個在新建了表或者改了結(jié)構(gòu)之后特別有用CtrlR顯示/隱藏結(jié)果面板CtrlShiftM在查詢編輯器中插入模板參數(shù)這個屬于進階技巧后面會細說還有一個容易被忽略的選項“工具 - 選項 - 查詢結(jié)果 - SQL Server - 將結(jié)果保存到文件”的默認編碼和結(jié)果格式。如果日常經(jīng)常要用SSMS導(dǎo)出查詢結(jié)果給業(yè)務(wù)同學(xué)建議把默認結(jié)果格式設(shè)置成CSV并且把“執(zhí)行查詢后自動保存”打開這樣每次查詢完可以直接落盤。默認那個網(wǎng)格結(jié)果粘貼到Excel里很容易出現(xiàn)格式錯亂尤其涉及超過15位的數(shù)字ID時會被Excel自動轉(zhuǎn)成科學(xué)計數(shù)法臉上笑嘻嘻心里已經(jīng)在罵人了。2. 查詢編輯器從寫腳本到調(diào)優(yōu)的完整鏈路2.1 智能感知IntelliSense的正確打開方式很多人吐槽SSMS的智能感知“不好用”“不提示”其實大部分時候是沒開啟或者緩存沒刷新。檢查方法是在查詢編輯器里輸入前幾個字母如果沒有任何代碼提示優(yōu)先確認“工具 - 選項 - 文本編輯器 - Transact-SQL - IntelliSense”下的“啟用IntelliSense”復(fù)選框是否勾選。勾選之后重啟查詢窗口一般就能生效。智能感知生效之后它有幾種很實用的表現(xiàn)輸入表名、視圖名、存儲過程名時會彈出候選列表。輸入表名后輸入空格或點號會列出這個表的列名。輸入SELECT后開始輸入列名能基于已選表補全提示。函數(shù)名、系統(tǒng)變量、關(guān)鍵字也都有提示。不過智能感知有一個歷史遺留問題它對剛創(chuàng)建的新表經(jīng)?！翱床灰姟?。比如你剛用腳本建了一張表然后在同一個查詢窗口里直接寫SELECT FROM這張表怎么敲都不提示。這時候按CtrlShiftR刷新IntelliSense緩存或者直接新開一個查詢窗口問題就消失了。實操層面還有一個習(xí)慣值得培養(yǎng)寫SELECT之前先把表名列出來不要一上來就寫“SELECT * FROM 某表”。智能感知在完整列出表結(jié)構(gòu)后對后續(xù)條件的補全準確率要高很多。雖然這主要是個人代碼習(xí)慣問題但配合智能感知使用會讓整個編碼體驗順滑不少。2.2 執(zhí)行與結(jié)果交互的關(guān)鍵設(shè)置查詢窗口寫好后F5執(zhí)行是標配但很多人沒認真看過執(zhí)行結(jié)果面板的類型。SSMS有三種結(jié)果模式。文本結(jié)果CtrlT結(jié)果以文本方式展示適合快速目測小結(jié)果集。網(wǎng)格結(jié)果CtrlD默認模式適合查看表格數(shù)據(jù)、復(fù)制到Excel。文件結(jié)果CtrlShiftF直接把結(jié)果輸出到文件。日常用得最多的是網(wǎng)格結(jié)果。但如果查詢結(jié)果很大比如幾十萬行直接拖回客戶端會非常慢而且內(nèi)存占用會飆升。這時候把結(jié)果改成文件輸出讓服務(wù)端把結(jié)果流式寫入文件更穩(wěn)妥。還有一個很多人沒意識到的問題SSMS默認在“結(jié)果”頁簽旁邊會有一個“消息”頁簽里面會顯示影響的行數(shù)和查詢時間。想看執(zhí)行計劃、客戶端統(tǒng)計信息、IO統(tǒng)計都需要先開啟對應(yīng)選項。顯示實際執(zhí)行計劃快捷鍵CtrlM開啟后再按F5查詢執(zhí)行完會自動多出一個“執(zhí)行計劃”頁簽這是分析SQL性能最重要的入口一定要養(yǎng)成習(xí)慣。包括實際的執(zhí)行計劃CtrlShiftM這個對應(yīng)的是預(yù)估執(zhí)行計劃常用在不用真正執(zhí)行、只檢查查詢寫法是否會走合適的索引時。包括客戶端統(tǒng)計信息在“查詢 - 查詢選項 - 高級”里勾選能查看等待時間、處理時間等指標。包括服務(wù)器端IO統(tǒng)計和時間統(tǒng)計對應(yīng)SET STATISTICS IO ON; 和SET STATISTICS TIME ON;這兩個命令可以直接寫在查詢窗口頂部特別適合對比不同寫法的性能差異。我在實際優(yōu)化慢查詢時固定套路就是先CtrlM打開實際執(zhí)行計劃執(zhí)行一次看IO統(tǒng)計和耗時然后改寫查詢再對比兩次IO統(tǒng)計里“邏輯讀取”和“掃描計數(shù)”的變化。邏輯讀數(shù)是判斷SQL好壞的核心指標之一因為它不依賴硬件環(huán)境差異是一致可比的對象。2.3 執(zhí)行計劃里最容易忽略的“反常識”細節(jié)很多人一開始看執(zhí)行計劃就盯著“缺失索引提示”總覺得只要按提示建了索引性能就上來了。但SSMS的缺失索引提示只是一個“基于當前查詢估算”的建議有時候它建議建的索引是一個中看不中用的巨型復(fù)合索引列順序不對或者包含列太多不僅消耗額外磁盤空間還會拖慢寫入。正確做法不是看到提示就建索引而是先看執(zhí)行計劃里運算子的物理操作類型。常見的幾個坑如下。表掃描Table Scan毫無索引可用整表遍歷小表無所謂大表必優(yōu)化。聚集索引掃描Clustered Index Scan有聚集索引但查詢條件沒法利用它定位本質(zhì)上也是掃全表只是代價比堆表掃描稍微可控一點。索引查找Index Seek這才是理想狀態(tài)表示通過索引B樹定位到了極少量數(shù)據(jù)。鍵查找Key Lookup非聚集索引命中了但還需要回到聚集索引去取其他列的數(shù)據(jù)。如果返回的列多鍵查找次數(shù)巨大反而比掃描更慢。新手最容易栽在“鍵查找”上。判斷基準很簡單當鍵查找的次數(shù)達到數(shù)萬甚至數(shù)十萬級別時優(yōu)化器可能已經(jīng)悄悄選擇表掃描了。解決方式是使用覆蓋索引把查詢需要返回的列都包含進索引的包含列中。執(zhí)行計劃里還有一個細節(jié)兩個表做Hash連接時如果右側(cè)輸入是大量數(shù)據(jù)而左側(cè)只有幾條時要注意觀察估計行數(shù)和實際行數(shù)是否嚴重不一致。這個不一致往往源于過期的統(tǒng)計信息。遇到這種情況最快的方法是對相關(guān)表執(zhí)行UPDATE STATISTICS而不是急著改SQL寫法。2.4 模板資源管理器被低估的腳本加速器SSMS里有一個“模板資源管理器”窗口默認可能沒打開可以在“視圖”菜單里找到。它內(nèi)置了大量模板建表、建索引、備份恢復(fù)、添加作業(yè)、創(chuàng)建登錄用戶等幾乎覆蓋所有常見DDL/DML操作。模板的價值不在于“內(nèi)容有多高級”而在于它的參數(shù)化機制。打開任何一個模板按CtrlShiftMSSMS會彈出“替換模板參數(shù)”對話框自動列出模板里的變量比如表名、架構(gòu)名、數(shù)據(jù)文件路徑這些。用戶只需要填一次值就能生成完整腳本。舉一個實際場景新接一個項目需要給三十張表逐一加一個統(tǒng)一命名規(guī)范的審計字段。如果手寫腳本每張表復(fù)制黏貼改表名半小時就沒了。從模板資源管理器里拉出“添加列”的模板用CtrlShiftM填充列名和數(shù)據(jù)類型然后批量替換表名五分鐘搞定。還有一個配套功能是“代碼片段”。在查詢編輯器里右鍵 - “插入代碼段”可以調(diào)出光標位置的SQL代碼模板比如創(chuàng)建存儲過程、創(chuàng)建臨時表、WHILE循環(huán)這些。自己常用的代碼塊也可以提前存成代碼片段放進SSMS的“我的代碼片段”目錄里下次插入時一鍵調(diào)出。3. 對象管理建庫建表、權(quán)限分配與自動化生成腳本3.1 圖形化建表時的默認值陷阱SSMS的圖形化建表界面很友好右擊“表”節(jié)點選擇“新建”就能彈出一個表格設(shè)計器可以直接鍵入列名、選擇數(shù)據(jù)類型、設(shè)置長度和允許Null。但越友好的界面越容易埋坑這里有幾個重點必須強調(diào)。第一SSMS在圖形化建表時默認可能會勾選“允許 Null 值”但業(yè)務(wù)上很多字段明明不該為空。在下線之前一定要根據(jù)業(yè)務(wù)仔細過一遍不然上線后的數(shù)據(jù)清洗和校驗工作會非常痛苦。第二decimal類型在建表時精度和小數(shù)位數(shù)如果不理解清楚隨便填一個比如decimal(10,2)事后要擴容不僅麻煩還可能造成阻塞。第三圖形化建表界面里默認值是直接寫在“默認值或綁定”那一列但如果你不寫這個字段就沒有默認值約束插入數(shù)據(jù)時漏了就會報錯或插Null。實際業(yè)務(wù)里創(chuàng)建時間和更新時間這類字段最好在建表時就設(shè)置默認值。再補充一個設(shè)計器的大坑在SSMS的“設(shè)計”視圖里修改表結(jié)構(gòu)比如改列的數(shù)據(jù)類型、加一個不允許Null的列SSMS默認是“阻止保存要求重新創(chuàng)建表的更改”。一旦這個設(shè)置開啟很多看似簡單的改動都無法直接保存而且當你去改列順序或者加非空列時SSMS會提示必須重建表。重建表意味著整表數(shù)據(jù)復(fù)制到臨時表、重新創(chuàng)建索引、重新設(shè)置權(quán)限一旦表數(shù)據(jù)量超過幾百萬行這個過程極容易造成長時間鎖表。所以有兩個建議如果只是加一個允許Null的新列直接ALTER TABLE ADD列是最快的。如果要修改列類型、刪除列、改約束應(yīng)該評估用腳本ALTER操作而不是在圖形界面里拖來拖去。3.2 數(shù)據(jù)庫屬性里的關(guān)鍵配置項恢復(fù)模式與自動收縮在數(shù)據(jù)庫上右鍵 - “屬性”能進入數(shù)據(jù)庫級別的配置面板。新手一般只看“常規(guī)”頁里的排序規(guī)則和數(shù)據(jù)庫名稱但有兩個配置項需要特別關(guān)注。一個是“選項”頁里的“恢復(fù)模式”。默認是“完整”模式。在完整模式下所有事務(wù)都會寫入事務(wù)日志數(shù)據(jù)備份時可以利用日志做時間點恢復(fù)但對初學(xué)者來說如果不配套進行定期日志備份事務(wù)日志會不斷膨脹很快就能撐爆磁盤。實際項目里只要不是核心交易庫完全可以用“簡單”恢復(fù)模式讓SQL Server自動回收失效日志空間省心很多。另一個是“自動收縮”選項默認是False。但有些版本的SSMS在新建數(shù)據(jù)庫時不會自動開啟而網(wǎng)上很多教程建議開自動收縮這是誤導(dǎo)。自動收縮開啟后文件會在每次釋放大量空間后收縮到很小但下一次大量數(shù)據(jù)寫入時又得重新增長和初始化文件產(chǎn)生大量的文件增長操作頻繁影響性能。我見過不少數(shù)據(jù)庫因為開著自動收縮每幾分鐘就發(fā)生一次文件自動擴展寫日志和IO都受到嚴重影響。正確做法是關(guān)閉自動收縮手動定期評估文件大小一次性回收。3.3 權(quán)限管理既別“一刀切”也別“裸奔”權(quán)限管理這塊SSMS有圖形化界面也有腳本方式。圖形化操作是在“安全性 - 登錄名”中新建登錄名然后在“用戶映射”里把登錄名映射到具體數(shù)據(jù)庫打勾分配數(shù)據(jù)庫角色。常見誤區(qū)是給業(yè)務(wù)賬號直接分配db_owner角色。db_owner在對應(yīng)數(shù)據(jù)庫內(nèi)幾乎擁有所有權(quán)限可以刪除表、修改結(jié)構(gòu)。很多外包項目為了省事全員db_owner等出問題時才發(fā)現(xiàn)連審計的痕跡都不好排查。更穩(wěn)妥的做法是建立一個“最小權(quán)限”賬號組只授予SELECT、INSERT、UPDATE、DELETE這類DML權(quán)限schema和DDL權(quán)限單獨把控。在SSMS圖形界面里不用細摳每一個權(quán)限可以在“用戶映射”頁面選擇“public”和“db_datareader”、“db_datawriter”這類預(yù)定義角色然后針對個別表通過“安全對象”頁面單獨授權(quán)。權(quán)限腳本化也很簡單右擊登錄名或用戶 - “編寫腳本為” - “CREATE 到” - “新查詢編輯器窗口”就能得到完整創(chuàng)建腳本。這個方法對所有對象都通用是SSMS里最能“復(fù)制黏貼”的自動化技巧。3.4 把重復(fù)操作做成腳本生成腳本與計劃任務(wù)日常運維中很多操作都是重復(fù)的每周備份一次數(shù)據(jù)庫、定期清理歷史表、把某張表的數(shù)據(jù)同步到分析庫。SSMS在做這類事情時最核心的價值不是“記住操作步驟”而是把所有操作變成一條可保存的腳本或作業(yè)。以備份為例右擊數(shù)據(jù)庫 - 任務(wù) - 備份在圖形界面上跑完一次后不必再手動做第二次。可以改成在“SQL Server代理” - “作業(yè)”里新建作業(yè)步驟里選擇“T-SQL腳本”把備份腳本填進去然后配置“計劃”選擇每周執(zhí)行一次。SQL Server代理的作業(yè)里還可以設(shè)置“步驟之間失敗轉(zhuǎn)移”。比如備份步驟失敗時自動執(zhí)行一個“發(fā)送郵件警告”的步驟這樣DBA不需要時刻盯著任務(wù)日志。配置數(shù)據(jù)庫郵件功能時也是在SSMS里操作在“管理 - 數(shù)據(jù)庫郵件”中配置SMTP服務(wù)器然后在“SQL Server代理 - 運算符”里綁定郵件通知。配置成功后作業(yè)執(zhí)行失敗會自動發(fā)警報到郵箱。不過SQL Server代理服務(wù)可能不是默認啟動的。新裝的實例中“SQL Server代理”服務(wù)狀態(tài)往往是“停止”需要在“配置管理器”里啟動并且設(shè)為自動啟動。很多人拍了半天桌子說“作業(yè)怎么不跑”十有八九就是代理服務(wù)根本沒啟動。4. 性能監(jiān)控與故障排查活動監(jiān)視器、死鎖和慢查詢4.1 活動監(jiān)視器怎么看活動監(jiān)視器是SSMS自帶的直觀性能儀表盤入口在“對象資源管理器”的根節(jié)點下方也可以在某個數(shù)據(jù)庫實例上右鍵 - “活動監(jiān)視器”打開。打開后默認有四個區(qū)塊概覽、等待任務(wù)、數(shù)據(jù)文件IO、最近耗費大量資源的查詢。概覽區(qū)域里能看到CPU時間百分比、等待時間百分比、數(shù)據(jù)庫IO百分比和批處理請求量。這里的“等待時間”不是指查詢執(zhí)行消耗的時間而是指SQL因為某些資源如鎖、IO、內(nèi)存而“卡住”的時間。如果等待時間長期偏高意味著系統(tǒng)有瓶頸。舉個例子某段時間“等待類型”大量是PAGEIOLATCH_SH說明正在經(jīng)歷大量磁盤頁讀取并等待磁盤IO完成這時候加內(nèi)存、換SSD、優(yōu)化索引都能緩解。而如果等待類型主要是LCK_M_X則表明大量會話在等待鎖這通常是邏輯問題比如事務(wù)過長、索引缺失導(dǎo)致鎖范圍過大單純加硬件沒用。最近耗費大量資源的查詢列表默認按CPU時間排序。它給出的只是“命令文本”和“運行中的總CPU時間”等指標。點開某條查詢可以看到對應(yīng)的執(zhí)行計劃按鈕點擊后能進一步看到這個查詢的詳細執(zhí)行計劃。這個功能對于快速定位“現(xiàn)在哪個查詢最占資源”非常方便尤其適合線上問題初步排查。4.2 死鎖與阻塞把所有線索同步到一條命令里死鎖排查方面SSMS里最常見的方式是開啟跟蹤標志1222或者1204把死鎖圖形事件抓出來。SSMS沒有直接為死鎖圖形提供實時界面需要借助擴展事件或者SQL Server Profiler來捕獲但這些工具在SSMS的“工具”菜單下都有入口。完整做法是這樣的打開SQL Server Profiler新建跟蹤。在“事件選擇”里勾選“Deadlock graph”事件跟蹤。當死鎖發(fā)生時跟蹤日志里會生成一個圖形化死鎖節(jié)點兩個進程、資源對象和鎖模式都一目了然。把捕獲到的死鎖圖導(dǎo)出成文件作為后續(xù)問題和業(yè)務(wù)方溝通的依據(jù)。死鎖的“圖形”看起來像兩個進程各握著一個資源然后都在等對方手里的另一個資源。圖形里的鎖模式通常顯示為“排他鎖”和“共享鎖”兩個箭頭交織成一個循環(huán)。依據(jù)這個圖能快速定位是哪兩張表的哪個索引導(dǎo)致的死鎖然后從業(yè)務(wù)角度統(tǒng)一訪問順序、縮小事務(wù)范圍而不是單純靠提高隔離級別來壓問題。如果是阻塞問題某一個會話卡住了另一個會話SSMS里最常見的排查方式是用系統(tǒng)視圖SELECT blocking.session_id AS 阻塞會話ID, blocking.blocking_session_id AS 被阻塞會話ID, blocking.wait_type, blocking.wait_time, blocking.wait_resource, sql_text.text AS 當前SQL FROM sys.dm_exec_requests AS blocking CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) AS sql_text WHERE blocking.blocking_session_id IS NOT NULL這條命令能在阻塞發(fā)生時把阻塞者和被阻塞者當前的SQL都帶出來。鎖定資源的具體描述比如“key: hash(某值)”可以配合DBCC SQLPERF或者專用查詢翻譯成具體的表名和索引鍵值。SSMS里支持把結(jié)果網(wǎng)格直接跳轉(zhuǎn)到對應(yīng)對象只要點擊資源名旁邊相關(guān)的項即可。4.3 慢查詢定位不只是看總耗時慢查詢的定位有很多維度。SSMS的“活動監(jiān)視器”只能看到當前正在運行的查詢過去已經(jīng)跑完的慢查詢需要借助DMV或者查詢存儲Query Store功能。查詢存儲是較新版本SQL Server自帶的功能默認是關(guān)閉狀態(tài)需要在數(shù)據(jù)庫屬性 - “選項” - “查詢存儲”里啟用。啟用后它會把所有查詢的編譯信息和執(zhí)行計劃實時記錄下來包括平均耗時、邏輯讀寫、物理讀寫、執(zhí)行次數(shù)等。遇到“昨天還正常今天突然某個報表頁變慢”的問題用查詢存儲可以直接查看某條查詢在過去幾天的執(zhí)行耗時曲線以及對應(yīng)的執(zhí)行計劃變化。這個功能比在日志里翻SQL文本高效得多因為問題往往不是SQL寫法變了而是統(tǒng)計信息更新或參數(shù)嗅探導(dǎo)致執(zhí)行計劃漂移查詢存儲里能直觀體現(xiàn)出“計劃變了性能退化”的因果鏈條。沒有開啟查詢存儲的老庫臨時分析時可以用以下DMVSELECT TOP 20 qs.total_worker_time / qs.execution_count AS 平均CPU時間, qs.total_logical_reads / qs.execution_count AS 平均邏輯讀, qs.total_elapsed_time / qs.execution_count AS 平均耗時, qs.execution_count AS 執(zhí)行次數(shù), 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 sql_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.total_worker_time DESC這條語句能快速找出累計CPU時間最高的前20條SQL。但要注意total_worker_time是“累計”而非“單次”如果一條SQL執(zhí)行了一百萬次單次開銷很小累計也很高。所以看數(shù)據(jù)時一定要結(jié)合平均耗時和平均邏輯讀來綜合判斷不能只看累計值。5. 備份恢復(fù)與日常操作避坑實錄5.1 備份腳本的正確寫法和標準實踐SSMS的圖形化備份功能非常簡單點幾下就能完成一次備份。但生產(chǎn)環(huán)境的備份不是“能備份就行”必須考慮恢復(fù)目標。我每次給新庫配置備份時都會寫一段類似這樣的完整備份腳本BACKUP DATABASE [你的數(shù)據(jù)庫] TO DISK ND:\Backup\你的數(shù)據(jù)庫_20250601.bak WITH INIT, NAME N你的數(shù)據(jù)庫-完整備份, COMPRESSION, CHECKSUM, STATS 10;這里面每一句話都有講究。WITH INIT表示覆蓋同名文件避免同一天的重復(fù)備份追加成多段備份導(dǎo)致文件越來越大。COMPRESSION啟用備份壓縮備份文件體積能減少一半以上而且SQL Server可以把壓縮卸載到備份過程中恢復(fù)時也能自動解壓。CHECKSUM開啟校驗和計算在備份時能檢測頁損壞避免靜默的壞頁一路帶到備份中。STATS 10表示每完成10%就輸出一次進度方便觀察大庫備份進度。事務(wù)日志備份則寫成BACKUP LOG完整恢復(fù)模式的數(shù)據(jù)庫建議開啟定期日志備份。如果從來不做日志備份完整恢復(fù)模式下的日志會一直累積到下一次完整備份才截斷時間一長磁盤就滿了。5.2 恢復(fù)操作中最常見的“假成功”備份恢復(fù)時SSMS圖形界面有一個默認選項“覆蓋現(xiàn)有數(shù)據(jù)庫”很多新手沒注意直接勾上恢復(fù)完卻發(fā)現(xiàn)數(shù)據(jù)庫還是舊的或者恢復(fù)失敗然后一臉懵。實際原因是一些恢復(fù)細節(jié)沒配好。正確做法如下。第一恢復(fù)時一定要指定“選項”頁里的“恢復(fù)到”狀態(tài)。如果選項是RESTORE WITH RECOVERY恢復(fù)完成后數(shù)據(jù)庫會處于在線狀態(tài)可以直接訪問如果選了RESTORE WITH NORECOVERY數(shù)據(jù)庫會處于“正在還原”狀態(tài)用戶可以繼續(xù)追加備份文件比如從差異備份或日志備份做增量還原這個模式適合搭建可用性組或做時間點恢復(fù)但在普通單機場景里如果最后沒有執(zhí)行RECOVERY數(shù)據(jù)庫會一直顯示“正在還原”看起來像假死了。第二默認恢復(fù)時數(shù)據(jù)文件路徑是備份文件記錄的原始路徑。如果源庫和目標庫的磁盤盤符不一致恢復(fù)會直接報錯“找不到文件”。解決方法是在“文件”頁里手動修改邏輯文件名對應(yīng)的物理路徑到目標位置。這個坑幾乎每個從一臺服務(wù)器遷移到另一臺服務(wù)器的同學(xué)都會踩一次。第三恢復(fù)前要確認備份文件的完整性?？梢杂靡韵旅顑H讀取備份信息不實際恢復(fù)RESTORE HEADERONLY FROM DISK ND:\Backup\你的數(shù)據(jù)庫.bak; RESTORE FILELISTONLY FROM DISK ND:\Backup\你的數(shù)據(jù)庫.bak;這兩條命令能快速查看備份集里包含了哪些文件、備份時間、是否包含日志等。在真正執(zhí)行恢復(fù)之前先跑這兩個命令是一種很穩(wěn)的低成本驗證方式。5.3 千萬別在生產(chǎn)庫上做的幾件“小事”有些操作在生產(chǎn)環(huán)境中一旦執(zhí)行后果可能比丟了數(shù)據(jù)還麻煩。第一件在SSMS里直接雙擊表然后“編輯前200行”手工改數(shù)據(jù)。圖形化編輯數(shù)據(jù)很容易因為誤操作把某一行改了而且沒有臨時保存的概念改動會跟著緩沖一起提交。如果一定要手工改先執(zhí)行SELECT … FOR UPDATE或者用事務(wù)包裹確認無誤后提交。更穩(wěn)妥的方式是寫UPDATE腳本放事務(wù)里執(zhí)行。第二件不用WHERE條件直接對全表執(zhí)行UPDATE或者DELETE。很多人會在開發(fā)環(huán)境寫順了切到生產(chǎn)窗口忘記把WHERE補上等回車了才發(fā)現(xiàn)“影響行數(shù)”是幾十萬。這類事故已經(jīng)成了數(shù)據(jù)庫事故排行榜里的?？?。習(xí)慣性做法是先把SELECT寫出來看行數(shù)再把SELECT改成UPDATE或DELETE并對影響行數(shù)做二次確認。第三件隨便重啟SQL Server服務(wù)。在SSMS里右擊實例選擇“重啟”會把當前所有連接斷開正在跑的長事務(wù)回滾。生產(chǎn)實例在業(yè)務(wù)高峰期重啟回滾操作本身就可能讓整個庫不可用很久。重啟前務(wù)必檢查是否有長時間運行的會話可以用活動監(jiān)視器或DMV查詢確認。5.4 斷連和“無法連接到服務(wù)器”的排查順序“無法連接到服務(wù)器”這個報錯是SSMS相關(guān)的提問里出現(xiàn)頻率最高的。我總結(jié)了一個排查順序基本能覆蓋90%的場景。第一步看服務(wù)是否啟動。在“SQL Server配置管理器”里確認SQL Server服務(wù)狀態(tài)是“正在運行”。如果服務(wù)沒有啟動SSMS當然連不上這時候連的是本機還是遠程都一樣。第二步看網(wǎng)絡(luò)和防火墻。遠程連接場景需要在防火墻里放行SQL Server使用的TCP端口默認是1433。可以用telnet或者Test-NetConnection去測端口是否開放。注意SQL Server可能有多個實例命名實例的端口不是默認1433而是動態(tài)分配的端口需要先通過SQL Server Browser服務(wù)或者客戶端別名來定位端口。第三步看身份驗證模式。如果實例沒有開啟“SQL Server和Windows身份驗證模式”創(chuàng)建了SQL賬號也登錄不了。在SSMS連接對話框里切換身份驗證方式時可以記住這個規(guī)則先確保你在Windows本機有管理員權(quán)限否則很多配置項無權(quán)更改。第四步數(shù)據(jù)庫實例名拼寫。遠程連接時服務(wù)器名稱要寫“IP地址\實例名”如果只寫了IP地址連的是默認實例。如果SQL Server以命名實例方式安裝這樣連當然會失敗。第五步登錄名是否被鎖定或者密碼過期。如果設(shè)置了密碼策略多次輸入錯誤密碼賬號會被鎖定。查看“安全性 - 登錄名”里的狀態(tài)即可確認。這個順序幾乎是一條鏈路走下來太多人上來就重裝SQL Server重裝完問題還在最后還是老老實實按這個順序排查出是防火墻端口沒放行。6. 進階操作把SSMS變成你的數(shù)據(jù)庫百寶箱6.1 使用“編寫腳本為”功能快速導(dǎo)出結(jié)構(gòu)在對象資源管理器里右擊任意對象選擇“編寫腳本為”能生成該對象的創(chuàng)建腳本。這個功能在很多場景下比圖形化管理更快也更適合版本控制。比如要把開發(fā)庫里的某張表結(jié)構(gòu)同步到測試庫最穩(wěn)妥的方式不是直接在測試庫里重新建一遍表而是右擊表 - “編寫腳本為” - “CREATE 到” - “新查詢編輯器窗口”然后把生成好的腳本拿到目標庫執(zhí)行。因為這段腳本包含了列定義、索引、約束和默認值不會像手工建表那樣漏掉細節(jié)。如果想把整個數(shù)據(jù)庫的所有對象都導(dǎo)出成腳本可以右擊數(shù)據(jù)庫 - “任務(wù)” - “生成腳本”。在“選擇對象”頁面可以選擇“整個數(shù)據(jù)庫及所有數(shù)據(jù)庫對象”在“高級”選項里可以設(shè)定是否包含索引、觸發(fā)器、統(tǒng)計信息等。整套腳本導(dǎo)出后基本就是一份數(shù)據(jù)庫結(jié)構(gòu)文檔放在版本控制里非常合適。6.2 自定義快捷鍵和代碼片段SSMS的編輯器擴展性雖然不如Visual Studio那么完善但支持自定義快捷鍵。在“工具 - 選項 - 環(huán)境 - 鍵盤”里可以搜索某個命令并分配新快捷鍵。比如默認的注釋和取消注釋快捷鍵不是所有人都習(xí)慣可以改成一個自己順手的組合。代碼片段這塊前面提到了實際使用中可以自定義。SSMS的代碼片段文件是XML格式存放在安裝路徑下的“Code Snippets”目錄里。按自己的常用邏輯寫一個模板文件下次寫復(fù)雜查詢時就省掉大量重復(fù)輸入。舉一個我自己的例子我常寫“分頁查詢”腳本就專門存了帶ROW_NUMBER、OFFSET-FETCH和包含列名的代碼片段每次套用后只需改表名和篩選條件。6.3 日常連接多個實例時的“已注冊服務(wù)器”很多DBA和開發(fā)手上不止一個SQL Server實例開發(fā)環(huán)境、測試環(huán)境、生產(chǎn)環(huán)境可能還有各類歷史環(huán)境。每次連接都輸入服務(wù)器地址和憑據(jù)重復(fù)且易出錯。SSMS里有“已注冊服務(wù)器”窗口視圖 - 已注冊服務(wù)器可以把所有實例分組保存配上顏色標記。比如開發(fā)庫標成綠色生產(chǎn)庫標成紅色。雙擊一條記錄就能直接連接不用每次手輸服務(wù)器名稱?！耙炎苑?wù)器”還有一個很實用的特性你可以在一個組上右鍵選擇“新建查詢”SSMS會同時為組內(nèi)的所有實例打開查詢窗口。運維批量執(zhí)行腳本時比如統(tǒng)一查所有實例的版本、統(tǒng)一收集基礎(chǔ)配置這種方法能節(jié)省大量切換時間。但生產(chǎn)環(huán)境批量執(zhí)行時需要注意事務(wù)一致性和執(zhí)行順序別把一批操作以不同順序跑在多個庫上。6.4 SQL Agent作業(yè)與日常自動化SQL Server代理是SSMS里最容易忽略但十分重要的模塊。日常運維中備份、索引維護、統(tǒng)計信息更新、歷史數(shù)據(jù)清理都能做成作業(yè)。新建作業(yè)時要注意“步驟”頁里的“類型”選擇“Transact-SQL 腳本(T-SQL)”然后寫執(zhí)行腳本。數(shù)據(jù)庫一欄要顯式指定數(shù)據(jù)庫名不要默認因為代理作業(yè)的默認上下文可能不是你預(yù)期的庫。腳本里如果涉及USE語句務(wù)必在開頭標明?！坝媱潯表撛O(shè)置頻率時常用“每周”、“每天”兩種。建議不要把所有作業(yè)都塞到凌晨02:00因為多個作業(yè)同時啟動時可能發(fā)生資源爭搶和實例負載波動?？梢园磳嵗撦d情況把備份作業(yè)放在22:30索引維護放在01:00統(tǒng)計更新放在03:00盡量錯峰?！巴ㄖ表摽梢栽O(shè)置“作業(yè)失敗時發(fā)送電子郵件”。配置數(shù)據(jù)庫郵件后在通知頁勾選“電子郵件”選項選擇操作員。這樣作業(yè)執(zhí)行失敗時能第一時間收到通知而不是等到業(yè)務(wù)反饋才發(fā)現(xiàn)備份一整個月都沒成功。7. 一些容易踩的版本兼容的坑7.1 SSMS版本和SQL Server版本的對應(yīng)關(guān)系SSMS雖是官方客戶端但并非所有版本都能連接所有SQL Server版本。之前提到高版本SSMS一般向下兼容低版本實例但低版本SSMS連接高版本實例時經(jīng)常遇到“對象資源管理器不支持此服務(wù)器版本”的提示。舉個例子老版本SSMS連新版本實例時新建數(shù)據(jù)庫向?qū)?、查詢存儲界面、部分DMV查看頁面可能直接報錯。為了省麻煩最好保持SSMS為最新穩(wěn)定版本。同時要注意SQL Server的某個分支版本比如早期版本甚至更老的2000/2005已經(jīng)不再受官方支持SSMS最新版也不一定兼容。這時候要么升級實例要么繼續(xù)使用對應(yīng)年代的舊版SSMS。但舊版SSMS在新操作系統(tǒng)上運行又容易出兼容性問題?？傊谏a(chǎn)環(huán)境長期使用時保持SQL Server和SSMS都在受支持的生命周期內(nèi)是很值得安排的一件事。7.2 連接Azure SQL Database時與本地SSMS的差異連接Azure SQL Database云數(shù)據(jù)庫時SSMS的基本操作習(xí)慣和本地實例差不多但有幾個重要差異。登錄名和用戶映射邏輯不一致。Azure SQL Database里沒有“登錄名映射到數(shù)據(jù)庫用戶”的傳統(tǒng)概念登錄用戶就是數(shù)據(jù)庫用戶在連接時要選擇正確數(shù)據(jù)庫不能像本地實例那樣先連實例再切換庫。某些本地DMV不可用。Azure SQL Database會屏蔽很多和操作系統(tǒng)、服務(wù)狀態(tài)相關(guān)的視圖遇到不支持的DMV時只能使用云數(shù)據(jù)庫特有的DMV。備份恢復(fù)方式是服務(wù)托管的SSMS里部分備份任務(wù)無法在本地實例上執(zhí)行需要通過Azure門戶或者自動化方式做。防火墻規(guī)則是以IP白名單方式配置的有時連接報錯的原因不是SQL賬號密碼而是客戶端的公網(wǎng)IP沒有加進白名單。很多一開始在本地跑SQL Server的人第一次連云端會有點不適但實際上只要理解了“實例邊界”這個概念大部分問題都能順著SSMS的報錯提示和文檔定位出來。8. 日常維護建議和長期習(xí)慣8.1 每月做一次索引和統(tǒng)計信息體檢不管業(yè)務(wù)量大小SQL Server用久了索引碎片和統(tǒng)計信息陳舊都是必然的。SSMS圖形界面里有現(xiàn)成的“維護計劃”向?qū)г凇肮芾?- 維護計劃”里可以圖形化配置索引重建、統(tǒng)計信息更新、數(shù)據(jù)庫一致性檢查等任務(wù)。實際操作時不建議對整庫所有索引一律重建。碎片率低于30%的索引重組比重建更合適因為重建會頻繁鎖表影響在線業(yè)務(wù)。而統(tǒng)計信息更新也不建議全部更新大表的完整掃描更新耗時很長。比較穩(wěn)妥的方式是分庫分表評估或使用采樣更新。8.2 定期檢查數(shù)據(jù)庫一致性SQL Server在正常關(guān)機或異常斷電后數(shù)據(jù)頁可能發(fā)生損壞SSMS的“DBCC CHECKDB”功能可以檢測這種頁損壞。在執(zhí)行DBCC CHECKDB時需要注意它可能會消耗大量IO和CPU。生產(chǎn)環(huán)境盡量安排在低峰期執(zhí)行。若檢查發(fā)現(xiàn)錯誤可以先查看錯誤日志了解錯誤級別。部分頁損壞可能可以通過備份恢復(fù)所以定期備份和定期一致性檢查要搭配執(zhí)行。8.3 善用SSMS自帶報表SSMS里每個數(shù)據(jù)庫節(jié)點下右鍵 - “報表” - “標準報表”有一堆監(jiān)控報表可以直接使用。磁盤使用情況、緩存命中率、索引使用統(tǒng)計、死鎖跟蹤等都能以圖形化方式生成。雖然這些報表和第三方監(jiān)控工具相比不算深入但勝在零部署、零成本在中小型環(huán)境里完全能看出趨勢。我平時會定期跑一份“索引使用情況”報表看看哪些索引幾乎從不被使用識別出低效索引后和業(yè)務(wù)確認再決定是否刪除。這種刪除無用索引的操作為數(shù)據(jù)庫寫入性能帶來的正向影響常常被不少團隊忽略。9. 最后再分享一點自己的經(jīng)驗用SSMS這么多年我最大的體會是它不是一個“打開了寫SQL”的連接工具而是一整套數(shù)據(jù)庫日常操作的中控臺。新手期會把它當查詢工具工作幾年后才會慢慢意識到SSMS的真正價值在于把備份、恢復(fù)、權(quán)限、監(jiān)控、調(diào)度全部收斂到一個圖形界面下同時保留了隨時生成腳本的能力。這種“圖形界面腳本化”的雙軌模式才是SQL Server日常管理最順手的方式。如果讓我給剛接觸的人一個具體建議我會說從今天開始所有在SSMS圖形界面里做完的操作馬上用“編寫腳本為”重新生成一份腳本并保存。堅持一個月你就有了一整套屬于自己環(huán)境的運維腳本集之后無論是遷移、快速搭建還是排查歷史操作都有據(jù)可查。這個習(xí)慣比背住任何一條快捷鍵都值錢。最后多提一句“工具 - 選項 - 環(huán)境 - 啟動時”可以設(shè)置“打開空環(huán)境”不讓SSMS每次啟動就自動連接上次的服務(wù)器。如果你經(jīng)常手滑連錯環(huán)境這個設(shè)置能幫你減少一次操作失誤的機會。別小看這些小配置日常管理數(shù)據(jù)庫最浪費時間的往往不是技術(shù)難題而是那些反復(fù)出現(xiàn)的小差錯。