SQL查詢效能分析

原文出處：http://blog.csdn.net/dba_huangzj/article/details/7623926

SQL查詢效能的好壞直接影響到整個資料庫的價值，對此，必須鄭重對待。

SQL Server提供了多種工具，下面做一個簡單的介紹：

 

一、SQL Profiler工具

SQL Profiler可用於：

l  圖形化監視SQLServer查詢；

l  在後臺收集查詢資訊；

l  分析效能；

l  診斷像死鎖這樣的問題；

l  除錯Transact-SQL（T-SQL）語句；

l  模擬重放SQLServer活動

 

注意：定義一個跟蹤最有效的方法是通過系統儲存過程，但是學習的起點還是通過GUI。

 

1．1、  Profiler跟蹤：

建議使用標準模版

1．2、  事件：

一個事件表現SQLServer中執行的各種活動。可以簡單分類為：事件類、遊標事件、鎖事件、儲存過程事件和T-SQL事件。

對於效能分析，主要關心以下部分：

l  SQL活動涉及哪一類的CPU使用？

l  使用了多少記憶體？

l  涉及多少I/O操作？

l  SQL活動執行了多長時間？

l  特定的查詢執行的頻率多高？

l  查詢面對哪類錯誤和警告？

跟蹤查詢結束的事件：

事件類	事件	描述
Stored Procedures	RPC:Completed	RPC完成事件
	SP:Completed	儲存過程完成事件
	SP:StmtCompleted	在儲存過程中一條SQL語句完成事件
TSQL	SQL:BatchCompleted	T-SQL批完成事件
	SQL:StmtCompleted	一條T-SQL語句完成事件

RPC事件表示儲存過程使用遠端過程呼叫（RPC）機制通過OLEDB命令執行。如果一個資料庫應用程式使用T-SQL EXECUTE語句執行一個儲存過程，那麼會被轉化為一個SQL批而不是一個RPC，RPC通常比EXECUTE請求快，因為它們繞過了SQLServer中的許多語句解析和引數處理。

         T-SQL批是一組被一起提交到SQLServer的SQL查詢，以GO結束。GO不是一條T-SQL語句，而是有Sqlcmd使用程式和Management Studio識別。象徵著批的結束。T-SQL批由一條或多條T-SQL語句組成。語句或T-SQL語句在儲存過程（以下簡稱SP）中也是獨立和離散的。用SP:StmtCompleted或SQL:StmtCompleted事件捕獲單獨的語句可能代價很高。收集時要非常謹慎，特別在生產環境上。

跟蹤查詢效能的事件：

事件類	事件	描述
Security Audit（安全審計）	Audit Login（登入審計）	記錄使用者連線到SQL Server或斷開連線時資料庫的連線
	Audit Logou（登出審計）
Seesions（會話）	ExistingConnection（現有連線）	表示所有在跟蹤開始之前連線到SQLServer的使用者
Cursors（遊標）	CursorImplicitConversion（遊標隱含轉換）	表明建立的遊標型別與所請求的型別不同。
Errors and Warnings（錯誤和警告）	Attention（注意）	表示由於客戶撤銷查詢或者資料庫連線破壞引起的請求中斷
	Exception（異常）	表明SQLServer中發生了異常
	Execution Warnings（執行警告）	表明在查詢或SP執行過程中出現了警告
	Hash Warning（hash警告）	表明hash操作中發生了錯誤
	Missing Column Statistics（列統計丟失）	表明優化器要求的確定處理策略用的列統計丟失。
	Missing Join Predicate（連線斷言丟失）	表明查詢在兩表之間沒有連線斷言情況下執行。
	Sort Warnings（排序警告）	表明像select這樣的查詢中執行的排序操作沒有合適的記憶體。
Locks（鎖）	Lock: Deadlock（死鎖）	標誌著死鎖的出現
	Lock: Deadlock Chain（死鎖鏈）	顯示產生死鎖的查詢鏈條
	Lock: Timeout（鎖超時）	表示鎖已經超過了其超時引數，該引數由SET LOCK_TIMEOUT timeout_period（MS）命令設定
