Hadi Fadlallah

An overview of SQL Server monitoring tools

December 12, 2023 by

This article explains some of the popular SQL Server monitoring tools and techniques.

Database monitoring: a quick reminder

Database monitoring is a set of tools and techniques used to create and maintain an application infrastructure that is highly available and performs at high speed. It helps track the resource consumption, such as the amount of read and write operations, CPU and RAM consumption, storage, temporary storage size, etc. The resource consumption can be retrieved for a precise time or continuously measured in real time.

Several tasks could be performed with the information collected for monitoring, for example:

  • Performance analysis of SQL queries, stored procedures, or applications.
  • Analyzing low system performance.
  • Analyzing how tables and indexes are used.
  • Analyzing the impact of new features and updates (application, database management system, code optimization…)
  • Predicting resource requirements (CPU, Memory, Disk …) based on previous monitoring output.
  • The health of the database cluster.
  • Assessing performance issues and abnormal behavior.

In the rest of this article, we will explain the different techniques available for SQL Server monitoring operations.

SQL Server Profiler

Since SQL Server 2000, Microsoft has provided a standalone tracing tool called SQL Server Profiler. This feature allows you to trace activities and operations executed on a SQL Server database engine or Analysis Service for later analysis. It is responsible for two primary SQL Server monitoring operations:

  • Tracing: It can monitor all operations executed on an instance
  • Replay: It can rerun all operations logged in a trace later

This tool can be accessed from Windows Explorer or SQL Server Management Studio.

Opening SQL Server Profiler from Management Studio

Figure 1 – Opening SQL Server Profiler from Management Studio

SQL Server Profiler allows tracing various operations performed by a specific server object or related to a specific database. Also, it can monitor specific databases or the whole SQL Server instance. Moreover, this tool can detect the commands executed by an application over a database for security and tuning purposes. 

SQL Server Profiler Events selection

Figure 2 – SQL Server Profiler Events selection

Although Microsoft deprecated this tool several years ago, it is still one of the most popular and straightforward SQL Server monitoring tools.

More details on the SQL Server Profiler can be found in the article I published on the SQL Shack website: An overview of the SQL Server Profiler.

SQL Server Extended Events

SQL Server Extended Events is a performance monitoring tool integrated within SQL Server Management Studio. It was introduced with SQL Server 2008 as a replacement for SQL Server Profiler.

This SQL Server monitoring tool contains more templates than SQL Server Profiler.

available event templates for Sql server monitoring

Figure 3 – Choosing a monitoring template while creating an extended event

The extended event session will automatically start and collect data after we create it. Analyzing the collected data is possible using Watch Live Data.

live sql server monitoring using extended events

Figure 4 – Watch live data

To learn more about SQL Server Extended Events, you can refer to the article on SQL Shack: Using SQL Server Extended Events to monitor query performance.

SQL Server Management Studio Reports

Another available SQL Server monitoring technique is utilizing the available reports within the SQL Server management studio. Those reports are available for different server objects (database, tables …).

generating reports for sql server monitoring

Figure 5 – Generating reports

Users can choose from a list of standard reports available in the SQL Server management studio or create custom reports.

Disk usage report

Figure 6 – Disk usage report

To learn more about SQL Server Management Studio standard reports, you can refer to the following article: SQL Server Management Studio performance reports.

SQL Server Management Studio Activity Monitor

Another tool available within SQL Server Management Studio is the activity monitor. This tool displays information about SQL Server processes and how these processes affect the current instance of SQL Server. It provides information about the running processes, wait locks, read and write operations, and expensive queries.

Activity Monitor

Figure 7 – Activity Monitor

Dynamic management views and functions

Monitoring an SQL Server instance’s health, diagnosing problems, and tuning performance can be done using dynamic management views and functions. Those views and functions are available in two different scopes:

  • Server-scoped dynamic management views and functions.
  • Database-scoped dynamic management views and functions.

There are plenty of categories for the available system dynamic management views and functions:

  • Always On Availability Groups
  • Change Data Capture Related
  • Change Tracking Related
  • Common Language Runtime-Related
  • Database Mirroring Related
  • Database-Related
  • Execution-Related
  • Extended Events
  • Filestream and FileTable
  • Full-Text Search and Semantic Search
  • Geo-Replication
  • Index Related
  • I/O Related
  • PolyBase
  • Memory-Optimized Table
  • Object-Related
  • Query Notifications Related
  • Replication-Related
  • Resource Governor-Related
  • Security-Related
  • Server-Related
  • Service Broker-Related
  • Spatial Data-Related
  • Azure Synapse Analytics and Parallel Data Warehouse
  • SQL Server Operating System-Related
  • Stretch Database
  • Transaction-Related

To learn more about dynamic management views and functions, you can refer to the following published articles on SQL Shack;

Windows Resources Monitor

When installing SQL Server on Windows, users can benefit from the Windows resources monitor application available in the Task manager. We can get detailed information about the running SQL Server instance by clicking on the sqlservr.exe process, which relates to running SQL Server instance.

Windows resource monitor

Figure 8 – Windows resource monitor

Third-Party Tools

After illustrating some of the SQL Server monitoring tools installed with SQL Server or found in Windows, this section illustrates some of the popular third-party tools.

Sp_Whoisactive stored procedure

As defined on their official website, sp_whoisactive is a comprehensive activity monitoring stored procedure that worked for all versions of SQL Server from 2005 through 2017. This stored procedure was created by Adam Machanic, and SQL Server DBAs widely use it.

To learn more about this stored procedure, you can refer to the following articles:

SQL Server First Responder Kit

The SQL Server first responder kit is an open-source project started by Brent Ozar. It contains a set of SQL Scripts and stored procedures that allow performing a health check for the SQL Server instance besides tuning several server objects such as stored procedures and indexes.

Popular stored procedure in the first responder kit

Figure 9 – Popular stored procedure in the first responder kit

Also, it contains scripts for analyzing query plan cache, backup and restore operations, and query performance.

Power BI dashboard

To visualize the output of the Real-Time Performance Advisor procedure “sp_blitzfirst”, Brent Ozar provided a Power BI dashboard that can facilitate reading the state of the SQL Server instance.

Power BI dashboard for sql server monitoring

Figure 10 – Power BI dashboard (Reference)

Quest Foglight for SQL Server

Foglight for SQL Server quickly diagnoses and resolves performance problems and optimizes your workload to prevent future performance issues. This SQL Server monitoring tool includes real-time and historical diagnostics, reporting, and more, so you can confidently ensure the health of your entire database environment.


In this article, we explained SQL server monitoring and why it is essential. Then, we illustrated different free SQL server monitoring tools and services which provide critical information on the server and databases. Then, we listed some of the third-party commercial tools.

Hadi Fadlallah
Monitoring, Performance

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah