Exploring SQL Server Activity Monitor (P1)

Understanding what SQL Server is currently doing can be quite challenging, especially if you are not a database administrator. Besides identifying which queries are being executed, you can also determine their impact on the current performance of the server. SQL Server Activity Monitor is a feature in SQL Server Management Studio that helps monitor ongoing processes and how system resources are being used. Today, we will explore this Activity Monitor tool.

SQL Server Activity Monitor Dashboard

There are two ways to open this dashboard: either click the icon on the toolbar (as shown in Figure 1) or right-click on the instance name and select Activity Monitor from the Object Explorer window (as shown in Figure 2).

After choosing one of these methods, you will see the main dashboard of the Activity Monitor appear and start loading relevant data. There are six sub-panels: Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries, and Active Expensive Queries. These sub-panels can be expanded or collapsed for easy monitoring, and SQL Server Activity Monitor will only load data for the panels that are selected (expanded). By default, the Overview sub-panel is expanded, as shown in the image below.

SQL Server Activity Monitor collects information from various sources, mainly by directly querying the instance you are monitoring. You can right-click on the empty space in the Overview sub-panel to set the "Refresh interval" value from 1 second to 1 hour for the entire main dashboard. I choose 1 second to easily observe changes during the demo, and the Activity Monitor will update the dashboard information every second.

Permissions Required to Use SQL Server Activity Monitor

Since Activity Monitor queries summary data at the instance level, the login needs to have the VIEW SERVER STATE permission. Additionally, other sub-panels gather information about database I/O and query contents, so you will also need the CREATE DATABASE, ALTER ANY DATABASE, and VIEW ANY DEFINITION permissions. As some panels support additional functions like tracing specific processes with the profiler or killing certain processes, additional permissions like ALTER TRACE and the sysadmin role are required to kill processes.

To make using this dashboard easy and effective, in the following part I will explain the meaning of each parameter along with illustrative examples so that you can see which situations and actions affect the increase or decrease of the corresponding parameters so that later just by looking at SQL Server Activity Monitor you can visualize how the SQL Server you are monitoring is under load.

Everyone wait for the next part.

#wecommit100xshare

#SqlServer

#Monitor

#DatabaseOptimization

#PerformanceTuning