Stored Procedures（儲存過程）	SP:Recompile（重編譯）	表明用於一個儲存過程的執行計劃必須重編譯，原因是執行計劃不存在，強制的重編譯，或者現有的執行計劃不能重用。
	SP:Starting（開始） SP:StmtStarting（語句開始）	分別表示一個SP:StmtStarting儲存過程和儲存過程中的一條SQL語句的開始。它們對於識別開始但因為一個操作導致Attention事件而未能結束的查詢很有用。
Transactions（事務）	SQLTransaction（SQL事務）	提供資料庫事務的資訊，包括事務開始/結束的時間、事務持續時間的資訊。

 

1．3、  資料列：事件的特性。如事件的類、用於該事件的SQL語句、鎖資源開銷及事件來源。

資料列	描述
EventClass（事件類）	事件型別，如SQL：StatementCompleted
TextData	事件所用的SQL語句
CPU	事件的CPU開銷（ms）
Reads	為一個事件所執行的邏輯讀運算元量。
Writes	一個事件所執行的邏輯寫運算元量。
Duration	事件的執行事件（ms）
SPID	該事件的程式ID
StratTime	事件開始的事件

邏輯讀、寫由記憶體中的8KB頁面活動組成，可能需要0或者多個物理I/O。找到物理I/O運算元，使用系統監視工具。

 

二、跟蹤的自動化

注意：SQL Profiler對效能存在負面影響，如非必要不要在生產環境長期使用。

1.        使用GUI捕捉跟蹤：

可以使用兩種方法建立指令碼化的跟蹤——手工或GUI:

可以使用Profiler的匯出功能匯出指令碼。

2.        使用儲存過程捕捉跟蹤：

l  Sp_trace_create:建立一個跟蹤定義。

l  Sp_trace_setevent:新增事件和事件列到跟蹤中。

l  Sp_trace_setfilter:將過濾器應用到跟蹤。

可以使用內建函式：fn_trace_getinfo確定正在執行的跟蹤：

SELECT * FROM ::fn_trace_getinfo(default);

可以使用：sp_trace_setstatus停止特定的跟蹤：

EXEC sp_trace_setstatus 1,0

—停止id為1的跟蹤。

關閉跟蹤後，必須刪除：

EXEC sp_trace_setstatus 1,2

可以重新執行fn_trace_getinfo函式確認是否已經關閉。

 

三、結合跟蹤和效能監視器輸出

可以結合SQL Profiler和效能監視器來分析效能，此處不多說

 

四、SQL Profiler建議

使用SQL Profiler時，要考慮以下幾點：

l  限制事件和資料列的數量；

l  拋棄用於效能分析的啟動事件；

l  限制跟蹤輸出大小；

l  避免聯機資料列排序；

l  遠端執行Profiler

 

1、  限制事件和資料列：

捕捉像鎖和執行計劃這樣的事件時應該小心進行，因為輸出會變得非常大並降低SQL Server效能。

2、  丟棄效能分析所用的啟動事件：

像SP:StmtStarting這樣的啟動事件不提供分析資訊，因為只有事件完成才能計算I/O量、CPU負載和查詢的持續時間。

使用捕捉啟動事件的時機是：預期某些SQL查詢因為錯誤而不能結束執行，或者頻繁發現Attention事件按的時候捕捉。因為Attention事件一般表示使用者中途撤銷了查詢或者查詢超時，可能因為查詢執行了太長時間。

3、  限制跟蹤輸出大小：

在Edit Filter（編輯過濾器）對話方塊中做以下設定：

l  Duration-Greater than or equal:2（持續事件>=2）：持續事件等於0或1ms的查詢不能進一步優化。

l  Reads-Greater than or equal:2（讀運算元量>=2）：邏輯讀數量等於0或1的查詢不能進一步優化。

4、  避免線上資料列排序：

（1）、捕捉跟蹤，不做任何排序或分組。

（2）、儲存跟蹤輸出到一個跟蹤檔案。

（3）、開啟跟蹤檔案並按照需要排序。

