SQL Server Management Studio’s Activity Monitor is a powerful tool that allows database administrators to monitor and analyze the performance of their SQL Server instances. While many of us are familiar with its basic functionalities, there are some lesser-known features within Activity Monitor that can provide valuable insights into the performance of our databases.

Resource Waits

One of the building blocks within Activity Monitor is the Resource Waits section. This section provides a summary of the types of waits that are occurring at the fastest rate. Each wait type is categorized into a wait category, and the tool saves the current total wait count and time into a temporary table. By comparing the difference in waits and wait time between snapshots, we can identify in-progress waits rather than just seeing spikes after the wait completes.

It’s important to note that queries that encounter a lot of waits may not rank high enough to show up in the “Recent Expensive Queries” section. This is because these queries are waiting and do not consume CPU or perform logical reads/writes at a fast enough rate. Therefore, they may not be included in the top queries reported in that grid.

Data File I/O

Another useful section in Activity Monitor is the Data File I/O section. This section shows which databases and files are responsible for the most physical I/O. We can see the databases with the highest physical reads or writes, as well as the average response time per I/O during the interval. This information is retrieved from the sys.dm_os_virtual_file_stats DMV, which includes both data and log files.

It’s worth mentioning that the underlying DMV clears its data when a database is taken offline. Therefore, if a database is taken offline and online at the same interval as the Activity Monitor refresh interval, the I/O rates displayed in this section may be inaccurate.

Recent Expensive Queries

The Recent Expensive Queries section of Activity Monitor displays queries that are currently running or have run in the last four hours and have significant resource consumption. The chart displays the top queries based on various metrics such as CPU consumption rate, physical reads rate, logical writes rate, logical reads rate, and average duration.

This section uses delta values between the current collection and a previous collection of data to compute a rate of resource consumption. By ordering the queries based on different metrics, we can identify the queries that are using the most CPU, performing the most I/O, and so on. The underlying queries from the DMVs are grouped by query and plan fingerprints, allowing Activity Monitor to find similar queries that collectively consumed significant resources.

By exploring these lesser-used building blocks within Activity Monitor, we can gain a deeper understanding of the performance of our SQL Server instances. Whether it’s identifying in-progress waits, analyzing data file I/O, or pinpointing resource-intensive queries, Activity Monitor provides valuable insights that can help us optimize our databases.

How have you used Activity Monitor in your environments? Share your thoughts and experiences in the comments below!