Brad’s Sure Guide to SQL Monitor 3.0

This is an updated article by Brad M McGehee on how DBAs can use SQL Monitor to help monitor their SQL Server instances. It focuses on the latest edition, SQL Monitor 3.0, which includes several key new features, including custom metrics, user roles, and SQL Server 2012 support.

Contents

  • Introduction
  • Why SQL Monitor 3.0 is Different
  • Key SQL Monitor 3.0 Features
  • What Kinds of Data Does SQL Monitor 3.0 Collect and Report On?
  • SQL Monitor 3.0 Architecture
  • How to Start SQL Monitor
  • How to Configure SQL Monitor
  • Adding an Instance to SQL Monitor
  • Configure Groups
  • Set up Email Notifications
  • Setting Maintenance Windows
  • Changing Alert Settings
  • Custom Metrics
  • Users Roles – New feature
  • How Much Historical Data Do You Want to Store?

Using SQL Monitor to Track Alerts Getting the Most Out of the Global Overview Screen Using SQL Monitor to Analyze Performance Summary

Introduction

When I started out as a new SQL Server DBA many years ago, I was in charge of about 20 instances. So as my morning ritual when I got to work, I remotely logged into each instance from my desktop PC, one at a time, and went through a series of checklists, verifying that the instances were running as expected. Some of the checklist items included examining error logs, checking that all the jobs ran successfully, checking for available disk space, examining key performance counters, and much more. Even with only 20 instances, this manual checking was very time consuming. In fact, on some very busy days, I wasn’t able to check every instance because of the lack of time. Added to this, the process got boring and tedious very quickly.

There were very few monitoring applications available then, but I did check out several different ones, and finally settled on one product that more or less worked. It wasn’t a SQL Server-specific monitoring solution, but it was better than manually logging into each server every day and running through my complete checklist. Of course, there was some SQL Server-specific information I wanted to collect that it couldn’t, so I still ended up having to manually monitor some key data. I really wished there had been a SQL Server-specific monitoring solution available when I first started out.

Today, seventeen years later, there are a number of SQL Server-specific monitoring solutions available, and in this article, I am going to take a deep dive into Red Gate Software’s SQL Monitor 3.0 to describe its features, review its architecture, talk about how to configure it, and offer some examples of how you might use it in your environment.

I can already tell you right now that I wish I had this tool back when I was a novice DBA. It would have made my life much easier.

Why SQL Monitor 3.0 is Different

SQL Monitor is both a performance monitoring and an alerting tool. It identifies common performance problems and other key issues of interest to the DBA, only collecting enough diagnostic data to help you identify why it occurred. It is not designed to identify and diagnose every possible SQL Server-related problem, or to recommend ways to boost the performance of your servers, as many other monitoring products try to do. Another way to look at it is by explaining it in terms of the Pareto Principle (also known as the 80-20 rule). In other words, SQL Monitor is designed to identify the most common SQL Server problems. This allows SQL Monitor to maintain a very narrow focus, which results in these important advantages:

  • It keeps the product easy to install, configure, and use. In fact, it only takes minutes to get SQL Monitor up and running. It comes configured, out-of-the-box, to monitor key aspects of your SQL Server instances, saving you time. Many other monitoring products require complex configuration and aren’t as easy to learn to use.
  • It keeps the product lightweight, preventing it from unnecessarily using SQL Server resources that can better be spent on servicing the needs of users. SQL Monitor does not require any software to be installed on monitored instances. Many other monitoring products require an agent to be stored on each monitored instance.
  • It keeps the product inexpensive, so that every one of your SQL Server instances can be monitored, not just the mission-critical servers, as is often the case with other monitoring products that are expensive to license.

So as you read this article, keep in mind that SQL Monitor’s focus is very narrow, and it is narrow for very good reasons.

Key SQL Monitor 3.0 Features

Some of the key features offered by SQL Monitor to make the lives of DBAs a little easier include:

  • SQL Monitor is designed to automatically collect the most common alerts and performance-related issues; all you need to do is tell SQL Monitor which instances to monitor. If you want to tweak a particular alert’s settings, you can do so with only a couple of mouse clicks.
  • Introduced in SQL Monitor 3.0 are custom metrics. This feature allows you to write your own Transact-SQL code to collect virtually any metric you want. Once collected, you can analyze the data graphically and create alerts if desired. To get you started, a library of tested Transact-SQL custom metrics is available.
  • SQL Monitor uses a web interface, which means you don’t have to install a fat client on every computer you want to monitor from. In fact, you can use SQL Monitor from virtually any web browser, no matter the device, or where you are.
  • Another new feature added to SQL Monitor 3.0 is user roles, which allow different people to have different levels of access. They include Administrators, Standard Users, and Read-only Users.
  • SQL Monitor’s web interface offers not only a dashboard to make it easier to get a quick overview of what is happening with your servers, it also offers drill-down screens for viewing more in-depth information.
  • The user interface is streamlined and allows you to quickly see alerts, including diagnostic information that can help you determine what caused them. You aren’t overwhelmed by too much information.
  • Alerts are near real-time, so you are notified quickly of any key problems.
  • Alerts can be viewed from SQL Monitor’s web interface, they can be emailed to you, or they can be integrated with Pagerduty for very flexible notifications.
  • Alerting is intelligent, meaning that you won’t get a lot of unnecessary alerts flooding your inbox.
  • Alerts and diagnostic information are stored for historical analysis, allowing you to rewind the data to the point where a problem occurred, so you can figure out what happened.
  • Because historical data is stored, you can create your own custom reports showing you the information you are most interested in, in a wide variety of formats.
  • SQL Monitor’s three-tier architecture minimizes the performance hit of collecting alerts and performance data from monitored instances, and doesn’t require a resource-intensive agent to be installed.
  • SQL Monitor 3.0 is able to monitor servers running Windows 2000 and higher and SQL Server 2000 and higher (including SQL Server 2012).

Now that you know a little about its major features, let’s learn a little more about what problems it can identify, along with the kinds of performance data it collects.

What Kinds of Data Does SQL Monitor 3.0 Collect and Report On?

Let’s take a brief look at the kinds of data SQL Monitor collects and reports on.

Alerts

SQL Monitor 3.0 includes 22 alerts directly related to your SQL Server instances, and another 11 related to the host machine. I haven’t listed them all here, but you can see them by visiting monitor.red-gate.com/Configuration/Alerts. The monitor.red-gate.com website is a live copy of SQL Monitor that allows anyone on the Internet to view the alerts and performance of the two SQL Server instances that run the backend databases for both the SQLServerCentral.com and Simple-Talk.com websites. Each alert is pre-configured with default thresholds, but if you want to tweak a setting, you can easily do so. Later in this article you will see how alerts work.

Performance Monitor Counters

SQL Monitor tracks a wide variety of Performance Monitor counters, helping you to identify bottlenecks in CPU, memory, disk I/O, or network resources. To make this easier, Performance Monitor counters are divided into three categories:

  • Ten counters related specifically to the host machine the instance is running on.
  • Eighteen counters related specifically to each instance.
  • Nine counters related to each individual database on each instance.

If you would like to see a list of all the Performance Counters collected by SQL Monitor, visit http://monitor.red-gate.com/Analysis/Graphs. Later in the article you will see how they work.

Custom Metrics

If the built-in alerts and performance counters don’t provide all the data you want to monitor your SQL Server instances, then you can write your own Transact-SQL code to create any type of custom metrics and alerts you like. This will be demonstrated a little later in this article.

Top Ten Most Expensive Queries

SQL Monitor has the ability to identify the top 10 most expensive queries by instance and by database. I’ll talk more about this feature a little later.

Top Ten Most Expensive System Processes

It is often important to know what processes are running on your SQL Server box, and this feature keeps track of the most resource intensive processes running on your server, helping you to determine if it is a SQL Server instance, or perhaps another application, that is using up valuable resources.

Basic Server, Instance, and Database Properties

For each server, instance, and database, essential properties are collected, such as the OS version, SQL Server version, Service Pack levels, and much more. This information helps you stay informed about the basic configuration of your environment. We will see this demonstrated later.

Profiler Data

While all of the above information can be important when troubleshooting problems, sometimes you need even more detailed information. Because of this, SQL Monitor has the ability to collect selected Profiler data as needed. Normally, this feature is left off because it can be resource intensive to collect lots of Profiler data, even though a server-side trace is used. Generally, it is only turned on to help provide more extensive data about specific alerts. For example, if tracing has been turned on, and a specific alert fires, then you will be able to see the Profiler events that occurred before and after the alert.

