Debugging Long-Running SQL Queries

The T-SQL PRINT statement lets you write to the Messages window in SQL Server Management Studio, which makes it a helpful aid when debugging SQL scripts and stored procedures.

Unfortunately there is one major issue with using PRINT. The messages output are only displayed when the query completes, so if you have a long-running query then you won’t be able to see any of the output until the end.

Here’s a quick example:

declare @i int
set @i = 0
print 'Starting'
while @i < 5 begin
	print @i
	waitfor delay '00:00:10'
	set @i += 1
end
print 'Complete'

Rubbish huh?

An alternative, and much better, approach is to use RAISERROR. This time the messages are printed immediately so you no longer have to wait for execution to complete to view them. Just be sure to use a non-fatal severity level – anything 10 or under is a good as it’s also unlikely to be picked up by any database monitoring systems.

declare @i int
declare @message varchar(100)
set @i = 0
RAISERROR('Starting', 10, 1) WITH NOWAIT
while @i < 5 begin
	set @message = cast(@i as nvarchar(100))
	RAISERROR(@message, 10, 1) WITH NOWAIT
	waitfor delay '00:00:10'
	set @i += 1
end
RAISERROR('Complete', 10, 1) WITH NOWAIT

As you can see, the logging syntax is slightly more verbose as RAISERROR takes a char/varchar but it’s a small price to pay for being able to see what’s going on as your queries run.