Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have simple query

WHILE ( @counter < 50 )
    BEGIN
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

When I look at messages, the PRINT command does not return row every loop but shows up as multiple rows at ones. I have added one line and now it shows up 1 line at the time and the the whole process is much faster.

WHILE ( @counter < 50 )
    BEGIN
        WAITFOR DELAY '00:00:00.5' --< This line
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

so why is it when I have added .5 second wait time to each loop iteration it actually runs faster and allows the print message to come out one at the time?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

PRINT output is buffered. If you replace PRINT ... with RAISERROR ... WITH NOWAIT you will see results as they happen.

DECLARE @msg VARCHAR(MAX);
DECLARE @counter INT;
WHILE (@counter < 50)
BEGIN
    BEGIN TRANSACTION
    SET @msg = 'Counter = ' + CONVERT(VARCHAR(4), @counter);
    RAISERROR (@msg, 0, 1) WITH NOWAIT;
    ;WITH CTE
    AS (
        SELECT TOP(50000) * 
        FROM MySchema.MyTable
        )
    DELETE FROM CTE OPTION (MAXDOP 1);
    SET @counter += 1;
    COMMIT TRAN
END
share|improve this answer
    
So why does adding WAITFOR DELAY allows it to come as it happens or close to it? –  Vladimir Oselsky May 15 at 16:41
1  
I am surmising from the Books Online documentation, where it states that WAITFOR runs on a separate thread. I imagine the context switch to the WAITFOR thread flushes the PRINT buffer prior to running. I could easily be wrong. –  Max Vernon May 15 at 17:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.