Turning Back Time

While SQL Monitor maintains and displays real-time data, it also stores past data in a database so you can roll back to see what was happening at a particular point in time. This is great for diagnosing problems that occurred when you were not around. By default, data is stored for one week, but you can vary the range of historical data that is collected to meet your needs. There’s more on this feature later in the article.

SQL Monitor 3.0 Architecture

Before we dive into the details of how to configure and use SQL Monitor, we need to take a high-level look at how it is designed. SQL Monitor is divided into three key parts:

  • Base Monitor Service: This is the tier of the application that includes the business logic and does most of the work, such as collecting alerts and performance data from your monitored SQL Servers. It runs as an ordinary Windows service.
  • Web Server: You can choose to use the web server included with SQL Monitor (optionally installed during installation) or use an existing IIS web server. It is used to serve up the SQL Monitor user interface via a web browser.
  • Data Repository Database: This is a SQL Server database where configuration and historical data are stored.

1302-image001.png

Figure 1: SQL Monitor uses a three-tier architecture.

While all three components are required to run SQL Monitor, these components can be installed on a single server, two different servers, or three different servers, depending upon your needs. Installing them on different servers allows you to better scale SQL Monitor so that it can monitor more SQL Server instances.

So how does SQL Monitor collect all the data it needs without installing an agent on each SQL Server instance that is monitored? It does this by remotely connecting to each server, using a combination of WMI, T-SQL, remote registry, remote file access, and ping, collecting information and storing it in the data repository. Because it avoids installing any components on the monitored servers, the resources used to monitor each SQL Server instance are minimal.

How to Start SQL Monitor

Once SQL Monitor is installed, you can access it by pointing to the web server used for SQL Monitor, using an address such as http://webservername:8080. Notice that you need to include not only the web server’s name, but the port number specified during the installation. Once you are connected for the first time, you will be asked for a password. This password is used to prevent non-authorized people from accessing SQL Monitor. You will need to use the password every time you access SQL Monitor through whichever web browser you choose to use.

If you want to access the SQL Monitor web server outside your organization’s firewall, you will need to configure the web server so that it can be accessed through the firewall. I found this very easy to do on my home network, and was able to use my iPad to access SQL Monitor using the Safari web browser.

Note: After installing SQL Monitor, you will also need to enter licensing information and activate it. See the Help file for instructions on how to do this. You don’t have to enter the licensing information immediately, as SQL Monitor will work as a free trial for 14 days.

How to Configure SQL Monitor

Once you have accessed SQL Monitor for the first time and entered a password, you are ready to begin adding the instances you want monitored. Once you have done this, monitoring begins immediately and no further setup is required. Of course, if you are like me, you may want to tweak some of the optional configuration settings, but this is not required. In this section, I am going to show you how to add an instance to SQL Monitor, and then give you a brief introduction on the available configuration options.

Adding an Instance to SQL Monitor

After logging in the first time, click on the link “Monitored Servers” (see figure below) to add your first SQL Server instance.

1302-image002small.png

Figure 2: This is the Global Overview screen, which is where you always start when using SQL Monitor.

After clicking the link, the “Monitored servers” screen appears.

1302-image003small.png

Figure 3: Adding an instance to SQL Monitor requires entering two sets of credentials.

To add a new instance to SQL Monitor, you must enter the server’s name and two sets of credentials. Enter the name of the server to be monitored in the box at the top of the screen. Next, you must enter two sets of credentials, or just one set if they are both the same.

The “Host machine” credential is an account that generally has local admin rights to the server where the SQL Server instance is located, although local admin rights are not required. See the SQL Monitor documentation for the specific rights and permissions needed. It is a best practice to create a dedicated domain account for this purpose. This credential is needed by the SQL Monitor Base Monitor Service in order to gather data from Windows Server.

The “SQL Server instance” credential is used to access the SQL Server instance, and generally has SQL Server sysadmin rights, although this is not mandatory. As with the “Host machine” credential, the SQL Monitor documentation lists the specific permissions required to allow SQL Monitor to communicate with SQL Server. As with the “Host machine” credential, it is a best practice to create a dedicated domain account for this purpose. This credential is needed to allow the SQL Monitor Base Service to gather data from the SQL Server instance.

If you like, you can use the same domain account for both purposes, but for best security, two separate accounts should be used.

Once you have added the first SQL Server instance to SQL Monitor, you can proceed to add further instances by clicking on the Manage monitored servers link on the “Global Overview” page, and repeating the steps above, until you have added all the SQL Server instances you want to monitor.

Configure Groups

If you intend to manage many SQL Server instances with SQL Monitor, you can group the managed instances into “Production” and “Development”, or whatever groups make sense. This allows you to:

  • Display the related instances on the SQL Monitor screen together, so they are seen together as a group; and
  • Filter and configure Alerts for all of the instances in the group at one time, which can save you work.

To create a group, click on Manage groups from either the “Global Overview” or ‘Configuration” screens.

Set Up Email Notifications

Immediately after an instance is added to SQL Monitor, it begins to collect data and report any alerts on the “Alerts” screen. You will probably want to set up email notifications so that you will be notified if an alert is fired when you’re not watching. Email notifications are set up from the Configuration screen.

SQL Monitor 3.0’s alerting feature works with PagerDuty, which allows email messages to be sent via phone and SMS, if you need this. This is a third-party cloud-based application subscription service.

Setting Maintenance Windows

Most of your SQL Server instances will have maintenance windows where a lot of additional server activity will occur, such as backups, rebuilding indexes, and running DBCC CHECKDB, all of which can put an extra burden on the server’s resources and could trigger some performance-related alerts. To avoid this, you can set a maintenance window for each of your monitored servers, which tells SQL Monitor not to trigger any alerts during this time interval, although monitoring still continues. This is a good thing, as you don’t want to get email alerts at night or on weekends that are essentially false positives.

Changing Alert Settings

While alerts come pre-configured in SQL Monitor, you have the ability to modify the alerts, such as turning them off or changing the threshold for when they are fired. To change any alert, click on the “Configuration” tab from SQL Monitor and select “Alert settings”.

1302-image004small.png

Figure 4: This screenshot only shows a portion of the entire screen.

The “Alert” settings screen lists every default alert type and allows you drill down into each alert. For example, let’s say you want to change the “Long-running query” alert. To do that, all you have to do is click on it, and the screen changes, as you see below.

1302-image005small.png

Figure 5: You have many options when configuring alerts.

You can see that you have many options when configuring an alert. For example, you can turn it on or off, set the thresholds for when different types of alerts are fired, and for this particular alert, specify particular SQL process names or queries that you want to exclude from an alert. For example, let’s say that a once a month report typically takes 30 minutes. Since you know this is always the case, if there is nothing you can do about it, you can exclude it so that you won’t be alerted every time it runs.

Custom Metrics

Creating custom metrics is a powerful feature and they are easy to add to the defaults ones already built into SQL Monitor. To create a custom metric, you use a three page wizard that allows you to

  1. Define the metric.
  2. Optionally create an alert on the metric.
  3. See a summary of the options you have selected as last review before it is created.

Let’s take a look at how you can create a custom metric alert that fires when a database file increases in size. As a side note, I like to avoid using autogrowth to grow my data files, instead, manually increasing them myself as needed. This custom metric alert will let me know when a data file grows, which tells me that autogrowth has probably kicked in (or someone else has manually grown the data file), letting me know that I haven’t been paying close enough attention to the growth of the database.

After choosing “Custom metrics” from the “Configuration” screen, the following screen appears.

1302-image006small.png

Figure 6: Custom metrics are created with this screen.

To start the Custom Metric Wizard, click “Create Custom Metric” and the Wizard displays this first screen, which is shown in two screen shots below.

1302-image007small.png

Figure 7: The first steps are to name the metric, add a description, and add the Transact-SQL code.

To create a custom metric, give it a name, a description, and then enter the Transact-SQL code that you want to run to collect the metric data. It is a good idea to create and this Transact-SQL code in SSMS first to ensure that it not only works, but that it is as lightweight as possible. You will want this code to run as fast as possible, using the least amount of server resources.

Note: The sample Transact-SQL code above is taken from the sample library (in PDF form) provided by Red Gate Software here: http://www.red-gate.com/products/dba/sql-monitor/assets/files/example-custom-metrics-and-alerts.pdf.

1302-image008small.png

Figure 8: Additional custom metric options.