5、  遠端執行Profiler：

使用系統儲存過程比使用GUI對效能方面有好處。

6、  限制使用某些事件：在已經遇到壓力的系統上，不要使用Showplan XML事件

 

 

五、沒有Profiler情況下的查詢效能度量

對於需要立即捕捉系統，使用DMV:sys.dm_exec_query_stats比Profiler有效，如果需要查詢執行機器單獨開銷的歷史記錄，跟蹤仍是更好的工具。

sys.dm_exec_query_stats：獲取伺服器上查詢計劃統計的資訊：

列	描述
Plan_handle	引用執行計劃的指標
Creation_time	計劃建立的時間
Last_execution time	查詢最後一次使用計劃的時間
Execution_count	計劃已經使用的次數
Total_worker_time	從建立起計劃使用的CPU時間
Total_logical_reads	從建立起計劃使用的讀運算元量
Total_logical_writes	從建立起計劃使用的寫運算元量
Query_hash	可用於識別有類似邏輯的查詢的一個二進位制hash
Query_plan_hash	可用於識別有相似邏輯的計劃的一個二進位制hash

為了過濾資訊，需要關聯其他DMF。如sys.dm_exec_sql_text來檢視查詢文字。

Sys.dm_query_plan顯示查詢的執行計劃。從而限制不必要的返回資訊。

 

六、開銷較大的查詢

對於收集結果，應該分析兩部分：

l  導致大量系統資源壓力的查詢；

l  速度降低最嚴重的查詢

 

1、  識別開銷較大的查詢：

對於返回的跟蹤資料，CPU和Reads列顯示了查詢開銷所在。在執行讀操作時，記憶體頁面必須在操作查詢中被備份，在第一次資料訪問期間寫入，並在記憶體瓶頸時被移到磁碟。過多頁面CPU還會增加管理頁面的負擔。

           導致大量邏輯讀的查詢通常在相應的大資料集上得到鎖。即使讀，也需要在所有資料上的共享鎖。阻塞了其他請求修改的查詢。但不阻塞讀資料的查詢。如果查詢很久，那麼會持續阻塞其他查詢，被阻塞的查詢進一步阻塞其他查詢，引起資料中的阻塞鏈。

結論，識別開銷大的查詢並首先優化它們從而達到以下效果：

l  增進開銷較大的查詢本身的效能；

l  降低系統資源上的總體壓力；

l  減少資料庫阻塞；

開銷大的查詢有兩類：

l  單次執行：查詢一次開銷較大

l  多次執行：查詢本身不大，但是重複執行導致系統資源上的壓力。

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

可以使用SQL Profiler，或者查詢sys.dm_exec_query_stats來識別開銷大的查詢。

（1）、捕捉表示典型工作負載的Profiler跟蹤。

（2）、將跟蹤輸出儲存到一個跟蹤檔案。

（3）、開啟跟蹤檔案進行分析。

（4）、開啟跟蹤的Properties（屬性）視窗，單擊Event Selection(事件選擇)選項卡。

（5）、單機按鈕開啟Organize Columns（組織列）視窗。

（6）、在Reads列上分組跟蹤輸出。

（7）、使用分組的跟蹤。

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

l  這種情況下，Profiler中跟蹤輸出的以下列上分組：EventClass、TextData和Reads。

l  匯出Profiler跟蹤表。使用內建函式fn_trace_gettable匯入到一個跟蹤表。

l  訪問sys.dm_exec_query_statsDMV從生產伺服器檢索資訊。

把資料裝入到資料庫的一個表中

SELECT  *
INTO    Trace_Table
FROM    ::
        FN_TRACE_GETTABLE('C:\PerformanceTrace.trc', DEFAULT)

 

執行下面語句查詢多次執行的讀操作總數：

SELECT  COUNT(*) AS TotalExecutions ,
        EventClass ,
        TextData ,
        SUM(Duration) AS Duration_Total ,
        SUM(CPU) AS CPU_Total ,
        SUM(Reads) AS Reads_Total ,
        SUM(Writes) AS Writes_Total
