mssqltips logo

Optimize SQL Server estate monitoring with Redgate's SQL Monitor

By:   |   Updated: 2019-09-19   |   Comments   |   Related: More > Database Administration

Problem

SQL Server performance monitoring over the years has received the lion’s share of attention due to the immediate feedback from the organization. If an application is perceived as running “slow,” the technology group is notified to troubleshoot the problem. While daily operational tasks for SQL Server DBAs and Developers are just as critical, the initial impact is generally much less visible. With large SQL Server environments, the demands are high with limited team members, and it’s imperative to work as efficiently as possible with servers on premises, in virtualized environments, and in the cloud.

Overlooked failed database backups, getting lost in the myriad of problems, will result in major data loss if a server has a hardware failure. Similarly, there may be no notification to upstream or downstream processes as well as the impacted users with failed SQL Server Agent Jobs. This could lead to additional failed processes, incorrect data sets, and inaccurate decision making.

How can we balance our need to monitor SQL Server performance and operational processes across hundreds of SQL Servers, but also save time by focusing on the highest priority issues?

Solution

Balancing operational consistency and performance monitoring is imperative for large SQL Server environments. Generally, this is a time-consuming and overwhelming undertaking for any size team. Often it can feel like a constant uphill battle with many of the underlying issues never truly being solved. The SQL Server Team is pulled in many directions based on who is complaining the most, and not necessarily toward the most critical needs of the organization.

This immense task of daily monitoring with limited team members leaves the SQL Server DBAs in an even more compromised position. Instead of proactively correcting issues they are responding to failures over the weekend and on holidays as well as users reporting issues. It’s not realistic to log in to every SQL Server instance to try to review critical processes, check database status, storage, etc. Some SQL Server DBAs build T-SQL and PowerShell scripts to query system objects in system and user defined databases, but it can be time consuming to build and maintain the code base with numerous SQL Server versions. SQL Server Agent also ships with Alerts to notify team members of a failed Job, but this would need to be configured per Job, and does not include functionality related to new, modified, or deleted Jobs. The need remains to run and check the output for every instance because the data is very difficult to consolidate and then prioritize. The situation is further complicated since the information in the system objects is generally only for a point in time and there is no historical retention by default.

In order for SQL Server DBAs to streamline, prioritize, and simplify SQL Server monitoring, I would like to introduce SQL Monitor from Redgate. SQL Monitor saves significant time for operational tasks and performance monitoring for large SQL Server environments with instances on premises, in the cloud (Azure, AWS and Google), running in virtualized environments, clusters, and Availability Groups. From an architecture perspective, SQL Monitor has the scalability and flexibility to monitor a large environment with a self-hosted architecture and agents on each SQL Server instance. The web-based and mobile-friendly interface is the SQL Server Professional’s time saver, with a single pane of glass to see all servers including intuitive color coding and the ability to dig into the details with 1 to 2 clicks from the main SQL Monitor interface.

Let’s dig into SQL Monitor to see how it can help.

SQL Monitor Environment Overview

The Overviews portion of SQL Monitor provides a single pane of glass into the entire SQL Server environment. In one interface you can organize servers based on environment (i.e. Production, Azure, Staging, Test, Development), location, department, or however else you’d like to group them. Once organized you can discern if there are any issues based on the color coding (Red for Critical, Orange for Warning, and Green for Healthy), if the server is in the cloud, part of an Availability Group, as well as high level performance metrics (wait time, CPU percentage, and disk I/O). In addition, you can see the latest alerts from all of the servers on the right side of the interface.

SQL Monitor Single Pane of Glass to Monitor critical operational and performance metrics

SQL Server Performance Metrics

Clicking on a SQL Server instance provides the details for CPU, Disk, Memory, and Waits, with over a dozen categories of data from performance to blocking to security to error logs and more. You can fast track troubleshooting the most critical errors.

SQL Server Performance Metrics in SQL Monitor from Redgate

SQL Server Query Metrics

The next portion of the server performance metrics is a prioritized list of queries impacting the server. In this example the data is sorted by Duration, but you can sort via the remainder of the columns to help focus your efforts. You can see a preview of the Top 10 Queries and Top 10 Waits including the Execution Count, Duration in milliseconds, CPU time, Physical Reads, Logical Reads, Logical Writes, and the associated database.

Top 10 Queries and Top 10 Waits including the Execution Count, Duration in milliseconds, CPU time, Physical Reads, Logical Reads, Logical Writes

However, the troubleshooting really begins when you click on the arrow to the left of the query to see the full set of code and the query plan. The left portion of the interface includes Expensive Operations and Data-Heavy Operations that can be clicked on to navigate to that statement in the query plan and get the performance metrics for that statement. You also have the ability to scroll left and right / up and down to see the entire query plan and focus on the costs in red that are at the top of each operation.

SQL Server Query Plan with all performance metrics in Redgate's monitor

The Query Metrics section of the interface also includes a historical view of data based on duration. Alternatively, you can use CPU time, Logical Reads, Logical Writes, or Physical Reads. The time period can be for all history, or, alternatively, a Zoom Range or Window Range to see how the query has performed in those specific time periods.

Historical Query Metrics in SQL Monitor

SQL Server Instance Metrics

SQL Monitor includes over a dozen instance level metrics to explain how the overall SQL Server instance is performing. Examples include: Batch Requests / second, SQL Compilations, Page Splits, User Connections, Locking, Latching, and more.

SQL Server Instance Metrics in Redgate's SQL Monitor

Additionally, disk usage for each drive (including disk, overall volume size, space usage, read, write and transfers) are updated in real time to view disk drive performance.