Next, further down on the same screen, you must specify which servers, instances, and databases you want the metric to collect data on, how often it will collect the data, and if you want the metric to use a collected or calculated value. A calculated value is the difference between two captured values over the collection frequency. This option if often needed for counters that increment continuously whenever SQL Server is restarted. In my example, I have configured it for all servers, instances, and user databases, to collect the data once a minute, and to use a calculated value.

Note: The more often you collect data for your custom metric, the more resources will be needed. Because of this, it is a good idea to collect data only as often as you really need to.

Notice near the middle of Figure 8 a button called “Test metric collection”. This allows you to test your metric to see if it really does what you are expecting it to do. I highly recommend you perform this test to ensure that you are getting the custom metric data returned as you expect.

Once you are done with the first screen of the Wizard, click “Next”.

1302-image009small.png

Figure 9: A custom metric can be associated with an alert, if desired.

The second page of the Wizard allows you to create an alert that can fire if the custom metric you create matches a threshold value you specify. You don’t have to create an alert on a custom metric, but it can be handy, depending on your goal of the custom metric. In my example in Figure 9, I have specified that I want to be notified with an alert if any data file increases in size. You can set whatever threshold works for your situation.

Once you are done with the second screen of the Wizard, click” Next”.

1302-image010small.png

Figure 10: The last page of the Custom Metric Wizard.

The last page of the Wizard lets you review the custom metric before creating it. If everything looks OK, click on “Create metric and alert”, and it is created for you. Now that the custom metric and alert have been created, they will act just like any of the built-in metrics and alerts, and will begin working immediately.

Users Roles

Another new feature in SQL Monitor 3.0 is user roles. This feature allows different users to have different levels of access to SQL Monitor. The three available roles are:

  • Administrator: Unrestricted access to every feature in SQL Monitor.
  • Standard User Role: Able to manage and configure alerts, view most administrative screens, and view reports.
  • Read-Only User Role: Cannot do any configuration, but can view most administrative screens and view reports.

If you are the only person using SQL Monitor, then you won’t need to create additional roles. But if you want others to access SQL Monitor, but with less privileges, then you will want to set up roles. Creating roles is done under the “Manage user roles” option on the “Configuration” screen.

How Much Historical Data Do You Want to Store?

By default, SQL Monitor only keeps one week’s worth of data before purging it. If you like, you can reduce or increase this amount, depending on how much historical data you want to keep, and how much hard disk space you have available. This setting is made at the “Configuration” screen under the “Data purging” link. If you have the space, I would recommend keeping a month’s worth of data, which will make it easier for you to view trends over the typical business cycle of a month.

That sums up all the most important configuration settings. Now it’s time to see how you can use SQL Monitor. We will learn about using “Alerts”, using the “Global Overview” screen, and using the “Analysis” screen.

Using SQL Monitor to Track Alerts

Once SQL Monitor has been installed and the instances added, it automatically begins to collect data. The first screen that comes up after starting SQL Monitor is the “Global Overview” screen, which you see below.

1302-image011small.png

Figure 11: The “Global Overview” screen is where you begin in SQL Monitor.

To keep my example simple, only one SQL Server instance is currently being monitored. The host machine and the SQL Server instance are listed in a hierarchy on the screen. Why? Firstly, a host machine might be running two or more SQL Server instances, and this view makes this obvious to anyone looking at this screen. Secondly, there are different alerts for the host machine and the SQL Server instance. The higher level of the hierarchy (bradpc in this example), rolls up all the alerts for all the lower levels (the SQL Server local instance). For example, the 107 alerts shown for bradpc include the 101 alerts for the local instance.

Note: If you have a clustered SQL Server, which SQL Monitor fully supports, you will see a hierarchy of cluster name, host machine names, and instance names. These are automatically grouped together for your convenience when monitoring a cluster.

As you can see, this particular SQL Server instance and its host machine currently have 107 alerts. This may seem like a lot, but the number is large for two reasons. First, because I have intentionally caused a lot of alerts for this demonstration, and second because I have not cleared any of the alerts.

When an alert is raised, it stays raised until you, or another DBA with proper permission, clears it. This provides you with a list of tasks (alerts that need attention) for you to investigate. Once you have finished investigating an alert, you can clear the alert and continue with your next task, and so on, until all your tasks are done. This can be a convenient way to know which alerts you have investigated and which still need attention.

