October 2006 - Posts

Have a look and try the following statements, and try and think what the expected results are. The key aspect is that the query does not match any rows, no object exists with id = -99
declare @maxValue datetime
set @maxValue = '1/1/1900'
select @maxvalue = max(crdate) from sysobjects where id = -99
select 'Using max' ,@maxvalue maxValue
declare @maxValue datetime
set @maxValue = '1/1/1900'
select top 1 @maxValue = crdate from sysobjects where id = -99 order by crdate desc
select 'Using top' ,@maxvalue maxValue
The results are as follows,
Test      maxValue
--------- -----------------------
Using max NULL
Test          maxValue
--------- -----------------------
Using top 1900-01-01 00:00:00.000
There are no rows returned so in the first instance @maxValue will NULL, however in the second the @maxValue will still be the default value
SELECT only assigns a value if a row is returned. With MAX a row is returned, no rows matching the criteria result in a row with a NULLvalue, however with TOP there are no rows so it can't return a row.
Depending on the behaviour you want you can achieve the NULL value with TOP if you use SET. This ensures you have consistency.
With SET if your subquery returns no rows then a NULL value will be assigned to your variable. i.e.
declare @maxValue datetime
set @maxValue = '1/1/1900'
SET @maxvalue = (select top 1 crdate from sysobjects where id = -99 order by crdate desc)
select 'Using top with SET' ,@maxvalue maxValue
Test               maxValue
------------------ -----------------------
Using top with SET NULL

Tony has a post on the T-SQL Value assignment SET vs SELECT that deals with the other side of the situation when you have multiple rows in a sub query.


Posted by simonsabin | 2 comment(s)
Filed under: ,

I can't say how many times I've read or been told that before you complain about performance issues with SQL2005 you should rebuild your indexes and update your statistics.

Well we've just upgraded our search boxes and was having performance problems, timeouts galore. The reason was the plan being produced was far from optimal. It was estimating it would get 1 row from a very large table, which meant the nested loops it was doing made sense. However the estimation was way off, in fact it was getting ~8,000 rows. This meant the nested loops weren't very effiecient. Even after 90s of running the query hadn't finished and performed more than 20 million reads. (on 2000 it was doing 100,000, this is a complex search involving full text).

I was sure we had done the reindexing etc but based on the plan we couldn't have.

I re-ran the re-index maintenance plan and found it was failing when it got to our indexed views. One of them had a table hint in it. This is obviously fine in SQL 2000 (may have been ignored) but a complete no-no in SQL2005. This failure meant the remaining tables didn't get re-indexed.

Re-indexing the remaining tables resolved the problem and we are all happy.

So when doing an upgrade to SQL2005 make sure you get that reindex and update statistics task in the plan.

Posted by simonsabin | 1 comment(s)
Filed under:

Got to jump on the band wagon and tell the world what they already know.

IE7 has been RTM'd


Posted by simonsabin | with no comments

I remember back when I moved from Oracle to 6.5 I found the lack of a create or replace really annoying. Whenever I create new sps I always curse at not being able to do "create or replace myproc..." having to do "if exists (select 1 information_schema...)" or "if object_id(...) is not null" or "if not exists(select 1 from sys.objects..)" is really annoying, especially as there are so many different options.

So I go to connect to put a suggestion and find one already there.


So if you want this implemented go and vote


I've just mentioned Remus's post on service broker procedures. The other thing that came up when looking at the conversation group size was the time it took to load the queue. This set of figures shows the load time, the first is the number of conversations and the second the number of messages per conversation, and the final number, the time it took in seconds.

1x10000 = <1
10x1000 = <1
100x100 = 1.5-2.5
1000x10 = 5.8-6.7
10000x1 = 45-52

It is clear that the overhead of creating a dialog for a conversation is neglible compared to the rest of the code to send the message, when the conversation group size is high.

However when the number of conversations increase and the number of messages per conversation decreases, it is clear the overhead of the extra dialogs is huge.

This adds further weight to my argument that you need to put as many messages in your conversation groups as possible, if you are batch processing messages.

Have a look at Roger Walters blog on recycling dialogs


Remus has written a great article on use of activation SPs and the way of achieving the best performance.


I am looking into this as a means of asynchronous logging and so have taken Remus's script and expanded on it.

Remus's script always has 100 conversations with 100 messages per conversation. You need to be aware that the RECIEVE statement will only RECIEVE messages from ONE conversation group (by default all conversations are in their own conversation group)

What does that mean? Well the solutions that Remus's puts forward are about doing batch processing on messages. The gains are huge by batching up results, however a batch is a conversation group. That means if your conversation group only has 1 message in your batch will be 1 row. Not much of a batch, similarly if your conversation group has 10000 messages in it your batch can have 10000 rows in it.

If you re-run Remus's scripts with different conversation sizes the results show that as the size tends to 1 then the performance of all the options become similar (the batch comit providing the only gain). Also as the batch size increases the gain obtained by using the batch solutions increases to 1700%.

So what does this mean?

You need to put as many messages/conversations in a conversation group as possible. You obviously need to test your situation to find the optmium size.

You can achieve this by reusing an existing conversation group is one already exists. Have a look in sys.conversation_endpoints.


I will be presenting at the next UK SQL Server Usergroup meeting, at Microsofts Reading campus.

My topic is Asynchronous processing in SQL Server. This will include some techniques for SQL Server 2000 and more from SQL Server 2005

Full details can be found here http://www.sqlserverfaq.com/?eid=82


Following my previous post about the requirement of semi-colons when using service broker statements, I have raised a suggestion to improve the "Incorrect syntax near ..." error message.

If you find the above message less than helpful, then vote for the suggestion.



In response to a recent support incident we raised we got a response about use of detours in SQL Server.

A detour is a means by which you can change what SQL Server does. This is used by Litespeed to change native backup commands into Litespeed backup commands. Whilst I always thought this a neat idea I always felt it a bit risky. If I want to do a litespeed backup I can easily write the code or use the UI to do so.

So it seems my caution has proved correct.

Use of detours by a third-party solution is not supported with SQL Server


Further to my last post about the BULK UPDATE suggestion I have made, if you have any suggestions or bugs of your own then please put them into connect.

Please also support the bugs and suggestions that others have raised. The list of non-closed bugs and suggestions can be found here.

Posted by simonsabin | with no comments
More Posts Next page »