none
Top 10 CPU, Disk IO, Memory consuming queries

    Question

  • Hi All,

    I need DMV queries to find top 10 queries by CPU,Memory,Disk IO, duration in sql server 2005.

    Regards

    Rahul 

    Wednesday, April 8, 2015 11:42 AM

Answers

  • Hi,

    http://www.sqlservercentral.com/blogs/mssqlfun/2013/04/03/dmv-4-find-top-most-expensive-cached-queries-sysdm_exec_query_stats/

    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.



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    • Marked as answer by Trehan11 Tuesday, April 14, 2015 8:31 AM
    Tuesday, April 14, 2015 7:26 AM

All replies

  • https://sqlserverperformance.wordpress.com/2009/05/21/sql-server-2008-diagnostic-script/

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Shanky_621MVP Wednesday, April 8, 2015 1:09 PM
    Wednesday, April 8, 2015 12:40 PM
    Answerer
  • Hi Rahul,

    To achieve your requirement, you can use the query below.

    SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
     ((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(qt.TEXT)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1),
    qs.execution_count,
    qs.total_logical_reads, qs.last_logical_reads,
    qs.total_logical_writes, qs.last_logical_writes,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
    qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
    qs.last_execution_time,
    qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    ORDER BY qs.total_logical_reads DESC -- logical reads
    -- ORDER BY qs.total_logical_writes DESC -- logical writes
    -- ORDER BY qs.total_worker_time DESC -- CPU time

    Reference
    http://blog.sqlauthority.com/2014/07/29/sql-server-ssms-top-queries-by-cpu-and-io/
    http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, April 14, 2015 7:05 AM
    Moderator
  • Hi,

    http://www.sqlservercentral.com/blogs/mssqlfun/2013/04/03/dmv-4-find-top-most-expensive-cached-queries-sysdm_exec_query_stats/

    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.



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    • Marked as answer by Trehan11 Tuesday, April 14, 2015 8:31 AM
    Tuesday, April 14, 2015 7:26 AM
  • Hi All,

    This has been very helpful.Thanks a ton everyone.

    Regards

    Rahul

    Tuesday, April 14, 2015 8:32 AM