For example, let’s say you have just come to work in the morning and you bring up SQL Monitor and look over the “Global Overview” screen. The first thing you want to do is check to see if there are any alerts, and if there are, drill down into them to investigate. Alerts are color-coded so you can determine which ones are the most critical, and you can prioritize them in this order, thus focusing your time on the most important alerts first.

To view the alerts, click on the “Alerts” tab from the “Global Overview” screen.

1302-image012small.png

Figure 12: A portion of the alerts.

Typically, my next step would be to review the alerts and see which one looks most important to me, then deal with it. For the sake of this article, let’s drill down into the “Long-running query” alert (the first one on the screen). To drill down, all you have to do is click on the alert, and the following drill-down screen appears.

1302-image013small.png

Figure 13: This screen shot shows only a portion of the alert drill-down screen.

Because this screen is too large to easily show, I will discuss it in sections. You’ll have noticed that there are tabs near the top, and in the middle of the screen. We will look at some of these shortly.

For now, I want to see what query caused the alert to be fired. In this case, it seems that an ad hoc query (SELECT * FROM dbo.in_tran_tbl) was executed at 2:59 PM, and that it took 45 seconds to run. Assuming that I had not seen this ad hoc query before, my goal would be to find out who was running the query and see what I could do to get the person running it to rewrite it, replacing the * with the columns they really needed to return, and adding a WHERE clause to limit the number of rows returned. Of course, I am making the assumption that this will help speed up the query, but for now, it is a good working hypothesis.

The next thing I would probably do is look at some key performance counters when the ad hoc query ran. What is really nice is that the performance counters I currently see on the screen are a snapshot of them when the query actually ran, so I can see what else was going on at the same time. To better see these counters, I need to scroll down the screen.

1302-image014small.png

Figure 14: A quick snapshot of what was happening on the host machine during the alert.

In each of the graphs you see a gray and green bar. The gray bar represents when the alert was fired, and the green bar (if there is one), indicates when the alert ended. Because these bars represent the alert, not the actual query, these two bars don’t show when the query started and ended, although they are a close approximation. This is because an alert won’t be fired until its threshold is broken, and this will be some time after the query began. But we can look at the start time of the query, which is 2:59 PM, and since we know it ran for 45 seconds, we can check out the graphs for this time frame to see what the performance counters were doing when the query was running.

The six graphs you see of performance counters are from the “Host machine” tab, and by looking at them, we can see how busy the server was during the time the query ran.

For additional performance counters, I can click on the “SQL Server” tab (see figure 14) and see three SQL Server-specific counters for even more information that might help me determine what the effect of the query was.

1302-image015small.png

Figure 15: A quick snapshot of what was happening on the SQL Server instance during the alert.

While we are at it, let’s check out some of the other tabs available on the “Alert” tab to see if we can pick up any additional useful information. I am not going to look at all of the tabs, as I don’t have enough time to write about each one, plus some of the tabs have data that is not particularly relevant to the specific problem at hand. As an example, let’s take a look at the “Occurrences” tab.

1302-image016small.png

Figure 16: This particular alert has executed only once.

The “Occurrences” tab tells us that this particular query has only created a single alert. If the same query had run 30 times, then you would see 30 occurrences on this screen. The purpose of this tab is to let you know how often this particular alert occurs, which can be useful when troubleshooting potential problematic queries. Since this query was executed once, it might be a one-off ad hoc query that may never happen again. But if you see many occurrences of this same alert for the same query, then you may want to investigate what application or user is running it.

So how do you find out who ran a particular query? If you look back at figure 13, which shows the first screen of the alert, you can see that process 54 is where the query ran. So who ran process 54? To find out, you can click on the SQL processes/Profiler trace tab. This tab displays all of the processes that were running at the time of the alert, and tells you who the guilty culprit was.

1302-image017small.png

Figure 17: You can identify who (or what application) ran a query.

The “SQL processes/Profiler trace” tab lists all the processes, so I have only shown you the one that relates to the problem at hand. And as you can see, the guilty party, in this case, was me. I am my own worst enemy. Well, actually, I did this for the demo. Really.

In any event, now you know who has executed this poorly designed ad hoc query, you can track them down and make them fix it, or at least take away their permissions if they don’t fix the problem. As you can see, SQL Monitor makes it easy to identify slowly running queries, provide you with background information about what was going on when the query executed, and help you identify the culprit.

