SQL Server性能優化與管理的藝術（簡體書）

本書共15章，分為三部分，第一部分（第1-2章）為概述部分，闡述SQLServer方面的“性能”及相關概念。并給出常規的性能及性能相關的問題偵測的“方法論”，讀者可以通過這兩章的介紹，對SQLServer性能問題有一個高層次的認識。第二部分（第3-10章）為知識準備部分，這部分介紹了SQLServer性能相關的基礎知識。只有了解了性能及影響性能的相關部分，才能準確地、高效地進行優化。第三部分（第11-15章）為工具使用及優化演示，在多服務器、大數據的環境下，不應該再使用原始的故障偵測方法，借用各種工具能更全面、更高效地找到問題并且解決問題。

黃釗吉 數據平臺架構師，從事數據庫管理、優化及平臺搭建工作，獲得MCITP 2008 DBA認證、MCSE DATA Platform 認證，CSDN論壇SQL Server 大版主、博客專家，2013年至2014年連續兩年被評為SQL Server MVP。曾參與服飾類電子商務網站、銀行系統的開發管理工作及ERP系統、歐洲信用卡平臺系統（基于亞馬遜云服務，AWS）的數據庫管理及平臺架構工作。

1. 深入剖析SQL Server的優化與管理機制及核心技術，從“方法論”的角度指導讀者定位和解決問題，以獨特視角展示SQL Server管理之道。 

2. 收錄了與SQL Server性能優化相關的各類問題和工作中的“雷區”并給出了解決方法，包含大量技巧和最佳實踐。 

目　錄

前　言
第一部分　SQL Server性能優化概述
第1章　性能概述 2
1.1　何為性能 2
1.2　性能指標 3
1.3　性能目標 3
1.4　影響性能的常見因素 4
1.4.1　應用程序的體系結構 4
1.4.2　應用程序設計 5
1.4.3　事務和隔離級別 5
1.4.4　T-SQL代碼 5
1.4.5　硬件資源 6
1.4.6　SQL Server配置 6 目　錄

