SQL Server Blogs

Voices from the UK SQL Server Community
Welcome to SQL Server Blogs Sign in | Help
in Search

My two cents

Tips, opinions, tests (and misspellings) from a Brazilian DBA in UK

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.

Published Jul 03 2007, 12:14 AM by leo.pasta
Filed under:

Comments

 

SimonS SQL Server Stuff said:

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

July 3, 2007 12:26 AM
 

SimonS' SQL Server Stuff said:

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

July 3, 2007 12:26 AM
 

Julian-Kuiters said:

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>

July 4, 2007 6:13 AM
 

Tony Rogerson's ramblings on SQL Server said:

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

July 5, 2007 7:57 AM
 

Jeff Moden said:

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)

July 8, 2007 6:00 AM
 

leo.pasta said:

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.

July 9, 2007 8:46 PM
 

Jeff Moden said:

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...

July 10, 2007 12:42 AM
 

leo.pasta said:

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.

July 10, 2007 9:38 AM
 

Jeff Moden said:

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.

July 11, 2007 3:53 AM
 

DATALEXIS said:

Return messages during long-running T-SQL Batches

August 27, 2007 12:45 AM
Powered by Community Server (Commercial Edition), by Telligent Systems