Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Monitor Storage Performance with a Single Application


By:   |   Read Comments (1)   |   Related Tips: More > Hardware

Attend a SQL Server Conference for FREE >> click to learn more


Problem

My organization has multiple storage solutions with different models for the same vendor, and different models across different vendors. Each product has its own set of tools for monitoring and managing. However, this is a headache because we have to keep bouncing between the tools in order to look at everything. Is there a solution that handles the major vendors in a single application?

Solution

There are solutions on the market to handle this sort of problem. One of them is SolarWinds’ Storage Resource Monitor, which has a useful interface, operates with the major vendors and their offerings, and gives you a good view on performance, including predictive analysis to help you plan to avoid capacity and performance problems. Let’s take a look at what it can do.

“Single Pane of Glass” with a Nice Web Interface

Most vendor products have fallen into one of two categories:

  • Client which needs to be installed on administrator workstations
  • Web-based solution which makes heavy use of Java or Silverlight

When I have a choice between these two options, I generally prefer the client which can be installed on administrator workstations. Using typical deployment solutions, I can automate the installs. I certainly don’t want to have to keep up with Java or Silverlight and patching either. However, I would rather not have to install anything. SolarWinds’ Storage Monitor has a useful web interface without requiring Java or Silverlight. Here’s the initial dashboard:

Storage Monitor Initial Dashboard - Description: Storage Monitors web interface presents a lot of information in a compact space. Right where you need it.

The drill-down capability is excellent, such as when I’m trying to troubleshoot a storage performance issue, because the clickable links are where you’d expect. Also, there’s no check a checkbox and click an action or pull down an action from a drop down. Therefore, navigating through the web interface is quick, which is important when folks are screaming because of a performance issue.

Vendor Agnostic Monitoring Solution

As you’d expect of a third party solution, Storage Resource Monitor handles all the major players in the storage space. Here’s a snapshot of the storage vendor solutions being monitored in a demo environment.

Storage Vendors Being Monitored - Description: All the major players and a lot of the most common storage solutions.

As you can see by this sample, storage solutions in a good state are marked with green, while ones which need investigation have the yellow yield sign with the exclamation point. Storage Resource Monitor makes effective use of these visual cues to draw your attention to where it needs to be.

Performance Troubleshooting Capability

Speaking of visual cues, the main point of a monitoring solution is to help us get to the pain point as quickly as possible. Storage Resource Monitor makes effective use of colors & icons as well as overlay boxes to convey information. For instance, let’s take a look at the CLARiiON warning we saw above:

CLARiiON showing an issue

When I click on CLARiiON, I’m taken to a page where I can see the basic information about the storage device. My eyes are immediately drawn to a shaded area with red circles.

CLARiiON Issue showing an issue with applications.

Hovering over one of those circles, I get an overlay box which tells which app and why this particular application was flagged by Storage Resource Monitor.

Overlay box tells us the issue is with SSRS due to a tasks queued issue.

Clicking on the red circle, I am pulled to the application page itself. Once again, I’m drawn to another shaded box with another red circle. There’s a problem with the LUN which supports SSRS:

Drilling into the app, we see an issue with one of the LUNs.

Hovering over the circle, I get all the information I need to know what’s going on. We have a latency problem, which is highlighted in red.

I’m also advised as to other potential issues, in this case due to the IO size. It took me seconds to get to this information. Drilling through to locate the performance issue is fast and responsive – just what I need in a monitoring solution.

Predictive Risk Built-In

One of the things I like about Storage Resource Monitor is its ability to give predictions about which storage devices will have capacity and performance problems in the future. I hate being in fire-fighting mode, where I’m always having to jump from one problem to another. I’d rather get ahead and increase capacity, whether its storage size or performance, prior to their being a problem. Problems mean angry users. Angry users could result in a negative career altering event (bad performance review or worse, being fired). Therefore, I like that potential capacity and performance issues are flagged based on trending data. Storage Resource Monitor not only tells me it anticipates a problem, but it tells me when it expects the problem to show up. Therefore, I can budget and plan accordingly.

It does report well on current performance issues, too. Here’s a drill down of one particular problem child, Virtual Pool. Even if we’re looking at it from a performance problem, we can still see capacity issues.

Performance Risk Worst Culprits

Drilling in we see that one particular LUN is over-provisioned. If everything uses that LUN to try to max out space, we’re going to be short:

