Sometimes when you are running a long running script or stored procedure it’s nice to know whereabouts it has got to, often we put in print statements for this purpose. The problem is that half the time you won’t see the printed statement until SQL Server decides it can’t buffer anymore and shoves it back to the client (e.g. Management Studio) which may not be until it has finished in some cases.
Instead of using print in these situations the following syntax can be used:
raiserror ('This is my message, show it now!', 0, 1) with nowaitThis will have the same effect as if you had said:
print 'This is my message, show it now!'
Except it will send it to the client immediately.
Also note that sys.dm_exec_requests may also be used to find out exactly what statement is being executed at any particular instant and from within which stored procedure, UDF, etc.