SQL Server性能優(yōu)化與管理的藝術(shù)

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