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 (http://blog.sqlauthority.com)

SQL SERVER – Visibility into the Overall Health of Your SQL Server Environment – You Can’t Fix What You Can’t See

I am sure most of us have experienced driving a four wheeler. The first time I went behind the wheels, it was an amazing experience. My excitement was multiplied by tension in the face of my father. After a while, I could see a sigh of relief and confidence even in his eyes. This is a classic case of us being pampered, guided and always shadowed by our loved ones. I loved the learning experience then. The real challenge came way later. One of the days while I was driving to office, the car stopped in the middle of the road. I was clueless to what could be the problem. The human instinct was to look at the dashboard for some signals. I checked the fuel level, oil level indicators or for any indicator that could guide me in finding the problem. It was time to call the experts was my opinion. A lot of times, we are faced with situations where the health of the components cannot be diagnosed easily – what can we do?

The above analogy is just an example of what happens inside SQL Server too. If we don’t know or cannot see the problem in hand, how can we fix the problem or even worst how do we know a problem exists on first place? In the recent past, I was quizzed with a similar problem and didn’t know what course of action has to be taken.

During one of the chat sessions with a DBA friend, he said – “most of the testing and integration environments are managed by junior DBA’s and seniors handle only the production environments.” He explained there was a recent incident that caught his attention and wanted to know if there is a way to solve the problem in hand.

This got me curious to what had really happened. He said, one of the test environments crashed just couple of days before a major release cycle. The testing team goes ahead and creates databases for a release cycle and loads tons of relevant data. After the crash, the DBA’s didn’t have any Log backup because it was failing and they didn’t monitor the same. The DBA said the maintenance plans were in place, yet it had failed.

The junior DBA got curious and asked this question to him. There must be something fundamentally wrong here.

FULL recovery acts like SIMPLE

Sometimes we need to understand the basics first. Even though the database was set in FULL recovery model, it acts like Simple recovery model till the first Full backup is taken. This is a well-known concept. To illustrate this, let me take a Log backup of a newly created database. The T-SQL looks like:

BACKUP LOG [Bigdata] TO DISK = N'C:\Backup\Bigdata.bak'
NAME = N'Bigdata-Log Backup',

This will throw the below error 4214. This is the exact error they were getting too.

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

The error is self-explanatory that we don’t have a database backup yet and that will solve the problem. The GUI steps will yield the following error message dialog –

Now that my friend understood this concept, he wanted to be vigilant and proactive in solving this problem. There is no way he can track all the databases created in the test and integration environments. He wanted to know if there is a way to know this ahead of time.

Overall health using Spotlight for SQL Server

There is no way one can keep track of databases that are created randomly by application / testing team every single day. If you have a monitoring tool like Spotlight, this task becomes easy for sure. Yes, we can make a deployment standard that everyone needs to take a Full backup as soon as the database is created. But this required constant monitoring and discipline from users / script writers.

In this example below, I have gone ahead and pointed Spotlight to a newly built server. As soon as this happens, the heatmap view shows us some critical data which needs attention and acknowledgement. Here you can see the tooltip suggest – “Full backups are missing for few databases”. I felt this was a nifty suggestion that can save lives for many.

On double clicking the same, we are taken to the Server home page where we can look at the databases node (marked in Red) and get the detailed information to which all databases are pending for backup.

The learnings here are two folds. We understood the principle that a log backup is not available till the first full backup happens. Secondly, the FULL recovery model acts like Simple recovery model till the first Full backup is taken. To mitigate the risks on business critical environments, it is important to have some monitoring capability like Spotlight for SQL Server.

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’s SQL Server Resource Center for video, guides and other useful content.

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

SQL SERVER – Proving that the Source of the Problems aren’t Tied to the Database

I have always wondered how Sherlock Holmes cracked the toughest of mysteries every single time. This fascination always lead me to learn new techniques every single time. The backbone for every successful detective is to be logical in their approach while working on the case. While watching movies, I have always seen a twist in the plot always makes the movie interesting and we are glued to the seat. If there is no drama, high octane action, restrictions – the movie would be boring. These techniques apply to real life too.

Being an application developer can always be demanding. You never know what situations we will get pushed into the next day. Here is one such situation which was just like a detective work. Once I was called for performance tuning exercise of our application that was running healthy for more than a year. The distress call from the customer got me thinking. What could have gone wrong in the application? Why all of a sudden this is happening? Did we release any upgrade or update to the database in the recent past as release? None of these were making sense as I kept thinking about possible root cause analysis. As soon as I landed at the customer site, I starting my interrogation with the DBA.

Pinal: Hello Sir!

DBA: Hi Pinal. Nice to see you here. Thanks for making time.

Pinal: It is a pleasure. I am surprised I had to come in such a short notice. Something must be really wrong then.

DBA: Yes. Our application users are complaining of very bad performance of your application for the past 2 weeks.

Pinal: Was it running fine before that?

DBA: I think, it must have been. They haven’t complained about it for more than a year. This seems to be a recent phenomenon. I think the application data must have increased considerably for this behavior.

Pinal: Hmmm … How is it possible that the application data increased all of a sudden in the past 2 weeks?

DBA: I am not sure. You are the expert. I am just a DBA and not an application developer. Must be something in your code.

Pinal: Can I get a chance to see the server console once?

DBA: Well, you know we are very sensitive with data privacy. I can surely get you access to an SSMS console. That must not be a problem.

Pinal: I can’t see the server even once?

DBA: Wait, let me get you the network / systems admin for this. They must know a way out.

After sometime, I get a chance to meet the Systems admin and continue the conversation.

Pinal: Hi there!

Admin: Hi. How can I be of help?

Pinal: I was wondering, if I can get access to the SQL Server box for my application?

Admin: Well, it is against our policy to open up the server directly. But what do you want to do?

Pinal: Ok. I am not sure what is going wrong on your server. Our application was running fine till about 2 weeks back. And now users are complaining about slowness in performance. I wanted to know what has changed in this timeframe all of a sudden.

Admin: Oh yeah, I forgot to tell you. We are in the process of doing server consolidation and are moving lot of our workloads from Physical to Virtual environment. And I know, we did move your database into a virtual environment.

Pinal: Cool. Are the configurations same as you moved these servers?

Admin: We made sure they are identical. So please don’t worry Pinal. It is something about your application code mostly.

Pinal: hmmm … Sure let me check. Thanks for your time.

I have always been a believer, accept but validate. There is nothing that can confirm your hypothesis but by doing the analysis all by yourself. So I bid a goodbye to Systems Admin and started my conversation with the DBA. I turned around and asked – “What do you use to monitor your SQL Server environments my friend?” He suggested the use of Spotlight on SQL Server for SQL Server. I felt so relieved that they had something.

Spotlight on SQL Server for SQL Server to the rescue

Having some monitoring tool is essential when working with SQL Server. Though there are a number of them, I had had my opportunity to look at Spotlight on SQL Server in detail in the past. It surely has come a long way and has nifty features that are worth a note.

In our conversation above, I was sure there was something fishy about the configuration the Systems Admin have given to SQL Server and I wanted to understand what are the complete Hardware Configuration for our database server.

The first report I pulled out as part of my diagnosis was “Hardware Configurations”. Voila, I had my answers right in front of my eyes and I was delighted my work was complete in less than 15 mins within the organization.

In the previous configuration (physical configuration), they used an 8 Processor machine for the application’s database server. As part of the consolidation, the network team accidentally made it a single CPU machine and it deprived the application of essential CPU cycles. So in a single click I was able to get the answers. I didn’t look further because all the physical hardware configurations were in black-and-white in this dashboard for me.

In fact, if we use the SQL Server Management studio’s dashboard we can get limited information. But nothing as exhaustive as what I got to see as part of data collection on Spotlight on SQL Server. The Server Dashboard does have the “# of CPU’s” values embedded into the report.


A lot of times we get misdirected with the problem in hand. These take us into a spin of wrong diagnosis. Don’t always work on the symptoms of the problem. Try to find out all the data points that lead to the problem and work your way out in solving the same. In our above example, the problem was never about performance of our applications. It was about wrong configuration and wrong migration done by the network team. It is essential we are aware of these pitfalls.

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’s SQL Server Resource Center for video, guides and other useful content.

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

SQL SERVER – Optimizing Three Important Resources – CPU, Memory and Disk

For any DBA there are three most important resources – CPU, Memory and Disk. Here is a real life story just happened recently which discusses CPU, Memory & Disk along with a new manager and frustrated employee. Well, thanks to Dell Software’s Spotlight on SQL Server Enteprise,  the story has a happy ending.  Trust me, you will enjoy this mini story, so continue reading.

Beginning of the Story

Recently, I was traveling to Delhi to speak at a popular annual conference. Due to a lenghy commute, I decided to arrive one day early to catch up with an old friend. My friend and I had plans to have dinner together as we had not seen each other for quite a while. However, when I called up my friend at 4 PM, he said he will be not able to join me and encouraged me to go to dinner alone. Trust me – I was here early to catch up with my friend, so dinner was not the priority. I immediately asked the usual question WHY? He replied that he has a new boss that is giving him a hard time with the database server.

After listening to his situation for about 10 minutes, I asked him if I can join him in his office to help him understand what is going on with his servers. If I can be any help and resolve the problem, maybe we can still catch up for the dinner, if not, well it’s still fun to work with a new database server and solve its problem. I arrived shortly at my friend’s office and he guided me to his cubicle.

My friend was recently moved to a new team in his organization where he was responsible of managing a critical server, but this server was suffering from a slow performance  issue. To make the matter worse, his team had a new manager who had just joined two days prior. In this world, there are many great managers (I have been rather fortunate), but there are always  people with “room for improvement”. His manager was only two days into his role, but due to this tense situation, he got aggravated at my friend and asked him to solve a problem before the end of the day. Additionally, he asked him to prepare the details related three important resources – CPU, Memory and Disk (with a presentation to match).

The Resources – CPU, Memory and Disk

For any system, there are three major resources that need to be accounted for – CPU, Memory and Disk . It is extremely crucial to know how each of these resources are used properly and optimally in any system. If due to any reason, any one of the resources is over used or under used, it can create a negative effect on the performance of the entire server. Here my friend was challenged to solve this problem very quickly –  as well as was asked to create reports on the subject. Well, this was indeed not easy for him to do in such a short period of the time.

Spotlight on SQL Server to the Rescue

Honestly, I knew this was not going great. I asked my friend that there is no way he can do everything in such a short period of the time. He needs to now depend on third party tools which are built for performance analysis and tuning. Sometimes it makes sense to leave the expert’s work to experts. There is simple no need to re-engineer the entire wheel. I suggested he download Dell Software’s Spotlight on SQL Server Enterprise and install it on his production server. The installation is pretty straight forward and right after it was installed, it brought up following screen. There are four sections in the Home Page sections.

  1. Sessions (Active sessions)
  2. SQL processes (CPU usages)
  3. SQL Memory (Buffer Cache)
  4. Disk Storage (Log File and Indexes)

When you look at each of the sections you can find various information very clearly and easily. It is absolutely impossible to explain each section in words, hence, I have created this 150 seconds video which explains how each of the sections works. Trust me, it is very easy to use tool.

Finally – Dinner Time

Well, my friend immediately installed Spotlight on SQL Server Enterprise on his server and was able to discover various issues in no time. In our case we had issues with blocked processes as well as slow disk. With the help of the following two areas on the home screen, we were able to quickly resolve the problem on his production server. Once the problem was solved, his manager was presented with various screenshots and graphs from the tool itself  – he was rather delighted to see all the tabular information,  as well as in a graphical way. Around 8 PM we left the office for dinner.


If you wonder what is the status of my friend’s organization today, here is the quick note.

Action Item for my friend – He needs to pay for our dinner next time.

Action Item for you – Download Dell Spotlight on SQL Server Enterprise today and let me know what you think?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Monitor Database via a Heatmap, Alarms List and Realtime Diagnostics for FREE

Note: This review is based on the performance monitoring and tuning free product Spotlight on SQL Server Freemium.

If you think of a DBA’s life, it has one very simple goal – their server should never go down and all the queries should always perform excellently. However, just like any other life goals o it is not possible to achieve that easily. I have often seen many DBA’s continuously watching their monitor to make sure that their servers are running fine. Another habit of most  DBAs is to continuously check their mobile phone for alerts. Nowadays we get so many alerts it is  getting harder to keep watch on the most important alerts for the health of our server. The habit of looking at the phone and computer monitor is so rooted into a DBA’s mind that they keep on looking at their phone at home to catch a suspicious alert.

Earlier this year, when I attended SQL PASS 2013, I  stopped by the Dell Software booth to see what they have  new in the SQL Server world. I  noticed Spotlight on SQL Server Freemium running on their monitor. When I inquired about the price, I was happy, in fact I was very happy as it was totally FREE! After returning home, I got much too busy with my day job, but  I recently I got some time and I downloaded the Spotlight on SQL Server Freemium FREE tool. The installation was pretty straight forward and easy. It took me less than 10 seconds for me to install the tool, just make sure that your SSMS is closed when you install  Spotlight on SQL Server Freemium, otherwise it will show you a warning to turn off SSMS.

Once I installed the plug-in, it was very easy to use it as it becomes an integral part of the SQL Server Management Studio, the interface is a very user friendly.

There are three distinct options in the Spotlight on SQL Server Freemium tool bar menu. Once you click on Monitoring it will give  three options. 1) Heatmap 2) Alarms 3) Connections. Let’s look at  them very quickly over here.

