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