亂馬客

sys.dm_exec_query_stats

傳回 SQL Server 中之快取查詢計畫的彙總效能統計資料。 此檢視會針對快取計畫內的每個查詢陳述式包含一個資料列，而資料列的存留期取決於計畫本身。 從快取移除計畫時，對應的資料列也會從這個檢視中刪除。

我們可以從「sys.dm_exec_query_stats」取得很多的資訊，如執行的次數、時間等資訊。

而SQL 2008 R2 又多了以下4個欄位。

total_rows : 查詢傳回的資料列總數。 
last_rows : 上次執行查詢時所傳回的資料列數目。
min_rows :  自上次編譯計畫以來，在所有已經執行計畫的次數中，查詢所傳回的最小資料列數目。 
max_rows : 自從上次編譯計畫以來，在所有已經執行計畫的次數中，查詢所傳回的最大資料列數目。

以下透過取得執行次數最多的前20名來說明，

SELECT TOP 20
        qs.execution_count ,
		qs.statement_start_offset,
		qs.statement_end_offset,
        SUBSTRING(qt.text,
				 ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE WHEN qs.statement_end_offset = -1
                           THEN DATALENGTH(qt.text)
                           ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) AS [Individual Query] ,
        qt.text AS [Parent Query] ,
        DB_NAME(qt.dbid) AS DatabaseName
		, qt.dbid
FROM    sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY execution_count DESC;

透過CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 取得執行的SQL。

那為何要取得單獨的SQL呢? 比如在Store Procedure中會有多個SQL，我們就可以知道每個SQL的執行狀況，比如說，在SP中執行了幾次、執行多久等資訊。

效果類似在跟SQL Profiler中勾選「SP:StmtCompleted」然後將這些SQL統計。

那要如何取得單獨的SQL呢? 就是要透過SUBSTRING，字串當然就是qt.text，然後可透過statement_start_offset及statement_end_offset來判斷要從那裡開始，及要取多長。

因為qt.text是nvarchar(max)，而statement_start_offset及statement_end_offset是由 0 開始並以位元組為單位，所以計算位置時，要除以2，如下

( qs.statement_start_offset / 2 )

因為SUBSTRING是由1開始，所以要加上1。

所以取得單獨的SQL就如下的方式，

SUBSTRING(qt.text,
     ( qs.statement_start_offset / 2 ) + 1,
      ( ( CASE WHEN qs.statement_end_offset = -1
               THEN DATALENGTH(qt.text)
               ELSE qs.statement_end_offset
          END - qs.statement_start_offset ) / 2 ) + 1) AS [Individual Query]

另外，如果要清除 sys.dm_os_latch_stats 及 sys.dm_os_wait_stats DMV的話，可透過DBCC SQLPERF來清除，如下，

DBCC SQLPERF("sys.dm_os_latch_stats" , CLEAR)
DBCC SQLPERF("sys.dm_os_wait_stats" , CLEAR)

參考資訊

sys.dm_exec_query_stats

SUBSTRING

SP:StmtCompleted

DBCC SQLPERF

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^