效能優化查詢語句

Sys.dm_exec_sql_text 來獲得特定查詢的文字 他可以 與

  Sys.dm_exec_query_stats 動態管理檢視結合使用，已獲得所有資料庫中效能最差的查詢

  獲得查詢執行次數，平均的CPU 時間和持續時間以及查詢的文字

SELECT
(total_elapsed_time / execution_count)/1000 N'平均時間ms'
,total_elapsed_time/1000 N'總花費時間ms'
,total_worker_time/1000 N'所用的CPU總時間ms'
,total_physical_reads N'物理讀取總次數'
,total_logical_reads/execution_count N'每次邏輯讀次數'
,total_logical_reads N'邏輯讀取總次數'
,total_logical_writes N'邏輯寫入總次數'
,execution_count N'執行次數'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'執行語句'
,creation_time N'語句編譯時間'
,last_execution_time N'上次執行時間'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY
total_elapsed_time / execution_count DESC;

SQLServer
SELECT top 50 creation_time N'語句編譯時間'
,last_execution_time N'上次執行時間'
,total_physical_reads N'物理讀取總次數'
,total_logical_reads/execution_count N'每次邏輯讀次數'
,total_logical_reads N'邏輯讀取總次數'
,total_logical_writes N'邏輯寫入總次數'
,execution_count N'執行次數'
,total_worker_time/1000 N'所用的CPU總時間ms'
,total_elapsed_time/1000 N'總花費時間ms'
,(total_elapsed_time / execution_count)/1000 N'平均時間ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'執行語句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
oracle
select *
from
(select
LAST_ACTIVE_TIME AS "上次執行時間",
DISK_READS AS "讀磁碟次數",
SQL_TEXT AS "執行語句",
PARSE_CALLS AS "編譯次數",
EXECUTIONS AS "執行次數",
CPU_TIME/1000 AS "所用的CPU總時間ms",
ELAPSED_TIME/1000 AS "總花費時間ms",
ROUND(ELAPSED_TIME/EXECUTIONS/1000, 2) AS "平均時間ms",
USER_IO_WAIT_TIME
from    sys.v_$sqlarea
where EXECUTIONS > 0
and MODULE = 'SFCMN03003S.exe'
order by ELAPSED_TIME/EXECUTIONS desc)
where rownum < 51;

sys.dm_exec_query_stats (Transact-SQL)

sys.dm_exec_query_stats (Transact-SQL)

返回快取查詢計劃的聚合效能統計資訊。快取計劃中的每個查詢語句在該檢視中對應一行，並且行的生存期與計劃本身相關聯。在從快取刪除計劃時，也將從該檢視中刪除對應行。

注意：
如果伺服器中當前正在執行工作負荷，則 sys.dm_exec_query_stats 的初始查詢可能會生成不準確的結果。可以通過重新執行查詢來確定更準確的結果。
列名	資料型別	說明
sql_handle	varbinary(64)	表示包含查詢的批查詢或儲存過程的標記。 通過呼叫 sys.dm_exec_sql_text 動態管理函式，sql_handle 可以和 statement_start_offset 及 statement_end_offset 一起用於檢索查詢的 SQL 文字。
statement_start_offset	int	指示行所說明的查詢在其批查詢或持久化物件文字中的開始位置（位元組，以 0 開頭）。
statement_end_offset	int	指示行所說明的查詢在其批查詢或持久化物件文字中的結束位置（位元組，以 0 開頭）。值 -1 指示批查詢的結尾處。
plan_generation_num	bigint	可用於在重新編譯後區分不同計劃例項的序列號。
plan_handle	varbinary(64)	表示包含查詢的編譯計劃的標記。可以將此值傳遞給 sys.dm_exec_query_plan 動態管理函式來獲取查詢計劃。
creation_time	datetime	編譯計劃的時間。
last_execution_time	datetime	上次開始執行計劃的時間。
execution_count	bigint	計劃自上次編譯以來所執行的次數。
total_worker_time	bigint	此計劃自編譯以來執行所用的 CPU 時間總量（以微秒為單位報告，但僅精確到毫秒）。
last_worker_time	bigint	上次執行計劃所用的 CPU 時間（以微秒為單位報告，但僅精確到毫秒）。
min_worker_time	bigint	此計劃在單次執行期間所用的最小 CPU 時間（以微秒為單位報告，但僅精確到毫秒）。
max_worker_time	bigint	此計劃在單次執行期間所用的最大 CPU 時間（以微秒為單位報告，但僅精確到毫秒）。
total_physical_reads	bigint	此計劃自編譯後在執行期間所執行的物理讀取總次數。
last_physical_reads	bigint	上次執行計劃時所執行的物理讀取次數。
min_physical_reads	bigint	該計劃在單個執行期間所執行的最少物理讀取次數。
max_physical_reads	bigint	該計劃在單個執行期間所執行的最多物理讀取次數。
total_logical_writes	bigint	此計劃自編譯後在執行期間所執行的邏輯寫入總次數。
last_logical_writes	bigint	上次執行計劃時所執行的邏輯寫入次數。
min_logical_writes	bigint	該計劃在單個執行期間所執行的最少邏輯寫入次數。
max_logical_writes	bigint	該計劃在單個執行期間所執行的最多邏輯寫入次數。
total_logical_reads	bigint	此計劃自編譯後在執行期間所執行的邏輯讀取總次數。
last_logical_reads	bigint	上次執行計劃時所執行的邏輯讀取次數。
min_logical_reads	bigint	該計劃在單個執行期間所執行的最少邏輯讀取次數。
max_logical_reads	bigint	該計劃在單個執行期間所執行的最多邏輯讀取次數。
total_clr_time	bigint	編譯計劃後，因執行此計劃而在 Microsoft .NET Framework 公共語言執行時 (CLR) 物件內使用的時間（以微秒為單位報告，但僅精確到毫秒）。CLR 物件可以是儲存過程、函式、觸發器、型別和聚合。
last_clr_time	bigint	在上一次執行此計劃期間，在 .NET Framework 公共語言執行時 (CLR) 物件內執行所用的時間（以微秒為單位報告，但僅精確到毫秒）。CLR 物件可以是儲存過程、函式、觸發器、型別和聚合。
min_clr_time	bigint	此計劃在單次執行期間在 .NET Framework CLR 物件內所用的最小時間（以微秒為單位報告，但僅精確到毫秒）。CLR 物件可以是儲存過程、函式、觸發器、型別和聚合。
max_clr_time	bigint	此計劃在單次執行期間在 .NET Framework CLR 內所用的最大時間（以微秒為單位報告，但僅精確到毫秒）。CLR 物件可以是儲存過程、函式、觸發器、型別和聚合。
total_elapsed_time	bigint	上次完成執行此計劃所用的總時間（以微秒為單位報告，但僅精確到毫秒）。
last_elapsed_time	bigint	最近一次完成執行此計劃所用的時間（以微秒為單位報告，但僅精確到毫秒）。
min_elapsed_time	bigint	任何一次完成執行此計劃所用的最小時間（以微秒為單位報告，但僅精確到毫秒）。
max_elapsed_time	bigint	任何一次完成執行此計劃所用的最大時間（以微秒為單位報告，但僅精確到毫秒）。
query_hash	Binary(8)	對查詢計算的二進位制雜湊值，用於標識具有類似邏輯的查詢。可以使用查詢雜湊確定僅僅是文字值不同的查詢的聚合資源使用情況。有關詳細資訊，請參閱 使用查詢和查詢計劃雜湊值查詢和優化類似查詢。
query_plan_hash	binary(8)	對查詢執行計劃計算的二進位制雜湊值，用於標識類似的查詢執行計劃。可以使用查詢計劃雜湊查詢具有類似執行計劃的查詢的累積成本。有關詳細資訊，請參閱 使用查詢和查詢計劃雜湊值查詢和優化類似查詢。

示例

A. 查詢 TOP N 查詢

以下示例按平均 CPU 時間返回排名前五個的查詢的相關資訊。此示例將根據查詢的查詢雜湊對查詢進行聚合，以便按照查詢的累積資源消耗來分組在邏輯上等效的查詢。

複製

USE AdventureWorks2008R2;

GO

SELECT TOP 5 query_stats.query_hash AS "Query Hash",

    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

    MIN(query_stats.statement_text) AS "Statement Text"

FROM

    (SELECT QS.*,

    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

    ((CASE statement_end_offset

        WHEN -1 THEN DATALENGTH(ST.text)

        ELSE QS.statement_end_offset END

            - QS.statement_start_offset)/2) + 1) AS statement_text

     FROM sys.dm_exec_query_stats AS QS

     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

GO

 

sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_query_plan

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