簡介

　　很多客戶偶爾會遇到sql server 數據庫性能下降。原因可能涉及從不良好的數據庫設計到不正確的負載配置。作為一個管理員，你應該預先阻止或最小化問題，并當問題發生時，診斷原因并盡可能的做出正確的操作來解決問題。這片白皮書所述的問題通常來源于microsoft® corporation 的customer support service（css or pss）部門所遇到的，因為將所有可能的問題都詳盡的分析是不合實際的。我們提供了按部就班的指導，通過使用可用的工具例如sql server profiler，system monitor和在sql server 2005中新的dynamic management view來為一般的性能問題診斷和排錯。

　　目標

　　這篇文章的主要目標是提供一套常規的方法通過使用公開的工具在一般的客戶場景中診斷和排錯sql server性能問題。

　　sql server 2005在用戶支持上有了很大的提高。內核層（sql-os）被重新架構過，內部結構和統計數據通過動態管理視圖(dmvs)暴露為關系型行集。sql server 2000通過像sysprocesses這樣的系統表暴露一些信息，但是有時你需要將sql server進程內存映射為物理文件并從中抽取內部結構的相關信息。這里有2個主要的問題。第一，客戶不能總是提供物理映射文件，因為文件的尺寸原因，并且這個過程很耗時。第二，這將花費更長的時間診斷問題，因為文件必須傳回microsoft corporation來分析。

　　這帶給我們本文的第二個目標，展示dmvs的優點。dmvs通過除去大多數情況下需要的生成和分析物理映射步驟可以加速調試的過程。本文盡可能的提供了和sql server 2000中同樣問題的比較。dmvs提供為獲取關鍵系統信息的簡單而熟悉的界面。這些信息也可以用于監視目的，警告管理員潛在的問題。或者也可以被周期性的收集為以后的分析所用。

　　方法

　　這里有很多降低sql server速度的原因。我們使用下列3個主要癥狀來診斷問題。

　　◆資源瓶頸: cpu，內存，和i/o瓶頸是在本文中主要涉及的。這里我們不考慮網絡因素。對每種資源瓶頸，我們會描述如何識別問題并闡述可能的原因。例如，內存瓶頸可以導致過多的分頁，最后影響性能。

　　◆tempdb 瓶頸:因為每個sql server 實例只有一個tempdb，這可以產生性能和磁盤空間的瓶頸。不好的應用程序在過多的ddl和dml操作會使tempdb過載。這導致其他在這臺服務器上運行的不相關的應用程序運行緩慢或失敗。

　　◆緩慢運行的用戶查詢:現有的查詢性能下降或新的查詢顯示比預期時間更長。這可能有很多原因。例如：

　　◆改變統計信息可以導致現有查詢的較差的查詢計劃。

　　◆制表掃描，降低查詢性能。

　　◆即使資源利用正常由于阻塞也可以導致應用程序運行緩慢。

　　過多的阻塞可能是由于不良的應用程序設計或架構設計或者是選擇了錯誤的事務隔離級別的原因。

　　這些癥狀的原因不需要每個都獨立出來。不良的查詢計劃選擇可以使系統資源加重并導致整體性能的下降。所以，如果大表缺失的有用的索引，或查詢優化器沒有選擇它，這樣不僅導致查詢緩慢，也會導致將不需要數據頁讀取到內存（buffer pool）中在緩存中存儲，這樣會加重i/o子系統的壓力。同樣的，頻繁運行查詢的重編譯可以導致cpu的壓力。

　　資源瓶頸

　　接下來的部分討論了cpu，內存和i/o子系統資源及這些瓶頸如何產生。（網絡問題不在本文的討論范圍）每個資源瓶頸，我們描述了如何識別問題并找到原因。例如，一個內存瓶頸可以導致過渡分頁，將會影響性能。

　　在決定你遇到資源瓶頸前，你需要了解在普通環境下資源是如何使用的。你可以使用在這片白皮書中所列出的方法收集有關資源使用的基線信息（即使你沒有遇到性能問題）。

　　你也許找到問題是一個資源運行到設計容量并且sql server當前的配置不能支持這種負載。為了解決這問題，你也許需要添加處理能力，內存或增加i/o的帶寬或網絡通道。但是，在你進行下一步之前，理解這種資源瓶頸的通常原因是非常重要的。有一些解決方案不需要添加額外資源，例如重新配置。

