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

 

Seamless Monitoring and Reporting for SQL Server


By:   |   Updated: 2017-09-19   |   Comments   |   Related: More > Monitoring

Problem

Fully monitoring your SQL Server and its related environment can be quite a chore. The SolarWinds Server and Application Monitor (also known as SAM) provides a one-stop method to easily and seamlessly implement a top tier monitoring and reporting system for your SQL Servers. The SAM application goes way beyond just SQL Server, but for this tip, the focus will be on the many advantages and features available for SQL Server products.

Solution

When working with SQL Server, several key components can individually or in combination have an impact on a database’s or even a particular query’s performance and available results. This situation lends itself to many of the global features that SolarWinds Server and Application Monitor (SAM) provides out of the box. The SAM product provides a single reporting dashboard to track and monitor all your SQL Servers (and many other applications too). With just a quick setup, you can be tracking and monitoring your SQL database servers in a very short time, often less than an hour. The setup is assisted by the Network Sonar Wizard; this tool allows for the auto discovery of applications to monitor (it is recommended that initially you limit the scope of the applications to monitor, both to be sure you are tracking the correct machines and to be sure you are monitoring the correct applications).

Discovery Method - Description: Discover Nodes on Network

In a similar fashion, you can manually add a single Node which allows you to add a target server quickly and easily.

Manually Add Node - Description: Manually Add Node

Server and Application Management (SAM) in Action

Once the SQL Server nodes are setup for monitoring, the monitoring can immediately begin. SAM actually provides two distinct templates for observing SQL Server:

  1. AppInsight template for SQL Server which creates a complete and full view of your SQL Server system and
  2. Microsoft SQL Monitor template, which is a basic template and is shown below

Both templates can be easily added to any node, which has been discovered.

Furthermore, a third option is also available that allows for the creation of a customized template that you design. Such a template provides the ability to finely monitor just specific metrics that are self-defined, normally for a very specific purpose.

Add Template - Description: Add Template to a Node.

Even so, the AppInsight template for SQL Server is likely the best choice for most organizations. It provides a holistic and 360 view of a whole sundry of available metrics all predefined for you; it includes over 100 out of the box metrics comprising of counters such as: lock wait time, average wait lock time, active sessions, top expensive queries, top active sessions, top tables and indexes, page splits, and page writes / reads. Furthermore, the AppInsight for SQL template can actually be customized for an organizations specific purpose.

Once the AppInsight for SQL template has been assigned to a node, the metrics flow directly into the dashboard and you can begin to explore all the insight available. As shown in the below illustration, the Application Summary screen gives you a high-level view of all the SQL Server nodes. You are able to quickly see on the left side, all your SQL Servers which have been added to SAM; notice how the icons clue you into issues with particular instances while also providing a list of servers with potential “problems” to be addressed. Red and exclamation points are bad and green stoplights are good. Similarly, the top right side graphic provides a quick visual of your SQL Server environment as a whole.

App Summary - Description: Application Summary Sheet


As an alternative, the SAM Application Summary screen can be used as the starting point for navigating to Application Insight screen for a particular server. This view also provides a quick tool tip pop up with a summary of what is going on with that particular server and why it is being tagged the way it is.


From either navigation path, we can rapidly drill down into the metrics and seek out where SAM is indicating a possible problem. For example, in the below illustration, the average disk reads and writes are causing an alert. However, we can also see that Buffer, Cache, and Connections have a green light indicator.

AppInsight - Description: AppInsight warning.

Clicking on an individual item drills into the full details on the particular data point that is being evaluated. The amount of detail available for the metric is just amazing.  A definition of the statistical value is provided, along with details about what the possible problem is and even remediation strategies you can use to fix the problem. Additionally, an interactive trended chart illustrates a historical view of the data metric and provides a method to change the period for that chart.

Metric Detail - Description: Full Metric details.

While reviewing this screen, you will notice on the left side, all the performance metrics for SQL Server are listed; this navigation bar allows you to quickly select and view another metric that may be related and of interest to the dashboard viewer. Certainly, formal alerting functionality is often very helpful and will be reviewed next.

Page Reads - Description: Pages Read, second metric

Alerts

Certainly as you review your environment, you want to be alerted if certain conditions occur, and SAM includes robust alerting functionally.  As shown below, alerts are aggregated into a single Dashboard object under Active Alerts.

Alerts - Description: Main Alert Screen

In a way similar to the warning notifications that were noted on the Server details, clicking on a particular alert drills into the data for that item. Of course, if you decided that a particular alert is not needed, you are able to disable that alert by using the Turn Off this alert link. That is helpful when you use the alerts defined by the AppInsight template for SQL, but you want to disable one particular alert that is not relevant to your workload.

Alert Detail - Description: Alert Detail Screen

Alternately, the ability is available to Edit the Alert definitions is available, which allows for adjustments to the specific details used to define the alert. Items available for adjustment include fine-tuning the trigger period, the responsible party, and whether an alert is enabled or disabled.

Edit Alert - Description: Edit Alert Window

