Since the early betas of "Yukon," which later became SQL Server 2005, I remember being very eager to play with the reports that Management Studio provided (e.g. Performance - Top Queries by Average IO). These reports promised to give us a very quick way to analyze a snapshot of server health and -- more importantly -- diagnose issues; sometimes before they became issues.

I soon realized that some of these reports either gave too much information or not enough. For example, take the Activity - All Blocking Transactions report. A lot of the information here is meaningless to me when I am trying to figure out who is holding up our OLTP system. There is a ton of data here, but it takes many clicks to get to the useful parts, and often I will find that the textbox is empty (--) for either the Blocking SQL Statement or the Blocked SQL Statement. And yes, the SPID is provided, but it is a lot of work to backtrack and manually obtain information about the session_ids involved (e.g. DBCC INPUTBUFFER).

During the beta I asked if we could customize the reports, or write our own; the response was that this capability would not ship with RTM -- maybe SP1? SP1 came and went. Finally, SP2 is on the horizon, and the capability to create our own custom reports (using RDL) has been introduced to us in the CTPs for this next service pack.

So guess what I set out to do first? I created my own blocking report that includes the information I deem most useful to finding the root cause and stamping it out. Why should I spend all my time typing out sp_who2, and sp_lockinfo, and select * from sys.dm_exec_requests, and DBCC INPUTBUFFER, when I can create a report that does most of that work for me in a couple of clicks?

Based loosely on the procedure I created in my article, "Can I create a more robust and flexible version of sp_who2 using SQL Server 2005's DMVs?", I wrote the following stored procedure that would be consumed by a custom report: .ab_code { padding:10px; background:#ccc; font-family:lucida console,courier new }

USE master;
GO

CREATE PROCEDURE dbo.Custom_BlockerReport
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE
		@spid BIGINT,
		@sql NVARCHAR(MAX);
		
	SELECT 
		spid = CONVERT(BIGINT, s.session_id),
		blocker = CONVERT(NVARCHAR(12), 
			COALESCE(RTRIM(NULLIF(r.blocking_session_id, 0)), '.')),
		[database] = COALESCE(db_name(r.database_id), ' '),
		command = CONVERT(NVARCHAR(128), COALESCE(r.command, ' ')),
		[status] = CONVERT(NVARCHAR(128), UPPER(COALESCE(r.status, s.status))),
		cpu = CONVERT(BIGINT, COALESCE(s.cpu_time,0) + COALESCE(r.cpu_time,0)),
		memory = CONVERT(BIGINT, COALESCE(r.granted_query_memory, 0)),
		reads = CONVERT(BIGINT, COALESCE(s.reads,0) + COALESCE(r.reads,0)),
		writes = CONVERT(BIGINT, COALESCE(s.writes,0) + COALESCE(r.writes,0)),
		trancount = CONVERT(BIGINT, COALESCE(r.open_transaction_count, 0)),
		perccomp = CONVERT(BIGINT, COALESCE(r.percent_complete, 0.00)),
		SQLText = x.text,
		hostname = CONVERT(NVARCHAR(128), COALESCE(s.[host_name], ' ')),
		ip = COALESCE(c.client_net_address, '-'),
		[application] = s.[program_name]
	INTO
		#spids
	FROM
		master.sys.dm_exec_sessions s
	INNER JOIN
		master.sys.dm_exec_requests r
	ON
		s.session_id = r.session_id
	LEFT OUTER JOIN
	(
		SELECT r.session_id,
		t.text
		FROM master.sys.dm_exec_requests r
		CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
	) x
	ON
		s.session_id = x.session_id
	LEFT OUTER JOIN
		master.sys.dm_exec_connections c
	ON
		s.session_id = c.session_id
	WHERE 
		r.blocking_session_id > 0
		OR EXISTS
		(
			SELECT 1
				FROM master.sys.dm_exec_requests r2
				WHERE r.session_id = r2.blocking_session_id
		);	
		
	IF @@ROWCOUNT > 0
	BEGIN	
		
		CREATE TABLE #dbcc
		(
			col1 SYSNAME,
			col2 SYSNAME,
			EventInfo NVARCHAR(MAX)
		);
		
		DECLARE c_spids CURSOR 
		LOCAL FORWARD_ONLY STATIC READ_ONLY
		FOR 
			SELECT Spid
				FROM #spids s1
				WHERE LEN(SQLText) = 0
				ORDER BY Spid;

		OPEN c_spids;
		
		FETCH NEXT FROM c_spids INTO @spid;
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @sql = 'DBCC INPUTBUFFER('+RTRIM(@spid)+');';

			INSERT #dbcc(col1,col2,EventInfo) 
				EXEC master.dbo.sp_executesql @sql;

			UPDATE #dbcc
			SET EventInfo = 
				LEFT(LTRIM(
				REPLACE(REPLACE(REPLACE(REPLACE(
				SQLText, '  ', ' '), 
				CHAR(13), ' '),CHAR(10), ' '),
				CHAR(9), ' ')), 128);

			UPDATE #Spids
			SET SQLText = 
			(
				SELECT 
					COALESCE(EventInfo, '')
					FROM #dbcc
			)
			WHERE
				spid = @Spid;

			TRUNCATE TABLE #dbcc;
				
			FETCH NEXT FROM c_spids INTO @spid;
		END
		
		CLOSE c_spids;
		DEALLOCATE c_spids;
		
		DROP TABLE #dbcc;
	END

	SELECT
		spid,
		blocker,
		[database],
		command,
		[status],
		cpu,
		memory,
		reads,
		writes,
		trancount,
		perccomp,
		SQLText,
		hostname,
		ip,
		[application]
	FROM
		#Spids
	ORDER BY
		spid;		

	DROP TABLE #spids;	
END

You can feel free to augment the procedure to drop some of the columns above, and add others (for example, login_name, last_request_start_time, transaction_isolation_level) that you feel are relevant and that can come from the dynamic management views. [Note that I did not fully test the procedure for case-sensitive collations.]

The fun part is getting Management Studio to consume this report. Without a visual designer, the RDL for the report can become quite tedious to author. So you may not want to rush into changing the layout, never mind the columns included. I also found some strangely repetitive tasks that resembled what CSS took care of in HTML... for example, why can't I apply some kind of class to all rows, or all cells, instead of having to repeat the attribute values every single time? Perhaps I just don't know enough about RDL yet.

Anyway, I've done all the grunt work, and the RDL can be found in the attachment to this post (ShowBlockers.rdlc.txt). It's roughly 40kb of uninteresting XML, so I didn't think it would be proper to visualize it.

Save the attachment with an .rdl extenstion (December CTP) or an .rdlc extension (November CTP). Initially Management Studio would only accept .rdlc files as custom reports, but from the December CTP onward they will need to be .rdl.

Be aware that you will need to change line 8, which currently shows:

<ConnectString>
  data source={server name};
  initial catalog=master;
  user id={username};
  password={password}
</ConnectString>

Obviously, you'll want to configure that for the data source you're interested in. In my case, this issue was only prevalent on a specific server under my charge; many RDL tutorial sites will show you how to adapt the code to take the connection information from the context of Management Studio (or, maybe that technique is not widely known yet?).

Now, in Management Studio, right-click a server node in Object Explorer, choose Reports > Custom reports ... browse to the .rdl/.rdlc file you created above, and select Open. You should see something like this:

(click image to enlarge)

Yes, I apologize, you have to scroll over to the right to get more specific information about hostname/ip/application name, and maybe even a portion of the actual SQLText, depending on your screen resolution. I'll write a better report when I get deeper into RDL.

If your system is experiencing blocking, of course, you will have actual rows. And note that once you select a report, it will be in the context menu for Reports; in other words, you won't have to browse again. But I've observed funny behavior when refreshing or re-opening a report that has changed, compared to browsing for it. So I'm not sure if there is some funny caching going on there.

I'll leave it to you to play with it. Please let me know what you think about this feature and about the way I've implemented this report. But take it easy on me; after all, this was my first try.