SQL Server – Identify Expensive Queries using Michael K. Campbell Script

Prelude

This is hopefully the first one of many queries.

It uses Michael K. Campbell’s query to get a list of expensive queries besetting our SQL Instance.

Code

dbo.sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript

use master
go

/*
	drop procedure [dbo].[usp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]
*/
if object_id('[dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]') is null
begin

	exec('create procedure [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript] as ')  

end
go

alter procedure [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]
(
	  @filterDatabase   sysname      = null
	, @filterObjectType sysname		 = null
	, @orderBy	        varchar(60)  = null
	, @maxNumberofRows  int			= 300
)
as

begin

	/*
		Performance Tip: Find Your Most Expensive Queries
		Sep 13, 2012 by Michael K. Campbell in Practical SQL Server
		http://sqlmag.com/blog/performance-tip-find-your-most-expensive-queries
	*/

	/*
		Parameters:

			@filterDatabase
				a) Specific database name if you will like to filter on specific database

			@orderBy
				a) SubTreeCost
				b) GrossCost
				c) UseCount
				d) queryPlanHash

			@maxNumberofRows
				a) Maximum Number of Rows

	*/
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

	set nocount on;

	declare @filterDatabaseID int

	declare @tblCache TABLE
	(
	      [id]						int not null identity(1,1)
		, [QueryPlan]				xml not null
		, [PlanHandle]				varbinary(64) not null

		, [query_hash]				Binary(8)
		, [query_plan_hash]			Binary(8)

		, [Statement]				nvarchar(max)

		, OptimizationLevel			sysname null

		, [SubTreeCost]				float
		, [UseCount]				int
		, [SizeInBytes]				int

		, [database]				sysname	null
		, [objtype]					sysname not null
		, [object]					sysname null

		, attributeDBIDValue		int null

		, epaUserIDValue			int null
		, [epaUserIDUserContext]    sysname null

	)

	declare @queryPlanHashUnique TABLE
	(

		  [id] int not null
			identity(1,1)

		, [query_plan_hash]			Binary(8)

		, [numberofEntries]			int

	)

	if (@filterDatabase is not null)
	begin

		set @filterDatabaseID = db_id(@filterDatabase)

	end

	; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	, core 
	(

		  [QueryPlan]
		, [PlanHandle]

		, [query_hash]
		, [query_plan_hash]

		, [Statement]

		, OptimizationLevel

		, [SubTreeCost] 
		, [UseCount]
		, [SizeInBytes]

		, [database]
		, [objtype]
		, [object]

		, attributeDBIDValue

		, epaUserIDValue
		, [epaUserIDUserContext]

	)
	AS 
	(
		SELECT
				  eqp.query_plan AS [QueryPlan]

				, ecp.plan_handle [PlanHandle]

				, qs.query_hash

				, qs.query_plan_hash

				, q.[Text] AS [Statement]

				, OptimizationLevel
					= n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') --AS OptimizationLevel 

				, [SubTreeCost] 
					= ISNULL(CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') as float),0) --AS SubTreeCost 

				, [UseCount]
					= ecp.usecounts 

				, [SizeInBytes]
					= ecp.[size_in_bytes] 

				, [database]
					= case 
							when epaDBID.[value] = '32767' then 'Resource DB'
							else db_name
									(
										cast(epaDBID.[value] as int)
									)
					  end

				, ecp.objtype

				, [Object]
					= object_schema_name
						(
							  eqp.objectid
							, eqp.[dbid]
						)
						+ '.'
						+ object_name
						(
							  eqp.objectid
							, eqp.[dbid]
						)

				, cast
					(
						epaDBID.[value] as int
					)

				, cast
					(
						epaUserID.[value]
							as int
					)

				, [epaUserNameContext]
					= case

							when (cast(epaUserID.[value] as int) = -2) 
									then 'Generic'

							else user_name
									(
										  cast(epaUserID.[value] as int)
									)

					  end

		FROM sys.dm_exec_cached_plans AS ecp

		LEFT OUTER JOIN sys.dm_exec_query_stats as qs

				on ecp.[plan_handle] = qs.[plan_handle]

		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 )

		OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) AS epaDBID

		OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) AS epaUserID

		where epaDBID.[attribute] = 'dbid'

		and   (
					   ( @filterDatabaseID is null )

					or ( epaDBID.[value] = @filterDatabaseID )  

			  )

		and   epaUserID.[attribute] = 'user_id'

		and   q.[text] not like '%sys%'

		and   (
					   ( ecp.objtype = @filterObjectType )
					or ( @filterObjectType is null )
			  )	

		
	)
	insert into @tblCache
	(
		  [QueryPlan]
		, [PlanHandle]

		, [query_hash]
		, [query_plan_hash]

		, [Statement]

		, OptimizationLevel

		, [SubTreeCost] 
		, [UseCount]
		, [SizeInBytes]

		, [database]
		, [objtype]
		, [object]

		, attributeDBIDValue

		, epaUserIDValue
		, [epaUserIDUserContext]

	)

	select 
		  [QueryPlan]
		, [PlanHandle]

		, [query_hash]
		, [query_plan_hash]

		, [Statement]

		, OptimizationLevel

		, [SubTreeCost] 
		, [UseCount]
		, [SizeInBytes]

		, [database]
		, [objtype]
		, [object]

		, attributeDBIDValue

		, [epaUserIDValue]
		, [epaUserIDUserContext]
	from   core



	insert into @queryPlanHashUnique
	(
		  [query_plan_hash]
		, [numberofEntries]	
	)

	select 
			  tblC.query_plan_hash
			, count(*)
	from	@tblCache tblC
	group by
			tblC.query_plan_hash
	order by
			count(*) asc

	SELECT TOP 
				(
					 isNull
					 ( 
						  @maxNumberofRows
						, 200
					)
				)

			  core.QueryPlan

			, core.[database]

			, core.objtype

			, core.[object]

			, PlanHandle

			, [Statement]

			, OptimizationLevel

			, SubTreeCost

			, UseCount

			, [GrossCost]	
				= [SubTreeCost] * [UseCount] 

			, [SizeInBytes]
				= SizeInBytes

			--, core.attribute

			--, core.[value]
			, [epaUserIDValue]

			, [epaUserIDUserContext]

            , [existSort]
                = core.QueryPlan.exist('//Sort')
 
            , [existWarning]
                = core.QueryPlan.exist('//Warning')
 
            , [existMissingIndexes]
                = core.QueryPlan.exist('//MissingIndexes')

			, core.[query_hash]

			, core.[query_plan_hash]

			, tblQPHU.numberofEntries


	FROM @tblCache core

	left outer join @queryPlanHashUnique tblQPHU

			on core.query_plan_hash = tblQPHU.query_plan_hash

	where (

				( 

					( 
						core.[database] 
							= isNull(@filterDatabase, core.[database]  )
					)

				)
		  )

	ORDER BY
			case

				when (@orderBy = 'SubTreeCost')	
					then cast(SubTreeCost as varchar(60))

				when (@orderBy = 'GrossCost')	
					then cast(
								([SubTreeCost] * [UseCount] ) 
								as varchar(60)
							)

				when (@orderBy = 'UseCount')	
					then cast(
								[UseCount]  
									as varchar(60)
							)

				when (@orderBy = 'queryPlanHash')	
					then cast(
								tblQPHU.numberofEntries
									as varchar(60)
							)
						
				else (
						[SubTreeCost] * [UseCount]
					 )
		
			end DESC 

	

