My two cents - All Comments

Link Listing - May 11, 2008

Christopher Steen — Mon, 12 May 2008 07:41:14 GMT

Link Listing - May 11, 2008

re: SQLBits

Christian — Fri, 07 Mar 2008 09:42:23 GMT

Thanks for the write-up Leo :) I added a post to my blog with a link to the slidedeck here:

http://sqlblogcasts.com/blogs/christian/archive/2008/03/06/my-session-at-sqlbits-ii.aspx

Regards,

Christian Bolton MCA: Database

Database Architect

http://coeo.com - The SQL Server Experts

Return messages during long-running T-SQL Batches

DATALEXIS — Sun, 26 Aug 2007 23:45:19 GMT

Return messages during long-running T-SQL Batches

re: Page splits

Colin Leversuch-Roberts — Tue, 14 Aug 2007 08:35:48 GMT

I've done some "non scientific" testing to see if and how I could slow down inserts, I was trying to prove that adding indexes could slow performance by a measurable amount. So far I've not really had much success, inserting several >8k text columns will slow things , however in the main my inserts to the table were occuring at one end ( like a heap except it wasn't ) the secondary indexes split because I set the data to do that. It's interesting to extract that information and I wish the actual counter had more granularity - as you've suggested it probably has little to do with the issue and I'd sort of agree.

re: Instant gratification

Jeff Moden — Wed, 11 Jul 2007 02:53:39 GMT

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.

re: Instant gratification

leo.pasta — Tue, 10 Jul 2007 08:38:17 GMT

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

Jeff Moden — Mon, 09 Jul 2007 23:42:09 GMT

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

leo.pasta — Mon, 09 Jul 2007 19:46:10 GMT

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

Jeff Moden — Sun, 08 Jul 2007 05:00:40 GMT

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)

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

Tony Rogerson's ramblings on SQL Server — Thu, 05 Jul 2007 06:57:07 GMT

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

re: Instant gratification

Julian-Kuiters — Wed, 04 Jul 2007 05:13:40 GMT

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>

TSQL : How to get messages sent first class

SimonS' SQL Server Stuff — Mon, 02 Jul 2007 23:26:26 GMT

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

SimonS SQL Server Stuff — Mon, 02 Jul 2007 23:26:24 GMT

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