Technical Article

Find Top 5 expensive Queries from a Write IO perspective

,

Recently I wrote this query that helps us find the most expensive queries from a write IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 sqltxt.text AS 'SQL', qstats.total_logical_writes AS [Total Logical Writes],
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time, GetDate()) AS 'Logical Writes Per Second',
qstats.execution_count AS 'Execution Count',
qstats.total_worker_time AS [Total Worker Time],
qstats.total_worker_time/qstats.execution_count AS [Average Worker Time],
qstats.total_physical_reads AS [Total Physical Reads],
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS 'TimeInCache in Hours',
qstats.total_physical_reads/qstats.execution_count AS 'Average Physical Reads',
db_name(sqltxt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS sqltxt
WHERE sqltxt.dbid = db_id()
ORDER BY qstats.total_logical_writes DESC

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.2 (5)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.2 (5)

You rated this post out of 5. Change rating

Related content

External Article

The SQL Server 2016 Query Store: Accessing Query Store Information Using DMVs

The SQL Server 2016 Query Store provides several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. In using the Query Store to ensure that performance is as good as possible, it isn't long before it becomes important to be familiar with the DMVs that are associated with the query store, and using them in custom queries.

2016-02-09

3,612 reads