SQL Server 2008查詢性能優(yōu)化

第1章 SQL查詢性能調(diào)整 1
1.1 性能調(diào)整過程 2
1.1.1 核心過程 2
1.1.2 迭代過程 4
1.2 性能vs.價格 7
1.2.1 性能目標(biāo) 7
1.2.2 “足夠好”的調(diào)整 7
1.3 性能基線 8
1.4 工作的重點(diǎn) 9
1.5 SQL Server性能殺手 10
1.5.1 低質(zhì)量的索引 10
1.5.2 不精確的統(tǒng)計 11
1.5.3 過多的阻塞和死鎖 11
1.5.4 不基于數(shù)據(jù)集的操作 11
1.5.5 低質(zhì)量的查詢設(shè)計 12
1.5.6 低質(zhì)量的數(shù)據(jù)庫設(shè)計 12
1.5.7 過多的碎片 12
1.5.8 不可重用的執(zhí)行計劃 13
1.5.9 低質(zhì)量的執(zhí)行計劃 13
1.5.10 頻繁重編譯計劃 13
1.5.11 游標(biāo)的錯誤使用 13
1.5.12 錯誤配置數(shù)據(jù)庫日志 14
1.5.13 過多使用或者錯誤配置tempdb 14
1.6 小結(jié) 14
第2章 系統(tǒng)性能分析 15
2.1 性能監(jiān)視器工具 15
2.2 動態(tài)管理視圖 17
2.3 硬件資源瓶頸 18
2.3.1 識別瓶頸 18
2.3.2 瓶頸解決方案 19
2.4 內(nèi)存瓶頸分析 19
2.4.1 SQL Server內(nèi)存管理 20
2.4.2 Available Bytes 23
2.4.3 Pages/sec和Page Faults/sec計數(shù)器 23
2.4.4 Buffer Cache Hit Ratio 24
2.4.5 Page Life Expectancy 24
2.4.6 Checkpoint Pages/sec 24
2.4.7 Lazy writes/sec 24
2.4.8 Memory Grants Pending 25
2.4.9 Target Server Memory（KB）和Total Server Memory（KB） 25
2.5 內(nèi)存瓶頸解決方案 25
2.5.1 優(yōu)化應(yīng)用程序工作負(fù)載 26
2.5.2 為SQL Server分配更多內(nèi)存 27
2.5.3 增加系統(tǒng)內(nèi)存 27
2.5.4 更換32位處理器為64位處理器 27
2.5.5 啟用3GB進(jìn)程空間 28
2.5.6 在32位SQL Server中使用4GB以上內(nèi)存 28
2.6 磁盤瓶頸分析 29
2.6.1 磁盤計數(shù)器 30
2.6.2 % Disk Time 30
2.6.3 Current Disk Queue Length 31
2.6.4 Disk Transfers/sec 31
2.6.5 Disk Bytes/sec 32
2.6.6 Avg. Disk Sec/Read和Avg. Disk Sec/Write 32
2.7 磁盤瓶頸解決方案 32
2.7.1 優(yōu)化應(yīng)用程序工作負(fù)載 33
2.7.2 使用更快的磁盤驅(qū)動器 33
2.7.3 使用一個RAID陣列 33
2.7.4 使用SAN系統(tǒng) 35
2.7.5 恰當(dāng)?shù)貙R磁盤 35
2.7.6 使用電池后備的控制器緩存 36
2.7.7 添加系統(tǒng)內(nèi)存 36
2.7.8 創(chuàng)建多個文件和文件組 36
2.7.9 將表和索引放在不同的磁盤上 39
2.7.10 將日志文件保存到獨(dú)立的物理磁盤 39
2.7.11 表的分區(qū) 40
2.8 處理器瓶頸分析 40
2.8.1 % Processor Time 41
2.8.2 % Privileged Time 41
2.8.3 Processor Queue Length 42
2.8.4 Context Switches/sec 42
2.8.5 Batch Requests/sec 42
2.8.6 SQL Compilations/sec 42
2.8.7 SQL Recompilations/sec 43
2.9 處理器瓶頸解決方案 43
2.9.1 優(yōu)化應(yīng)用程序工作負(fù)載 43
2.9.2 消除過多的編譯/重編譯 43
2.9.3 使用更多或更快的處理器 44
2.9.4 使用大的二級（L2）/三級（L3）緩存 44
2.9.5 運(yùn)行更高效的控制器/驅(qū)動程序 44
2.9.6 不運(yùn)行不必要的軟件 45
2.10 網(wǎng)絡(luò)瓶頸分析 45
2.10.1 Bytes Total/sec 45
2.10.2 % Net Utilization 46
2.11 網(wǎng)絡(luò)瓶頸解決方案 46
2.11.1 優(yōu)化應(yīng)用程序工作負(fù)載 46
2.11.2 增加網(wǎng)絡(luò)適配器 47
2.11.3 節(jié)制和避免中斷 47
2.12 SQL Server總體性能 47
2.12.1 丟失索引 48
2.12.2 數(shù)據(jù)庫阻塞 49
2.12.3 不可重用的執(zhí)行計劃 50
2.12.4 總體表現(xiàn) 50
2.13 創(chuàng)建一個基線 51
2.13.1 創(chuàng)建性能計數(shù)器的一個可重用列表 51
2.13.2 使用性能計數(shù)器列表創(chuàng)建一個計數(shù)器日志 54
2.13.3 最小化性能監(jiān)視器開銷 55
2.14 以基線為標(biāo)準(zhǔn)的系統(tǒng)狀態(tài)分析 56
2.15 小結(jié) 57
第3章 SQL查詢性能分析 58
3.1 SQL Profiler工具 58
3.1.1 Profiler跟蹤 59
3.1.2 事件 60
3.1.3 數(shù)據(jù)列 62
3.1.4 過濾器 64
3.1.5 跟蹤模板 65
3.1.6 跟蹤數(shù)據(jù) 65
3.2 跟蹤的自動化 66
3.2.1 使用GUI捕捉跟蹤 66
3.2.2 使用存儲過程捕捉跟蹤 67
3.3 結(jié)合跟蹤和性能監(jiān)視器輸出 68
3.4 SQL Profiler建議 69
3.4.1 限制事件和數(shù)據(jù)列 69
3.4.2 丟棄性能分析所用的啟動事件 70
3.4.3 限制跟蹤輸出大小 70
3.4.4 避免在線數(shù)據(jù)列排序 71
3.4.5 遠(yuǎn)程運(yùn)行Profiler 71
3.4.6 限制使用某些事件 71
3.5 沒有Profiler情況下的查詢性能度量 71
3.6 開銷較大的查詢 72
3.6.1 識別開銷較大的查詢 73
3.6.2 識別運(yùn)行緩慢的查詢 77
3.7 執(zhí)行計劃 78
3.7.1 分析查詢執(zhí)行計劃 80
3.7.2 識別執(zhí)行計劃中開銷較大的步驟 82
3.7.3 分析索引有效性 83
3.7.4 分析連接有效性 84
3.7.5 實際執(zhí)行計劃vs.估算執(zhí)行計劃 88
3.7.6 計劃緩存 89
3.8 查詢開銷 90
3.8.1 客戶統(tǒng)計 90
3.8.2 執(zhí)行時間 91
3.8.3 STATISTICS IO 92
3.9 小結(jié) 94
第4章 索引分析 95
4.1 什么是索引 95
4.1.1 索引的好處 97
4.1.2 索引開銷 98
4.2 索引設(shè)計建議 100
4.2.1 檢查WHERE子句和連接條件列 100
4.2.2 使用窄索引 102
4.2.3 檢查列的唯一性 103
4.2.4 檢查列數(shù)據(jù)類型 106
4.2.5 考慮列順序 107
4.2.6 考慮索引類型 109
4.3 聚簇索引 109
4.3.1 堆表 110
4.3.2 與非聚簇索引的關(guān)系 110
4.3.3 聚簇索引建議 112
4.4 非聚簇索引 117
4.4.1 非聚簇索引維護(hù) 117
4.4.2 定義書簽查找 117
4.4.3 非聚簇索引建議 118
4.5 聚簇索引vs.非聚簇索引 118
4.5.1 聚簇索引相對于非聚簇索引的好處 119
4.5.2 非聚簇索引相對于聚簇索引的好處 120
4.6 高級索引技術(shù) 121
4.6.1 覆蓋索引 122
4.6.2 索引交叉 124
4.6.3 索引連接 125
4.6.4 過濾索引 126
4.6.5 索引視圖 128
4.6.6 索引壓縮 132
4.7 特殊索引類型 134
4.7.1 全文索引 134
4.7.2 空間索引 135
4.7.3 XML 135
4.8 索引的附加特性 135
4.8.1 不同的列排序順序 135
4.8.2 在計算列上的索引 136
4.8.3 BIT數(shù)據(jù)類型列上的索引 136
4.8.4 作為一個查詢處理的CREATE INDEX語句 136
4.8.5 并行索引創(chuàng)建 136
4.8.6 在線索引創(chuàng)建 137
4.8.7 考慮數(shù)據(jù)庫引擎調(diào)整顧問 137
4.9 小結(jié) 137
第5章 數(shù)據(jù)庫引擎調(diào)整顧問 139
5.1 數(shù)據(jù)庫引擎調(diào)整顧問機(jī)制 139
5.2 數(shù)據(jù)庫引擎調(diào)整顧問實例 143
5.2.1 調(diào)整一個查詢 143
5.2.2 調(diào)整一個跟蹤工作負(fù)載 146
5.3 數(shù)據(jù)庫引擎調(diào)整顧問的局限性 148
5.4 小結(jié) 149
第6章 書簽查找分析 150
6.1 書簽查找的目的 150
6.2 書簽查找的缺點(diǎn) 152
6.3 分析書簽查找的起因 153
6.4 解決書簽查找 155
6.4.1 使用一個聚簇索引 155
6.4.2 使用一個覆蓋索引 155
6.4.3 使用索引連接 158
6.5 小結(jié) 160
第7章 統(tǒng)計分析 161
7.1 統(tǒng)計在查詢優(yōu)化中的角色 161
7.2 索引列上的統(tǒng)計 162
7.2.1 更新統(tǒng)計的好處 162
7.2.2 過時統(tǒng)計的缺點(diǎn) 164
7.3 在非索引列上的統(tǒng)計 165
7.3.1 在非索引列上統(tǒng)計的好處 166
7.3.2 丟失非索引列上的統(tǒng)計的缺點(diǎn) 169
7.4 分析統(tǒng)計 172
7.4.1 密度 174
7.4.2 多列索引上的統(tǒng)計 174
7.4.3 過濾索引上的統(tǒng)計 175
7.5 統(tǒng)計維護(hù) 176
7.5.1 自動維護(hù) 177
7.5.2 人工維護(hù) 179
7.5.3 統(tǒng)計維護(hù)狀態(tài) 181
7.6 為查詢分析統(tǒng)計的有效性 182
7.6.1 解決丟失統(tǒng)計問題 182
7.6.2 解決過時統(tǒng)計問題 184
7.7 建議 186
7.7.1 統(tǒng)計的向后兼容性 186
7.7.2 自動創(chuàng)建統(tǒng)計 186
7.7.3 自動更新統(tǒng)計 187
7.7.4 自動異步更新統(tǒng)計 189
7.7.5 收集統(tǒng)計的采樣數(shù)量 189
7.8 小結(jié) 190
第8章 碎片分析 191
8.1 碎片的成因 191
8.1.1 UPDATE語句引起的頁面分割 193
8.1.2 INSERT語句引起的頁面分割 196
8.2 碎片開銷 197
8.3 分析碎片數(shù)量 200
8.4 碎片解決方案 204
8.4.1 卸載并重建索引 204
8.4.2 使用DROP_EXISTING子句重建索引 205
8.4.3 執(zhí)行ALTER INDEX REBUILD語句 205
8.4.4 執(zhí)行ALTER INDEX REORGANIZE語句 207
8.5 填充因子的重要性 209
8.6 自動維護(hù) 212
8.7 小結(jié) 217
第9章 執(zhí)行計劃緩沖分析 218
9.1 執(zhí)行計劃生成 218
9.1.1 解析器 219
9.1.2 代數(shù)化器 220
9.1.3 優(yōu)化 221
9.2 執(zhí)行計劃緩沖 227
9.3 執(zhí)行計劃組件 227
9.3.1 查詢計劃 227
9.3.2 執(zhí)行上下文 227
9.4 執(zhí)行計劃的老化 228
9.5 分析執(zhí)行計劃緩沖 228
9.6 執(zhí)行計劃重用 229
9.6.1 即席工作負(fù)載 230
9.6.2 預(yù)定義工作負(fù)載 231
9.6.3 即席工作負(fù)載的計劃可重用性 231
9.6.4 預(yù)定義工作負(fù)載的計劃可重用性 239
9.7 查詢計劃Hash和查詢Hash 248
9.8 執(zhí)行計劃緩沖建議 251
9.8.1 明確地參數(shù)化查詢的可變部分 252
9.8.2 使用存儲過程實現(xiàn)業(yè)務(wù)功能 252
9.8.3 使用sp_executesql編程以避免存儲過程維護(hù) 252
9.8.4 實現(xiàn)準(zhǔn)備/執(zhí)行模式以避免重傳查詢字符串 253
9.8.5 避免即席查詢 253
9.8.6 對于動態(tài)查詢sp_executesql優(yōu)于EXECUTE 253
9.8.7 小心地參數(shù)化查詢的可變部分 254
9.8.8 不要允許查詢中對象的隱含解析 254
9.9 小結(jié) 254
第10章 存儲過程重編譯 256
10.1 重編譯的好處和缺點(diǎn) 256
10.2 確認(rèn)導(dǎo)致重編譯的語句 258
10.3 分析重編譯起因 260
10.3.1 架構(gòu)或綁定變化 261
10.3.2 統(tǒng)計變化 261
10.3.3 延遲對象解析 264
10.3.4 SET選項變化 266
10.3.5 執(zhí)行計劃老化 266
10.3.6 顯式調(diào)用sp_recompile 267
10.3.7 顯式使用RECOMPILE子句 268
10.4 避免重編譯 269
10.4.1 不要交替使用DDL和DML語句 270
10.4.2 避免統(tǒng)計變化引起的重編譯 271
10.4.3 使用表變量 273
10.4.4 避免在存儲過程中修改SET選項 275
10.4.5 使用OPTIMIZE FOR查詢提示 276
10.4.6 使用計劃指南 277
10.5 小結(jié) 281
第11章 查詢設(shè)計分析 282
11.1 查詢設(shè)計建議 282
11.2 在小結(jié)果集上操作 283
11.2.1 限制選擇列表中的列數(shù) 283
11.2.2 使用高選擇性的WHERE子句 284
11.3 有效地使用索引 284
11.3.1 避免不可參數(shù)化的搜索條件 285
11.3.2 避免WHERE子句列上的算術(shù)運(yùn)算符 289
11.3.3 避免WHERE子句列上的函數(shù) 290
11.4 避免優(yōu)化器提示 292
11.4.1 連接提示 293
11.4.2 索引提示 295
11.5 使用域和參照完整性 296
11.5.1 非空約束 297
11.5.2 聲明參照完整性 299
11.6 避免資源密集型查詢 301
11.6.1 避免數(shù)據(jù)類型轉(zhuǎn)換 301
11.6.2 使用EXISTS代替COUNT（*）驗證數(shù)據(jù)存在 303
11.6.3 使用UNION ALL代替UNION 304
11.6.4 為聚合和排序操作使用索引 305
11.6.5 避免在批查詢中的局部變量 306
11.6.6 小心地命名存儲過程 309
11.7 減少網(wǎng)絡(luò)傳輸數(shù)量 311
11.7.1 同時執(zhí)行多個查詢 311
11.7.2 使用SET NOCOUNT 311
11.8 降低事務(wù)開銷 312
11.8.1 減少日志開銷 312
11.8.2 減少鎖開銷 314
11.9 小結(jié) 315
第12章 阻塞分析 316
12.1 阻塞基礎(chǔ)知識 316
12.2 理解阻塞 317
12.2.1 原子性 317
12.2.2 一致性 320
12.2.3 隔離性 320
12.2.4 持久性 321
12.3 數(shù)據(jù)庫鎖 321
12.3.1 鎖粒度 322
12.3.2 鎖升級 325
12.3.3 鎖模式 326
12.3.4 鎖兼容性 332
12.4 隔離級別 332
12.4.1 未提交讀 333
12.4.2 已提交讀 333
12.4.3 可重復(fù)讀 335
12.4.4 可序列化（Serializable） 338
12.4.5 快照（Snapshot） 343
12.5 索引對鎖的作用 343
12.5.1 非聚簇索引的作用 344
12.5.2 聚簇索引的作用 346
12.5.3 索引在可序列化隔離級別上的作用 346
12.6 捕捉阻塞信息 347
12.6.1 使用SQL捕捉阻塞信息 347
12.6.2 Profiler跟蹤和被阻塞進(jìn)程報告事件 349
12.7 阻塞解決方案 351
12.7.1 優(yōu)化查詢 352
12.7.2 降低隔離級別 352
12.7.3 分區(qū)爭用的數(shù)據(jù) 353
12.7.4 爭用數(shù)據(jù)上的覆蓋索引 354
12.8 減少阻塞的建議 354
12.9 自動化偵測和收集阻塞信息 355
12.10 小結(jié) 359
第13章 死鎖分析 360
13.1 死鎖基礎(chǔ)知識 360
13.2 使用錯誤處理來捕捉死鎖 361
13.3 死鎖分析 362
13.3.1 收集死鎖信息 362
13.3.2 分析死鎖 364
13.4 避免死鎖 368
13.4.1 按照相同的時間順序訪問資源 368
13.4.2 減少被訪問資源的數(shù)量 369
13.4.3 最小化鎖的爭用 369
13.5 小結(jié) 370
第14章 游標(biāo)開銷分析 372
14.1 游標(biāo)基礎(chǔ)知識 372
14.1.1 游標(biāo)位置 373
14.1.2 游標(biāo)并發(fā)性 374
14.1.3 游標(biāo)類型 376
14.2 游標(biāo)開銷比較 378
14.2.1 游標(biāo)位置的開銷比較 378
14.2.2 游標(biāo)并發(fā)性上的開銷比較 380
14.2.3 在游標(biāo)類型上的開銷比較 381
14.3 默認(rèn)結(jié)果集 383
14.3.1 好處 384
14.3.2 缺點(diǎn) 384
14.4 分析SQL Server游標(biāo)開銷 386
14.5 游標(biāo)建議 390
14.6 小結(jié) 392
第15章 數(shù)據(jù)庫工作負(fù)載優(yōu)化 393
15.1 工作負(fù)載優(yōu)化基礎(chǔ)知識 393
15.2 工作負(fù)載優(yōu)化步驟 394
15.3 捕捉工作負(fù)載 397
15.4 分析工作負(fù)載 399
15.5 識別開銷最大的查詢 400
15.6 確定開銷最大的查詢的基線資源使用 402
15.6.1 總體資源使用 402
15.6.2 詳細(xì)資源使用 402
15.7 分析和優(yōu)化外部因素 405
15.7.1 分析應(yīng)用程序使用的批級別選項 405
15.7.2 分析統(tǒng)計有效性 406
15.7.3 分析碎片整理需求 406
15.8 分析開銷最大的查詢的內(nèi)部行為 410
15.8.1 分析查詢執(zhí)行計劃 410
15.8.2 識別執(zhí)行計劃中開銷較大的步驟 412
15.8.3 分析處理策略的效率 412
15.9 優(yōu)化代價最大的查詢 412
15.9.1 修改現(xiàn)有索引 413
15.9.2 分析連接提示的應(yīng)用 415
15.9.3 避免聚簇索引掃描操作 417
15.9.4 修改過程 418
15.10 分析對數(shù)據(jù)庫工作負(fù)載的影響 420
15.11 迭代各個優(yōu)化階段 421
15.12 小結(jié) 424
第16章 SQL Server優(yōu)化檢查列表 425
16.1 數(shù)據(jù)庫設(shè)計 425
16.1.1 平衡不足和過多的規(guī)范化 426
16.1.2 從實體完整性約束中得利 427
16.1.3 從域和參照完整性約束中得利 428
16.1.4 采用索引設(shè)計最佳實踐 430
16.1.5 避免在存儲過程名稱中使用sp_前綴 431
16.1.6 最小化觸發(fā)器的使用 431
16.2 查詢設(shè)計 432
16.2.1 使用SET NOCOUNT ON命令 432
16.2.2 顯式定義對象所有者 432
16.2.3 避免不可參數(shù)化的搜索條件 432
16.2.4 避免WHERE子句列上的算術(shù)運(yùn)算符 433
16.2.5 避免優(yōu)化器提示 434
16.2.6 遠(yuǎn)離嵌套視圖 434
16.2.7 確保沒有隱含的數(shù)據(jù)類型轉(zhuǎn)換 435
16.2.8 最小化日志開銷 435
16.2.9 采用重用執(zhí)行計劃的最佳實踐 435
16.2.10 采用數(shù)據(jù)庫事務(wù)最佳實踐 436
16.2.11 消除或減少數(shù)據(jù)庫游標(biāo)開銷 437
16.3 配置設(shè)置 437
16.3.1 Affinity Mask 437
16.3.2 內(nèi)存配置選項 437
16.3.3 并行性開銷閾值 438
16.3.4 最大并行度 438
16.3.5 優(yōu)化即席工作負(fù)載 438
16.3.6 查詢調(diào)控器開銷限制 439
16.3.7 填充因子（%） 439
16.3.8 被阻塞過程閾值 439
16.3.9 數(shù)據(jù)庫文件布局 439
16.3.10 數(shù)據(jù)庫壓縮 440
16.4 數(shù)據(jù)庫管理 440
16.4.1 保持統(tǒng)計最新 440
16.4.2 保持最小數(shù)量的索引碎片數(shù)量 441
16.4.3 循環(huán)使用SQL錯誤日志文件 441
16.4.4 避免像AUTO_CLOSE或AUTO_SHRINK這樣的自動化數(shù)據(jù)庫功能 441
16.4.5 最小化SQL跟蹤開銷 442
16.5 數(shù)據(jù)庫備份 442
16.5.1 增量和事務(wù)日志備份頻率 442
16.5.2 備份分布 443
16.5.3 備份壓縮 444
16.6 小結(jié) 444