利用SQL Profiler處理開銷較大的查詢

zping發表於2018-03-09
SQL

　當SQL Server的效能變差時，最可能發生的是以下兩件事：

  • 首先，某些查詢產生了系統資源上很大的壓力。這些查詢影響整個系統的效能，因為伺服器無法足夠快速地服務其他SQL查詢。
  • 另外，開銷較大的查詢阻塞了其他請求相同資料庫資源的查詢，進一步降低了這些查詢的效能。優化開銷較大的查詢不僅改進它們本身的效能，而且減少資料庫阻塞和SQL Server資源壓力從而提高了其他查詢的效能。

識別開銷較大的查詢

　　SQL Server的目標是在最短時間內將結果集返回給使用者。為此，SQL Server查詢優化器生成一個成本效益高的查詢執行計劃。查詢優化器計算許多因素的權重，包括執行查詢所需要的CPU、記憶體以及磁碟I/O的使用情況-這些均來自於由索引維護或過程中生成的統計。通常開銷最低的計劃有最少的I/O，因為I/O操作代價昂貴。

　　邏輯讀提供指出了查詢產生的記憶體壓力。它還提供了磁碟壓力指標，因為記憶體頁面必須在操作查詢中被備份，在第一次資料訪問期間寫入，並且在記憶體瓶頸時被移到磁碟上。查詢的邏輯讀數量越大，磁碟壓力的可能性就越大。過多的邏輯頁面也增加了CPU用於管理這些頁面的負載。

　　導致大量邏輯讀的查詢通常在相應的大資料集上得到鎖。即使讀也需要在所有資料上的共享鎖。這些查詢阻塞了其他請求修改這些資料的查詢，但是不阻塞讀取資料的查詢。因為這些查詢固有的開銷並且需要長時間執行，他們持續地阻塞其他查詢。被阻塞的查詢進一步阻塞查詢，引入了資料中的阻塞鏈。

　　識別開銷較大的查詢並優化它們有如下意義：

  • 　　增進開銷較大的查詢本身的效能；
  • 　　降低系統資源上的總體壓力；
  • 　　較少資料庫阻塞；

　　其中開銷較大的查詢可以被分為如下兩類：

  • 　　單詞執行：查詢的一次單獨執行開銷較大；
  • 　　多次執行：查詢本身開銷並不大，但是該查詢的重複執行導致系統資源上的壓力；

　　1、單次執行開銷較大的查詢

　　可以分析SQL Profiler跟蹤輸出檔案來識別開銷較大的查詢。比如，如果對識別執行大量的邏輯讀的查詢感興趣，應該在跟蹤輸出的Reads資料列上排序。

  • 　　捕捉表示典型工作負載的Profiler跟蹤；
  • 　　將跟蹤輸出儲存到一個跟蹤檔案；
  • 　　開啟跟蹤檔案進行分析；
  • 　　通過事件選擇選項卡，單擊組織列按鈕，在Reads列上分組跟蹤輸出。

　　

　　跟蹤輸出如下：

　　

　　在某些情況下，可能從系統監視器輸出中識別CPU上的大壓力。CPU上的壓力可能是因為大量CPU密集型操作，如儲存過程重編譯、總計函式、資料排序、雜湊連線等。在這種情況下，應該在CPU列上排序Profiler跟蹤輸出以識別使用大量處理器週期的查詢。

　　2、多次執行開銷較大的查詢

　　有時候一個查詢可能本身開銷並不大，但是同一查詢多次執行的累積效應可能造成系統資源的壓力。在Reads列上排序對識別這種型別的查詢沒有幫助。如果希望知道查詢的多次執行進行的總讀取數，不幸的是Profiler在這裡不能直接提供幫助，但是仍然可以用以下方法得到這一資訊。

  • 　　在Profiler中跟蹤輸出的以下列上分組：EventClass、TextData和Reads。對於相同EventClass和TextData的分組，手工計算所有對應的Reads的總和。
  • 　　在Profiler中選擇檔案=》另存為=》跟蹤表將輸出到一個跟蹤表。也可以使用內建函式fn_trace_gettable和Profiler的跟蹤檔案輸出匯入到一個跟蹤表。
  • 　　訪問sys.dm_exec_query_stats DMV從生產伺服器上檢索資訊。這假設打算處理一個即時的問題並且不關注歷史問題。