　　解決資源瓶頸的工具

　　下列工具是在解決特殊性能瓶頸時經常使用的工具。

　　◆系統監視器(perfmon)：這是windows所帶的一個工具。更多信息，請見系統監視器文檔。

　　◆sql server profiler：在sql server 2005程序組中的performance tools組中可以看到sql server profiler。

　　◆dbcc 命令：詳細信息請見sql server聯機叢書中和附錄a。

　　◆dmvs: 詳細信息請見sql server聯機叢書。

　　cpu 瓶頸

　　當沒有額外負載，突然發生的cpu瓶頸通常由于沒有查詢計劃，不良的配置或設計因素和不足的硬件資源所引起。在購買更快或更多處理器前，你需要首先確定cpu的最大處理能力帶寬并查看是否他們都在使用中。

　　系統監視器一般是確定cpu資源的最好工具。你應該查看processor:% processor time計數器是否偏高；該計數器值超過80%一般被認為是瓶頸。你也可以使用sys.dm_os_schedulers視圖來監視是否正在運行的任務不是0。非0的值預示有任務需要等待時間切片來運行；這個數值高表明一個cpu瓶頸的征兆。你可以用下列查詢列出所有的調度器并產看等待運行的任務數量。

　　下列查詢給你一個查看當前緩存中最耗費cpu的批或過程高級別視圖。查詢根據具有相同plan handle（意味著屬于同一個批或過程）的語句聚合cpu的調用。如果給出的plan handle對應多個語句，你將不得不繼續找到在整個cpu使用中最占用資源的查詢。

　　本節剩下的部分將討論一些其他的在sql server 中cpu敏感的操作，也有有效的檢查和解決這些問題的方法。

　　過多的編譯和重編譯

　　當批或遠端過程調用(rpc)被提交到sql server，在開始執行前，服務器檢查查詢計劃的有效性和正確性。如果這些檢查中的一個失敗時，批將被再次編譯聲稱不同的查詢計劃。這種編譯就是所說的重編譯。當服務器確認當底層數據改變時有更優化的查詢計劃時，重編譯確認正確性再完成編譯。編譯是cpu敏感的，因此過多的重編譯將導致在系統中的cpu性能問題。

　　在sql server 2000中，當sql server重編譯一個存儲過程，整個存儲過程都被重編譯，不僅是觸發重編譯的語句。sql server 2005引入了語句級的存儲過程的重編譯。當sql server 2005重編譯存儲過程，只有導致重編譯的語句被編譯-而不是整個存儲過程。這減少了cpu的負載并減少了對例如compile鎖的資源爭用。重編譯可以有多種原因出發，例如：

　　◆架構變化

　　◆統計變化

　　◆延期編譯

　　◆set屬性改變

　　◆臨時表改變

　　◆存儲過程創建時使用了recomplie查詢提示或使用了option （recompile）。

　　檢測

　　你可以使用系統監視器（perfmon）或sql trace(sql server profiler)來檢測過多的編譯和重編譯。

　　系統監視器(perfmon)

　　sql statistics對象提供了監視重編譯的計數器和發送到sql server實例的請求類型。你必須監視查詢編譯和重編譯的數量以及相關聯的接受的批的數量來找出是否這個編譯是最耗費cpu資源。理想環境下，sql recompilations/sec和batch request/sec的比值應該很低，除非用戶提交了大量的單獨查詢。

　　下列顯示了關鍵的數據計數器。

　　◆sql server: sql statistics: batch requests/sec

　　◆sql server: sql statistics: sql compilations/sec

　　◆sql server: sql statistics: sql recompilations/sec

　　更多信息請看sql server聯機叢書中的 “sql statistics object”

　　sql 跟蹤

　　如果perfmon計數器顯出了很高的重編譯數量，編譯將在sql server中占用很多的cpu資源。我們將需要查看profiler 跟蹤并從中找到找到被重編譯的存儲過程。sql server profiler跟蹤給出我們重編譯原因的信息。你可以使用下列事件。

