John Sansom

SQL Server DBA Blog

How to identify the most costly SQL Server queries using DMV’s

by 14 Comments

I was investigating a performance issue recently and so thought I would share the T-SQL query that I created with you.

There’s nothing new or magic here, the code snippet simply identifies the top 20 most expensive queries (currently cached) based on the cumulative CPU cost.

The query returns both the SQL Text from the sys.dm_exec_sql_text DMV and the XML Showplan data from the sys.dm_exec_query_plan DMV.

T-SQL to identify the Top 20 most costly queries in terms of Total CPU

SELECT TOP 20
	qs.sql_handle,
	qs.execution_count,
	qs.total_worker_time AS Total_CPU,
	total_CPU_inSeconds = --Converted from microseconds
	qs.total_worker_time/1000000,
	average_CPU_inSeconds = --Converted from microseconds
	(qs.total_worker_time/1000000) / qs.execution_count,
	qs.total_elapsed_time,
	total_elapsed_time_inSeconds = --Converted from microseconds
	qs.total_elapsed_time/1000000,
	st.text,
	qp.query_plan
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 (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

Dynamic Management Views (DMV’s) Used

  • sys.dm_exec_query_stats
    • Returns performance statistics for cached query plans. This contains one row per query plan so if a stored procedure or batch contains two SELECT statements you may get two rows here
  • sys.dm_exec_sql_text
    • Returns the text of the sql statement based on the SQL handle
  • sys.dm_exec_query_plan
    • Returns the showplan in XML format for a batch or module based on the plan handle

Reviewing the results

If you want to investigate a specific query plan further, you may view the graphical execution plan of a particular query by completing the following steps.

  1. Click the hyperlink in the query_plan column for the appropriate row. This will open the XMLShowplan data into a new tab within SQL Server Management Studio.
  2. Save the document by using the Save As option.
  3. Choose the “All File(*.*) fily type option and type .sqlplan as the file extension.
  4. Close the XML document.
  5. Open the file with the .sqlplan extension.

For more comprehensive instruction, see the reference below “How to: Save an Execution Plan in XML Format”.

Useful References