sql-server performance optimization by removing print statements

Question

We're going through a round of sql-server stored procedure optimizations. The one recommendation we've found that clearly applies for us is 'SET NOCOUNT ON' at the top of each procedure. (Yes, I've seen the posts that point out issues with this depending on what client objects you run the stored procedures from but these are not issues for us.)

So now I'm just trying to add in a bit of common sense. If the benefit of SET NOCOUNT ON is simply to reduce network traffic by some small amount every time, wouldn't it also make sense to turn off all the PRINT statements we have in the stored procedures that we only use for debugging?

I can't see how it can hurt performance. OTOH, it's a bit of a hassle to implement due to the fact that some of the print statements are the only thing within else clauses, so you can't just always comment out the one line and be done. The change carries some amount of risk so I don't want to do it if it isn't going to actually help.

But I don't see eliminating print statements mentioned anywhere in articles on optimization. Is that because it is so obvious no one bothers to mention it?

Answer 1

If you are calling PRINT inside of a loop (like printing while cutsoring through rows), then yes, you could save time and it could be quite significant if you are looping through enough records. Short of that, unless your stored procedures themselves are being calle repetitively in a loop, I wouldnpersonally leave the PRINT statements in there. Their performance, for small strings, should generally be negligible.

Answer 2

I had a problem with large amounts of diagnostic data being printed in a stored proc which caused an error when called from ADO.NET (no error when called directly). I can't recall the error, unfortunately, but I added a flag so the diagnostic messages were wrapped in conditionals to avoid them being emitted in production.