You are handed a poor performing SQL statement that selects against 2 tables, using a join and also a where clause. How would you troubleshoot the performance problem?
Answer by Jeff Moden ·
Most folks aren't going to like my answer and, to be honest, I have no problem with that.
The correct answer is that if you have to ask for the correct answer on a forum for this question, then you might not actually be qualified to do the job that you're being interviewed for simply due to a lack of experience. As Jon posted above, this is a "thinking"question where you shouldn't actually have to "think" about it during an interview to be able to answer it.
I'll also state that if you "fake" such an answer with any interviewer worth their salt, they will eat you alive and finally determine that you've lied about the supposed experience you said you had to get the interview to begin with. Since being a Developer or a DBA requires very high integrity, that will almost instantly cost you the job.
Heh... and if you think that an interviewer can't actually tell, let me introduce you to the real world. :D
Answer by Jon Crawford ·
I think the "proper answer" is probably fishing for "how does this person think through a problem?". The reason for the poor performance is less important than your honest attempt at trying to solve it, and the process you go through to get there. So, what would your answer be?
Show the interviewer that you are familiar with SQL Server functionality that addresses performance. (lots of options here, from syntax to "best practices" in coding, to system views to execution plans, etc.)
Show that you are aware of how to search and learn about the topic if you are unaware. Give them an example of when you did this.
Show that you are a team player and willing to accept that you don't know all the answers when you need help (getting it right is more important than getting credit, and knowing how long to spend on a problem before asking for help)