December 19, 2006 | SQL Server

A custom report for Management Studio : Show Blocking

Note that the techniques described in this post require the latest CTP of Service Pack 2 for SQL Server 2005 on the client (but the custom reports can run against a server that is at a lower build level).

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:

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.

File Attachment: ShowBlockers.rdlc.zip

8 comments on this post

    • Scott Whigham - May 24, 2007, 2:27 PM

      Great report – thanks for sharing, Aaron. I've added a link to this in our custom reports sub-forum: http://forums.learnsqlserver.com/SqlServerTopic109.aspx
      I hope you write some more and share them with us 🙂

    • Nazish Ali - August 12, 2007, 9:27 AM

      Yes Off Course Great Attempt.But My question is different .
      Question: I want to generate a report on runtime .like user select fields from menu and  he can also perform some basic operations of sum ,Average etc.
      when he click generate report ,I have to generate a rdl,rdlc on runtime to display his desire report.
      How I do it ?
      Other Solutions are also welcome.

    • John - August 20, 2007, 2:18 PM

      Very cool

    • Ranga - August 22, 2007, 11:18 PM

      Very interesting…Thanks..
      How do I pass the connect string for windows auth ? Also, can the servername be made as a parameter in the connection string ?

    • Ranga - August 22, 2007, 11:18 PM

      Very interesting…Thanks..
      How do I pass the connect string for windows auth ? Also, can the servername be made as a parameter in the connection string ?

    • Ranga - August 22, 2007, 11:18 PM

      Very interesting…Thanks..
      How do I pass the connect string for windows auth ? Also, can the servername be made as a parameter in the connection string ?

    • Ranga - August 22, 2007, 11:18 PM

      Very interesting…Thanks..
      How do I pass the connect string for windows auth ? Also, can the servername be made as a parameter in the connection string ?

    • Ranga - August 22, 2007, 11:18 PM

      Very interesting…Thanks..
      How do I pass the connect string for windows auth ? Also, can the servername be made as a parameter in the connection string ?

Comments are closed.