透過DMV查詢CPU時間最長的語句和查詢計劃
SELECT TOP 50
execution_count, total_worker_time [CPU time],
total_logical_reads / execution_count AS [Avg Logical Reads],
total_elapsed_time / execution_count AS [Avg Elapsed Time],
total_worker_time / execution_count as [Avg CPU time],
db_name(st.dbid) as [database name],
object_name(st.dbid) as [object name],
object_name(st.objectid) as [father object name],
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,
qp.query_plan, qs.creation_time [PlanCompileTime]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE total_worker_time > 300
and db_name(st.dbid) is not null and db_name(st.dbid) not in ('distribution')
ORDER BY [CPU time] DESC, [Avg CPU time] DESC[@more@]
execution_count, total_worker_time [CPU time],
total_logical_reads / execution_count AS [Avg Logical Reads],
total_elapsed_time / execution_count AS [Avg Elapsed Time],
total_worker_time / execution_count as [Avg CPU time],
db_name(st.dbid) as [database name],
object_name(st.dbid) as [object name],
object_name(st.objectid) as [father object name],
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,
qp.query_plan, qs.creation_time [PlanCompileTime]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
WHERE total_worker_time > 300
and db_name(st.dbid) is not null and db_name(st.dbid) not in ('distribution')
ORDER BY [CPU time] DESC, [Avg CPU time] DESC[@more@]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/66009/viewspace-1040590/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 實時查詢最耗CPU資源的SQL語句SQL
- 查詢最佔資源、CPU、記憶體、和執行最長的SQL語句記憶體SQL
- 查詢操作佔用cpu很高語句
- golang透過mysql語句實現分頁查詢GolangMySql
- 透過查詢檢視sql執行計劃SQL
- [Mysql 查詢語句]——查詢欄位MySql
- oracle查詢語句執行計劃中的表消除Oracle
- mysql查詢語句5:連線查詢MySql
- [Mysql 查詢語句]——分組查詢group byMySql
- [Mysql 查詢語句]——查詢指定記錄MySql
- Hql查詢語句
- mysql查詢語句MySql
- 查詢正在執行的sql語句及該語句執行的時間SQL
- ORACLE 查詢語句處理過程(Oracle
- Oracle常用的查詢語句Oracle
- Elasticsearch查詢語句語法Elasticsearch
- mysql查詢語句集MySql
- SQL查詢語句 (Oracle)SQLOracle
- MySQL查詢阻塞語句MySql
- SQL server 查詢語句SQLServer
- Hibernate 查詢語句
- oracle查詢語句大全Oracle
- sql查詢語句流程SQL
- SQL mother查詢語句SQL
- Mysql之查詢語句MySql
- JDBC 增刪改查程式碼 過濾查詢語句JDBC
- 查詢Oracle正在執行和執行過的SQL語句OracleSQL
- 查詢某個表最後修改時間
- oracle查詢語句查詢增加一列內容Oracle
- oracle 查詢表空間使用率的語句Oracle
- MySQL的簡單查詢語句MySql
- mysql dba常用的查詢語句MySql
- 常用Sqlserver中的查詢語句SQLServer
- 複雜查詢語句的使用
- SGA區查詢的基本語句
- 用SQL語句檢查CPU和磁碟空間SQL
- Laravel 子查詢語句用法Laravel
- 資料庫查詢語句資料庫