Art of the DBA Rotating Header Image

troubleshooting

WHERE to JOIN?

I’ve been really enjoying the DBA StackExchange site recently.  Not only can you see what challenges and hurdles people have, the site construction gives people a great way to contribute to an ever expanding library of database solutions.  Questions range from the very simple to the highly esoteric, but in all cases the community comes together to groom both questions and answers in such a way that a comprehensive knowledge base is built for future use.

One of these questions that recently came up was:  Which performs better, creating your joins in the FROM clause or the WHERE clause?  Most people have been using the ANSI-92 syntax, so this question may seem a little odd, but I still see a lot of SQL code out there that uses the prior syntax where the joins are declared in the WHERE portion of your query.  If you want to read more, Mike Walsh(@Mike_Walsh) has a great post on how the syntax has evolved and how its changed in SQL Server 2012.

Back to the question, though.  Does it really make any difference?  Well, I could tell you straight out, but what sort of blog post would that make?  Instead, let’s test it out ourselves.  Using a basic schema, I’ve put together two very basic queries:

SELECT
  s_id
  ,s_desc
  ,b_desc
  ,f_desc
FROM
  snafu s
  ,bar b
  ,foo f
WHERE
  s.b_id = b.b_id
  AND b.f_id = f.f_id;

SELECT
  s_id
  ,s_desc
  ,b_desc
  ,f_desc
FROM
  snafu s
  INNER JOIN bar b ON (s.b_id = b.b_id)
  INNER JOIN foo f ON (b.f_id = f.f_id);

As you can see, the only real difference here is that in the first query we have our joins in the WHERE clause. The second follows ANSI-92 syntax and places the joins in the FROM clause. Now how do we tell if they perform differently? Query plans, of course!

Query 1 (WHERE clause)

WHERE_JOIN

Query 2 (FROM clause)

FROM_JOIN

Notice how both queries have exactly the same plan.  This is because our friend, the Optimizer, understands the two approaches and will build the plan accordingly.  Want to play with it yourself?  You can check out the full example over at SQL Fiddle.

There are three things I’d like you to take with you after this brief exercise:

  • Functionally, it doesn’t matter if you declare your JOINs in your FROM or your WHERE clause, the optimizer will treat both as the same.  However, if you read Mike Walsh’s blog post, you really should be using the ANSI-92 standard.  The “old” syntax only works if you have your database in SQL 2000 compatibility mode (which means it doesn’t work at all in SQL 2012).
  • Query plans will answer most of your performance questions regarding SQL syntax.  If you haven’t been looking at them, I strongly suggest you pick up Grant’s book and start checking those plans out.
  • I’ve only recently discovered SQL Fiddle, but this is a great tool for mocking up and testing concepts for databases.  I haven’t built anything larger than 2-3 tables, but for basic test cases and examples to demonstrate something, it’s really cool(it even lets you look at query plans!).  Check it out.

Standing on the Shoulders of Giants

When I went to SQL Rally last week, I fully expected to be learning from many of the presenters there. The excitement about being immersed in all that awesomesauce is infectious. What I didn’t expect is that I would take my turn at teaching, handing out information like candy to twelve year olds. It was one of those times where I was talking shop with some other folks while we were all taking a session off. The group started chatting about performance tuning(something I geek out about) and I shared out some of the fundamental links, blogs, and tools I use when I get the inevitable “My query is slow!” call.

Now, just to be clear, none of the information I shared was “mine”. The beauty of the SQL Server community is that we have so much great talent and everyone is willing to help educate. What follows here is a list of my core tools and information links, resources that I use on almost a daily basis. I also see them as the start of a journey, because none of this is the last word on a particular subject. If you’re like me you’ll continue to browse the Google ‘verse with what you pick up here.

Brent Ozar:

Full disclosure, I’m a Brent Ozar fanboy. I started reading his blog about a year or so ago and he’s made me laugh and taught me a whole heck of a lot. It’s because of him that I started blogging and getting involved in the community.

Glenn Berry:

Glenn Berry is a local Denver MVP and I see him from time to time at the Denver SQL User’s Group meetings. Not only has he provided these great DMV queries, but he really understands hardware and has some great posts on the nitty-gritty of your machines.