　　sp:recompile和sql:stmtrecompile事件類指出了哪個存儲過程和語句被重編譯。當你編譯一個存儲過程，一個事件為這個存儲過程生成，其中每條語句將被編譯。然而，當存儲過程重編譯時，只有導致重編譯的語句被重編譯（在sql server 2000中將是整個存儲過程）。下面列出了sp:recompile事件類更多重要的數據列。特別是eventsubclass數據列決定重編譯的原因。sp:recompile當存儲過程或觸發器被重編譯被觸發一次，但不會被獨立查詢引發。在sql server 2005中，監視sql:stmtrecompiles也非常有用，該事件類在所有類型的重編譯中都會被觸發，包括批，獨立查詢，存儲過程和觸發器。如下是我們關系的事件中關鍵的數據列：

　　◆eventclass

　　◆eventsubclass

　　◆objectid (represents stored procedure that contains this statement)

　　◆spid

　　◆starttime

　　◆sqlhandle

　　◆textdata

　　更多信息，請見sql server 聯機叢書中“sql:stmtrecompile event class”。

　　如果你有保存的跟蹤文件，你可以使用下列查詢查看所有捕捉的重編譯事件。

　　事件類37是 sp:recompile， 75 是 cursorrecompile， 166是sql:stmtrecompile

　　你可以通過sqlhandle和objectid列或其他列將這個查詢的結果分組，也可以查看是否最多的重編譯類型是存儲過程或其他原因（例如set選項改變等）。

　　showplan xml for query compile.

　　showplan xml for query compile事件發生于microsoft sql server編譯或重編譯一段sql語句時。該事件有關于語句編譯或重編譯的信息。信息包括查詢計劃和過程的對象id。捕獲這些事件是有性能開銷的，因為它捕獲了每次編譯或重編譯。如果你在系統監視其中看到很高的sql compilations/sec計數器值，你應該監視這個事件。通過這些信息，你可以看到那條語句被頻繁的重編譯。你可以使用這些信息改變這些語句的參數。這將影響重編譯的數量。

　　dmvs.

　　當你使用sys.dm_exec_query_optimizer_info dmv，你可以得到sql server花費在優化的時間。如果獲取了這個dmv的2個快照，你可以得到在給定的時間段內花費在查詢優化的時間。

　　特別是查看elapsed time，該時間由于優化而產生。因為優化過程的時間基本上就是用戶優化操作的cpu時間（因為優化處理是cpu時間的主要部分），你可以得到一個好的度量，找到那段編譯時間占用了大量的cpu時間。

　　其他包含有用信息的dmv有：

　　sys.dm_exec_query_stats.

　　你希望查看的數據列有：

　　◆sql_handle

　　◆total worker time

　　◆plan generation number

　　◆statement start offset

　　更多信息請查看sql server 聯機叢書相關主題

　　sys.dm_exec_query_stats.

　　特別是plan_generation_num預示了查詢編譯時的次數。下面是示例給你展示了被重編譯次數最多的25個存儲過程。

　　更多信息請見batch compilation, recompilation, and plan caching issues in sql server 2005

　　解決

　　如果你監測到過多的編譯/重編譯，考慮下列選項。

　　◆如果重編譯因為set選項改變而發生，使用sql server profiler確定哪個set選項被改編。避免在存儲過程中改變set選項。如果改變最好在連接級別設置。確認在該連接的生存周期內不要改變set選項。

　　在臨時表上重編譯的閥值比在普通表上的低。在臨時表上的重編譯時由于統計改變而引起，你可以降臨時表改為使用表變量。表變量的改變不會引起重編譯。這種方法的確定是查詢優化器不識別表變量，因為統計不會被創建或維護表變量。這將導致沒有查詢計劃。你可以測試不同的選項，并選擇最好的方法。另外一個選項時使用keep plan查詢提示。設置臨時表的這個閥值與使用永久表相同。

　　eventsubclass 列預示了在臨時表上的”statistics changed”的操作。

　　◆為避免由于改變統計而產生的重編譯（例如，因為數據統計導致計劃不理想），特別是keepfixed plan查詢提示。根據設置的影響，重編譯可以僅因為相關正確的原因（例如，當底層表結構改變導致計劃不再適用），而不根據統計的變化。如果語句引用的表的架構改變時或者表是被標記為sp_recompile的存儲過程，重編譯將發生。

