Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitoring and Tuning SQL Server with SQL Diagnostic Manager


By:   |   Last Updated: 2015-01-07   |   Comments   |   Related Tips: More > Monitoring

Problem

Managing SQL Server can be quite a challenge and as a DBA you can only do so much in a day, so there are many things that just get overlooked on a day to day basis for most SQL Server instances.  This is not the fault of the DBA it's just reality.  Unless you have a plethora of DBAs managing a handful of SQL Server instances there are just things that get overlooked from day to day or worse yet get overlooked entirely.  The general mode of operation is to address the items that are right in front of you and this is generally the case when things go awry.

The other problem companies face is not having a dedicated DBA.  The DBA role is bundled together as a secondary role for Network Admins, Developers or the general IT Professional.  This further erodes the time this person has to address DBA level activities and therefore crucial items are definitely getting overlooked until it's too late.

The role of the SQL Server DBA continues to get more complex as Microsoft continues to add new features and technology to the SQL Server offerings.  It takes a lot of time to get up to speed to understand the new features and how they can be utilized which in turn takes time away from daily operational matters.  The operational items are what keeps our companies in business, sure the new features could lend to future success or resolve problems you have been facing for years, but if an operational issue occurs this affects the entire system and is noticeable almost immediately by the system users both internal and external.

So to balance the day to day needs as well as planning, testing and implementing for future needs DBAs need tools that can offload time consuming operational tasks such as checking that SQL Agent jobs were successful, that backups completed, processes are not blocking and the list goes on.  The other item that is key to a DBAs success is having the data to troubleshoot problems, not just the problems occurring right now, but problems that may have occurred off hours or at a time when a DBA wasn't able to immediately troubleshoot the problem. The last area that is critical is being alerted in a timely manner for only the things that really matter.  If too many alerts are sent the tendency is to begin to ignore these alerts and eventually ignore all alerts which defeats the entire purpose of getting alerted in the first place.

SQL Server offers so many methods of data collection such as event/error logs, traces, extended events, the system default trace, auditing, PerfMon, alerts, activity manager, performance warehouse and more.  Each of these takes time to setup as well as gaining the knowledge to know how to use these tools effectively without just data overload.  So what's a DBA to do?

Solution

Thankfully there are third party tools like Idera SQL Diagnostic Manager which can help us tackle all of these problems and more.  SQL Diagnostic Manager was introduced over 10 years ago and Idera has a full-time dedicated development team that constantly makes improvements as well as adding new functionality to address all of our DBA needs.  The advantage of a tool like this is it gives us one unified interface to manage our entire SQL Server enterprise, not just one instance at a time.  By taking the time to learn how to use this one tool, we can eliminate the need to learn how to use each individual tool that comes with SQL Server.  SQL Diagnostic Manager pulls all of this data together, so we can focus on problem resolution instead of data collection.

SQL Diagnostic Manager Enterprise view of all your SQL Servers

One of the challenges when managing SQL Server is that you probably have several instances that need to be monitored.  Just connecting to each instance and going through the same motions over and over again is time consuming.  So having a global view of your environment is a major time saver and with SQL Diagnostic Manager you have a couple of global views to see your entire SQL Server enterprise all at once. 

The first screen is the SQLdm Today screen as shown below.  This screen gives you a status summary for all of your servers, so you can drilldown into each server to address any issues.  In the image below, on the left side we have a summary listing of our servers and whether they have issues that need to be addressed.  On the right side we can see a list of alerts that need to be addressed for all of our servers.  This is a quick way to get a To Do list of items that need to be addressed for all SQL Server instances instead of having to check instance by instance.

SQLdm Today screen

Another global view is the ability to look at All Servers at once to see what's currently happening.  The below screen shows three servers that I am monitoring and the current state of each server. We can see high level information such as Response Time, Sessions, CPU, Memory and Disk utilization as well the Red X that indicates there are items that need to be addressed for that instance of SQL Server.  From here we can drilldown into a specific instance to address issues or to get further details as to what is going on.  This is a great way to see your entire enterprise at a glance.

SQL dm all servers view

SQL Diagnostic Manager SQL Server details by instance

If we use SQL Diagnostic Manager to drill into a specific instance we can see a lot more data as well as several graphs that can help with managing current usage and performance as well as historical insight.  The first view is the dashboard view as shown below.  Here we have many more performance counters exposed and displayed in a graphical manner. Each graph has drilldown capabilities to get even more data about that specific component which can also be accessed from the Resources tab.

SQL dm drill into an instance of SQL Server

Under each tab (Overview, Sessions, Queries, Resources, Databases, Services and Logs) there are several Views, Actions and History options that can be utilized. Starting from our All Servers view, with just one click we can lots of insight for that SQL Server instance.

SQL Diagnostic Manager History Browser

The History Browser is key when troubleshooting issues that happened in the past or even to just get a comparison for different points in time.  This feature in SQL Diagnostic Manager allows you to get to different points in time to see what the instance metrics looked like for that time slice.  This is extremely helpful when you are trying to troubleshoot a problem that occurred off-hours or you just weren't sitting at your desk to troubleshoot the problem in real time.  In the view below, I have selected 11:04am.  The graphs display data based on how the server was being utilized at that time.  I can also use the Previous and Next Snapshot buttons to scroll forward or backward through each time period to see how things progressed throughout the day.