Alerts also include a neat feature, which “Acknowledges” the alert. With this feature, an alert is muted and future notifications for that alert are halted. Furthermore, the acknowledgement is recorded in an audit table and notes can be entered pertaining to the acknowledgement. For those wanting to allow acknowledgement to occur directly from email, this SolarWinds Thwack Community posts provides a suggestion for completing that process: https://thwack.solarwinds.com/docs/DOC-188081#start=25.

Acknowledge Alert - Description: Acknowledge Alert Window

SAM’s assessment and monitoring extends way beyond simple alerting and notifications; drill down is available in multiple directions.

Holistic View

Certainly, the individual alerts are beneficial, but being able see the full 360 degree picture of potential reasons for a problem is even more beneficial. Generally, the first step in the monitoring and troubleshooting process would be to explore a particular SQL Server in your environment. As noted in the below illustration, we are able to quickly see a large and sundry set of different metrics for the SQL Server being reviewed. We can effortlessly tie these metrics back to an alert (date and time) to get a high-level picture of current issues on the server. The database server metric screen includes items such as:

  • SQL version and edition
  • All active alerts
  • List of database and sizes
  • Current issues posted to the error log
  • SQL related services which are running
  • Trended views of items such as buffer, cache, memory, locks, disks, and connections
Main Database details - Description: Main Database Details Window
Database Dashboard - Description: Database Dashboard

For a DBA, the dashboard visual, which lists the Top 10 most expensive queries, could very beneficial.

Queries - Description: Most Expensive Queries

Clicking on one of the queries drills into full details about the query including the text of the query.

Details for a Most Expensive Query in SolarWinds Application Manager SAM

Insight on the database is obtained by clicking on the database and drilling into statistics about the database. On this screen, metrics such as database and transaction log I/O, database size, log size, and table sizes are all readily available.

SQL Database Details in SolarWinds Application Manager SAM - Description: Database Details

Furthermore, trended values can be reviewed centering on storage and log statistics, as can rankings of index fragmentation.

Storage Statistics in SolarWinds Application Manager SAM - Description: Database Storage Stats

In a similar fashion, we can navigate up the processor chain and drill into specifics about the server itself. At this level, we are able to see a full picture of the physical or virtual machine. The number of available statistics is quite impressive and thorough. The dashboard delivers a bevy of metrics focused on all components of a server such as:

  • Network latency and pack loss
  • Memory utilization
  • CPU utilization
  • Response times
Server Details - Description: Server Level Details

Similarly, helpful and available items certain around virtual machine specifics and component listings.

Additional Server Dashboard details - Description: Additional Server Dashboard details

Further drill down is entirely possibly into the virtualization components by clicking on the Virtual Machine name.

Virtual - Description: Virtualization Monitor

Reports

So far, we reviewed examining the statistics that are related directly to SQL Server. SAM allows you to supplement its online presence and dashboards by generating reports based on the most common features within SAM.  These reports cover just about every aspect of SAM.

Reports - Description: Reports List

Each report, after being executed, can simply be printed or exported to Excel or PDF.

Database Status DB Size - Description: Database Status Size of Each Database Window

For those individuals wanting to customize the reports, the Edit Report link opens the report editor.

Database Status - Description: Database Status Window

After clicking on the Edit Table button, the report can be easily modified to add a new field, remove an existing field, or adjust items such as grouping and sorting.

Edit Report - Description: Edit Report for SAM

Data Correlation with PerfStack Analysis

With SolarWinds Server and Application Monitor data across the application is very helpful for trending and troubleshooting.  This functionality is further extended with PerfStack Analysis enabling data correlation for a specific period of time with layered performance metrics.  In the image below user connections and active connections in SQL Server are correlated with SQL Server Average Reads and Writes in addition to CPU metrics.  This layering of metrics provides tremendous insight into the overall application performance and user experience.

PerfStack Analysis of SQL Server and CPU related metrics

Keep in mind SAM provides a great deal of insight across your applications.  With PerfStack Analysis this is taken to the next level with the ability to see correlated performance metrics across the application in one interface.

Additional Features

SAM also provides the ability to monitor Cloud based services from Amazon Web Services and includes monitoring for applications such as IIS and Exchange (both these have an Application Insight template) and IBM DB2, MySQL , Windows, Linux, and Apache. For the full list of supported vendors, please see: http://www.solarwinds.com/server-application-monitor/supported-vendors.

Application List - Description: Application List

As you can see, the SAM product provides a wide reaching and thorough monitoring of your SQL Server environment and beyond. The SAM features are easy to implement, often allowing you to install and “get working” in a matter of minutes and without technical support. You can see the full system requirements at https://support.solarwinds.com/Success_Center/Server_Application_Monitor_(SAM)/SAM_6-4_Recommended_Requirements. Certainly, deep level monitoring benefits DBAs, developers, and system administrators alike by offering:

  • Automatic discovery of server and applications
  • Shorter troubleshooting and down times
  • Ease of use via the web-based interface
  • Flexibility to manually add and modify servers and applications monitored
  • Real time alerts and notifications

Furthermore, you are able to extend your monitoring and research beyond just an application to the physical server, to the virtualization layer, and even to the cloud.

Next Steps

MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Server and Application Monitor.



Last Updated: 2017-09-19


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

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