　　◆關閉自動更新索引統計和表或視圖的統計，防止由于對象的統計改變而產生的重編譯。注意，通過使用這種方法關閉”auto-stats”特性不是一個好的想法。這是因為查詢優化器不再為在這些對象上的數據改變而敏感，將導致不良的查詢計劃。使用這種方法僅在嘗試了所有其他選擇之后，做為最后的手段。

　　◆批應該盡量使用對象全名（例如，dbo.table1）避免重編譯并避免不明確的對象。

　　◆為避免由于延期編譯導致的重編譯，不要混雜dml和ddl或從條件結構創建ddl，例如if語句。

　　◆運行database engine tuning advisor(dta)查看改變索引是否可以改善編譯時間和查詢的執行時間。

　　◆檢查是否存儲過程通過with recompile選項創建或使用了recompile查詢提示。如果過程通過with recompile選項創建，在sql server 2005中，如果在過程中特殊的語句需要被重編譯，我們可以利用語句級的recompile提示。這將避免在每次執行的時候對整個過程重編譯，而同時允許個別語句被編譯。更多有關recompile提示的信息，請查看sql server聯機叢書。

　　效率低的查詢計劃

　　當為一個查詢生成查詢計劃時，sql server查詢優化器嘗試選擇一個計劃為查詢提供最快的響應時間。注意最快的查詢時間并不意味最小的i/o開銷，也不意味使用最少的cpu資源-它會在各種資源中平衡。

　　某些操作類型比其他操作對cpu更敏感。hash操作和sort操作掃描他們各自的輸入數據。使用掃描向前讀取（prefetch）時，在需要操作頁面前，頁面幾乎都在緩存中。因此可以減少或消除物理i/o操作。這使這些操作的類型將不被物理i/o所限制。與之相比，嵌套循環連接有很多索引查找，如果索引查找使用很多不同的表以至于頁面不適合緩存的大小，將導致生成i/o負載。

　　最有意義的輸入優化用于評估為每中操作生成不同查詢計劃開銷的評估，你可以在showplan（estimaterows和estimateexecution屬性）中看到結果。沒有精確的評估，用于優化的主輸入是有缺陷的。

　　為獲取sql server優化器如何使用統計的詳細信息，請查看 statistics used by the query optimizer in microsoft sql server 2005 。該白皮書討論了優化器如何使用統計，維護和更新統計的最佳實踐，以及一些常見的查詢設計問題。

　　檢測

　　低效率的查詢計劃通常可以被檢測出來。低效率的查詢計劃可以導致增加cpu的消耗。

　　查詢sys.dm_exec_query_stats是確定哪個查詢累計使用cpu時間最多的有效方法。

　　還可以選擇，也可以查詢sys.dm_exec_cached_plans并通過使用過濾器查找可疑的類似于‘%hash match%’, ‘%sort%’這樣cpu敏感的各種操作。

　　解決

　　如果你監測到效率低的查詢計劃，考慮下列選項。

　　◆使用database engine tuning advisor調節查詢，查看是否生成對修改索引的建議

　　◆檢查有問題的評估。

　　編寫的查詢中使用的更有限制性的where從句是否合適？無限制條件的查詢是資源敏感的。

　　在查詢中涉及的表上運行update statistics，檢查是否還有這種問題。

　　是否查詢使用的構造導致優化器不能精確的評估？

　　考慮是否可以將查詢修改為其他的方法，避免這種問題。

　　◆如果不能修改架構或查詢，sql server 2005有一個新的查詢計劃特性，允許你將指定查詢提示添加到滿足某種文本的查詢中。這可以用在獨立查詢中，也可以用在存儲過程內。例如option(optimize for)這樣的提示允許你影響評估而忘記所有列出的潛在計劃。其他的提示，類似option(force order)或 opiton(use plan)允許你改變控制查詢計劃的程度。

　　內部查詢的并行

　　當為一個查詢生成執行計劃時，sql server優化器嘗試為該查詢選擇最快的相應計劃。如果查詢的開銷超過了在cost threshold for parallelism選項中指定的值，并行不會被禁用，優化器嘗試生成一個可以用于并行的計劃。并行查詢計劃使用多線程處理查詢，每個線程分布在可用的cpu上并同時利用每個cpu的時間資源。最大的并行度可以通過服務器上的max degree of parallelism選項或每個查詢使用option(maxdop)提示限制。