SQLdm history browser

SQL Diagnostic Manager Alert System

Alerts are a very important part of SQL Diagnostic Manager for monitoring SQL Server.  If we had nothing better to do all day, we could sit and watch a console and take action when something doesn't look quite right.  That would be nice if we had the time, but it would probably get pretty boring.  Luckily with SQL Diagnostic Manager we have the ability to setup alerts, so when a certain threshold is reached a notification is sent or a corrective action is taken automatically. 

One problem with setting up alerts is knowing what to setup as well as determining what the thresholds should be.  Because of this, alerts are rarely setup for SQL Server. The nice thing about SQL Diagnostic Manager is that it comes preconfigured with over 100 alerts that you can use as is or further configure for your environment.  Below is a screen shot of the default template that has alerts for Databases, Logs, Services, Resources, Virtualization and more.

SQL dm alert templates

Here is a closer look at setting up an alert.  This alert tracks to see how full disk storage is with the ability to setup various states whether the alert is just a warning if it is between 75% and 90% or that it is critical if it is between 90% and 100% full.  Again all of these measures are configurable, so you can adjust to meet your specific needs.

sql dm alert disk space

In addition to alerts, SQL Diagnostic Manager also has the ability to take Actions based on the alert.  The below template allows you to setup Conditions that must be met and then Actions to be taken such as running a script, running a job, sending an email, etc.

sql diagnostic manager alert actions

SQL Diagnostic Manager Mobile Interface

DBAs are not always at their desk waiting for something to happen, this is why SQL Diagnostic Manager offers a mobile interface to check on status of your environment as well as take corrective action such as running a query, restarting a job, etc.  Below is one view of the mobile interface. 

SQL diangostic manager mobile interface

SQL Diagnostic Manager Reports

In addition, to the features we discussed above there are many built-in reports.  The screenshot below shows some of the reports that are available. So if you need reports for historical records or if you need reports to use for meetings, these built-in reports allow you to pull various data in a useable format instead of having to create custom reports or spreadsheets to get similar results.  And these reports can be used for any and all servers that you are monitoring with SQL Diagnostic Manager.

sql dm reports

Here is a quick look at the Enterprise Summary report.  This gives us a global view of our monitored services with the click of just one button.

sql dm enterprise summary report

Tools to recreate the functionality of SQL Diagnostic Manager

As mentioned in the problem statement, there are several tools that you need to use to monitor SQL Server.  To recreate the functionality that we covered in SQL Diagnostic Manager you would need to use some or all of the following:

  • PerfMon
  • WMI
  • SQL Server Alerts
  • Create custom GUI applications
  • Activity Manager
  • PowerShell
  • Windows Alerts
  • Scheduled Jobs or Tasks to collect the data
  • SQL Server DMVs
  • T-SQL queries to read system tables
  • SQL Server trace
  • Use of the system default trace
  • Create a custom data repository
  • Create code to read the SQL Server and Windows Event Logs
  • Custom Reporting Services reports
  • Build an Alert System to fire based on thresholds
  • and more

In addition to the above, you would also need to figure out what to capture and how to capture the data.  This may sound trivial, but this is usually one of the hardest things to figure out. You could just start collecting data and create some alerts based on known issues, but what about all of the things you are not aware of?  The other thing you will need to deal with are the differences from version to version of SQL Server. With each release Microsoft adds or tweaks how things are done, so you will need to constantly keep abreast of these changes and modify your data collection processes to work with each version of SQL Server.  Why bother when it has already been done for you?

Recommendation

Based on all of the functionality that comes with SQL Diagnostic Manager, the fact that Idera has a dedicated development team, the need to handle differences between versions of SQL Server as well as build new processes as new features are added it's pretty much a no-brainer that in order to effectively monitor SQL Server using a third party tool is the way to go.

I know cost is always a factor when there is a need to purchase tools, but think about how much time it would take and the cost to reproduce just a fraction of the above capabilities.  When you think about what you get with third party tools, the cost is just a fraction of what it would cost to build something like this in house. 

To take a look at the cost in real numbers, if one senior DBA spent two full weeks to recreate all that SQL Diagnostic Manager can do, you would have already paid for the product.  I highly doubt that in two weeks anyone would be able to recreate all of the functionality, in reality it would probably take you two weeks just to scope out the project and write up requirements. To look at this another way, if you spend just one hour per week in trivial tasks such as connecting to each instance, running the same queries, etc. again in one year you could pay for the cost of the product and now spend that hour on doing things that really make a difference.

So download a free 14 day fully functional trial of SQL Diagnostic Manager and see for yourself. Installation takes as little as 10 minutes and the tool is agentless, so you do not need to install anything on your SQL Server boxes. It supports SQL Server 2000 and all later versions. Also, with the trial there is no limit to how many SQL Server instances you can monitor.

Next Steps

MSSQLTips.com Product Spotlight sponsored by Idera makers of SQL Diagnostic Manager.  



Last Updated: 2015-01-07


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools