SQL Query Optimization

Optimizing queries isn't always straight forward. Sometimes you think you've created a great index but the query optimizer just won't agree. You update stats, try maxdop hints, clear caches, and maybe even curse a little but nothing works.
You suck it up and admit you're going to have to make an exception to what you know is right and use an index/table/locking hint.

Well in doing that today I made extensive use of a few options most people may or may not be aware of. These are:
SET STATISITICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS PROFILE ON;

The output of the first two options are pretty easy to understand, but the third (which is AMAZINGLY valuable!) can be tricky.
I suggest taking a look at this blog post on the subject as it gives great detail on the use of this excellent tool.
http://blogs.msdn.com/queryoptteam/archive/2006/08/29/730521.aspx

Interesting problem with SQL 2008, VCS, and MSX-TSX Servers

I thought I was going nuts today when I couldn't get a TSX server to use it's MSX server. Enrollment went through without a hitch, but the TSX would never get any jobs or even probe (although the MSX said it was probed).
After a LOT of digging I eventually found the problem. The TSX is actually a Veritas/Symantec cluster with a virtual name but when the MSX was calling things like msdb.dbo.sp_sqlagent_probe_msx it would use the hostname of the cluster node running the SQL service for the value of @server_name. This was weird. Both @@SERVERNAME and SERVERPROPERTY('SERVERNAME') returned the virtual name and the TSX enrolled as the virtual name but it was always calling the damn hostname!
The fix for this was quite obscure.
I eventually happened upon a key
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT]
"Environment"

And this key was missing for this cluster, yet there for every other SQL server I've got. I took a chance and put in the data for the key that was present under the service for this instance and bounced the service. BAMN! Just like that it downloaded all it's jobs and the MSX started issuing sp_sqlagent_probe_msx calls using virtual name!

I have no idea why this happened (SQL 2008 seems 'flaky' with permissions like this) but at least the fix was easy.
Keep an eye open for this if you're running a similar setup!

Fanboys: Pros & Cons Are Everywhere. Why can't you see them?

I just don't understand fanboys. These are people who take interest in things like cars, phones, computers, OS's, etc to a whole new (and scary) level.
There are people out that that would sooner spit on you than shake your hand because you use MS Windows instead of Linux. Why? I HAVE NO IDEA!
I just can't wrap my head around it. I suspect it is some sort of undiagnosed social problem and/or an intelligence issue but I'm no doctor.
I make my living supporting and architecting a Microsoft product (SQL Server), I'm posting this from Linux (Ubuntu), and I urged my parents to buy an Apple (Macbook) when I got tired of supporting them on XP ago and was given reason to fear for their information security. Why the diversity? Because everything has pros and cons. EVERYTHING.
Recently, I've been spending time digging into the pros and cons of Oracle 11g vs. SQL 2008 and I've got to say there aren't too many feature-wise. The big differences start to show up when you start to examine dealing with huge (multi-TB) databases that need very high up time numbers. MS still isn't there 100% with that sort of stuff (ever dug into an AWR report and seen what Oracle keeps track of? WOW!) but they are making major progress; no doubt.
In doing this I was also forced to recognize some of my own bias in that I have slammed Oracle over costs or complexity but ultimately; there is some serious value for that money and the complexity is a result. Of course, this point is moot unless you have DBAs that know what they're doing; really know what they're doing. As I see it the benefit of the MS solution is that it'll pretty much manage itself and tell you when something is seriously wrong; up to a point. This is a pretty common theme with all MS products and it's why Linux is still nowhere near a threat to Windows on the desktop (I won't get into that religious debate here). You'll never scale over a few hundred GB with MS SQL unless you have talented DBAs (haha! sales pitch for me *wink*) because you have to start fiddling with the knobs and actually understand the architecture. You need to be able to comprehend what all the DMVs expose and do it without going to Google/Bing/Ms Connect! Oracle lacks that touchy-feely stuff from the get-go and you won't get anywhere with it out of the box unless you have the right people.
One last thing before I move on from this Oracle/SQL rambling bable; what is up with them comparing things like Oracle RAC to SQL 2008 clustering?? How can anyone honestly claim that these two products compete in the same space? One is a simple low cost active-passive HA setup and the other is a full-blown active-active horizontally scalable massively-parallel solution that requires you to code specifically for it! 99% of all marketing is utter crap in the IT world.
In getting back to the topic, I'm forced to acknowledge that my mind just doesn't allow me to become too fanatical for any one thing; I always see pros and cons. I can't help but wonder if that is a positive or a negative. Is it evolutionarily (is that a word?) "better" to have this ability to reason and judge to such a degree, or is it better to be able to fully commit to something and then blindly follow your decision? I wonder. If I had to make a call I'd say the former is superior (wow... really high on my horse tonight, eh?) as I reason it to be a divergence from the typical tribal mentality we would have had in us many thousands of years ago. Without the ability to see fault you won't have innovation. The Model-T may have been "good enough". The steam engine "efficient enough". Nuclear power "too dangerous"...
I end with this; is the fanboy mentality an age-old human trait, a sign of a dumbing-down with society, or something else entirely? I can't help but wonder that everyime I read their comments/editorials/words. Part of me hopes the entire thing is nothing more than some elaborate marketing scheme running amok! ;)