前　言
第一部分　SQL Server性能優化概述
第1章　性能概述 2
1.1　何為性能 2
1.2　性能指標 3
1.3　性能目標 3
1.4　影響性能的常見因素 4
1.4.1　應用程序的體系結構 4
1.4.2　應用程序設計 5
1.4.3　事務和隔離級別 5
1.4.4　T-SQL代碼 5
1.4.5　硬件資源 6
1.4.6　SQL Server配置 6
1.5　小結 8
第2章　初探優化 9
2.1　優化論 9
2.2　定義問題 10
2.2.1　使用工具找到性能瓶頸 12
2.2.2　通過性能數據進行分類 12
2.3　根據性能數據分析問題 14
2.4　驗證處理手段及部署 14
2.5　問題歸檔 15
2.6　小結 15
第二部分　SQL Server性能優化理論知識
第3章　體系結構 18
3.1　SQL Server查詢體系 18
3.2　數據庫事務 22
3.2.1　事務特性 22
3.2.2　事務類型 22
3.3　查詢的生命周期 23
3.3.1　SQL Server組件 23
3.3.2　緩沖池 23
3.3.3　簡單的SELECT查詢過程 23
3.4　執行模型 28
3.5　SQLOS 30
3.6　SQL Server 內存 30
3.6.1　物理內存和虛擬內存 30
3.6.2　SQL Server 內存 32
3.6.3　內存問題診斷 34
3.6.4　優化SQL Server內存配置 34
3.6.5　優化Ad-Hoc 工作負載 36
3.7　小結 38
第4章　硬件資源 39
4.1　CPU 39
4.1.1　SQL Server工作負載類型 39
4.1.2　CPU評估 40
4.1.3　CPU配置 43
4.2　存儲系統 43
4.2.1　磁盤I/O 43
4.2.2　驅動器類型 44
4.2.3　RAID配置 45
4.2.4　配置存儲系統 46
4.2.5　檢查讀寫速率 46
4.3　CPU 性能偵測 48
4.3.1　偵測CPU壓力 48
4.3.2　研究CPU相關的等待信息 49
4.3.3　查找CPU消耗高的查詢 50
4.3.4　常見高CPU利用率的原因 51
4.4　I/O性能偵測 59
4.5　小結 59
第5章　查詢優化器 60
5.1　查詢過程 60
5.2　查詢優化器 62
5.2.1　產生執行計劃 62
5.2.2　連接 63
5.3　執行引擎 66
5.3.1　數據訪問操作 66
5.3.2　聚合操作 70
5.3.3　并行執行 73
5.4　統計信息和開銷預估 73
5.4.1　統計信息 73
5.4.2　統計信息維護 77
5.4.3　計算列上的統計信息 78
5.4.4　過濾索引上的統計信息 79
5.4.5　預估數量錯誤 81
5.4.6　更新統計信息 81
5.5　優化器工作過程 83
5.6　小結 88
第6章　索引及統計信息 89
6.1　索引基礎 90
6.1.1　為什么要索引 90
6.1.2　索引的主要類型 91
6.1.3　索引元數據 91
6.2　索引存儲基礎 92
6.2.1　SQL Server存儲基礎 92
6.2.2　頁的組織 95
6.2.3　檢查工具 98
6.2.4　頁碎片 110
6.3　索引統計信息 113
6.3.1　索引層級的統計信息 113
6.3.2　索引使用的統計信息 117
6.3.3　索引操作的統計信息 120
6.3.4　索引物理統計信息 126
6.4　索引誤區及使用建議 127
6.4.1　常見誤區 127
6.4.2　索引使用建議 135
6.4.3　關于索引的查詢建議 137
6.5　索引維護 143
6.5.1　索引碎片 143
6.5.2　索引統計信息維護 155
6.6　索引工具 156
6.6.1　缺失索引DMO 156
6.6.2　使用DMO 158
6.6.3　數據庫引擎優化顧問 159
6.6.4　使用DMO偵測索引問題 162
6.7　索引策略 165
6.7.1　堆 165
6.7.2　聚集索引 167
6.7.3　非聚集索引 168
6.7.4　索引存儲 182
6.7.5　索引視圖 185
6.8　索引分析 187
6.8.1　索引方法論 187
6.8.2　監控 188
6.8.3　分析 199
6.8.4　實施 212
6.8.5　重復 213
6.9　案例 213
6.10　小結 215
第7章　等待 216
7.1　等待簡介 217
7.1.1　什么是等待 217
7.1.2　為什么需要等待信息 218
7.1.3　保存等待信息 218
7.2　查詢等待 219
7.3　并行執行 219
7.3.1　CXPACKET 220
7.3.2　CXPACKET潛在問題 221
7.3.3　降低CXPACKET等待 221
7.3.4　CXPACKET深度分析 222
7.3.5　CXPACKET建議 222
7.4　多任務等待 223
7.4.1　SOS_SCHEDU-LER_YIELD 223
7.4.2　多任務類型 225
7.4.3　多任務潛在問題 226
7.4.4　降低多任務等待 226
7.5　I/O 等待 227
7.6　備份和還原等待 231
7.7　鎖定等待 231
7.8　數據庫日志等待 233
7.9　外部資源等待 235
7.10　其他常見的等待類型 237
7.11　小結 238
第8章　執行計劃 239
8.1　基礎知識 239
8.1.1　查詢提交 240
8.1.2　預估與實際執行計劃 241
8.1.3　執行計劃重用 242
8.1.4　清除緩存的執行計劃 243
8.1.5　執行計劃格式 243
8.1.6　使用DMO獲取緩存中的執行計劃 243
8.1.7　使用SQL Trace自動獲取執行計劃 244
8.2　圖形化執行計劃 244
8.2.1　基礎知識 245
8.2.2　單表查詢 245
8.2.3　表關聯 252
8.2.4　篩選數據 256
8.2.5　常見操作符 258
8.2.6　INSERT/UPDATE/DELETE的執行計劃 261
8.2.7　復雜查詢 264
8.3　控制執行計劃 280
8.3.1　查詢提示 281
8.3.2　聯接提示 293
8.3.3　表提示 297
8.4　擴展信息 298
8.4.1　閱讀龐大的執行計劃 298
8.4.2　并行操作 305
8.4.3　強制參數化 306
8.4.4　使用計劃指南 307
8.5　案例 313
8.6　小結 316
第9章　鎖、阻塞和死鎖 317
9.1　并發和事務 318
9.1.1　悲觀并發和樂觀并發 319
9.1.2　事務 320
9.1.3　丟失更新 325
9.2　鎖的基礎 327
9.2.1　鎖定概述 327
9.2.2　鎖資源/鎖類型 328
9.2.3　鎖模式 329
9.2.4　鎖的持續時間 330
9.2.5　鎖的所有權 330
9.2.6　鎖的元數據 331
9.3　高級鎖概念 332
9.3.1　鎖兼容性 332
9.3.2　鎖模式轉換 333
9.3.3　意向鎖 335
9.3.4　鍵范圍鎖 335
9.3.5　鎖升級 336
9.3.6　其他類型的鎖 338
9.3.7　非鎖定引起的阻塞 339
9.4　控制鎖行為 339
9.4.1　通過隔離級別控制并發性和鎖定行為 340
9.4.2　設定鎖的超時時間 340
9.4.3　鎖提示 341
9.5　悲觀并發的故障偵測 341
9.5.1　偵測鎖定 341
9.5.2　阻塞的故障排查 344
9.6　樂觀并發 348
9.6.1　行版本存儲 349
9.6.2　行版本存儲工作機制 349
9.6.3　基于快照的隔離模式 349
9.6.4　監控和管理版本存儲 350
9.6.5　管理版本存儲 351
9.6.6　選擇并發模式 353
9.7　死鎖 354
9.7.1　死鎖類型 354
9.7.2　自動死鎖偵測 356
9.7.3　捕獲死鎖 356
9.7.4　讀懂死鎖圖 363
9.7.5　最小化死鎖 364
9.8　監控和處理 366
9.8.1　使用DMV捕獲阻塞信息 366
9.8.2　使用Extended Events和blocked_process_report事件捕獲 368
9.8.3　阻塞問題解決方案 368
9.8.4　建議 369
9.9　小結 371
第10章　TempDB 372
10.1　TempDB簡介 372
10.1.1　TempDB是什么 372
10.1.2　什么操作會用到TempDB 373
10.2　TempDB上的常見問題及監控 378
10.2.1　空間問題 378
10.2.2　TempDB的I/O瓶頸 382
10.2.3　過多的DDL操作導致系統表上的瓶頸 383
10.3　優化TempDB 383
10.3.1　配置TempDB 384
10.3.2　優化TempDB 385
10.3.3　擴充閱讀 386
10.4　小結 386
第三部分　工具使用
第11章　使用傳統工具定位瓶頸 388
11.1　使用性能監視器及PAL收集和分析性能 389
11.1.1　性能監視器 389
11.1.2　數據收集器集 392
11.1.3　使用PAL分析 403
11.2　使用DMO獲取性能數據 407
11.2.1　DMO介紹 407
11.2.2　示例 407
11.3　使用Profiler獲取性能數據 414
11.3.1　用法及注意事項 414
11.3.2　Profiler示例 416
11.3.3　SQL Trace示例 419
11.4　DBCC命令 422
11.4.1　DBCC SQLPERF 422
11.4.2　DBCC INPUTBUFFER 424
11.4.3　DBCC TRACEON/TRACEOFF 425
11.4.4　DBCC SHOWCONTIG 425
11.4.5　DBCC OPENTRAN 426
11.5　小結 427
第12章　使用新工具定位瓶頸 428
12.1?PSSDIAG 428
12.2　PowerShell 434
12.2.1　簡介 434
12.2.2　打開PowerShell 434
12.2.3　使用PowerShell偵測服務器問題 435
12.3　小結 438
第13章　Extended Events 439
13.1　簡介 439
13.2　創建擴展事件 444
13.3　查詢收集的數據 449
13.3.1　監視實時數據 449
13.3.2　使用T-SQL查看 451
13.4　案例 452
13.5　小結 458
第14章　其他工具 459
14.1　SQLDiag 459
14.2　數據庫性能優化顧問 462
14.2.1　使用DTA進行單查詢分析 462
14.2.2　使用DTA進行全庫分析 466
14.3　Windows事件日志及
SQL Server Errorlog 470
14.3.1　Windows事件日志 470
14.3.2　SQL Server 錯誤日志 471
14.4　小結 472
第15章　優化服務器配置 473
15.1　數據庫工作負載特點 473
15.1.1　基礎知識 473
15.1.2　工作負載配置 474
15.2　工作負載優化步驟 477
15.2.1　數據庫設計 477
15.2.2　查詢設計 478
15.2.3　數據庫服務器配置 478
15.2.4　數據庫管理 479
15.2.5　數據庫備份 480
15.3　小結 481

顯示全部信息

第一部分
SQL Server 性能優化概述
本部分包含兩章，主要介紹何為性能及相關的一些術語，因為只有了解什么是性能，才能開始進一步的學習。如果讀者對此已經有了一定的基礎和經驗，可以跳過這部分，從第3章開始看起。但是基于連貫性，建議讀者抽空看看這部分的內容。


第1章
性 能 概 述
在工作、論壇、博客及一些QQ群中，很多人總會問：怎樣進行SQL Server性能優化？這個問題很難回答，一個千余人參與、發展了十幾年的產品，其所涉及的性能優化并不是三言兩語就可以說清楚的。想要熟練掌握該技能，需要系統地了解相關知識，而本書的主要目的就是幫助讀者全面深入地把握知識結構。書中會告訴讀者從哪里著手，優化的前提有哪些。但是，本書不是寫給那些沒有任何基礎，連一個相對簡單的查詢語句都不會，或者連SSMS（SQL Server Management Studio）都不知道怎么打開的人看的。如果你是這樣的人，建議你先看其他入門書籍。
本章首先會對全書進行一個簡單概述，幫助讀者了解一些與性能相關的知識。因為在處理性能問題之前，我們首先要知道，什么是性能問題。
1.1　何為性能
想要進行性能優化，首先必須要了解性能問題，也就是說，最起碼要對性能問題有一個較為明確的定義。試想，你生病了，去看醫生，一到醫院，你就對醫生說：醫生，我很不舒服，趕緊給我開藥。醫生連你哪里不舒服、怎么不舒服都不知道，憑什么開藥呢？所以我們首先要知道面臨的是什么問題，才能找到相應的對策。
作為DBA，經常會被程序員、公司管理層問道：為什么數據庫運行那么慢？如果你進一步詢問他們慢到什么程度，有什么表征時，得到的答案往往又很模糊，可能只有慢或者卡。
所以在處理性能問題時，首先要對其有一個清晰的定義，不然會浪費很多時間去查找問題的所在。但是作為非專業人員，的確很難清晰定義所有的問題。為此，不妨來對性能問題下一個非官方的定義：
在現有資源沒有達到最大吞吐量的前提下，若系統（包括操作系統、數據庫管理系統、應用程序等）不能滿足合理的預期表現，則可以定義為有性能問題。
注意上面的限定詞——合理。你不應該對所有的應用和操作都賦予很高的期望，比如，對于OLAP系統，它的某些操作往往需要大量時間和資源（比如ETL），你不要期望它總是能在幾秒內完成。當然，如果時間過長，也是可以定義為存在性能問題的。
另外，可以考慮一些非正式的定義，比如資源耗費明顯過多、運行速度的下降超過規劃速度等。
總之，在處理性能問題之前，盡可能給出清晰準確的定義，可以提高問題的解決效率。要分清什么是性能問題，什么不是性能問題（比如權限、某些硬件故障、某些程序bug），因為不同的問題對應的解決方案往往是不同的，所以首先應該對性能問題給出定義。
1.2　性能指標
定義性能問題時往往可以有很多指標，其中最常見、最重要的指標有3個：響應時間、吞吐量、可擴展性。響應時間這個指標其實很明顯，一個查詢運行得快，性能問題通常很少，但如果某個查詢的運行時間明顯過長，那就說明可能有一定的性能問題了，需要引起注意。終端用戶基本上只會關心他/她的請求是否能足夠快地得到響應，所以他們的“性能”匯報往往只是告訴你它很“慢”，而這個“慢”其實指的就是響應時間。吞吐量可以理解為網絡、設備、端口、虛電路或其他設備單位時間內成功地傳送數據的數量，也可以理解為資源的使用情況。比如磁盤，每秒的吞吐量越大，傳輸的數據就越多，SQL Server在向磁盤讀寫數據時延時就越短。可擴展性表示在遇到性能問題時，是否可以通過簡單的增加資源的方法來解決問題。
對于性能指標，并沒有一個固定值或者建議值，通常在要定義工作負荷（也叫做性能基線，將在后面章節中介紹）之后，通過監控及對比來把握。對于性能問題或者管理問題，常規的做法是先進行監控，然后分析監控數據，再根據分析結果進行處理，最后再次監控，一直如此循環往復，直到滿意為止。

1.3　性能目標
打個比方，筆者比較崇尚中醫的治療方法，他們所采用的“望聞問切”診斷方法對準確掌握病人的病情很有幫助。記得有人說過，DBA很多方面就像中醫，在面對性能問題的時候，使用這種中醫的診斷思維去處理會事半功倍。其實就是通過多方面檢測，找到性能問題的根源和一些潛在風險。中醫的一個思想是通過調理人體自身機能去抵抗外部的入侵，旨在把人體調整到一個“平衡”狀態，而不是像西醫那樣直接殺死染病細胞或者病毒。個人認為，優化性能的目的也是把系統調整到平衡狀態，要把事情做到極致，但是不要極端。比如，不要花幾個小時的時間，去嘗試優化一個已經在1～5s內能得到結果的查詢。
對于SQL Server的優化，一個比較通用的目標就是：盡可能最小化每個SQL語句（或者請求）的響應時間并增加系統的吞吐量，通過減少網絡延時、優化磁盤I/O吞吐量以及減少CPU的處理時間來最大化整個數據庫服務器的伸縮性，使系統能夠協調運作。
簡而言之，性能優化的目標就是通過一系列的手段，使系統能夠協調、平衡地運作，合理地響應外部及內部請求，實現資源利用的最大化。