FROM    Trace_Table
GROUP BY EventClass ,
        TextData
ORDER BY Reads_Total DESC

SQL Server 2008不支援在NTEXT資料型別進行分組。而TextData是ntext型別，要轉換成Nvarchar（max）

 

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_readsDESC

 

3.      識別執行緩慢的查詢：

需要定期監視輸入的SQL查詢的執行時間，並找出執行緩慢的查詢的響應時間。但是不是所有執行緩慢的查詢都是由於資源問題形成。如阻塞那些都有可能導致緩慢的查詢。

可以在Duration上跟蹤。

 

 

七、執行計劃

1、  分析查詢計劃

執行計劃從右到左，從上到下的順序閱讀。每個步驟代表獲得查詢最終輸出所執行的操作。執行計劃有以下特徵：

l  如果查詢由多個查詢的批組成，每個查詢的執行計劃按照執行的順序顯示。批中的每個執行將有一個相對的估算開銷，整個批的總開銷為100%。

l  執行計劃中的每個圖示代表一個操作符。有相對的估算開銷，所有節點的總開銷為100%。

l  執行計劃中的一個起始操作符通常表示一個資料庫物件（表或索引）的資料檢索機制。

l  資料檢索通常是一個表操作或索引操作。

l  索引上的資料檢索將是索引掃描或索引查詢。

l  索引上的資料檢索的命名慣例是[表名].[索引名]。

l  資料從右到左在兩個操作之間流動，由一個連線箭頭表示。

l  操作符之間連線箭頭的寬度是傳輸行數的圖形表示。

l  同一列的兩個操作符之間的連線機制將是巢狀的迴圈連線，hash匹配連線或者合併連線。

l  將游標放置在執行計劃的一個節點上，顯示一個具有一些細節的彈出視窗。

l  在Properties（屬性）視窗中有完整的一組關於操作符的細節。可以右鍵單擊操作符並選擇Properties。

l  操作符細節在頂部顯示物理和邏輯操作的型別。物理操作代表儲存引擎實際使用的，而邏輯操作是優化器用於建立估算執行計劃的結構。如果相同，只顯示物理操作。還會顯示其他資訊：I/O、CPU等。

l  操作符細節彈出視窗的Argument（引數）部分在分析中特別有用，因為顯示了優化器鎖使用的過濾或連線條件。

2、  識別執行計劃中開銷較大的步驟：

l  執行計劃中每個節點顯示整個計劃中的相對開銷，整個計劃總開銷為100%。關注最高相對開銷的節點。

l  執行計劃可能來自於一批語句，因此可能也需要查詢開銷最大的語句。

l  檢視節點之間連線箭頭的寬度。非常寬的連線箭頭表示對應節點之間的傳輸大量的行。分析箭頭左邊的節點以理解需要這麼多行的原因，還要檢查箭頭的屬性。可能看到估計的行和實際的行不一樣，這可能由過時的統計造成。

l  尋找hash連線操作。對於小的資料集，巢狀的迴圈連線通常是首選的連線技術。

l  尋找書籤查詢操作。對於大結果集的書籤操作可能造成大量的邏輯讀。

l  如果操作符上有一個歎號的警告，是需要立刻注意的領域。這些警告可能是由各種問題造成的，包括沒有連線條件的連線或者丟失統計的索引和表。

l  需找執行排序操作的步驟，這表示資料沒有以正確的排序進行檢索。

 

3、  分析索引有效性：

要關注【掃描】，掃描代表訪問大量的行。可以通過以下方式判斷索引有效性：

l  資料檢索操作

l  連線操作

有時候執行計劃中沒有【斷言】（predicate），缺乏斷言意味著整個表（聚簇索引就是該表）被作為合併連線操作符的輸入進行掃描。

4、  分析連線有效性：

SQLServer使用3中連線型別：

l  Hash連線；

l  合併連線

l  巢狀迴圈連線

1、  Hash連線：

1.1、       Hash連線高效處理大的、未排序的、沒有索引的輸入。

