Question

My answer

Transcript 

Question

All right, time for question number three. Let’s say that you get an alert that blocking is happening on a critical SQL Server. What do you do to handle it?

Answer

After watching the whole of this course, you probably have some idea about how I’m going to answer this question.

If I get an alert that blocking is happening on an important SQL Server, I want to connect to the instance as soon as I can and check out the Dynamic Management Views to see if the issue is still ongoing.

I like to use a free tool like sp_WhoIsActive. It makes querying the DMVs really easy, and I can see what’s running, how long it’s been running, and if queries are blocking one another, along with great information about what their wait types are, what their system status is.

I would also want to make sure that if performance is important on this instance and blocking is a problem, that we can be more proactive. So if we don’t have a robust, reliable monitoring system tracking things like blocking where I can look and see blocking in the past, I would want to use a tool like the free Blocked Process Report in SQL Server to track information about who’s blocking who – so that if we can’t get to the incident as it happens, we have information to troubleshoot after the fact.

This is a tools and process question

The questioner is likely to ask followup probing questions about what if X happens, and what if Y happens?

They also may phrase the questions in a way where they ask about a specific time where you had a blocking situation that you handled. That can be a great question to answer if you have experience in this in the real world.

Talking about the specific times you’ve handled something can be really powerful, but…

You have to be very careful here

You don’t want to identify anything about where you currently work or where you’ve previously worked that might be compromising or might reveal confidential information.

In other words, if you tell embarrassing stories about your current employer or previous employers, and it’s kind of clear who you’re talking about, your interviewer is immediately going to think about the stories that you will tell about THEIR company.

This is a tough situation because you want to get the job, you want to tell about your specific experience, but you also need to be really responsible. I mean, always, not just in an interview situation, about the databases that you work with, the data you work with, your clients, and your employer, right? Internal clients or external clients. You want to be very, very careful about any specifics, and it’s okay to say “I can’t talk, specifically about incidents. I can generalize and talk about how I would handle something. I appreciate that you want specifics, but I have to respect the privacy of my employers.” And an interviewer is going to need to respect that.

So a little bit of a delicate balance.

Don’t be overeager when it comes to killing SPIDS

If they start asking about specifics, make sure that you don’t get a little overeager about talking about killing the blocker.

Where you currently work, it might be okay to just kill off a blocking session. That’s not true everywhere. Some places, you need to do a change request, get approval, and a lot of times communication and process is going to be really important.

Before you talk about killing any sessions, you need to say: “and I’d want to figure out what the process is for handling blockers and how we clean that out. What kind of approval is required? Does a change happen at the application level to change the process elsewhere, you know, upstream?”

Be careful about that.

Generalize instead of focusing on vendor tools (unless they use one that you know well)

You also don’t want to talk exclusively about a vendor tool too much if you can help it. And this is nothing about any specific vendors, it’s just that they may not be familiar with the tool that you’re talking about, and they may use something else. They may not, so translate, essentially, anything you’re talking about with a vendor tool.

If you can translate what you see in that tool to general Dynamic Management Views in the SQL Server, that will help them understand where you’re coming from.

You can say, “I normally use X tool, do you have that?” upfront to figure out how much you need to translate into kind of general SQL Server-ese.

Mentioning Dynamic Management Views can really help you out on a question like this

There are a lot of folks out there who don’t seem to know what the Dynamic Management Views are, even though they were introduced way back in SQL Server 2005. A lot of more experienced folks are looking for people who are comfortable with them, even if they’re using the data from them that’s in a vendor monitoring tool.

If you know where that data is coming from and can use the names of some DMVs, that can really help. And that’s why, in this course, I tried to be really clear about what’s in sys.dm_os_wait_stats, what’s in sys.dm_os_waiting_tasks, that kind of thing.

Including comments about proactivity is a big thing too in interview questions

Not only, this question was really blocking happens, how would you handle it? You want to tackle that part of the question, but then being clear about being proactive in case it comes back can earn you big points as well with an interviewer.