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:

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.

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.

raiserror

This entry was posted in SQL Server. Bookmark the permalink.

One Response to Debugging Long-Running SQL Queries

  1. Pingback: BPOTW – 2015-05-15 | SQL Notes From The Underground

Leave a Reply

Your email address will not be published. Required fields are marked *