script to find most expensive queries across all databases

Question

script to find most expensive queries across all databases

Sridhar-137443

Hall of Fame

Points: 3698
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);

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 · Answer 2

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(

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 · Answer 3

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)