SQL Server Blog

The other day I had a very stressed developer coming to see me… he had been in the middle of coding something when his PC pulled the ever inventive “Blue Screen of Death” trick on him.



He wouldn’t have been quite so bothered but he had been working on new piece of SQL, hadn’t been saving his work as he went along, and had not yet written it to disk as a procedure but was working in ad-hoc mode whilst he fine tuned it.

In this first episode of Sheldon Cooper’s Fun with Flags I’m going to be looking at my favourite flag of the moment… Trace Flag 8649.



And yes, before anyone points it out, I know having a favourite trace flag is infinitely more disturbing than having a favourite real life flag but I’ve learnt to live with it and you should too.

Now this is a fun thing that I REALLY should have known, but have never really played with the therefore was mystified when it came up in conversation recently. I’ve asked a few other SQL guys and they were unaware of the true meaning of this setting either (which made me feel a little better). It’s not something I’ll be forgetting anytime soon though.

This is the last of the join operators and possibly the most powerful. It is possibly the most common join operator and, for some reason, one of the ones I hear people most saying they’d like to avoid. Personally I wouldn’t agree with that at all… although it does have a few down sides (which I’ll cover later in this post), it’s by far and away the best choice when joining large datasets together and should therefore be welcomed.

Continuing with the posts on Join Operators I’m going to move on from the Nested Loop join and give a very brief explanation of the MERGE operator.



To be honest this is one of the join types I actually see the least out of all three. I’m not sure why this is, but that’s my experience. Doesn’t mean it’s a bad join type though as it’s very, very fast when it does appear. But it does have a couple of pre-requisites before SQL will consider it a viable option.

Join operators within SQL Server seem to be very misunderstood in general as people are forever telling me that they want a query to use this or that types of operator for the joins in their plan and they will even use hints to force these. Sadly it seems to come from a misunderstanding that certain types are better than others. This is not the case as they are all designed for a reason and each have their speciality.

We all use local variables and in many cases they’re invaluable… however, there is a lesser known (in my experience anyway) gotcha with them which can badly affect your queries and leave you wondering why performance has unexpectedly dropped.

This is a quick post regarding a couple of things I found someone doing the other day when using Profiler and SSMS to tune one of his queries. Basically he was getting frustrated because he had been making changes to his code which he believed would speed up his query and yet he wasn’t getting anything consistent out of either SSMS or Profiler to prove this.



As it turned out, his understanding of what to monitor was very wrong as he was only using Profiler to look at Duration and likewise relying on the “timer” in SSMS to validate his results. This is not the best way to do things as these both have quite major limitations when used in this type of scenario.

Okay, in my last post I showed you how easy it is to include a table of results in an email, but by all accounts it was a little bit bland when produced. So in this post I’ll deviate a little from SQL Server to give you some HTML which will turn this: