SQL SERVER – How DBAs Have to be Proactive in Identifying and Fixing Problems Before They Happen

In real life our dear ones always love to have surprises. If we do things even before they can think, we are in for some fun for rest of the days/weeks. I vividly remember a conversation with my daughter in recent past. I had made a deal to get her a bicycle if she secured the top marks in her Math paper. I wanted to create curiosity and enough incentive for her to learn. Anyways, getting her a bicycle was on my shopping list for her birthday. So I knew she is going to put some extra effort in learning this time. When the day of results came, I was prepared mentally to get her the gift ahead of time. More than a surprise for her, she did surprise me with the extra effort in getting the top grade. When she returned home, I was ready to show her the surprise. Even before she could ask me for the gift, I had it in front of her at the parking lot. For a matter of fact, I went ahead and bought a bicycle so that both of us can cycle our way in the evenings and spend some quality time. Being proactive in our actions can get our dear ones happy and the same holds good at work.

At work, I always have had the feeling to finish work ahead of time and I try to surprise my bosses from time to time. In my consulting days, doing challenging work was my forte and solving tough customer problems were things I cherished a lot. Most of my blogs are based on this one learning that I keep sharing every single day.

In one of the assignments, I had to help a DBA in his day-today activities. The problem statement was so simple that it made me think twice for a possible solution.

Pinal: Hi buddy!

DBA: I need some urgent help.

Pinal: Oh sure. Will be glad to help. Please let me know.

DBA: We are in the business of 24×7 and some of these applications are critical.

Pinal: Ok. Is that a problem?

DBA: No. Some of the application users are complaining of errors in the night. And since most of us are gone, it is very difficult for me to know what is going on.

Pinal: Well, do you know the exact error they are getting?

DBA: I know few are getting few deadlocks because they said they were a victim etc and were asked to contact the administrator. I want to solve this one by one. But I need your guidance Pinal.

Pinal: I am glad you got to the core of the problem. Let us see how I can help you. Have you enabled any Trace Flags to capture the Deadlocks?

DBA: No. We have a long approval cycle to enable anything on our Servers. So we have not done any to my knowledge on this new box.

Pinal: Do you have Profiler, Extended events or anything that captures deadlock errors?

DBA: Not that I am aware off. But my guess was confirmed based on the error messages my application developers sent me. It just states the connection was terminated because of deadlock victim. I am not sure how I will debug this. They want help and are asking me for more details. I am stuck.

There are a number of ways to solve this problem. If nothing else, try to use some of the monitoring tools that are available in the market.

Monitoring using Spotlight

Monitoring SQL Server is a proactive approach to analyzing problems on our servers. In this example, since they had the Spotlight enabled, I had to get to their diagnostics server to see the SQL Server under question. I told my DBA friend get to the Spotlight Home page and look for “Red” marks. He was quick to spot the Blocked Process was in red. I said, answer was right in front of his eyes.

Clicking on the Blocked Process, A pop-up came with the deadlock occurrence date and time. It was corresponding to the previous night and he was surprised. The obvious next question was – “Thanks Pinal. This was a good start. Now how do I know what was Deadlocked? What was the …”. I interrupted and said – “Friend, this is just a start. Let us do our detective work next. We know it is deadlock problem. So let us try to dig little bit more into this.”

My next step was to jump into the “Reports” Section and I quickly went ahead and clicked onto the “Deadlock List” report. The name is self-explanatory. We will be presented with a viewer where the date range has to be selected and we need to search for results.

I knew this was an error from previous night. So I went ahead and selected the date range and pressed on “View Report”. As you can see below, we will be presented with all the deadlocks that have occurred on the server for that time range.

In our example, I knew the time when this occurred. The report has the complete stack with some important information that can be useful to application developers. We will know the connection details, objects under question, what were the locks taken, what were the waits types and many more. Below is a screenshot of information from the same report.

This is one of the many typical out-of-box reports available with Spotlight for SQL Server. My DBA friend was delighted to see the level of information he had now and was all set to see the home page every day as soon as he reaches office. Finding the problem before it is being raised by users is a critical and key competency of an experienced DBA. Moreover he went ahead and sent this information to his application team for code review.

I gave my piece of advice to my DBA friend:

Deadlocks are not a problem of SQL Server. It is the way SQL Server makes sure there are no inconsistency in data. Application developer must know the access pattern to tables and must use the same pattern. In the above scenario, it is a classic cyclic deadlock that has happened. Please tell your application developers to fix this in their code to avoid these errors.

Do let me know if you ever encountered such scenarios in your environment? Have you ever been in a situation to identify the problem proactively? What have you done? Do share your stories and let me know your thoughts.

Try Spotlight on SQL Server free for 30 days!

Spotlight on SQL Server continues to break new ground when it comes to providing DBAs with the ultimate in flexibility to suit their specific needs. Building on the success of the Wait Stat analysis workflow, Spotlight on SQL Server version 11.0 now includes multi-dimensional workload analysis to provide maximum insight into performance issues, an enhanced mobile capability to remotely diagnose issues directly from mobile phones and the introduction of SNMP traps. A fresh new look provides simple to use, powerful visualization to effectively monitor the health of your SQL Server environment.

If you’re looking for additional help on how to optimize your SQL Servers, Visit Dell Software’sSQL Server Resource Center for video, guides and other useful content.

Reference: Pinal Dave (https://blog.sqlauthority.com)