script to find most expensive queries across all databases

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  April 23, 2014 at 8:16 am

  #396649

  Hi, I have the following query that returns most expensive queries. The issue with this query is that it seems to be running against one database. I am looking for a script that returns most expensive queries across all databases. Would there a script for it?

  SELECT TOP(50) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],

  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,

  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

  qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count

  AS [avg_elapsed_time], qs.cached_time, qs.last_execution_time, qs.last_elapsed_time / (1000 * 1000.00) as last_elapsed_time, db_name(qs.database_id)

  FROM sys.procedures AS p WITH (NOLOCK)

  INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

  ON p.[object_id] = qs.[object_id]

  where qs.last_execution_time > DATEADD(HOUR, -4, getdate())

  ORDER BY qs.last_elapsed_time DESC OPTION (RECOMPILE);

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 23, 2014 at 8:30 am

  #1708137

  That one will work. Just remove the reference to sys.procedures and instead cross apply to sys.dm_exec_sql_text for the procedure's definition.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  April 23, 2014 at 8:44 am

  #1708142

  Here's an alternative query you could try. Keep in mind it could be a heavy load on a production box.

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  GO

  /*

  For statement optimization levels the QO performs before Selecting a Plan

  There are a number of methods, the documented one being to compare the contents of sys.dm_exec_query_optimizer_info

  before and after compilation.

  An undocumented (but reasonably well-known) alternative that works on all currently supported versions

  of SQL Server is to enable trace flags 8675 and 3604 while compiling the query.

  */

  SET NOCOUNT ON;

  WITH XMLNAMESPACES

  (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

  SELECT query_plan, plan_handle,sql_handle,execution_count

  ,n.value('(@StatementOptmEarlyAbortReason)[1]', 'VARCHAR(50)') AS StatementEarlyAbort

  ,n.value('(@StatementOptmLevel)[1]', 'VARCHAR(50)') AS StatementLevel

  ,n.value('(@StatementSubTreeCost)[1]','float') AS SubTreeCost

  ,n.value('(@StatementText)[1]','VARCHAR(MAX)') as StatementTxt

  ,ROW_NUMBER() OVER (PARTITION BY plan_handle ORDER BY (SELECT NULL)) AS ORowNum

  INTO #planeval

  FROM

  (

  SELECT query_plan,plan_handle,sql_handle,execution_count

  ,ROW_NUMBER() OVER (PARTITION BY plan_handle ORDER BY (SELECT NULL)) AS RowNum

  FROM (

  SELECT DISTINCT plan_handle,sql_handle,execution_count

  FROM sys.dm_exec_query_stats

  ) AS qs

  OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp

  ) AS tab (query_plan,plan_handle,sql_handle,execution_count,RowNum)

  CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)

  WHERE q.n.exist('//StmtSimple') = 1

  --AND q.n.exist('(@StatementOptmEarlyAbortReason)[1]') = 1

  --AND q.n.value('(@StatementOptmEarlyAbortReason)[1]', 'VARCHAR(50)') <> 'GoodEnoughPlanFound'

  AND q.n.value('(@StatementOptmLevel)[1]', 'VARCHAR(50)') <> 'Trivial'

  AND RowNum = 1;

  SELECT pe.query_plan,

  (SELECT

  REPLACE

  (--replace much?

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

  CONVERT

  (

  NVARCHAR(MAX),

  N'--' + NCHAR(13) + NCHAR(10) + ist.StatementTxt + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2

  ),

  NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),

  NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),

  NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),

  NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),

  NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),

  NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),NCHAR(0),N''

  ) AS [processing-instruction(query)]

  FROM #planeval AS ist

  FOR XML PATH(''),TYPE) as StatementTxt

  ,pe.plan_handle,pe.sql_handle,pe.execution_count,pe.StatementEarlyAbort,pe.StatementLevel

  ,pe.SubTreeCost AS SubTreeCost

  FROM #planeval pe

  WHERE ORowNum = 1

  ORDER BY SubTreeCost DESC;

  DROP TABLE #planeval;

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• glynn.seymour

  SSC Rookie

  Points: 40

  More actions

  May 2, 2014 at 3:09 am

  #1710282

  Some cool code on this thread.

  For an involuntary DBA such as myself, I found the Performance Dashboard reports invaluable, free easy to install and gives you some great pointers for tuning (maybe not optimal, but enough for my purposes)