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

第1章 SQL查詢性能調整 1
1.1 性能調整過程 2
1.1.1 核心過程 2
1.1.2 迭代過程 4
1.2 性能vs.價格 7
1.2.1 性能目標 7
1.2.2 “足夠好”的調整 7
1.3 性能基線 8
1.4 工作的重點 9
1.5 SQL Server性能殺手 10
1.5.1 低質量的索引 10
1.5.2 不精確的統(tǒng)計 11
1.5.3 過多的阻塞和死鎖 11
1.5.4 不基于數據集的操作 11
1.5.5 低質量的查詢設計 12
1.5.6 低質量的數據庫設計 12
1.5.7 過多的碎片 12
1.5.8 不可重用的執(zhí)行計劃 13
1.5.9 低質量的執(zhí)行計劃 13
1.5.10 頻繁重編譯計劃 13
1.5.11 游標的錯誤使用 13
1.5.12 錯誤配置數據庫日志 14
1.5.13 過多使用或者錯誤配置tempdb 14
1.6 小結 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 內存瓶頸分析 19
2.4.1 SQL Server內存管理 20
2.4.2 Available Bytes 23
2.4.3 Pages/sec和Page Faults/sec計數器 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 內存瓶頸解決方案 25
2.5.1 優(yōu)化應用程序工作負載 26
2.5.2 為SQL Server分配更多內存 27
2.5.3 增加系統(tǒng)內存 27
2.5.4 更換32位處理器為64位處理器 27
2.5.5 啟用3GB進程空間 28
2.5.6 在32位SQL Server中使用4GB以上內存 28
2.6 磁盤瓶頸分析 29
2.6.1 磁盤計數器 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)化應用程序工作負載 33
2.7.2 使用更快的磁盤驅動器 33
2.7.3 使用一個RAID陣列 33
2.7.4 使用SAN系統(tǒng) 35
2.7.5 恰當地對齊磁盤 35
2.7.6 使用電池后備的控制器緩存 36
2.7.7 添加系統(tǒng)內存 36
2.7.8 創(chuàng)建多個文件和文件組 36
2.7.9 將表和索引放在不同的磁盤上 39
2.7.10 將日志文件保存到獨立的物理磁盤 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)化應用程序工作負載 43
2.9.2 消除過多的編譯/重編譯 43
2.9.3 使用更多或更快的處理器 44
2.9.4 使用大的二級（L2）/三級（L3）緩存 44
2.9.5 運行更高效的控制器/驅動程序 44
2.9.6 不運行不必要的軟件 45
2.10 網絡瓶頸分析 45
2.10.1 Bytes Total/sec 45
2.10.2 % Net Utilization 46
2.11 網絡瓶頸解決方案 46
2.11.1 優(yōu)化應用程序工作負載 46
2.11.2 增加網絡適配器 47
2.11.3 節(jié)制和避免中斷 47
2.12 SQL Server總體性能 47
2.12.1 丟失索引 48
2.12.2 數據庫阻塞 49
2.12.3 不可重用的執(zhí)行計劃 50
2.12.4 總體表現 50
2.13 創(chuàng)建一個基線 51
2.13.1 創(chuàng)建性能計數器的一個可重用列表 51
2.13.2 使用性能計數器列表創(chuàng)建一個計數器日志 54
2.13.3 最小化性能監(jiān)視器開銷 55
2.14 以基線為標準的系統(tǒng)狀態(tài)分析 56
2.15 小結 57
第3章 SQL查詢性能分析 58
3.1 SQL Profiler工具 58
3.1.1 Profiler跟蹤 59
3.1.2 事件 60
3.1.3 數據列 62
3.1.4 過濾器 64
3.1.5 跟蹤模板 65
3.1.6 跟蹤數據 65
3.2 跟蹤的自動化 66
3.2.1 使用GUI捕捉跟蹤 66
3.2.2 使用存儲過程捕捉跟蹤 67
3.3 結合跟蹤和性能監(jiān)視器輸出 68
3.4 SQL Profiler建議 69
3.4.1 限制事件和數據列 69
3.4.2 丟棄性能分析所用的啟動事件 70
3.4.3 限制跟蹤輸出大小 70
3.4.4 避免在線數據列排序 71
3.4.5 遠程運行Profiler 71
3.4.6 限制使用某些事件 71
3.5 沒有Profiler情況下的查詢性能度量 71
3.6 開銷較大的查詢 72
3.6.1 識別開銷較大的查詢 73
3.6.2 識別運行緩慢的查詢 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 小結 94
第4章 索引分析 95
4.1 什么是索引 95
4.1.1 索引的好處 97
4.1.2 索引開銷 98
4.2 索引設計建議 100
4.2.1 檢查WHERE子句和連接條件列 100
4.2.2 使用窄索引 102
4.2.3 檢查列的唯一性 103
4.2.4 檢查列數據類型 106
4.2.5 考慮列順序 107
4.2.6 考慮索引類型 109
4.3 聚簇索引 109
4.3.1 堆表 110
4.3.2 與非聚簇索引的關系 110
4.3.3 聚簇索引建議 112
4.4 非聚簇索引 117
4.4.1 非聚簇索引維護 117
4.4.2 定義書簽查找 117
4.4.3 非聚簇索引建議 118
4.5 聚簇索引vs.非聚簇索引 118
4.5.1 聚簇索引相對于非聚簇索引的好處 119
4.5.2 非聚簇索引相對于聚簇索引的好處 120
4.6 高級索引技術 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數據類型列上的索引 136
4.8.4 作為一個查詢處理的CREATE INDEX語句 136
4.8.5 并行索引創(chuàng)建 136
4.8.6 在線索引創(chuàng)建 137
4.8.7 考慮數據庫引擎調整顧問 137
4.9 小結 137
第5章 數據庫引擎調整顧問 139
5.1 數據庫引擎調整顧問機制 139
5.2 數據庫引擎調整顧問實例 143
5.2.1 調整一個查詢 143
5.2.2 調整一個跟蹤工作負載 146
5.3 數據庫引擎調整顧問的局限性 148
5.4 小結 149
第6章 書簽查找分析 150
6.1 書簽查找的目的 150
6.2 書簽查找的缺點 152
6.3 分析書簽查找的起因 153
6.4 解決書簽查找 155
6.4.1 使用一個聚簇索引 155
6.4.2 使用一個覆蓋索引 155
6.4.3 使用索引連接 158
6.5 小結 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)計的缺點 164
7.3 在非索引列上的統(tǒng)計 165
7.3.1 在非索引列上統(tǒng)計的好處 166
7.3.2 丟失非索引列上的統(tǒng)計的缺點 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)計維護 176
7.5.1 自動維護 177
7.5.2 人工維護 179
7.5.3 統(tǒng)計維護狀態(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)計的采樣數量 189
7.8 小結 190
第8章 碎片分析 191
8.1 碎片的成因 191
8.1.1 UPDATE語句引起的頁面分割 193
8.1.2 INSERT語句引起的頁面分割 196
8.2 碎片開銷 197
8.3 分析碎片數量 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 自動維護 212
8.7 小結 217
第9章 執(zhí)行計劃緩沖分析 218
9.1 執(zhí)行計劃生成 218
9.1.1 解析器 219
9.1.2 代數化器 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 即席工作負載 230
9.6.2 預定義工作負載 231
9.6.3 即席工作負載的計劃可重用性 231
9.6.4 預定義工作負載的計劃可重用性 239
9.7 查詢計劃Hash和查詢Hash 248
9.8 執(zhí)行計劃緩沖建議 251
9.8.1 明確地參數化查詢的可變部分 252
9.8.2 使用存儲過程實現業(yè)務功能 252
9.8.3 使用sp_executesql編程以避免存儲過程維護 252
9.8.4 實現準備/執(zhí)行模式以避免重傳查詢字符串 253
9.8.5 避免即席查詢 253
9.8.6 對于動態(tài)查詢sp_executesql優(yōu)于EXECUTE 253
9.8.7 小心地參數化查詢的可變部分 254
9.8.8 不要允許查詢中對象的隱含解析 254
9.9 小結 254
第10章 存儲過程重編譯 256
10.1 重編譯的好處和缺點 256
10.2 確認導致重編譯的語句 258
10.3 分析重編譯起因 260
10.3.1 架構或綁定變化 261
10.3.2 統(tǒng)計變化 261
10.3.3 延遲對象解析 264
10.3.4 SET選項變化 266
10.3.5 執(zhí)行計劃老化 266
10.3.6 顯式調用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 小結 281
第11章 查詢設計分析 282
11.1 查詢設計建議 282
11.2 在小結果集上操作 283
11.2.1 限制選擇列表中的列數 283
11.2.2 使用高選擇性的WHERE子句 284
11.3 有效地使用索引 284
11.3.1 避免不可參數化的搜索條件 285
11.3.2 避免WHERE子句列上的算術運算符 289
11.3.3 避免WHERE子句列上的函數 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 避免數據類型轉換 301
11.6.2 使用EXISTS代替COUNT（*）驗證數據存在 303
11.6.3 使用UNION ALL代替UNION 304
11.6.4 為聚合和排序操作使用索引 305
11.6.5 避免在批查詢中的局部變量 306
11.6.6 小心地命名存儲過程 309
11.7 減少網絡傳輸數量 311
11.7.1 同時執(zhí)行多個查詢 311
11.7.2 使用SET NOCOUNT 311
11.8 降低事務開銷 312
11.8.1 減少日志開銷 312
11.8.2 減少鎖開銷 314
11.9 小結 315
第12章 阻塞分析 316
12.1 阻塞基礎知識 316
12.2 理解阻塞 317
12.2.1 原子性 317
12.2.2 一致性 320
12.2.3 隔離性 320
12.2.4 持久性 321
12.3 數據庫鎖 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 可重復讀 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跟蹤和被阻塞進程報告事件 349
12.7 阻塞解決方案 351
12.7.1 優(yōu)化查詢 352
12.7.2 降低隔離級別 352
12.7.3 分區(qū)爭用的數據 353
12.7.4 爭用數據上的覆蓋索引 354
12.8 減少阻塞的建議 354
12.9 自動化偵測和收集阻塞信息 355
12.10 小結 359
第13章 死鎖分析 360
13.1 死鎖基礎知識 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 減少被訪問資源的數量 369
13.4.3 最小化鎖的爭用 369
13.5 小結 370
第14章 游標開銷分析 372
14.1 游標基礎知識 372
14.1.1 游標位置 373
14.1.2 游標并發(fā)性 374
14.1.3 游標類型 376
14.2 游標開銷比較 378
14.2.1 游標位置的開銷比較 378
14.2.2 游標并發(fā)性上的開銷比較 380
14.2.3 在游標類型上的開銷比較 381
14.3 默認結果集 383
14.3.1 好處 384
14.3.2 缺點 384
14.4 分析SQL Server游標開銷 386
14.5 游標建議 390
14.6 小結 392
第15章 數據庫工作負載優(yōu)化 393
15.1 工作負載優(yōu)化基礎知識 393
15.2 工作負載優(yōu)化步驟 394
15.3 捕捉工作負載 397
15.4 分析工作負載 399
15.5 識別開銷最大的查詢 400
15.6 確定開銷最大的查詢的基線資源使用 402
15.6.1 總體資源使用 402
15.6.2 詳細資源使用 402
15.7 分析和優(yōu)化外部因素 405
15.7.1 分析應用程序使用的批級別選項 405
15.7.2 分析統(tǒng)計有效性 406
15.7.3 分析碎片整理需求 406
15.8 分析開銷最大的查詢的內部行為 410
15.8.1 分析查詢執(zhí)行計劃 410
15.8.2 識別執(zhí)行計劃中開銷較大的步驟 412
15.8.3 分析處理策略的效率 412
15.9 優(yōu)化代價最大的查詢 412
15.9.1 修改現有索引 413
15.9.2 分析連接提示的應用 415
15.9.3 避免聚簇索引掃描操作 417
15.9.4 修改過程 418
15.10 分析對數據庫工作負載的影響 420
15.11 迭代各個優(yōu)化階段 421
15.12 小結 424
第16章 SQL Server優(yōu)化檢查列表 425
16.1 數據庫設計 425
16.1.1 平衡不足和過多的規(guī)范化 426
16.1.2 從實體完整性約束中得利 427
16.1.3 從域和參照完整性約束中得利 428
16.1.4 采用索引設計最佳實踐 430
16.1.5 避免在存儲過程名稱中使用sp_前綴 431
16.1.6 最小化觸發(fā)器的使用 431
16.2 查詢設計 432
16.2.1 使用SET NOCOUNT ON命令 432
16.2.2 顯式定義對象所有者 432
16.2.3 避免不可參數化的搜索條件 432
16.2.4 避免WHERE子句列上的算術運算符 433
16.2.5 避免優(yōu)化器提示 434
16.2.6 遠離嵌套視圖 434
16.2.7 確保沒有隱含的數據類型轉換 435
16.2.8 最小化日志開銷 435
16.2.9 采用重用執(zhí)行計劃的最佳實踐 435
16.2.10 采用數據庫事務最佳實踐 436
16.2.11 消除或減少數據庫游標開銷 437
16.3 配置設置 437
16.3.1 Affinity Mask 437
16.3.2 內存配置選項 437
16.3.3 并行性開銷閾值 438
16.3.4 最大并行度 438
16.3.5 優(yōu)化即席工作負載 438
16.3.6 查詢調控器開銷限制 439
16.3.7 填充因子（%） 439
16.3.8 被阻塞過程閾值 439
16.3.9 數據庫文件布局 439
16.3.10 數據庫壓縮 440
16.4 數據庫管理 440
16.4.1 保持統(tǒng)計最新 440
16.4.2 保持最小數量的索引碎片數量 441
16.4.3 循環(huán)使用SQL錯誤日志文件 441
16.4.4 避免像AUTO_CLOSE或AUTO_SHRINK這樣的自動化數據庫功能 441
16.4.5 最小化SQL跟蹤開銷 442
16.5 數據庫備份 442
16.5.1 增量和事務日志備份頻率 442
16.5.2 備份分布 443
16.5.3 備份壓縮 444
16.6 小結 444