usp_SQLServerCarpenter_Tools_Get_Costliest_Queries

/*
Author:Brahmanand Shukla (SQLServerCarpenter.com)
Date:10-Jan-2020
Purpose:This procedure can get you the list of queries being executed frequently and for duration greater/less than a particular time. 
You may also find it very useful if you are doing multiple things to optimize the database (such as server or database setting changes, indexing, stats or code changes etc.) 
and you would like to track the duration to see if you are really making progress. 
You can create a job with this procedure 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.
*** 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 supplying @Use_DB_Filter as 0.
Example:
EXEC [dbo].[usp_SQLServerCarpenter_Tools_Get_Costliest_Queries]
@Duration_In_MS=60000
, @TOP_N_Queries=100
, @Use_DB_Filter=1
, @DB_Name=NULL
*/CREATE OR ALTER PROCEDURE [dbo].[usp_SQLServerCarpenter_Tools_Get_Costliest_Queries]
(
@Duration_In_MSBIGINT=60000/*This is duration filter. The default value is 1 minute. 
You can change it as per your wish while invoking the procedure. 
If left unchanged at the time of invoking the procedure, the default 1 minute shall be considered. */, @TOP_N_QueriesINT=9999999/*This is a filter to fetch the TOP N costliest queries in terms of execution_count and avg_elapsed_time stats counters.
Default value for this parameter is 9999999. With the default value in most of the cases, it will fetch all the queries. 
You can change it as per your wish while invoking the procedure. 
If left unchanged at the time of invoking the procedure, the default 9999999 shall be considered. */, @Use_DB_FilterBIT=1/*This parameter specifies whether to use the @DB_Name filter or not. 
Default is 1, which means use @DB_Name filter. 
The parameter can be set as your wish at the time of invokation of the procedure.
If left unchanged at the time of invoking the procedure, the default 1 shall be treated.*/, @DB_NameSYSNAME=NULL/*This is the Database filter. Default value is NULL, which means current DB name.
You can supply the desired database name at the time of invoking the procedure. 
The costliest queries will be fetched from the supplied database.
If left unchanged at the time of invoking the procedure, the current DB name shall be considered. */)
AS
BEGIN
SET NOCOUNT ON
IF NULLIF(RTRIM(LTRIM(@DB_Name)), '') IS NULL SET @DB_Name = DB_NAME();

/* 
Following query will return the queries (along with plan) taking more than supplied @Duration_In_MS values
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)) > @Duration_In_MS
)
SELECT TOP (@TOP_N_Queries) 
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 (@Use_DB_Filter = 0) OR (@Use_DB_Filter = 1 AND q.dbid = DB_ID(@DB_Name)) 
 ORDER BY 5 DESC, 6 DESC
END