Getting Real Time Feedback in SQL Stored Procedures

SQL Stored Procedures can be tough to debug sometimes. In typical scenarios, you have written a procedure that has been running fine for a few weeks, then all the sudden, it’s slower than molasses going up hill. How do you know which part of the procedure is the bottleneck? You could put a bunch of print statements between each section of the procedure, but the problem is that print statements do not show up until the procedure finishes.

Luckily, SQL Server has a nifty function called raiserror. Wait…raiserror?? “I Don’t want to raise an error”. Don’t worry! Raiserror can raise event messages that are not considered errors and instead act as print statements, but in real time!

To send back real time feedback in the middle of a procedure, simply write:

raiserror('This is the message I want to show up in real time!', 0, 1) with NOWAIT

The 0 and the 1 following the message report the error and severity, which in this case we want to report as 0 for the error since it’s not really an error we are reporting. The NOWAIT argument tells SQL Server to flush the buffer immediately and show the statement.

This statement can be extended further though to give you timestamps while you fire messages back to the client during a procedure run. Below is a simple stored procedure called “sp_Feedback” which takes a message and then runs the raiserror function to fire back a formatted message to the user. This procedure has helped me find bottlenecks quickly, and allowed me to re-tweak the sections of the procedure that need it, and then re-publish the procedure and get it back on the fast-fast-fast path. Have fun!

CREATE proc [dbo].[sp_Feedback]
	 @msg varchar(8000)
	,@row_cnt bigint = null
as
begin
	set @msg = concat(format(getdate(),'yyyy-MM-dd hh:mm:ss'),' -> ',@msg)
	
	if @row_cnt is not null
		set @msg = concat(@msg,' | rows affected: ',format(@row_cnt,'#,###'))
	
	raiserror(@msg,0,1) with nowait;

	-- exec sp_Feedback 'test'
	-- exec sp_Feedback 'test2', 50000
end

 

Getting Real Time Feedback in SQL Stored Procedures