end
go

exec sys.sp_MS_marksystemobject '[dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]'
go



Lab

Sample Invocation – 001




declare @filterDatabase    sysname      = null
declare @orderBy	       varchar(60)  = null
declare @maxNumberofRows   int			= 300

set @filterDatabase  = null
set @orderBy	     = null
set @maxNumberofRows = 300

set @filterDatabase = 'OdysseyExpress'
set @orderBy = 'SubTreeCost'


exec  [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]

		  @filterDatabase  = @filterDatabase 
		, @orderBy = @orderBy
		, @maxNumberofRows = @maxNumberofRows


Sample Invocation – HPALM



declare @filterDatabase    sysname      = null
declare @orderBy	       varchar(60)  = null
declare @maxNumberofRows   int			= 300

set @filterDatabase  = null
set @orderBy	     = null
set @maxNumberofRows = 300

set @orderBy = 'SubTreeCost'
set @orderBy = 'queryPlanHash'


exec  [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]

		  @filterDatabase  = @filterDatabase 
		, @orderBy = @orderBy

Output

hpalm-20161217-1133am

Explanation

  1. List above are entries for an HP-ALM database
    • The user id is 6 and the user_name returned is ##MS_AgentSigningCertificate##
    • And, the database is again an HP User DB
    • Unfortunately, because we can not pass a db_id to user_name and so we are not getting the actual user name

 

Connect Items

  1. Connect ID :- 3116749
    Title :- Please extend the user_name function to accept dbid
    Type :- Suggestion
    Status :- Active
    Opened :- 2016-Dec-17th
    Detail :-
    Both object_name and object_schema_name accept dbid and are more useful as they can be queried from the master db.
    This is often the case when accessing dmv and query plans.
    I have been using the sys.dm_exec_plan_attributes dmv for a while, mostly to get dbid (database name).
    Yesterday discovered that it exposes the user_id attribute, as well.

    It will be nice to be able to combine the user_id with the db_id and get back the actual username
    Link

One thought on “SQL Server – Identify Expensive Queries using Michael K. Campbell Script

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s