03 July 2007 00:14 leo.pasta

Instant gratification

I expected that my first “real” post would be about one of the features that had more appeal to me on SQL Server Katmai, but I will have to post about a discovery that was so useful to me, and yet so basic that I am almost ashamed to have found it only today. So let’s put aside my desire to pretend I knew it for a long time and post it, after all I hope that there is at least a couple of DBAs who still don’t know it:

Having beginning to work with VLDB recently, I am more involved in optimizing long running processes. So it is very useful to print the timings of the events as they occur. Since my Sybase days I always used the plain old “PRINT GETDATE()”.

But this will only print the output at the end of the batch, and when you are optimizing a job that takes 4 hours to complete, you get pretty anxious to know what the hell he is doing at any given time, even to decide if its better to cancel and try another change.

The solution is simple, instead of PRINTs, use:

DECLARE @Message varchar (100)
SET @Message = '<Step Description>: ' + CONVERT (CHAR (20), GETDATE (), 8)
RAISERROR (@Message, 0, 1) WITH NOWAIT

And you get the output as soon as they are generated.

By the way, the WITH NOWAIT option is properly described in the good old BOL.

Filed under:

Comments

# TSQL : How to get messages sent first class

03 July 2007 00:26 by SimonS SQL Server Stuff

Leo has posted a great tip about how to get messages output in a TSQL batch without waiting until the

# TSQL : How to get messages sent first class

03 July 2007 00:26 by SimonS' SQL Server Stuff

Leo has posted a great tip about how to get messages output in a TSQL batch without waiting until the...

# re: Instant gratification

04 July 2007 06:13 by Julian-Kuiters

I've taken Leo's code above and wrapped it into two easy to use stored procedures to save time.

You can download them from <a href="http://www.julian-kuiters.id.au/article.php/sql-snippet-instant-print-time">http://www.julian-kuiters.id.au/article.php/sql-snippet-instant-print-time</a>

# Couple RAISERROR with Profiler / Event Notifications - see whats going on!

05 July 2007 07:57 by Tony Rogerson's ramblings on SQL Server

Leo Pasta gives a good tip on using RAISERROR and NOWAIT , in addition to this in SQL 2005 we can trap

# re: Instant gratification

08 July 2007 06:00 by Jeff Moden

Doesn't seem to work in the face of agressive code...

--===== Create and populate a 1,000,000 row test table.

    -- Column RowNum has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times

    -- Takes about 42 seconds to execute.

SELECT TOP 1000000

       RowNum     = IDENTITY(INT,1,1),

       SomeInt    = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),

       SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))

                  + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),

       SomeCSV    = 'Part1,Part2,Part3,Part4,Part5,Part6,Part7,Part8,Part9,Part10',

       SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),

       SomeDate   = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)

  INTO dbo.JBMTest

  FROM Master.dbo.SysColumns sc1,

       Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

 ALTER TABLE dbo.JBMTest

       ADD PRIMARY KEY CLUSTERED (RowNum)

DECLARE @Message varchar (100)

SET @Message = 'First run complete ' + CONVERT (CHAR (20), GETDATE (), 8)

RAISERROR (@Message, 0, 1) WITH NOWAIT

DROP TABLE JBMTest

--===== Create and populate a 1,000,000 row test table.

    -- Column RowNum has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times

    -- Takes about 42 seconds to execute.

SELECT TOP 1000000

       RowNum     = IDENTITY(INT,1,1),

       SomeInt    = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),

       SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))

                  + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),

       SomeCSV    = 'Part1,Part2,Part3,Part4,Part5,Part6,Part7,Part8,Part9,Part10',

       SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),

       SomeDate   = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)

  INTO dbo.JBMTest

  FROM Master.dbo.SysColumns sc1,

       Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

 ALTER TABLE dbo.JBMTest

       ADD PRIMARY KEY CLUSTERED (RowNum)

PRINT CONVERT (CHAR (20), GETDATE (), 8)

# re: Instant gratification

09 July 2007 20:46 by leo.pasta

Hi Jeff,

 I tested on two instances I had handy and it work as expected, I received the messages as soon as they were produced. The servers were a SQL 2000 SP4 and a SQL 2005 SP2.

 All the tests were local connections, though. Let me try using remote connections and I will update on the results.

# re: Instant gratification

10 July 2007 00:42 by Jeff Moden

Not sure what I'm doing differently... for me, the message didn't appear in the "text" results window of QA until the final rowcount was produced.  Perhaps I'm doing something wrong.  Would really like this to work as the very concept is very useful and any suggestion you have would be very much appreciated.

Thanks, Leo...

# re: Instant gratification

10 July 2007 09:38 by leo.pasta

Jeff,

  I think I might have found the issue. I have noticed that if the output is set to grid, when we run a query, SSMS (and Query Analyzer) keeps on the "Grids" pane, hiding any messages until completion.

  If that's indeed the case, it's just a matter of select text output or click on the "Messages" tab as soon as you start the script.

Regards,

Leo.

# re: Instant gratification

11 July 2007 03:53 by Jeff Moden

Thanks, Leo... no, I was aware of that... I must have some other setting out of whack... I'll have to play with settings such as "Scroll as data is received", etc.

Thanks for the feedback... this is a really good idea.  If I figure it out, I'll post back.

# Return messages during long-running T-SQL Batches

27 August 2007 00:45 by DATALEXIS

Return messages during long-running T-SQL Batches