1.2、       Hash連線使用兩個連線輸入：建立輸入（build input）和探查輸入（probe input）。建立輸入是執行計劃中上面的那個輸入，探查輸入是下面那個輸入。

1.3、       最常見的hash連線方式——in-memory hash join，整個建立輸入被掃描或計算然後在記憶體中建立一個hash表。每個行根據計算的hash鍵值（相等斷言中的一組列）被插入一個hash表元中。

記憶體hash連線的示意圖：

2、  合併連線：

2.1、合併連線要求兩個輸入在合併列上排序，這將在連線條件中定義。如果兩個連線有索引，那麼連線輸入由該索引排序。由於每個連線輸入都被排序了，合併排序從每個輸入得到一行並比較是否相等。如果相等，匹配行被生成。過程被重複到所有行都被處理。

2.2、如果優化器發現連線輸入都在其連線列上排序，合併連線就比hash連線更快而被選中。

3、  巢狀迴圈連線：

3.1、始終從單獨的表中訪問有限數量的行，為了理解使用較小結果集的效果，在查詢中降低連線輸入。

3.2、使用一個連線輸入作為外部（outer）輸入表。另一個作為內部（inner）輸入表。外部表是執行計劃的上方輸入，內部表是下方輸入。外部迴圈逐行消費外部輸入表。內部迴圈為每個外部行執行一次，搜尋內部輸入表的匹配行。

3.3、如果外部輸入相當小，內部輸入大但有索引，巢狀迴圈連線是非常高效的。連線通過犧牲其他方面來提高速度——使用記憶體來取得小的資料集並快速與第二個資料集比較。合併排序與此類似，使用記憶體和一小部分tempdb排序，hash連線使用記憶體和tempdb建立hash表。

3.4、雖然迴圈連線更快，但是隨著資料集變得更大，比hash或合併消耗更多的記憶體。所以SQL Server會在不同資料集的情況下使用不同計劃的原因。

 

3種連線型別的特性：

連線型別	連線列上的索引	連線表的一般大小	預先排序	連線子句
Hash	內部表：不需要索引 外部表：可選 最佳條件：小的外部表，大的內部表	任意	不需要	Equi-join
合併	內部/外部表：必須 最佳條件：兩個表都有聚簇索引或覆蓋索引	大	需要	Equi-join
巢狀迴圈	內部表：必須 外部表：最好有	小	可選	所有

注意：在hash和巢狀迴圈連線中，外部表一般是兩個連線表中較小的一個。

5、  實際執行計劃vs估算執行計劃：

估算執行計劃對臨時表無法生成。

6、  計劃快取：

一般是儲存在記憶體空間。可以使用DMV來查詢：

SELECT  p.query_plan ,
        t.text
FROM    sys.dm_exec_cached_plansr
        CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
        CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) t

八、查詢開銷

1、  客戶統計：將計算機作為伺服器的一個客戶端，從這個角度去發出捕捉執行資訊。

點選SSMS中的【查詢】→【包含客戶統計】，但這一步不是很好的收集方法。有時候需要重置：【查詢】→【重置客戶統計】

2、  執行時間：

Duration和CPU都代表著查詢的時間因素，可以使用SET STATISTICS TIME來取得執行時間。

其中最後一行的CPU時間等於Profiler的CPU值，佔用時間代表Duration值。0毫秒的分析和編譯時間說明重用了執行計劃。可以執行：DBCC FREEPROCCACHE清除快取。但是不要在生產系統上執行，因為某種情況下，這和重啟的開銷相同。

3、  STATISTICS IO：

Profiler獲取的Reads列的讀取次數嚐嚐是Duration、CPU、Reads和Writes這些因素中最重要的。在解讀STATISTICS IO的輸出時，多半參考【邏輯讀】操作。有時候也會參考掃描計數。物理讀操作和預讀數量在資料不能在記憶體中找到時將不為0，但一旦資料填寫到記憶體，物理讀和預讀將趨向於0。在優化期間，可以監控單表的讀操作次數以確保確實減少了該表的資料訪問開銷。