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

Continue Reading »

1 Comment »

Time is of the utmost importance to almost any performance test, but, in .net, it can require a bit of scaffolding code. A triplet of C# classes can make short work of measuring performance, and of collating and persisting the results, in a handy XML format.

In the wild, this is often done with code similar to the following:

   Stopwatch sw = new Stopwatch();

   sw.Start();
   // Do some work here
   sw.Stop();

   Debug.WriteLine(sw.Elapsed.TotalMilliseconds.ToString();

This is hardly the end of the world, but it’s a bit cumbersome, especially over the course of many tests.  Perhaps a more natural construction would be:

   using (DebugStopwatch countIndexOf = new DebugStopwatch("IndexOf")) {
      // Do some work here
   }

… with the results being harvested later.  The literal text “IndexOf” can be replaced with any label that will be meaningful when you analyze the timing data.  Persistence of test results is accomplished through the magic of static classes. Continue Reading »

No Comments »