　　用于執行實際并行度（dop）的結果——度量有多少線程將在給定的操作上并行——是知道執行時才能確定。在執行查詢前，sql server 2005決定有多少個調度器未充分利用并為查詢選擇dop來充分利用剩余的調度器。一旦一個dop被選擇了，直到完成，查詢將使用這個選擇的并行度來運行。并行查詢的使用時cpu有一些偏高，但是它在elapsed time上的時間很短。如果沒有其他瓶頸，類似于物理i/o等待，并行計劃將會使用所有處理器的100%資源。

　　查詢開始執行后，一個關鍵的因素（系統有多空閑）可以導致運行并行計劃的改變。例如，如果查詢運行在空閑時間，服務器可以選擇使用并行計劃并使用dop為4，在4個不同的處理器上產生線程。一旦這種線程開始執行，現存的連接可以提交其他需要大量cpu的查詢。在這種情況，所有不同的線程將共享可用的cpu的時間切片，導致更高的查詢持續時間。

　　通過并行計劃運行不是一定是不好的，并行可以為查詢提供最快的響應時間。然而，給定查詢的響應時間必須與整體的吞吐量和系統其他查詢的響應進行衡量。并行查詢一般最適合批處理和決策支持系統，而不適合一個事務處理環境。

　　檢測

　　內部查詢的并行問題可以通過下列方法檢測。

　　系統監視器(perfmon)

　　考慮sql server:sql statistics – batch requests/sec 計數器，并查看sql server聯機叢書中的“sql statistics object”獲取更多信息。

　　因為在考慮使用并行計劃前，查詢必須評估開銷超過為并行配置設置的開銷閥值（默認被設置為5），服務器每秒處理的批小于運行在并行計劃中的批。運行很多并行查詢的服務器一般配置為較小的每秒批請求數（例如，小于100的值）。

　　dmvs

　　在運行的服務器上，你可以使用下列查詢確認在給定會話中是否可以并行運行任何活動的請求。

　　通過這些信息，查詢的文本可以通過使用sys.dm_exec_sql_text輕松獲取，而查詢計劃可以使用sys.dm_exec_cached_plan獲取。

　　你也可以搜索符合運行在并行的計劃。這可以通過搜索緩存的計劃來查看如果關系操作符有parrallel屬性為非零的值。這些計劃也許可以不運行在平行中，但是他們如果系統不忙，他們也適合這樣做。

　　一般來說，查詢的持續時間長于cpu時間總量，因為一些時間花費在等待資源上例如鎖或物理i/o。查詢使用cpu時間長于持續時間的唯一場景是當查詢運行在并行計劃例如多線程并發使用cpu。注意并不是所有并行查詢將證明這種行為（cpu時間大于持續時間）。

　　解決

　　任何運行在并行計劃的查詢被查詢優化器認為是成本昂貴的，并會超過并行閥值，默認為5（粗略的是在涉及的機器上5秒執行一次）。任何通過上述方法確認的查詢都是以后要調節的候選者。

　　◆使用database engine tuning advisor查看是否任何索引改變，改變索引視圖或分區改變能減少查詢的開銷

　　◆檢查實際值和評估集的重要不同因為評估集在評估查詢開銷中是重要因素。如果找到重要的不同：

　　如果auto create statistics數據庫設置被禁用，確認在showplan輸出的warnings列中沒有missing stats項。

　　嘗試在關閉評估的表上運行update statistics。

　　驗證查詢沒有使用優化器無法精確評估的查詢構造，例如多語句表值函數或clr函數，表值或transact-sql變量比較（參數比較是可以的）。

　　◆評估是否可以使用不同的transact-sql語句或表達式將查詢寫的更有效率

　　拙劣游標使用

　　sql server 2005之前的sql server 版本僅支持在每個連接上有單個活動的操作。一個查詢正在執行或有了結果等待發送到客戶端時將被認為是活動的。在一些情形中，客戶端應用程序也許需要從結果中讀取并向sql server提交其他基于剛剛從結果集中讀取的行的查詢。這在默認的結果集中是不能實現的，因為還有其他等待的結果。一般的解決方法是改變連接屬性是用服務器端游標。

