SQL Server Monitoring

Ensuring a Healthy and High-Performing Server

Why monitor?

In the fast-moving world of database administration, monitoring your SQL Server is crucial to ensure reliability, availability, and performance. Whether you’re running a mission-critical production environment or supporting smaller business workloads, effective monitoring catches issues before they become major problems.

In this week’s newsletter I walk you through three methods for setting up SQL Server monitoring: built-in alerting, free community tools and scripts, and commercial tools. Each solution offers unique benefits, so let’s explore your options!

?

Built-In SQL Server Monitoring and Alert Functionality

SQL Server comes with several native tools to monitor and alert you to problems:

SQL Server Agent Alerts

• The SQL Server Agent can notify you about specific error codes and severity levels. Severity 19-25 are where I generally start. Also include corruption errors 823-825.
• Useful for detecting deadlocks, login failures, and job failures.
• Uses SQL Server Database Mail and your SMTP server.

Performance Monitor (PerfMon)

• Windows PerfMon can track CPU usage, I/O operations, memory consumption, and disk activity. Some of these can also be found in sys.dm_os_performance_counters and sys.dm_os_sys_info
• Create custom counters to monitor key SQL Server metrics like Buffer Cache Hit Ratio and Page Life Expectancy.

Extended Events and Query Store

• Extended Events provides a lightweight method to collect granular data on system behavior.
• Query Store helps track and troubleshoot query performance by maintaining history of execution plans and runtime statistics.

?

While these built-in options are a great starting point, they have some limitations—alert configuration is manual and may not cover all the insights you need for long-term performance tuning.

Free Community Tools and Scripts

The SQL Server community offers a wealth of free tools that extend native functionality. These tools are perfect for small to mid-sized businesses or IT shops without the budget for premium tools.

SQL Server Diagnostic Scripts (by Glenn Berry)

• A comprehensive set of T-SQL scripts to monitor performance metrics, identify resource bottlenecks, and track database health.
• Includes queries for index usage, memory grants, and I/O stalls.
• I have not used these personally, but many DBAs I trust RAVE about them

Ola Hallengren’s Maintenance Solution

• A free, open-source solution for backups, integrity checks, and index optimization.
• Monitoring the jobs created with this tool ensures reliability by detecting issues early.

Brent Ozar’s First Responder Kit

• A collection of scripts that quickly diagnose common SQL Server performance issues.
• Provides insights into wait stats, plan cache, index issues, and active sessions.
• Most of these allow you log the output to a table for later analysis

?

These community solutions are lightweight and customizable, offering excellent coverage for environments that can’t afford commercial tools.

Commercial Monitoring Tools

If you need advanced monitoring, automation, and 24/7 alerting, commercial tools can be a game-changer. Many of these solutions provide real-time dashboards, predictive alerts, and historical trend analysis, helping you stay ahead of issues before they escalate.

SolarWinds Database Performance Analyzer (DPA)

• Monitors performance across SQL Server instances and highlights queries causing wait time.
• Offers machine learning-based alerts to detect anomalies.
• DPA is one of my favorite tools to use for “right-now” troubleshooting

Redgate SQL Monitor

• Provides real-time monitoring with customizable alerts for database health and query performance.
• Integrates with DevOps pipelines for continuous monitoring.

Minion Ware

• Offers modular, enterprise-level tools for maintenance, backup, and job monitoring.
• Minion CheckDB monitors database integrity continuously, while Minion Backup gives advanced backup management with detailed logging.
• Especially useful for DBAs managing multiple servers, Minion Ware products are highly customizable to fit complex environments.

SQLWATCH.IO

• A free, open-source SQL Server monitoring tool designed to collect and visualize server metrics using Power BI dashboards.
• SQLWATCH tracks performance trends, server health, and job status over time, giving you detailed insights without extra licensing fees.
• For DBAs or IT teams who prefer a customizable, open-source alternative to commercial tools.

?While these tools come at a cost, they deliver powerful automation, reporting, and scalability for businesses running complex SQL Server environments.

?

The Bottom Line:

• Built-in monitoring tools are ideal if you need something simple and immediately available.
• Free community tools can fill the gap for smaller teams with limited budgets, providing great flexibility.
• Commercial tools offer comprehensive, proactive monitoring - best suited for businesses with complex needs or multiple servers.

When it comes to monitoring, “what you don’t know can hurt you.” Whether it’s a failed backup, a performance bottleneck, or a storage issue, proactive monitoring helps you avoid costly downtime and maintain a reliable environment.

?

Disclaimer: I have personal and/or financial arrangements of various sorts with Brent Ozar Unlimited, SQLWatch, and Minion Ware’s founders.

Brent Ozar makes some of the best training for SQL Server available on the internet.

A LOT of the above information is covered.

Every November, he runs a Black Friday Sale with DEEP discounts. Starts today!!!

Please share or forward if you found this useful. Newly born kittens all over the world will thank you.

The full version of this newsletter including SQL tidBITs, my posts and interesting (to me) links can be found here: https://www.accidentaldba.co/subscribe