Query to Determine Top Queries that Cause Write I/O Pressure

Description

Query that utilizes DMVs to determine top queries that cause write I/O pressure for the entire system or for a specific database

Script

SELECT TOP 10
qs.execution_count AS Execution_Count, –Number of times that the plan has been executed since it was last compiled.
qs.total_elapsed_time/qs.execution_count/1000000.0 AS Avg_Elapsed_Time_Sec, –Average elapsed time for completed executions of this plan.
qs.total_elapsed_time/1000000.0 AS Total_Elapsed_Time_Sec, –Total elapsed time for completed executions of this plan.
qs.total_worker_time/qs.execution_count/1000000.0 AS Avg_Worker_Time_Sec, –Average amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_worker_time/1000000.0 AS Total_Worker_Time_Sec, –Total amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_logical_reads/qs.execution_count AS Avg_Logical_Reads_Sec, –Average number of logical reads performed by executions of this plan since it was compiled.
qs.total_logical_reads AS Total_Logical_Reads, –Total number of logical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads/qs.execution_count AS Avg_Physical_Reads_Sec, –Average number of physical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads AS Total_Physical_Reads, –Total number of physical reads performed by executions of this plan since it was compiled.
qs.total_logical_writes/qs.execution_count AS Avg_Logical_Writes_Sec, –Average number of logical writes performed by executions of this plan since it was compiled.
qs.total_logical_writes AS Total_Logical_Writes, –Total number of logical writes performed by executions of this plan since it was compiled.
qs.execution_count/DATEDIFF(SECOND,qs.creation_time,GETDATE()) AS Calls_Per_Sec,
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) AS Individual_Query,
o.name AS Object_Name,
DB_NAME(qt.dbid) AS Database_Name,
qp.query_plan AS 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
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
qt.dbid = DB_ID() — Comment if querying for the entire system
ORDER BY
qs.total_logical_writes DESC — Queries causing write I/O pressure

Sample Output

Query to Determine Top Queries that Cause Read I/O Pressure

Description

Query that utilizes DMVs to determine top queries that cause read I/O pressure for the entire system or for a specific database. 

Script

SELECT TOP 10
qs.execution_count AS Execution_Count, –Number of times that the plan has been executed since it was last compiled.
qs.total_elapsed_time/qs.execution_count/1000000.0 AS Avg_Elapsed_Time_Sec, –Average elapsed time for completed executions of this plan.
qs.total_elapsed_time/1000000.0 AS Total_Elapsed_Time_Sec, –Total elapsed time for completed executions of this plan.
qs.total_worker_time/qs.execution_count/1000000.0 AS Avg_Worker_Time_Sec, –Average amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_worker_time/1000000.0 AS Total_Worker_Time_Sec, –Total amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_logical_reads/qs.execution_count AS Avg_Logical_Reads_Sec, –Average number of logical reads performed by executions of this plan since it was compiled.
qs.total_logical_reads AS Total_Logical_Reads, –Total number of logical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads/qs.execution_count AS Avg_Physical_Reads_Sec, –Average number of physical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads AS Total_Physical_Reads, –Total number of physical reads performed by executions of this plan since it was compiled.
qs.total_logical_writes/qs.execution_count AS Avg_Logical_Writes_Sec, –Average number of logical writes performed by executions of this plan since it was compiled.
qs.total_logical_writes AS Total_Logical_Writes, –Total number of logical writes performed by executions of this plan since it was compiled.
qs.execution_count/DATEDIFF(SECOND,qs.creation_time,GETDATE()) AS Calls_Per_Sec,
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) AS Individual_Query,
o.name AS Object_Name,
DB_NAME(qt.dbid) AS Database_Name,
qp.query_plan AS 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
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
qt.dbid = DB_ID() — Comment if querying for the entire system
ORDER BY
qs.total_physical_reads DESC — Queries causing read I/O pressure

Sample Output

Query to Determine Top Queries that Cause Memory Pressure

