Being a Database Developer or Administrator, often we work on Performance Optimization of the queries and procedures. It becomes very necessary that we focus on the right queries to get major benefits.
Recently I was working on a Performance Tuning project. I started working based on query list provided by client. Client was referring the user feedbacks and Long Running Query extract from SQL Server. But it was not helping much. The database had more than 1K stored procedures and approx. 1K other programmability objects. On top of that, there were multiple applications triggering inline queries as well.
I got a very interesting request from my client that “Can we get the top 100 queries running most frequently and taking more than a minute?”. This made me write my own query to get the list of queries being executed frequently and for duration greater/less than a particular time.
This query can also play a major role if you are doing multiple things to optimize the database (such as server / database setting changes, indexing, stats or code changes etc.) and would like to track the duration. You can create a job with this query and dump the output in some table. Job can be scheduled to run in certain frequency. Later, you can plot trend out of the data tracked.
This has really helped me a-lot in my assignment. I hope you’ll also find it useful.
/*
Following query will return the queries (along with plan) taking more than 1 minute
and how many time executed since last SQL restart. We'll also get the average execution time.
*/ ; WITH cte_stag
AS
(
SELECT plan_handle
, sql_handle
, execution_count
, (total_elapsed_time / NULLIF(execution_count, 0)) AS avg_elapsed_time
, last_execution_time
, ROW_NUMBER() OVER(PARTITION BY sql_handle, plan_handle ORDER BY execution_count DESC, last_execution_time DESC) AS RowID
FROM sys.dm_exec_query_stats STA
WHERE (total_elapsed_time / NULLIF(execution_count, 0)) > 60000 -- This is 60000 MS (1 minute). You can change it as per your wish.
)
-- If you need TOP few queries, simply add TOP keyword in the SELECT statement.
SELECT DB_NAME(q.dbid) AS DatabaseName
, OBJECT_NAME(q.objectid) AS ObjectName
, q.text
, p.query_plan
, STA.execution_count
, STA.avg_elapsed_time
, STA.last_execution_time
FROM cte_stag STA
CROSS APPLY sys.dm_exec_query_plan(STA.plan_handle) AS p
CROSS APPLY sys.dm_exec_sql_text(STA.sql_handle) AS q
WHERE STA.RowID = 1
AND q.dbid = DB_ID()
/*
Either select the desired database while running the query or supply the database name in quotes to the DB_ID() function.
<code>Note: Inline queries being triggered from application may not have the object name and database name. In case you are not getting the desired query in the result, try removing the filter condition on dbid</code>
*/ ORDER BY 5 DESC, 6 DESC