Identify most expensive queries from SQL Server plan cache

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

WITH XMLNAMESPACES (DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’),
core AS (
 SELECT
  eqp.query_plan AS [QueryPlan],
  ecp.plan_handle [PlanHandle],
  q.[Text] AS [Statement],
  n.value(‘(@StatementOptmLevel)[1]’, ‘VARCHAR(25)’) AS OptimizationLevel ,
  ISNULL(CAST(n.value(‘(@StatementSubTreeCost)[1]’, ‘VARCHAR(128)’) as float),0) AS SubTreeCost ,
  ecp.usecounts [UseCounts],
  ecp.size_in_bytes [SizeInBytes]
 FROM
  sys.dm_exec_cached_plans AS ecp
  CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp
  CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) AS q
  CROSS APPLY query_plan.nodes (‘/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple’) AS qn ( n )
)

SELECT TOP 100
 QueryPlan,
 PlanHandle,
 [Statement],
 OptimizationLevel,
 SubTreeCost,
 UseCounts,
 SubTreeCost * UseCounts [GrossCost],
 SizeInBytes
FROM
 core
ORDER BY
 GrossCost DESC
 –SubTreeCost DESC

Identify actively running queries in a specific database

SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END –
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              /***Ignore system spids***/
AND session_Id NOT IN (@@SPID)     /***Ignore this current statement***/
AND sp.dbid=DB_ID()   /***Remove this filter to get SQL activity across all databases***/
ORDER BY 1, 2