Print Statement in SQL procedure should affect Performance ?

Question

I am using SQlserver procedure and i have habit of using of Print Statement in PRocedure to differentiate the code of PRocedure. I have almost 200-250 procedure in my DB. Should print statement affect the Performance? I am working on MultiUser Windows application.

Answer 1

A handful of PRINT statements will have a negligible effect on performance - PRINTs in loops that are executed many thousands of times, however, may well cause performance issues.

It's unlikely that if you're experiencing performance problems with your queries that PRINT is the culprit - however, if in doubt, try some experiments!

Answer 2

Essentially there is an additional overhead introduced to the overall performance of your given process because you are asking SQL Server to pass output to the client, which you would not be doing otherwise (i.e. without the use of the PRINT statement).

Quantifying an additional overhead, in terms of will it have an impact on overall performance, will depend on just how much of an additional overhead you are applying.

So in other words, go and test it out.

Answer 3

I found when running the below on my desktop that commenting out the print knocked about 15 seconds off the execution time meaning the average impact was 15µs in my simple test. RAISERROR WITH NOWAIT added an average of just over double that.

DECLARE @date DATETIME2

DECLARE 
  @count INT

SET @count = 1

SET @date = SYSUTCDATETIME()

WHILE @count < 1000000
BEGIN
--RAISERROR ('%d',0,1, @count) WITH NOWAIT
--PRINT @count
  SET @count = @count + 1
END

SELECT DATEDIFF(MICROSECOND, @date, SYSUTCDATETIME()) / 1000000.