　　當使用服務器端游標，數據庫客戶端軟件(ole db提供者或odbc驅動)顯然會封裝客戶端請求在特殊的擴展存儲過程中，例如sp_cursoropen，sp_cursorfetch等等。這提到了api游標（而不是tsql游標）。當用戶執行查詢，查詢文本通過sp_cursoropen被發送到服務器，請求讀取從sp_cursorfetch指示服務器進發送某些數量的行。通過控制獲取行的數量，可以為odbc驅動或ole db提供者緩存行。這阻止發生服務器等待客戶端都區所有發送的行的情形。因此，服務器可以在這個連接上接受新的請求。

　　一次性打開游標并獲取1行（或少量行）的應用程序能被網絡延時的網絡瓶頸影響，特別是在廣域網（wan）。在有快速網絡并有不同用戶連接時，處理很多游標請求的開銷變得更重要。因為開銷來自于游標位置的變化來適應在結果集上的位置改變，預請求的處理開銷，類似的處理，服務器處理1個請求返回100行必處理100不同請求相同的100行但是每次1行更有效率。

　　檢測

　　你可以使用下列方法為拙劣游標使用排錯。

　　系統監視器(perfmon)

　　通過考慮sql server:cursor manager by type – cursor requests/sec計數器，你可以通過這個性能計數器知道有多少游標在系統中使用。系統還有很高的cpu利用率，因為小量的讀取通常會有每秒數百個游標請求。這里沒有特殊的計數器告訴你關于獲取的緩存大小。

　　dmvs

　　接下來的查詢可以用于測定使用api游標（不是tsql游標）連接獲取一行使用的緩存大小。它對于大的獲取緩存更有效，例如100行。

　　sql 跟蹤

　　使用包括rpc：completed事件類的跟蹤用于搜索sp_cursorfetch語句。第4個參數的值是通過獲取返回的行數。請求返回的最大行數是被指定為與rpc:starting事件類關聯的參數。

　　解決

　　◆確定游標是完成操作的最佳方法或是否基于集合這種更有效的操作是可行的。

　　◆當連接到sql server 2005，考慮使用多活動結果集(mars)

　　◆參考你使用的api文檔決定如何指定游標的獲取緩存大小：

　　odbc - sql_attr_row_array_size

　　ole db – irowset::getnextrows or irowsetlocate::getrowsat

　　內存瓶頸

　　這部分給出了低緩存的條件和對不同內存錯誤診斷方法，可能的原因和排錯方法。

　　背景

　　引用不同的內存資源通過使用簡單的術語內存。但是卻有一些內存資源類型，對于理解和區分特殊的內存資源這是很重要的。

　　虛擬地址空間和物理內存

　　在microsoft windows®，每個進程都有自己的虛擬地址空間(vas)。進程可用所有虛擬地址有vas的大小決定。vas的大小依賴于架構（32位或64位）和操作系統。在排錯的上下文中，理解虛擬地址空間使用內存資源，了解應用程序可以超出vas即使在64位平臺只要物理地址可能一直可用，這些很重要。

　　更多有關于虛擬地址空間，請查看sql server聯機叢書中“process address space”和msdn中的virtual address space。

　　windows地址擴展和sql server

　　windows地址擴展(awe)是允許32位應用程序跨越32位地址限制操作內存的api。awe機制技術上不需要64位平臺。然而它出現了。內存頁通過awe機制涉及在64位平臺上的鎖定頁。

　　在32位和64位平臺上，內存通過awe機制分配不能分頁出界。這可以有益于應用程序（這是在64位平臺上使用awe機制的原因）。這也影響了系統和其他應用程序可用ram總數，這可能是有害的影響。因為這個原因為了使用awe，lock pages in memory權利必須分配該運行sql server的賬號。

　　從排錯的角度來看，要點是sql server緩存池使用awe映射內存；然而，只有數據庫（hash過的）頁面可以利用awe分配內存。通過awe機制內存分配將不會在任務管理器或在process: private bytes性能計數器中看到。你需要使用sql server特殊的計數器或動態管理視圖來獲取這些信息。

　　更多關于awe映射內存的信息，請在sql server聯機叢書中查看“managing memory for large databases” 和 “memory architecture”以及msdn中的large memory support

　　下列表匯總了不同sql server 2005支持的最大內存（注意特殊的sql server版本或windows可以有不同支持內存的限制）

　　表1

　　[1]更多有關于啟動參數，請參考sql server聯機叢書中“using awe” 文章。

最大的網站源碼資源下載站，