Optimize SQL Server estate monitoring with Redgate SQL Monitor

By: Jeremy Kadlec   |   Updated: 2022-09-08   |   Comments (1)   |   Related: More > Monitoring

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 performance issues. 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, downtime and inaccurate decision making.

How can our team balance the need to monitor SQL Server database 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 Microsoft SQL Server Data Platform 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 root cause 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 on a daily basis 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 Software. SQL Monitor is a monitoring tool that saves significant time for operational tasks and performance monitoring for large SQL Server environments with instances on premises, in the cloud (Azure SQL, AWS, RDS and Google), running on virtual machines, 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 Redgate 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 is designed to help you manage the demands expanding estates, bigger databases, and higher numbers of monitored SQL servers with simple tools, because it supports multiple base monitors, and one base monitor will monitor up to 400 servers. Even for very large server estates, spread across different networks, the administration team still retain a simple picture of the overall health, performance characteristics, and security of the estate - if one base monitor is offline or slow, the availability of the Global Dashboard isn’t affected.

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, which cannot be found in SQL Server Management Studio. You can fast track troubleshooting the most critical errors.

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 optimization 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.

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 SQL 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.

The Query Metrics section of the interface also includes a historical view of data based on duration, which can serve as baselines. 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.

SQL Server Instance Metrics

SQL Monitor includes over a dozen instance level metrics to explain how the overall SQL Server instance is performing and where bottlenecks exist. Examples include: Batch Requests / second, SQL Compilations, Page Splits, User Connections, Locking, Latching, Deadlocks and more. SQL Monitor also enables tempdb monitoring that comes with dedicated metrics and graphs.

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.

Azure Elastic Pools and Azure SQL DB

SQL Monitor also includes cloud specific performance data. Below is one example for Azure Elastic Pools and Azure SQL DB where the DTU usage is reported to help understand trends and associated costs.

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.

Microsoft 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.

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 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.

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

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

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.

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.

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 - automate data collection, troubleshooting, trending and decision making.
3. Register for a on-demand demo of SQL Monitor with a member of the Redgate Software team.
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 this SQL Server monitoring tool can help your organization:
   1. Daily time savings for troubleshooting performance problems, operational monitoring and new prioritization methodology
   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, including uptime, of your entire SQL Server environment and intuitively drill into the details to understand and begin troubleshooting performance problems
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

• Learn about the value of SQL Monitor.
• Check out the online demo or join an on-demand demo of SQL Monitor.
• Download a free trial of SQL Monitor.
• Check out these additional SQL Monitor resources.
• Find out how organizations are using SQL Monitor:
  • The Value of Monitoring at BMW
  • Keeping pace with hybrid monitoring needs
  • Think again about SQL Monitor
  • Database monitoring in 75% less time
• Redgate Software is a software company that delivers more than just SQL Monitor. Redgate's solutions for DBAs and Developers include: continuous delivery with Database DevOps, SQL Server and Oracle developer tools (SQL Prompt, SQL Compare, etc.), DBA toolbelt, automation solutions for database deployments and more, which are used around the globe.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor’s degree from SSU and master’s from UMBC.

View all my tips