1) Heatmap

If our server is down, we want to know right away, but if everything is smooth we do not want to keep on getting reminders about that. For that reason Heatmap is a very essential part of  Spotlight on SQL Server Freemium. It gives an ‘at-a-glance’ picture of the state of all the servers DBAs have in their environment. Colors communicate all the information about what is going on with your server. The heatmap takes this a step further by displaying each server as a tile and then aggregating all of the statuses of a server and assigning a size to that tile. It also displays alarms for the connection when touched.

2) Alarms

Alarms  is just an alternate way to view Heatmaps. They display alarms on each server ordered by severity. You can configure and sort alarms the way you prefer. Once an alarm rings an experienced user can do either of  two actions: a) Acknowledge the alarm and solve the issue  b) Snoozing it to be reminded in the future.

3) Connections

This particular area displays various connections to diagnose a server as well as the server which you are monitoring. You can make various adjustments in your server connection in this section.

System Health Check

One of the biggest features of Spotlight on SQL Server Freemium is health check and providing a prioritized list of the key health system issues. Users can pinpoint various issues with the help of this list and resolve SQL Server issues. There are major five categories this tool checks:  Security, Disaster Recovery, Index Optimization, Memory and SQL Best Practice.

In future blog posts we will cover each of these topics in depth. Meanwhile, I strongly suggest you download Spotlight on SQL Server Freemium and makes sure your servers are healthy. Additionally, visit www.SpotlightEssentials.com, the one-stop shop for all things Spotlight.

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