SQL Server 2008查詢性能優化（簡體書）

本書通過大量實例，詳細介紹了SQL Server數據庫系統優化的各種方法和技巧。內容涵蓋了數據庫應用系統中各種性能瓶頸的表現形式及其發生的根源和解決方法，從硬件瓶頸到查詢、索引設計以及數據庫管理等，貫穿了數據庫系統知識的各個方面。最后以一個實際的工作負載將所有技巧聯系起來，并且提供了“寶典”式的最佳實踐列表。 本書適合于關心數據庫應用系統性能的開發人員和數據庫管理人員閱讀。通過閱讀本書，不僅可以學習到數據庫性能管理的許多知識和技巧，還有助于養成良好的編程習慣，為實現高性能的數據庫應用系統打下基礎。

作者：（美國）Grant Fritchey Sajal Dam　譯者：姚軍

目　錄 第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　不精確的統計　11 1.5.3　過多的阻塞和死鎖　11 1.5.4　不基於數據集的操作　11 1.5.5　低質量的查詢設計　12 1.5.6　低質量的數據庫設計　12 1.5.7　過多的碎片　12 1.5.8　不可重用的執行計劃　13 1.5.9　低質量的執行計劃　13 1.5.10　頻繁重編譯計劃　13 1.5.11　游標的錯誤使用　13 1.5.12　錯誤配置數據庫日志　14 1.5.13　過多使用或者錯誤配置tempdb　14 1.6　小結　14 第2章　系統性能分析　15 2.1　性能監視器工具　15 2.2　動態管理視圖　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　優化應用程序工作負載　26 2.5.2　為SQL Server分配更多內存　27 2.5.3　增加系統內存　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　優化應用程序工作負載　33 2.7.2　使用更快的磁盤驅動器　33 2.7.3　使用一個RAID陣列　33 2.7.4　使用SAN系統　35 2.7.5　恰當地對齊磁盤　35 2.7.6　使用電池后備的控制器緩存　36 2.7.7　添加系統內存　36 2.7.8　創建多個文件和文件組　36 2.7.9　將表和索引放在不同的磁盤上　39 2.7.10　將日志文件保存到獨立的物理磁盤　39 2.7.11　表的分區　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　優化應用程序工作負載　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　優化應用程序工作負載　46 2.11.2　增加網絡適配器　47 2.11.3　節制和避免中斷　47 2.12　SQL Server總體性能　47 2.12.1　丟失索引　48 2.12.2　數據庫阻塞　49 2.12.3　不可重用的執行計劃　50 2.12.4　總體表現　50 2.13　創建一個基線　51 2.13.1　創建性能計數器的一個可重用列表　51 2.13.2　使用性能計數器列表創建一個計數器日志　54 2.13.3　最小化性能監視器開銷　55 2.14　以基線為標準的系統狀態分析　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　結合跟蹤和性能監視器輸出　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　執行計劃　78 3.7.1　分析查詢執行計劃　80 3.7.2　識別執行計劃中開銷較大的步驟　82 3.7.3　分析索引有效性　83 3.7.4　分析連接有效性　84 3.7.5　實際執行計劃vs.估算執行計劃　88 3.7.6　計劃緩存　89 3.8　查詢開銷　90 3.8.1　客戶統計　90 3.8.2　執行時間　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　並行索引創建　136 4.8.6　在線索引創建　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章　統計分析　161 7.1　統計在查詢優化中的角色　161 7.2　索引列上的統計　162 7.2.1　更新統計的好處　162 7.2.2　過時統計的缺點　164 7.3　在非索引列上的統計　165 7.3.1　在非索引列上統計的好處　166 7.3.2　丟失非索引列上的統計的缺點　169 7.4　分析統計　172 7.4.1　密度　174 7.4.2　多列索引上的統計　174 7.4.3　過濾索引上的統計　175 7.5　統計維護　176 7.5.1　自動維護　177 7.5.2　人工維護　179 7.5.3　統計維護狀態　181 7.6　為查詢分析統計的有效性　182 7.6.1　解決丟失統計問題　182 7.6.2　解決過時統計問題　184 7.7　建議　186 7.7.1　統計的向后兼容性　186 7.7.2　自動創建統計　186 7.7.3　自動更新統計　187 7.7.4　自動異步更新統計　189 7.7.5　收集統計的采樣數量　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　執行ALTER INDEX REBUILD語句　205 8.4.4　執行ALTER INDEX REORGANIZE語句　207 8.5　填充因子的重要性　209 8.6　自動維護　212 8.7　小結　217 第9章　執行計劃緩沖分析　218 9.1　執行計劃生成　218 9.1.1　解析器　219 9.1.2　代數化器　220 9.1.3　優化　221 9.2　執行計劃緩沖　227 9.3　執行計劃組件　227 9.3.1　查詢計劃　227 9.3.2　執行上下文　227 9.4　執行計劃的老化　228 9.5　分析執行計劃緩沖　228 9.6　執行計劃重用　229 9.6.1　即席工作負載　230 9.6.2　預定義工作負載　231 9.6.3　即席工作負載的計劃可重用性　231 9.6.4　預定義工作負載的計劃可重用性　239 9.7　查詢計劃Hash和查詢Hash　248 9.8　執行計劃緩沖建議　251 9.8.1　明確地參數化查詢的可變部分　252 9.8.2　使用存儲過程實現業務功能　252 9.8.3　使用sp_executesql編程以避免存儲過程維護　252 9.8.4　實現準備/執行模式以避免重傳查詢字符串　253 9.8.5　避免即席查詢　253 9.8.6　對于動態查詢sp_executesql優于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　統計變化　261 10.3.3　延遲對象解析　264 10.3.4　SET選項變化　266 10.3.5　執行計劃老化　266 10.3.6　顯式調用sp_recompile　267 10.3.7　顯式使用RECOMPILE子句　268 10.4　避免重編譯　269 10.4.1　不要交替使用DDL和DML語句　270 10.4.2　避免統計變化引起的重編譯　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　避免優化器提示　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　同時執行多個查詢　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　優化查詢　352 12.7.2　降低隔離級別　352 12.7.3　分區爭用的數據　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　游標并發性　374 14.1.3　游標類型　376 14.2　游標開銷比較　378 14.2.1　游標位置的開銷比較　378 14.2.2　游標并發性上的開銷比較　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章　數據庫工作負載優化　393 15.1　工作負載優化基礎知識　393 15.2　工作負載優化步驟　394 15.3　捕捉工作負載　397 15.4　分析工作負載　399 15.5　識別開銷最大的查詢　400 15.6　確定開銷最大的查詢的基線資源使用　402 15.6.1　總體資源使用　402 15.6.2　詳細資源使用　402 15.7　分析和優化外部因素　405 15.7.1　分析應用程序使用的批級別選項　405 15.7.2　分析統計有效性　406 15.7.3　分析碎片整理需求　406 15.8　分析開銷最大的查詢的內部行為　410 15.8.1　分析查詢執行計劃　410 15.8.2　識別執行計劃中開銷較大的步驟　412 15.8.3　分析處理策略的效率　412 15.9　優化代價最大的查詢　412 15.9.1　修改現有索引　413 15.9.2　分析連接提示的應用　415 15.9.3　避免聚簇索引掃描操作　417 15.9.4　修改過程　418 15.10　分析對數據庫工作負載的影響　420 15.11　迭代各個優化階段　421 15.12　小結　424 第16章　SQL Server優化檢查列表　425 16.1　數據庫設計　425 16.1.1　平衡不足和過多的規範化　426 16.1.2　從實體完整性約束中得利　427 16.1.3　從域和參照完整性約束中得利　428 16.1.4　采用索引設計最佳實踐　430 16.1.5　避免在存儲過程名稱中使用sp_前綴　431 16.1.6　最小化觸發器的使用　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　避免優化器提示　434 16.2.6　遠離嵌套視圖　434 16.2.7　確保沒有隱含的數據類型轉換　435 16.2.8　最小化日志開銷　435 16.2.9　采用重用執行計劃的最佳實踐　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　優化即席工作負載　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　保持統計最新　440 16.4.2　保持最小數量的索引碎片數量　441 16.4.3　循環使用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