• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

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

May 15, 2009 by John Sansom 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

  • How to: Save an Execution Plan in XML Format
  • Find Top N costly query plans in adhoc batches or modules…
  • SQL Snippet: SQL Server Wait Types
  • DMV Query to identify all active SQL Server Sessions

Filed Under: Performance Tuning Tagged With: CPU, DMV

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy