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.

Today

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

• My friend and his manager are now good friends.
• Dell Spotlight for SQL Server Enterprise is their primary tool for Performance Analysis and Tuning
• My friend is now lead DBA for his new team

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