　　在將跟蹤輸入儲存到檔案以後，先將跟蹤資料匯入到一張表：

SELECT * INTO TraceTable
FROM ::fn_trace_gettable('D:\123.trc',default)

　　然後執行以下語句：

複製程式碼
SELECT COUNT(*) AS TotalExecutions,EventClass,
CAST(TextData AS NVARCHAR(MAX)) TextData,
SUM(Duration) AS Duration_Total, 
SUM(CPU) AS CPU_Total, SUM(Reads) AS Reads_Total, 
SUM(Writes) AS Writes_Total 
FROM TraceTable 
GROUP BY EventClass,CAST(TextData AS NVARCHAR(MAX)) 
ORDER BY Reads_Total DESC
複製程式碼

　　指令碼中的TotalExecutions列指出了查詢被執行的次數，Reads_Total列指出了該查詢多次執行所進行的讀操作的總數。注意NTEXT不支援GROUP BY,因此要轉換一下型別。

　　這個方法識別出來的開銷較大的查詢比Profiler識別出的單次執行的開銷較大查詢更好地指出了負載。例如，一個需要50個讀操作的查詢可能執行1000次。這個查詢本身被認為足夠經濟了，但是執行的讀操作總是是5萬，這不能被認為是經濟的。優化這個查詢降低讀運算元，即使每次執行減少10次，讀運算元也將降低1萬次。這比優化一個5千次讀操作的查詢更有利。

　　從sys.dm_exec_query_stats檢視中得到相同的資訊只需要一個查詢：

複製程式碼
SELECT ss.sum_execution_count
,t.TEXT
,ss.sum_total_elapsed_time
,ss.sum_total_worker_time
,ss.sum_total_logical_reads
,ss.sum_total_logical_writes
FROM (SELECT s.plan_handle
,SUM(s.execution_count) sum_execution_count
,SUM(s.total_elapsed_time) sum_total_elapsed_time
,SUM(s.total_worker_time) sum_total_worker_time
,SUM(s.total_logical_reads) sum_total_logical_reads
,SUM(s.total_logical_writes) sum_total_logical_writes
FROM sys.dm_exec_query_stats s
GROUP BY s.plan_handle
)AS ss
CROSS APPLY  sys.dm_exec_sql_text(ss.plan_handle) t
ORDER BY sum_total_logical_reads DESC
複製程式碼

　　這比所有收集跟蹤資料所需要的工作要容易得多，那麼為什麼還要使用跟蹤資料？使用跟蹤的主要原因是精確性。sys.dm_exec_query_stats檢視是給定計劃已經存在於記憶體中時的流動總計，時間點並不精確。另一方面，跟蹤是執行的任何時間段的歷史記錄。甚至可以在資料庫中加入跟蹤，並且擁有一系列可以比依靠給定的瞬間更精確地生成總計的資料。但是對定位效能問題的理解關注是查詢執行緩慢的時點，這是sys.dm_exec_query_stats不可替代的場合。

　　3、識別執行緩慢的查詢

　　如果執行緩慢的查詢的響應時間變得不可接受，那麼應該分析效能下降的原因。但是不是所有執行緩慢的查詢都是由於資源問題造成的，其他需要關心的因素如阻塞也可能導致緩慢的查詢。

　　為了發現執行緩慢的查詢，在Duration列上分組跟蹤輸出。

　　

　　跟蹤輸出如下：

　　

　　對於執行緩慢的系統，應該注意優化過程前後執行緩慢的持續查詢時間。應用優化技術之後，應該計算在系統上的總體效能。優化步驟可能負面地影響其他查詢，使其變慢。

 

相關文章