Description

Query that utilizes DMVs to determine top queries that cause memory pressure for the entire system or for a specific database. 

Script

SELECT TOP 10
qs.execution_count AS Execution_Count, –Number of times that the plan has been executed since it was last compiled.
qs.total_elapsed_time/qs.execution_count/1000000.0 AS Avg_Elapsed_Time_Sec, –Average elapsed time for completed executions of this plan.
qs.total_elapsed_time/1000000.0 AS Total_Elapsed_Time_Sec, –Total elapsed time for completed executions of this plan.
qs.total_worker_time/qs.execution_count/1000000.0 AS Avg_Worker_Time_Sec, –Average amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_worker_time/1000000.0 AS Total_Worker_Time_Sec, –Total amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_logical_reads/qs.execution_count AS Avg_Logical_Reads_Sec, –Average number of logical reads performed by executions of this plan since it was compiled.
qs.total_logical_reads AS Total_Logical_Reads, –Total number of logical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads/qs.execution_count AS Avg_Physical_Reads_Sec, –Average number of physical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads AS Total_Physical_Reads, –Total number of physical reads performed by executions of this plan since it was compiled.
qs.total_logical_writes/qs.execution_count AS Avg_Logical_Writes_Sec, –Average number of logical writes performed by executions of this plan since it was compiled.
qs.total_logical_writes AS Total_Logical_Writes, –Total number of logical writes performed by executions of this plan since it was compiled.
qs.execution_count/DATEDIFF(SECOND,qs.creation_time,GETDATE()) AS Calls_Per_Sec,
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) AS Individual_Query,
o.name AS Object_Name,
DB_NAME(qt.dbid) AS Database_Name,
qp.query_plan AS 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
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
qt.dbid = DB_ID() — Comment if querying for the entire system
ORDER BY
qs.total_logical_reads DESC — Queries causing memory pressure

Sample Output

Query to Determine Top Queries that Cause CPU Pressure

Description

Query that utilizes DMVs to determine top queries that cause CPU pressure for the entire system or for a specific database.

Script

SELECT TOP 10
qs.execution_count AS Execution_Count, –Number of times that the plan has been executed since it was last compiled.
qs.total_elapsed_time/qs.execution_count/1000000.0 AS Avg_Elapsed_Time_Sec, –Average elapsed time for completed executions of this plan.
qs.total_elapsed_time/1000000.0 AS Total_Elapsed_Time_Sec, –Total elapsed time for completed executions of this plan.
qs.total_worker_time/qs.execution_count/1000000.0 AS Avg_Worker_Time_Sec, –Average amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_worker_time/1000000.0 AS Total_Worker_Time_Sec, –Total amount of CPU time that was consumed by executions of this plan since it was compiled.
qs.total_logical_reads/qs.execution_count AS Avg_Logical_Reads_Sec, –Average number of logical reads performed by executions of this plan since it was compiled.
qs.total_logical_reads AS Total_Logical_Reads, –Total number of logical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads/qs.execution_count AS Avg_Physical_Reads_Sec, –Average number of physical reads performed by executions of this plan since it was compiled.
qs.total_physical_reads AS Total_Physical_Reads, –Total number of physical reads performed by executions of this plan since it was compiled.
qs.total_logical_writes/qs.execution_count AS Avg_Logical_Writes_Sec, –Average number of logical writes performed by executions of this plan since it was compiled.
qs.total_logical_writes AS Total_Logical_Writes, –Total number of logical writes performed by executions of this plan since it was compiled.
qs.execution_count/DATEDIFF(SECOND,qs.creation_time,GETDATE()) AS Calls_Per_Sec,
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) AS Individual_Query,
o.name AS Object_Name,
DB_NAME(qt.dbid) AS Database_Name,
qp.query_plan AS 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
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
qt.dbid = DB_ID() — Comment if querying for the entire system
ORDER BY
qs.total_worker_time DESC — Queries causing CPU pressure

Sample Output