SQL Monitor disk drive

This is just a brief overview of the depth and breadth of performance monitoring in Redgate’s SQL Monitor. Check out the online demo to learn more about the product or request a free download to understand your SQL Server’s performance.

SQL Server Operational Monitoring

Beyond the performance aspects of SQL Monitor, this solution also has new SQL Server operational features including:

  • Installed Versions
  • Disk Usage
  • Backups
  • SQL Agent Jobs

Let’s see how this new functionality will help us as SQL Server Professionals.

SQL Server Installed Versions

In many large SQL Server environments, having all of the SQL Server versions in sync is very difficult. SQL Monitor’s new Installed Versions interface is beneficial to prioritize the servers that need an upgrade or a new patch. The Summary section outlines the entire environment with a snapshot of the versions. The main portion of the interface is broken down by environment then server with the version, edition, status, product level, version number, latest available update to download directly from Microsoft, and date for the end of mainstream support.

SQL Server Installed Versions Summary Pane
Version, edition, status, product level, version number, latest available update to download directly from Microsoft, and date for the end of mainstream support

With this information you can easily prioritize patching and plan for long term upgrades so your SQL Servers are continuously supported by Microsoft.

SQL Server Disk Usage

Running out of disk space is never a good situation. As DBAs it is very challenging to have eyes across every volume in an organization to know which volumes are running low on disk and if there will be sufficient storage in a year. SQL Monitor’s new Disk Usage interface solves this problem by mapping each database to a disk with the following metrics:

  • Space Used
  • Capacity
  • Percentage Used
  • Projected Space Used in One Year
  • Projected Change
  • Time Until Full

With this information you can get ahead of immediate fires and also plan for long term storage needs with a projected time table.

SQL Server Disk Usage in Redgate SQL Monitor
SQL Monitor’s new Disk Usage interface maps each database to a disk with key storage metrics

SQL Server Backups

For every organization, the last line of defense in case of a failure are your SQL Server database backups. Unfortunately, seeing the database backup status across a large SQL Server environment is a challenge and can be a very serious risk to the organization. To address this need, SQL Monitor has introduced the Backups interface with both a summary and detailed backup status per database to understand the Recovery Point Objective (RPO).

The Summary portion of the interface outlines the total number of databases, databases with no backups, and the overall percentage of databases backed up as well as a graph of the Recovery Point Objective for specific time periods from fifteen minutes to one year.

SQL Server Backups in Redgate's SQL Monitorg with the Recovery Point Objective

The detailed portion of SQL Monitor’s Backup interface is broken down by environment and database then includes the following information:

  • Backup Type – Full, Differential and Log
  • Backup Meta Data – Start Date, Duration and Size
  • Database Recovery Model
  • Worst RPO in last 30 days
SQL Monitor’s Backup interface is broken down by environment and database then includes the Backup Type – Full, Differential and Log, Backup Meta Data – Start Date, Duration and Size, Database Recovery Model and Worst RPO in last 30 days

From the main SQL Monitor Backup interface, you can expand any of the databases and its history is displayed graphically and textually including the following:

  • Backup Type
  • Start Date
  • Duration
  • Size
  • Backup Properties – Copy only, Compressed, and Encrypted
  • Backup Location
Detailed Backup History in Redgate's SQL Monitor

This visual and textual representation enables you to easily see any gaps in the expected backup process and work towards resolution to minimize the RPO.

SQL Server Agent Jobs

SQL Server Agent, which is SQL Server’s native job scheduler, has become indispensable for scheduling processes that interact with the databases on the instance. Whether the process is T-SQL based or a command shell script, PowerShell or Integration Services, validating all of the Jobs are completing successfully is very difficult. Although alerting can be enabled for SQL Server Agent Jobs, setting up and managing the alerts can be time consuming with SQL Server Jobs spread across a large SQL Server environment.

Redgate has recognized the challenges with discovering, consolidating, and prioritizing SQL Server Agent Job failures, and has introduced the SQL Agent Jobs interface in SQL Monitor to address these needs. Similar to the Backups and Disk Usage functionality, SQL Monitor includes a summary pane with graphs showing the most recent activity,the overall count of Jobs, Job Executions, Successful Executions, Failed Executions, and the associated Success Rate.

SQL Server Agent Jobs Summary Pane in Redgate's SQL Monitor

When you drill into the Failed Jobs, all the details are available from the date, time, duration, step, and error message returned to streamline troubleshooting the problem.

SQL Monitor Failed Job interface inlcudes date, duration, error, etc.

How do I get started with SQL Monitor?

  1. Record all of the time spent daily on addressing performance and operational issues. Then think about all of the issues that do not get the attention they need to best manage the SQL Server environment.
  2. Learn more about SQL Monitor via the online demo and outline the ways you think the application will help your organization.
  3. Schedule a personal demo of SQL Monitor with a member of the Redgate team and get your questions answered.
  4. Download and install your free personal evaluation version of SQL Monitor and start to see the solution in action in your own environment.
  5. Communicate to your team and management about how SQL Monitor can help your organization:
    1. Daily time savings for troubleshooting performance, operational monitoring, and prioritization
    2. Simple installation and minimal configuration
    3. Significant functionality out of the box to properly manage a large SQL Server environment
    4. Single pane of glass to see the status of your entire SQL Server environment and intuitively drill into the details to understand and begin troubleshooting issues
  6. After testing SQL Monitor in your environment, measure the tangible and intangible benefits, and determine your next steps to improve the operational efficiency of your DBA Team.
Next Steps


Last Updated: 2019-09-19


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

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.






download

























get free sql tips

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