Finding expensive queries can be challenging on SQL Server, this tutorial will help you in finding expensive queries on production. Using below DMVs:
SYS.DM_EXEC_QUERY_STATS
SYS.DM_EXEC_SQL_TEXT
SYS.DM_EXEC_CACHED_PLANS
SYS.DM_EXEC_TEXT_QUERY_PLAN
First is using average logical reads, execution count and total worker time. But still people can use other combinations as well. I have also explained how missing index can also help in finding and fixing expensive queries.
Script 1
CREATE function dbo.[fn_QueryTextFromHandle](@handle varbinary(64), @statement_start_offset int, @statement_end_offset int)
RETURNS @query_text TABLE (database_id smallint, object_id int, encrypted bit, query_text nvarchar(max))
begin
if @handle is not null
begin
declare @start int, @end int
declare @dbid smallint, @objectid int, @encrypted bit
declare @batch nvarchar(max), @query nvarchar(max)
-- statement_end_offset is zero prior to beginning query execution (e.g., compilation)
select
@start = isnull(@statement_start_offset, 0),
@end = case when @statement_end_offset is null or @statement_end_offset = 0 then -1
else @statement_end_offset
end
select @dbid = t.dbid,
@objectid = t.objectid,
@encrypted = t.encrypted,
@batch = t.text
from sys.dm_exec_sql_text(@handle) as t
select @query = case
when @encrypted = cast(1 as bit) then N'encrypted text'
else ltrim(substring(@batch, @start / 2 + 1, case when (@end - @start) / 2 >= 0 then (@end - @start) / 2 else 1000 end))
end
-- Found internal queries (e.g., CREATE INDEX) with end offset of original batch that is
-- greater than the length of the internal query and thus returns nothing if we don't do this
if datalength(@query) = 0
begin
select @query = @batch
end
insert into @query_text (database_id, object_id, encrypted, query_text)
values (@dbid, @objectid, @encrypted, @query)
end
return
end
Script 2
SELECT COALESCE(QP.DBID, ST.DBID) AS DATABASE_ID
,DB_Name(COALESCE(QP.DBID, ST.DBID)) AS DatabaseName
,'CACHED QUERIES' AS QueryState
,ROW_NUMBER() OVER (
PARTITION BY QS.PLAN_HANDLE ORDER BY QS.STATEMENT_START_OFFSET
) AS STATEMENT_ID
,QS.[SQL_HANDLE]
,QS.STATEMENT_START_OFFSET
,QS.STATEMENT_END_OFFSET
,QS.PLAN_GENERATION_NUM
,QS.PLAN_HANDLE
,QS.CREATION_TIME
,QS.LAST_EXECUTION_TIME
,QS.EXECUTION_COUNT
,QS.TOTAL_WORKER_TIME
,QS.LAST_WORKER_TIME
,QS.MIN_WORKER_TIME
,QS.MAX_WORKER_TIME
,QS.TOTAL_PHYSICAL_READS
,QS.LAST_PHYSICAL_READS
,QS.MIN_PHYSICAL_READS
,QS.MAX_PHYSICAL_READS
,QS.TOTAL_LOGICAL_WRITES
,QS.LAST_LOGICAL_WRITES
,QS.MIN_LOGICAL_WRITES
,QS.MAX_LOGICAL_WRITES
,QS.TOTAL_LOGICAL_READS
,QS.LAST_LOGICAL_READS
,QS.MIN_LOGICAL_READS
,QS.MAX_LOGICAL_READS
,QS.TOTAL_CLR_TIME
,QS.LAST_CLR_TIME
,QS.MIN_CLR_TIME
,QS.MAX_CLR_TIME
,QS.TOTAL_ELAPSED_TIME
,QS.LAST_ELAPSED_TIME
,QS.MIN_ELAPSED_TIME
,QS.MAX_ELAPSED_TIME
,QS.QUERY_HASH
,QS.QUERY_PLAN_HASH
,QS.TOTAL_ROWS
,QS.LAST_ROWS
,QS.MIN_ROWS
,QS.MAX_ROWS
,QS.STATEMENT_SQL_HANDLE
,QS.STATEMENT_CONTEXT_ID
,Object_Name(COALESCE(ST.OBJECTID, QP.objectid), COALESCE(QP.DBID, ST.DBID)) AS ObjectName
,ST.NUMBER
,ST.ENCRYPTED AS ISENCRYPTED
,ST.TEXT AS SQLTEXT
,QT.Query_Text
,QP.QUERY_PLAN
,CP.cacheobjtype
,CP.objtype
,CP.refcounts
,CP.usecounts
,CP.size_in_bytes
,CP.Parent_Plan_Handle
FROM (
SELECT QS.*
FROM SYS.DM_EXEC_QUERY_STATS QS
) QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.sql_handle) st
LEFT JOIN SYS.DM_EXEC_CACHED_PLANS CP ON CP.plan_handle = QS.plan_handle
OUTER APPLY dbo.fn_QueryTextFromHandle(QS.sql_handle, QS.statement_start_offset, QS.Statement_end_offset) AS Qt
OUTER APPLY SYS.DM_EXEC_TEXT_QUERY_PLAN(QS.plan_handle, QS.statement_start_offset, QS.statement_end_offset) QP