Alerts offer a lot more than what I have just shown you in this simple example, but hopefully you are now getting a feeling for their power.

Getting the Most Out of the Global Overview Screen

We have already seen the “Global Overview” screen, or at least a part of it, when we began to look at the alerts captured by SQL Monitor. Besides alerts, it also displays a graph showing the CPU utilization and memory used over the past 5 minutes. The purpose of these counters is to give you a quick idea of what is going on with the servers at a glance.

1302-image018small.png

Figure 18: The “Global Overview” screen has lots of hidden features.

Some of the features are somewhat hidden on the “Global Overview” screen: firstly, there is the clock icon at the top right hand side of the screen. When you click it, something very neat happens.

1302-image019.png

Figure 19: You can look back into the past with the Rewind time feature.

By selecting a specific point in time, you can go back in time and see what was going on with your SQL Server (as far back as you store historical data). For example, say a user called you today and said that a query he ran three days ago took too long. I can remember these types of questions coming up a lot when I was a full-time DBA, and it used to be impossible to answer them. But with SQL Monitor I can rewind time and see exactly what was going on with the server three days ago and pinpoint the problem.

Another powerful feature available from the “Global Overview” screen is the ability to drill down into details at both the host server and SQL Server instance levels. For example, at the “Global Overview” screen, if I click on the machine name, this screen appears.

1302-image020small.png

Figure 20: Viewing information about the host machine.

As you can see, there is a wealth of information about the host server, including key data on CPU, Memory, Disks, Network, Server Properties, and System Processes. In effect, it is a snapshot of what is happening on your server right now.

If you click the instance name from the “Global Overview” screen, you get information directly related to the SQL Server instance.

1302-image021small.png

1302-image022small.png

Figure 21: Viewing details of a specific instance. It took two screen shots above to see the entire page.

As you can see, there is a wealth of information provided about the SQL Server instance. One of the most interesting features is the Top 10 expensive queries list. This allows you to see the most expensive queries on the server for a variety of time ranges. The default is to show the last 5 minutes of data, but you can go back as far as you have historical data. This helps you identify resource-intensive queries that need to be examined in more depth.

While there is information on each of the databases on this screen, you can drill down even further by clicking on any of the databases, which brings up a screen of information specifically on that database.

1302-image023small.jpg

Figure 22: When you drill down into a database, this is the information provided.

As you can see above, there is a wealth of information available about the database. You can do this for any of the databases on your server.

Using SQL Monitor to Analyze Performance

The “Global Overview” screen also includes an “Analysis” tab. It is used to graph and view any built-in and custom-metric data. This is a great way to view performance data over different time periods, and to perform baseline testing.

When you click on the “Analysis” tab, the following screen appears.

1302-image024small.png

Figure 23: The Analysis tab.

When the screen first disappears, it is empty, so you need to select one of the default Performance Monitor counters that are included, or any custom metrics you have created. For example, when I select Machine: Processor time, the page changes to what you see below.

1302-image025small.png

Figure 24: You can graph many performance counters using different time frames.

By default, the graph shows the last 10 minutes of activity for the performance counter you have selected. But if you go to the Time range dropdown, you can select from the last hour, the last 24 hours, yesterday, last seven days, last 30 days, and even compare time periods, giving you great flexibility for viewing each counter. In addition, you can set any custom time span you want to view.

On the right of the graphs is a short explanation of each counter, and if you click on the Statistics tab, you will see a summary of the graph in the form of the mean, maximum, and minimum values.

I wish I could show you more of these counters, but I don’t have enough time to cover all of them. The best thing is to try all of them yourself using the live demo at monitor.red-gate.com.

Summary

SQL Monitor is unlike other SQL Server-specific monitoring tools, as its sole focus is making it easy and convenient to monitor all of the SQL Server instances in your environment as inexpensively as possible.

While this article was a little on the long side, it really just offers a glimpse into the power SQL Monitor offers DBAs. The only real way to find out its potential, and to see if it meets your needs, is to give it a try. The easiest way to give it a try is to visit monitor.red-gate.com, where you can see SQL Monitor in action as it monitors the two-node cluster than runs the databases for SQLServerCentral.com and Simple-Talk.com. Alternatively, you can download it for a 14-day free trial and see for yourself how it works in your own environment. Only after trying it yourself will you be able to discover how different SQL Monitor is from other monitoring tools.