From Performance Risk we see a Capacity Risk

We don’t have to go through Performance Risk to get information on capacity. Here’s an example of a storage LUN that should be okay for years to come unless the usage profile changes drastically. At this point, I don’t have to plan to increase the capacity on this LUN. This is useful information, especially when we get close to budget time.

LUN where there is no anticipated capacity risk within the next 5 years

Reporting Capabilities

Storage Resource Monitor comes with a number of useful reports, some intended for the technician and some for management. Here’s the list.

Reports in Storage Resource Monitor

We looked at capacity planning and there’s a report that gives the information of overall risk. If I want to know which LUNs I need to worry about, I can run a report. It tells me whether or not we’re okay. The following report shows a number of thin LUNs that are already past when we should have added capacity. I also can see volumes and there we’re okay. None of the volumes are in any imminent danger.

LUN and Volume Run Out Date Report

Let’s look into one of those problem children: Concrete.001. Even in the reporting, we still have drill down capabilities. Here’s what we see:

Drill down on Concrete.001 showing capacity planning issue

The total usable capacity is only 3.18 TB, but the provisioned capacity is 17.14 GB. This is the reason the LUN is showing up as having run out. However, when we look at what has been actually used, we’re using 0.01 TB, which is why the projected run-out is showing as over 5 years away. With that said, if everything provisioned began using the space it was given, we’d run out of storage fast. Therefore, it should be included in the Run Out report.

Some of the reports are intended for management. We can use them to justify what we’re seeing. An example is the Execute Storage Summary – Array Capacity. It shows the storage used for each array based on percentage over time. Here we see a 30 day snapshot:

Executive Summary Graph of Storage Usage

Correlated Monitoring with PerfStack Analysis

Beyond the native reporting capabilities with Storage Resource Monitor there is also an opportunity to correlate numerous performance counters with the PerfStack Analysis module.  As shown below, a time period can be selected to review a number of performance counters.  In this example IOPS related counters are being analyzed for a twelve hour period.  With the graphs that stack on top of one another it is easy to see counters that are trending the same, all with a consistent set of data types to avoid any conversion issues.

PerfStack Analysis of IO related counters

With additional products from the SolarWinds Orion suite, you have the ability to review each layer of the application from the application to the storage to pinpoint where the issues are occurring with the PerfStack Analysis module.  This dramatically streamlines the troubleshooting process.

Alerting and Actions

When you’re dealing with multiple vendor platforms, configuring alerting is a pain when you have to go to each interface. With Storage Resource Monitor, that’s not a problem. It can fire actions based on alerts for any of the storage devices it monitors.

The demo environment I was using had more that Storage Resource Monitor installed. As a result, there were a lot more alerts than just related to storage. Here’s a snapshot of a few of them:

However, if I pick a specific alert, such as when usable free space drops too low on a storage array, I can see the active alerts:

Alerts for low free space

If I drill down into the most recent, I get a bit more information (or at least easier to read). I can see why this alert fired, when it fired, and whether or not it was acknowledged (it hasn’t been):

Active Alert details

But alerting isn’t just limited to the web console. This may be hard to read, but I can tie multiple actions to any alert. You can see the number of actions (or the name of a specific action if just one is assigned):

Alerts and the actions assigned

Checking the actions for a particular alert, we’re taken to the action manager. Here’s what one alert activates each time it fires:

We see multiple event log actions along with a couple of email actions. You can specify a lot of actions, including ticket generation in an external case handling system. There’s a lot of flexibility in the actions.

In Summary

Storage Resource Monitor is a solid third-party monitoring solution capable of monitoring disparate vendors and products. It has an excellent web interface with a smart user experience. Getting to performance problems is quick and straight-forward with its drill-down capabilities. In addition, it does risk analysis both from a performance and capacity perspective. There are also included reports which help you justify to management additional purchases. Finally, there’s an excellent, unified alerting and action system which can let you set everything up in one place, rather than going to each individual product’s tool. All in all, it should help you manage your storage devices.

Next Steps

MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Storage Resource Monitor.



Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, September 09, 2017 - 9:48:47 PM - Rob Mandeville Back To Top

 The single pane of glass is great. What is a bonus is seeing downstream of your storage as well. After all, no one complains that LUN XYZed is slow. They call because an app is slow. Connecting the dots really helps (knowing dependencies).

 


Learn more about SQL Server tools