Seamless Monitoring and Reporting for SQL Server
Fully monitoring your SQL Server and its related environment can be quite a chore. The SolarWinds Server & Application Monitor (also known as SAM) provides a one-stop method to easily and seamlessly implement a top tier performance monitoring and reporting system for your SQL Servers. The SolarWinds SAM application goes way beyond just SQL Server, but for this tip, the focus will be on the many advantages and features available in this monitoring tool for SQL Server performance issues.
When working with SQL Server, several key components can individually or in combination have an impact on database performance, T-SQL scripts or even a particular query’s performance and available results. This situation lends itself to many of the global features that SolarWinds Server & Application Monitor (SAM) provides out of the box. The SolarWinds SAM product provides a single reporting dashboard to track and monitor all your SQL Servers on-premises and in the cloud (and many other applications too as part of the Orion platform). With just a quick setup, you can be tracking, monitoring and optimization of 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).
In a similar fashion, you can manually add a single Node which allows you to add a target server quickly and easily, but no time is needed to configure SAM to begin capturing data and providing deep insight into your environment.
SolarWinds SAM in Action
Once the SQL Server instances are setup for monitoring, the monitoring solution can immediately begin. SAM actually provides two distinct templates for observing SQL Server:
- AppInsight template for SQL Server which creates a complete and full view of your SQL Server system and
- Microsoft SQL Monitor template, which is a basic template and is shown below
Both SQL Server database 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 to identify specific bottlenecks. Such a template provides the ability to finely monitor just specific metrics that are self-defined, normally for a very specific purpose.
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 performance tuning counters such as: lock wait time, average wait lock time, active sessions, top expensive queries, top active sessions, top tables and indexes, page splits, deadlocks, memory usage 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 from the data collection. 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 Microsoft SQL Server environment as a whole.
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.
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.
While reviewing this screen, you will notice on the left side, all the performance metrics for SQL Server monitoring 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.
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.
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.
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.
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: click here to get started.
SAM’s assessment and monitoring extends way beyond simple alerting and notifications; drill down is available in multiple directions.
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 sizes and disk space
- Current issues posted to the error log
- SQL related services which are running
- Trended views include performance counters such as buffer cache, memory, locks, disk space and connections
For a DBA, the dashboard visual, which lists the Top 10 most expensive queries and SQL statements, could very beneficial for root cause analysis.
Clicking on one of the queries drills into full details about the query including the text of the query.
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.
Furthermore, trended values can be reviewed centering on storage and log statistics, as can rankings of index fragmentation.
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 performance monitor metrics to troubleshoot all components of a server such as:
- Network latency and pack loss
- Memory utilization
- CPU utilization
- Response times
Similarly, helpful and available items certain around virtual machine specifics and component listings.
Further drill down is entirely possibly into the virtualization components by clicking on the Virtual Machine name.
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.
Each report, after being executed, can simply be printed or exported to Excel or PDF.
For those individuals wanting to customize the reports, the Edit Report link opens the report editor.
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.
Data Correlation with PerfStack Analysis
With SolarWinds Server & 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.
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.
Application Dependency Mapping
SolarWinds Server & Application Monitor has flexible deployment options with either agentless or agent-based monitoring. One of the new benefits of using the agent-based option is using the Application Dependency Mapping feature. SAM will monitor OS and application connections, showing you a “network” view of who and what your applications are talking to. You can see information such as Latency, Packet Loss, Port, IOPs and more to serve as a network performance monitor.
SolarWinds SAM can take these application connections a step further by auto-creating maps showing you all a visual representation of an application’s physical, virtual, and software dependencies and relationships. These maps can be further refined and saved for individual departments and teams for infrastructure monitoring.
SolarWinds SAM also provides the ability to monitor Cloud based services from Azure and AWS. There is also application performance monitoring for IIS and Exchange (both these have an Application Insight template) and Oracle, IBM DB2 and MySQL databases. Monitoring for operating systems includes Windows, Linux, AIX, Citrix and VMware. For the full list of supported vendors, please see: Supported Vendors.
As you can see, the SolarWinds SAM product provides a wide reaching and thorough monitoring of your SQL Server environment and beyond including Active Directory. 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 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, notifications and uptime status
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. Check out these resources to learn more:
- SAM Template Reference - 1200+ pages of how to setup SAM and 300+ Templates
- SAM API Poller Template Guide - 35+ Poller Templates included with SAM
- Head over to SolarWinds site to try out the online demo. See how SAM works for yourself.
- Download a 30 day demo of SAM to get value from SAM in your shop.
MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Server & Application Monitor (SAM).
Last Updated: 2021-02-23
About the author
View all my tips