Ideally, SQL queries are executed really fast (sub-second). However there are times when it takes many seconds, even many minutes to execute each step. We have traditionally used the PRINT statement in SQL to output trace type information to the console so we can see what's happening.

Recently I noticed that the PRINT statement output wasn't coming out when I expected it should. In doing some research on the issue, I discovered that the PRINT statement output is queued and batched rather than real-time.

The solution for this problem is to use the RAISEERROR statement instead. Here is an example:

DECLARE @msg NVARCHAR(MAX) = 'status message'
RAISERROR (@msg, 0, 1) WITH NOWAIT

The severity of 0 tells SQL that everything is still fine, and NOWAIT instruction tells SQL not to queue it but to output immediately. The 1 is a locator that can be used to tell someone where to find the source of a particular message, if they are kept unique throughout the code.

So, for long-running queries, use RAISEERROR and get those status messages out in real-time!