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.

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)

About these ads

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)