Are you a SQL Server Guru?

At the moment I have one contract as a SQL Server consultant for a company that develops software, and – guess what – uses SQL Server 2008 as their DBMS. About one month ago lead of development asked me to conduct one part of an interview with an applicant, to find out his real SQL Server skills. When looking at the application I was truly amazed, because the candidate designated himself the title of a SQL Server Guru. I know some people who are experts regarding SQL Server, even I know something more than only the fundamentals, but I seriously think there’s only one SQL Server Guru alive :). (You know, who I’m talking about without following the link, don’t you?) I was very excited about how the interview would go. As it turned out, it went somewhat surprising, and so I developed the idea for this blog post.

But before I start repeating parts of the interview, I’d like to provide some background information about the company’s business model, just to get you a little more in context. The corporation not only builds software, but delivers complete systems to smaller stores, consisting of hardware and software, where the hardware usually encompasses one server and some client computers that are primarily used as checkout-counters. Of course the sales price has to be as low as possible, so there’s a very limited budget for building the system. Due to this limit, also the server hardware is quite imperfect, and SQL Server operates on the edge every so often, making performance and optimization a big issue. Actually I spend the majority of my time with performance optimization.

Ok, here are some excerpts from the interview. I’ve included my thoughts and annotations (written in italic and embedded in brackets).

The interview

Q: This is the first time, I have the opportunity to interview a SQL Server Guru. So, what kind of guru are you actually? Is it more Development or Administration? Are you familiar with MSFT’s BI stack?

A: I’m very good in TSQL and optimization and I also know Reporting Services.

Q: Very good, you know something about optimization! We really need reinforcement in that area. So, what’s your general approach to optimization? Do you follow a method or framework here?

A: In most cases I optimize TSQL.
(I loved to hear that. Really, I did. Tuning the database design, TSQL, and application requests is the most promising approach. The majority of people I’ve asked the same question so far were instantly telling me something about simply adding more indexes.)

Q: Very good! I really like that. How do you usually optimize TSQL? Is there a general approach, you follow?

A: I use query hints.
(Ugh! How surprising. Query hints as general optimizing approach! I had to find out more.)

Q: Query hints? Why is that?

A: Because the optimizer is very stupid and generates wrong plans most of the time.
(Hmm. About 20 years or so of development effort to make the optimizer perfect, all wasted. Does he really believe this?)

Q: That’s amazing. I didn’t know about that so far! Can you provide an example please?

A: Yes, for instance, the optimizer very often decides for using merge joins, and merge joins are always slow, particularly slower than nested loop joins. I have to force the optimizer using nested loop joins instead.
(If merge joins are always slow and outperformed by nested loop joins, why do we have those? Has he never thought about that?)

Q: So, that’s your approach to optimization? Query hints?

A: Yes, whenever I write a query, I develop the execution plan in my head, because I can do better than the optimizer. And then I provide as many query hints as required to force the optimizer in the direction of using this plan, my plan.
(I confess that I was tempted to ask for the company’s name where he implemented this, since I had a strong feeling this company would need some support. But at the moment I was fully booked and so I resisted. I recognized, this discussion would need to nowhere and decided it was time for a topic change.)

Q: Ok, thank you. So what did the guru do last week?

A: I used clustered indexes because this minimized locks.
(Hmm. Another interesting statement. Let’s see, if he can tell me more about this.)

Q: Clustered index? What is that? Can you explain it, please?

A: When a query uses a clustered index, the query is very fast.

Q: So, a clustered index is kind of magic? How come, a query is always fast, when a clustered index is used? Can you tell me some more about a clustered index please? How is it constructed, or structured.

A: A clustered index is ordered.
(Wow! The guru enlightened me with that perception!)

Q: So does this mean, a non-clustered index is non-ordered?

A: No no, a non-clustered index is also ordered.

Q: Ok, but what’s the difference then between a clustered index and a non-clustered index? What exactly is a clustered index?
(I was about loosing my patience at this point!)

A: A clustered index minimizes blocking.

Q: Really? How is this possible? How is a clustered index structured? Can you explain, please?

A: You know, I use clustered indexes every day. I’m so deeply involved with clustered indexes that I can’t explain it anymore.
(That was the moment, when I decided to send him back to where he came from. But unfortunately everybody else, including my interview pals were sitting in a strategic planning meeting, so I was the only one available and had to continue. I started getting angry, since he did nothing more than stealing my time.)

Q: Can you explain how a non-clustered index seek on a table works, if also a clustered index is existing?

I just asked this question to tease him a little. Of course he couldn’t explain this. How could he, if he not even doesn’t know what is clustered index is at all?

After a while our chief architect appeared, took over, and rescued me. Another 30 minutes later the candidate was sent home.

Ok, so what is this blog post all about?

About one year ago, Brent Ozar has written a very informative and entertaining series of blog posts, containing some interview tips which you may find here. I’d add another simple tip to his advices: Be honest. Only include those skills in your CV, you really can cope with. Know what you know but, more important, also know what you don’t know! While tuning your SQL Server is a good thing to do, tuning your CV too much is not! Be sure, someone will find out and you’d be pegged as being dishonest. You’ll be out of the game immediately when this happens.

But how come, people consider themselves as being SQL Server experts or even gurus? I think that’s because MSFT did a great job in making SQL Server an easy to use product. After you managed to install your server and client tools, you can start using the relational engine instantly. It’s very easy staring at zero and improving your skill to, lets say, 30% in a short period. So, should we blame MSFT for making SQL Server that simple to use in taking the first steps? Of course not. But it’s just reality that many people using SQL Server don’t know much more than only some fundamentals. That’s not a bad thing at all, because SQL Server will do the job, even if it’s not operated at its optimal configuration, and non ideal queries are fired at it. To be honest, I personally like this, since it gives me the opportunity of being called for rescue, when things go worse.

As with many things, also the term expert is relative. You can be that 30% expert in a particular environment, where you know only something about SQL Server, but everybody else knows almost nothing, making you the one-eyed amongst all the other blind persons.

I remember a presentation Kalen Delaney was holding in my hometown on the occasion of SQL Server 2005 inauguration, where she very frankly confessed that she has to face the fact, her SQL Server knowledge will decrease with every new release of SQL Server, simply because the product has an ever increasing complexity, and her knowledge will not able of keeping pace with this complexity in all fields. Therefore, she decided to focus on some things, and to be an expert on these.

You may compare this to the disappearance of Middle Ages’ universal scholars. Also universal SQL Server experts are certainly rare, or even non-existent. I’m pretty sure, even the guru himself would be somewhat conservative calling himself a guru.

What do you think?