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.價(jià)格 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)計(jì) 11
1.5.3 過多的阻塞和死鎖 11
1.5.4 不基于數(shù)據(jù)集的操作 11
1.5.5 低質(zhì)量的查詢?cè)O(shè)計(jì) 12
1.5.6 低質(zhì)量的數(shù)據(jù)庫設(shè)計(jì) 12
1.5.7 過多的碎片 12
1.5.8 不可重用的執(zhí)行計(jì)劃 13
1.5.9 低質(zhì)量的執(zhí)行計(jì)劃 13
1.5.10 頻繁重編譯計(jì)劃 13
1.5.11 游標(biāo)的錯(cuò)誤使用 13
1.5.12 錯(cuò)誤配置數(shù)據(jù)庫日志 14
1.5.13 過多使用或者錯(cuò)誤配置tempdb 14
1.6 小結(jié) 14
第2章 系統(tǒng)性能分析 15
2.1 性能監(jiān)視器工具 15
2.2 動(dòng)態(tài)管理視圖 17
2.3 硬件資源瓶頸 18
2.3.1 識(shí)別瓶頸 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計(jì)數(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 磁盤計(jì)數(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ū)動(dòng)器 33
2.7.3 使用一個(gè)RAID陣列 33
2.7.4 使用SAN系統(tǒng) 35
2.7.5 恰當(dāng)?shù)貙?duì)齊磁盤 35
2.7.6 使用電池后備的控制器緩存 36
2.7.7 添加系統(tǒng)內(nèi)存 36
2.7.8 創(chuàng)建多個(gè)文件和文件組 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 使用大的二級(jí)（L2）/三級(jí)（L3）緩存 44
2.9.5 運(yùn)行更高效的控制器/驅(qū)動(dòng)程序 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í)行計(jì)劃 50
2.12.4 總體表現(xiàn) 50
2.13 創(chuàng)建一個(gè)基線 51
2.13.1 創(chuàng)建性能計(jì)數(shù)器的一個(gè)可重用列表 51
2.13.2 使用性能計(jì)數(shù)器列表創(chuàng)建一個(gè)計(jì)數(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 跟蹤的自動(dòng)化 66
3.2.1 使用GUI捕捉跟蹤 66
3.2.2 使用存儲(chǔ)過程捕捉跟蹤 67
3.3 結(jié)合跟蹤和性能監(jiān)視器輸出 68
3.4 SQL Profiler建議 69
3.4.1 限制事件和數(shù)據(jù)列 69
3.4.2 丟棄性能分析所用的啟動(dòng)事件 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 識(shí)別開銷較大的查詢 73
3.6.2 識(shí)別運(yùn)行緩慢的查詢 77
3.7 執(zhí)行計(jì)劃 78
3.7.1 分析查詢執(zhí)行計(jì)劃 80
3.7.2 識(shí)別執(zhí)行計(jì)劃中開銷較大的步驟 82
3.7.3 分析索引有效性 83
3.7.4 分析連接有效性 84
3.7.5 實(shí)際執(zhí)行計(jì)劃vs.估算執(zhí)行計(jì)劃 88
3.7.6 計(jì)劃緩存 89
3.8 查詢開銷 90
3.8.1 客戶統(tǒng)計(jì) 90
3.8.2 執(zhí)行時(shí)間 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è)計(jì)建議 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 聚簇索引相對(duì)于非聚簇索引的好處 119
4.5.2 非聚簇索引相對(duì)于聚簇索引的好處 120
4.6 高級(jí)索引技術(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 在計(jì)算列上的索引 136
4.8.3 BIT數(shù)據(jù)類型列上的索引 136
4.8.4 作為一個(gè)查詢處理的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)整顧問實(shí)例 143
5.2.1 調(diào)整一個(gè)查詢 143
5.2.2 調(diào)整一個(gè)跟蹤工作負(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 使用一個(gè)聚簇索引 155
6.4.2 使用一個(gè)覆蓋索引 155
6.4.3 使用索引連接 158
6.5 小結(jié) 160
第7章 統(tǒng)計(jì)分析 161
7.1 統(tǒng)計(jì)在查詢優(yōu)化中的角色 161
7.2 索引列上的統(tǒng)計(jì) 162
7.2.1 更新統(tǒng)計(jì)的好處 162
7.2.2 過時(shí)統(tǒng)計(jì)的缺點(diǎn) 164
7.3 在非索引列上的統(tǒng)計(jì) 165
7.3.1 在非索引列上統(tǒng)計(jì)的好處 166
7.3.2 丟失非索引列上的統(tǒng)計(jì)的缺點(diǎn) 169
7.4 分析統(tǒng)計(jì) 172
7.4.1 密度 174
7.4.2 多列索引上的統(tǒng)計(jì) 174
7.4.3 過濾索引上的統(tǒng)計(jì) 175
7.5 統(tǒng)計(jì)維護(hù) 176
7.5.1 自動(dòng)維護(hù) 177
7.5.2 人工維護(hù) 179
7.5.3 統(tǒng)計(jì)維護(hù)狀態(tài) 181
7.6 為查詢分析統(tǒng)計(jì)的有效性 182
7.6.1 解決丟失統(tǒng)計(jì)問題 182
7.6.2 解決過時(shí)統(tǒng)計(jì)問題 184
7.7 建議 186
7.7.1 統(tǒng)計(jì)的向后兼容性 186
7.7.2 自動(dòng)創(chuàng)建統(tǒng)計(jì) 186
7.7.3 自動(dòng)更新統(tǒng)計(jì) 187
7.7.4 自動(dòng)異步更新統(tǒng)計(jì) 189
7.7.5 收集統(tǒng)計(jì)的采樣數(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 自動(dòng)維護(hù) 212
8.7 小結(jié) 217
第9章 執(zhí)行計(jì)劃緩沖分析 218
9.1 執(zhí)行計(jì)劃生成 218
9.1.1 解析器 219
9.1.2 代數(shù)化器 220
9.1.3 優(yōu)化 221
9.2 執(zhí)行計(jì)劃緩沖 227
9.3 執(zhí)行計(jì)劃組件 227
9.3.1 查詢計(jì)劃 227
9.3.2 執(zhí)行上下文 227
9.4 執(zhí)行計(jì)劃的老化 228
9.5 分析執(zhí)行計(jì)劃緩沖 228
9.6 執(zhí)行計(jì)劃重用 229
9.6.1 即席工作負(fù)載 230
9.6.2 預(yù)定義工作負(fù)載 231
9.6.3 即席工作負(fù)載的計(jì)劃可重用性 231
9.6.4 預(yù)定義工作負(fù)載的計(jì)劃可重用性 239
9.7 查詢計(jì)劃Hash和查詢Hash 248
9.8 執(zhí)行計(jì)劃緩沖建議 251
9.8.1 明確地參數(shù)化查詢的可變部分 252
9.8.2 使用存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)功能 252
9.8.3 使用sp_executesql編程以避免存儲(chǔ)過程維護(hù) 252
9.8.4 實(shí)現(xiàn)準(zhǔn)備/執(zhí)行模式以避免重傳查詢字符串 253
9.8.5 避免即席查詢 253
9.8.6 對(duì)于動(dòng)態(tài)查詢sp_executesql優(yōu)于EXECUTE 253
9.8.7 小心地參數(shù)化查詢的可變部分 254
9.8.8 不要允許查詢中對(duì)象的隱含解析 254
9.9 小結(jié) 254
第10章 存儲(chǔ)過程重編譯 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)計(jì)變化 261
10.3.3 延遲對(duì)象解析 264
10.3.4 SET選項(xiàng)變化 266
10.3.5 執(zhí)行計(jì)劃老化 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)計(jì)變化引起的重編譯 271
10.4.3 使用表變量 273
10.4.4 避免在存儲(chǔ)過程中修改SET選項(xiàng) 275
10.4.5 使用OPTIMIZE FOR查詢提示 276
10.4.6 使用計(jì)劃指南 277
10.5 小結(jié) 281
第11章 查詢?cè)O(shè)計(jì)分析 282
11.1 查詢?cè)O(shè)計(jì)建議 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（*）驗(yàn)證數(shù)據(jù)存在 303
11.6.3 使用UNION ALL代替UNION 304
11.6.4 為聚合和排序操作使用索引 305
11.6.5 避免在批查詢中的局部變量 306
11.6.6 小心地命名存儲(chǔ)過程 309
11.7 減少網(wǎng)絡(luò)傳輸數(shù)量 311
11.7.1 同時(shí)執(zhí)行多個(gè)查詢 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ǔ)知識(shí) 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 鎖升級(jí) 325
12.3.3 鎖模式 326
12.3.4 鎖兼容性 332
12.4 隔離級(jí)別 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 索引對(duì)鎖的作用 343
12.5.1 非聚簇索引的作用 344
12.5.2 聚簇索引的作用 346
12.5.3 索引在可序列化隔離級(jí)別上的作用 346
12.6 捕捉阻塞信息 347
12.6.1 使用SQL捕捉阻塞信息 347
12.6.2 Profiler跟蹤和被阻塞進(jìn)程報(bào)告事件 349
12.7 阻塞解決方案 351
12.7.1 優(yōu)化查詢 352
12.7.2 降低隔離級(jí)別 352
12.7.3 分區(qū)爭(zhēng)用的數(shù)據(jù) 353
12.7.4 爭(zhēng)用數(shù)據(jù)上的覆蓋索引 354
12.8 減少阻塞的建議 354
12.9 自動(dòng)化偵測(cè)和收集阻塞信息 355
12.10 小結(jié) 359
第13章 死鎖分析 360
13.1 死鎖基礎(chǔ)知識(shí) 360
13.2 使用錯(cuò)誤處理來捕捉死鎖 361
13.3 死鎖分析 362
13.3.1 收集死鎖信息 362
13.3.2 分析死鎖 364
13.4 避免死鎖 368
13.4.1 按照相同的時(shí)間順序訪問資源 368
13.4.2 減少被訪問資源的數(shù)量 369
13.4.3 最小化鎖的爭(zhēng)用 369
13.5 小結(jié) 370
第14章 游標(biāo)開銷分析 372
14.1 游標(biāo)基礎(chǔ)知識(shí) 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ǔ)知識(shí) 393
15.2 工作負(fù)載優(yōu)化步驟 394
15.3 捕捉工作負(fù)載 397
15.4 分析工作負(fù)載 399
15.5 識(shí)別開銷最大的查詢 400
15.6 確定開銷最大的查詢的基線資源使用 402
15.6.1 總體資源使用 402
15.6.2 詳細(xì)資源使用 402
15.7 分析和優(yōu)化外部因素 405
15.7.1 分析應(yīng)用程序使用的批級(jí)別選項(xiàng) 405
15.7.2 分析統(tǒng)計(jì)有效性 406
15.7.3 分析碎片整理需求 406
15.8 分析開銷最大的查詢的內(nèi)部行為 410
15.8.1 分析查詢執(zhí)行計(jì)劃 410
15.8.2 識(shí)別執(zhí)行計(jì)劃中開銷較大的步驟 412
15.8.3 分析處理策略的效率 412
15.9 優(yōu)化代價(jià)最大的查詢 412
15.9.1 修改現(xiàn)有索引 413
15.9.2 分析連接提示的應(yīng)用 415
15.9.3 避免聚簇索引掃描操作 417
15.9.4 修改過程 418
15.10 分析對(duì)數(shù)據(jù)庫工作負(fù)載的影響 420
15.11 迭代各個(gè)優(yōu)化階段 421
15.12 小結(jié) 424
第16章 SQL Server優(yōu)化檢查列表 425
16.1 數(shù)據(jù)庫設(shè)計(jì) 425
16.1.1 平衡不足和過多的規(guī)范化 426
16.1.2 從實(shí)體完整性約束中得利 427
16.1.3 從域和參照完整性約束中得利 428
16.1.4 采用索引設(shè)計(jì)最佳實(shí)踐 430
16.1.5 避免在存儲(chǔ)過程名稱中使用sp_前綴 431
16.1.6 最小化觸發(fā)器的使用 431
16.2 查詢?cè)O(shè)計(jì) 432
16.2.1 使用SET NOCOUNT ON命令 432
16.2.2 顯式定義對(duì)象所有者 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í)行計(jì)劃的最佳實(shí)踐 435
16.2.10 采用數(shù)據(jù)庫事務(wù)最佳實(shí)踐 436
16.2.11 消除或減少數(shù)據(jù)庫游標(biāo)開銷 437
16.3 配置設(shè)置 437
16.3.1 Affinity Mask 437
16.3.2 內(nèi)存配置選項(xiàng) 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)計(jì)最新 440
16.4.2 保持最小數(shù)量的索引碎片數(shù)量 441
16.4.3 循環(huán)使用SQL錯(cuò)誤日志文件 441
16.4.4 避免像AUTO_CLOSE或AUTO_SHRINK這樣的自動(dòng)化數(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