Monday, September 19, 2011

THE Interview Question

Not so long ago, in a galaxy not so far away, REAL SQL Guy was in need of a sidekick, a right-hand-man, a Robin to my Batman.  The "Help Wanted" sign was placed in the front window of the Guy-cave, and the interviews commenced.

REAL SQL Guy hates doing interviews, mostly because they turn out to be a waste of time, as yet another candidate turns out to be not-quite-DBA material.  "Can you spell S-Q-L?  No?  Door's over there."

As the process dragged on, and we continued to turn away the "I'm really good with Excel" and "it's time to stop delivering pizzas and get a real job" folks, frustration began to set in, and I started to think hard about the questions we were asking.

I finally settled on one question, just one, that would quickly filter out the guys who "used to have a SQL Server running on the network, somewhere".

"You're the on-call DBA, supporting, among other things, a 200-seat call center.  Each machine in that call center is running an application that reads and writes to your SQL Server database.  It's Friday afternoon, and you suddenly start getting calls from the Call Center users, each claiming that their application has hung.  There is no error message, the application has simply frozen.  What do you do?"

Some of the actual responses that were given to this question:

- use the Index Tuning Wizard to see if there's a missing index
- check to see if SQL Server has consumed all of the memory on the server, because it will do that sometimes
- have someone check each workstation to see if something is pegging the CPU
- use Profiler to record the server activity
- that's something our IT staff would handle

Of the dozen or more candidates that were asked this question, only two gave me the answer I was looking for:  sp_who2

In my opinion, under these circumstances, there is only one right answer.  Run sp_who2, look for a blocker, and kill that SPID.  That's the only reasonable solution when you have 200 call center agents, on the phone with customers, waiting for a resolution.

How would you answer the question?


Dev Nambi said...

If the application froze, it could be more than just the DB. I'd also check any web server or middle-tier server for CPU, memory, or network spikes.

You could also run sp_WhoIsActive if you have it installed.

Brannon said...

Use SELECT * FROM sys.dm_exec_requests to get the blocker spid. Get the sql_handle from that query and use sys.dm_exec_sql_text to get the query text of the blocker. Then kill the blocker spid. ;-)

REAL SQL Guy said...

All valid answers, and none of them would have earned my Scowl Of Death.

Post a Comment