SQL Server 2005效能調整一(zt)

zhouwf0726發表於2019-04-03
SQLServer
http://space.itpub.net/?uid-47598-action-viewspace-itemid-170255

簡介

很多客戶偶爾會遇到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瓶頸的徵兆。你可以用下列查詢列出所有的排程器併產看等待執行的任務數量。

select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

下列查詢給你一個檢視當前快取中最耗費CPU的批或過程高階別檢視。查詢根據具有相同plan handle（意味著屬於同一個批或過程）的語句聚合CPU的呼叫。如果給出的plan handle對應多個語句，你將不得不繼續找到在整個CPU使用中最佔用資源的查詢。

select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

本節剩下的部分將討論一些其他的在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”。如果你有儲存的跟蹤檔案，你可以使用下列查詢檢視所有捕捉的重編譯事件。

select
spid,
StartTime,
Textdata,
EventSubclass,
ObjectID,
DatabaseID,
SQLHandle
from
fn_trace_gettable ( 'e:recompiletrace.trc' , 1)
where
EventClass in(37,75,166)

事件類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個快照，你可以得到在給定的時間段內花費在查詢優化的時間。

select *
from sys.dm_exec_query_optimizer_info

counter occurrence value
---------------- -------------------- ---------------------
optimizations 81 1.0
elapsed time 81 6.4547820702944486E-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個儲存過程。

select *
from sys.dm_exec_query_optimizer_info

select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

更多資訊請見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時間最多的有效方法。

select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

還可以選擇，也可以查詢sys.dm_exec_cached_plans並通過使用過濾器查詢可疑的類似於‘%Hash Match%’, ‘%Sort%’這樣CPU敏感的各種操作。

解決

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

◆使用Database Engine Tuning Advisor調節查詢，檢視是否生成對修改索引的建議◆檢查有問題的評估。編寫的查詢中使用的更有限制性的WHERE從句是否合適？無限制條件的查詢是資源敏感的。

在查詢中涉及的表上執行UPDATE STATISTICS，檢查是否還有這種問題。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242516/，如需轉載，請註明出處，否則將追究法律責任。

相關文章