Software Development Blog

Our last post described how to see what another connection to a SQL Server is doing, with a user-defined function, dbo.InputBuffer. Given a spid, the function returns the SQL text being run on that connection - or the last command received from the connection, depending on which version you decided to use. Along the same lines, it can often be more useful to find the query plan.

Because the execution plan generated by a SQL command changes for many reasons ( schema changes, data volume, etc ), knowing what a spid is doing might not be as important as knowing how the spid is doing it. For example, an ETL might suddenly run for hours, as the result of a table scan … but why? The function below can help a developer or DBA troubleshoot performance and concurrency issues.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

If Exists (Select * From sys.objects Where object_id = object_id(N'dbo.QueryPlan') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
        Drop Function dbo.QueryPlan
GO

Create Function dbo.QueryPlan(@spid int) Returns xml As Begin
	Declare
		@plan_handle binary(32),
		@code xml

	Select @plan_handle = plan_handle
	From sys.dm_exec_requests
	Where session_id = @spid;

	Select @code = query_plan From sys.dm_exec_query_plan(@plan_handle);

	Return @code
End

This of course deserves the same warning as the input buffer function; the sys.dm_exec_requests view only tracks things that are running at the time you query it. If you’d like this function to return the most recent query plan used by a connection, change the code to look up the plan handle from sys.dm_exec_connections instead. Of course, there are good reasons for wanting a null return value for idle connections.

Because this is a scalar function, it can be used in a select list. We can extend the sample query introduced yesterday, to call dbo.QueryPlan and dbo.InputBuffer for all transactions involved in a blocking chain.

Select
	dbo.InputBuffer(spid) As RunningCode,
	dbo.QueryPlan(spid) As QueryPlan,
	*
From
	sys.sysProcesses
Where
	blocked != 0
	Or spid In (Select blocked From sys.sysProcesses)

fnbki8pjqe