Posted under Performance & SQL
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