General Community Resources

These two sites are invaluable resources for articles and tutorials on getting stuff done. When I was figuring out how to implement partitioning, I did a lot of reading on both of these sites.

Documents

Both of these docs live on my laptop and are constantly getting referenced. My only complaint about the waits and queues white paper is that it’s a little stale and I hope Microsoft does an updated one for Denali when it’s released.

If you don’t have a cheat sheet with your go to resources, hopefully this list gives you a good start. Or you can add this to your currently existing list. I have found each one of these links invaluable to my day to day work, not only enriching my general knowledge but giving me the tools to troubleshoot many of the performance problems that come my way.

Dumb Questions

“Can I ask a dumb question?”

Anyone who works with me for a short period of time will hear this tumble out of my mouth.  I can’t tell you the number of times someone launches into a 5-10 minute explanation of a really cool concept and giving me that “I have a cunning plan” look when they finish.  However, because I didn’t have that one thing in my head they figured was obvious, I end up having to ask a couple questions to get myself on the same page.  It’s never a big deal to fill in those gaps and the opposite party is usually happy to provide the context, but I do have to ask those questions.

Assumption: The Mother of all Screwups

The problem is with assumptions.  Whenever we communicate with each other, we have to make an assumption about what the other person knows.  Unfortunately, a lot of times those assumptions are wrong.  Remember the ill-fated Mars orbiter where there was a mixup between the imperial and metric measurement systems?  While we all smack our forehead and say “D’OH”, it’s pretty obvious that this is the result of one side making assumptions about the other side’s knowledge.  If someone had questioned the basic assumptions of both sides, they might have saved that $125 million orbiter, but instead, the two teams ran off on different tracks because they didn’t want to appear dumb.  (How how do they look now, right?)

I know we all get frustrated when the Level 1 support tech asks us if our network cable is plugged in or that we haven’t kicked out the power cable, but these are exactly the kind of questions we need to ask when troubleshooting database problems or building requirements for a project.  The reason for this is because asking the dumb questions helps establish a baseline starting point for figuring out the problem.  After all, when someone comes to us saying “the system is slow”, we don’t want to bash our heads with IO stats or memory pressure when all we had to do was deal with one blocking query.  We wouldn’t know about that unless we asked the right questions to start our troubleshooting.

So tell me a little about yourself

The best approach is to dealing with this is to teach yourself to reduce the number of assumptions you make.  Easier said than done, right?  What really helped me was an exercise a former boss/mentor had me do early on in my career.  It’s pretty simple:  In one paragraph (no real limit on sentences or words), describe your work to someone who has no idea what you do.

Seems easy at first, but think about it for a second.  Let’s break down the following sentence that I might use:
“I administer and maintain SQL Server databases.”

Already in this sentence we have several assumptions about the listener.  First, that they know what a database is, and they know what maintenance and administration is for SQL Server.  Chances are most folks don’t, so we would then have to explain those terms to them in a way they would understand.  We don’t need to get overly complex, but provide the listener a basic grasp of what we’re talking about.

The end result is that this exercise puts us in the mindset of someone who may not have the same knowledge or experience as we have, thus getting us to restructure our thinking and presentation in a way that covers the entire context of whatever idea we’re presenting.  It also gets us to start evaluating our assumptions based on our audience.  We’re always going to make assumptions, but what we want to do is make them in the context of our audience.  Speaking to a group of DBAs?  Yeah, leave out that stuff about relational theory.  Talking with IT infrastructure dudes?  Chat with them a bit about database file layouts and managing I/O contention.  Oh, monkeys from Kenya?  Throw ’em a couple of bananas.

Duh!

So whether we’re asking ourselves or someone talking to us, it’s always good to get those assumptions straight.  Sure, we all pride ourselves on figuring things out.   That’s why we’re in this business, for the joy of solving that process that no one else could.  But it never helps us when we get lost on that wild goose chase because we made the wrong assumption.  Save yourself some time and a lot of headache, make sure you’re on the same page with those you work with, and ask some “dumb” questions.  It never hurts.