Click here to Skip to main content
13,150,472 members (35,979 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

22.2K views
4 bookmarked
Posted 18 Apr 2013

How to Find the Top Most Expensive Cached Queries: sys.dm_exec_query_stats

, 18 Apr 2013
Rate this:
Please Sign up or sign in to vote.
Finding the top most expensive cached queries

Introduction

sys.dm_exec_query_stats DMV (Dynamic Management View) is described in BOL as follows: http://msdn.microsoft.com/en-us/library/ms189741.aspx

It returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

sys.dm_exec_query_stats provides a wealth of performance statistics for cached query plans. It’s a very useful DMV to get cached query details for performance and server load analysis.

Query 1: Top 10 Total CPU Consuming Queries

SELECT TOP 10
QT.TEXT AS STATEMENT_TEXT,
QP.QUERY_PLAN,
QS.TOTAL_WORKER_TIME AS CPU_TIME
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP
ORDER BY TOTAL_WORKER_TIME DESC

Query 2: Top 10 Average CPU Consuming Queries

SELECT TOP 10
TOTAL_WORKER_TIME ,
EXECUTION_COUNT ,
TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,
QT.TEXT AS QUERYTEXT
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT
ORDER BY QS.TOTAL_WORKER_TIME DESC ;

Query 3: Top 10 I/O Intensive Queries

SELECT TOP 10
TOTAL_LOGICAL_READS,
TOTAL_LOGICAL_WRITES,
EXECUTION_COUNT,
TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],
QT.TEXT AS QUERY_TEXT,
DB_NAME(QT.DBID) AS DATABASE_NAME,
QT.OBJECTID AS OBJECT_ID
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT
WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0
ORDER BY [IO_TOTAL] DESC 

Query 4: Execution Count of Each Query

SELECT QS.EXECUTION_COUNT,
QT.TEXT AS QUERY_TEXT,
QT.DBID,
DBNAME= DB_NAME (QT.DBID),
QT.OBJECTID,
QS.TOTAL_ROWS,
QS.LAST_ROWS,
QS.MIN_ROWS,
QS.MAX_ROWS
FROM SYS.DM_EXEC_QUERY_STATS AS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT
ORDER BY QS.EXECUTION_COUNT DESC

Remarks

  1. Statistics in the view are updated when a query is completed.
  2. User required VIEW SERVER STATE permission on the server.

Reference

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170924.2 | Last Updated 18 Apr 2013
Article Copyright 2013 by Rohitmssqlfun
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid