Read this article to know how to use SQL Server Activity Monitor and what other SQL monitoring tools you can use to monitor SQL Server performance to be aware of possible performance issues.
Table of contents:
- What is Activity Monitor in SQL Server?
- Permissions to use Activity Monitor
- How to start Activity Monitor
- SQL Server Activity Monitor panes
- Monitor SQL Server queries via T-SQL commands
- Enhanced real-time SQL Server monitoring tool
- Conclusion
What is Activity Monitor in SQL Server?
Activity Monitor in SQL Server Management Studio (SSMS) is a tabbed document window with six panes. On Activity Monitor, you can track SQL Server processes and the way how these processes affect the instance.
The tool allows controlling and troubleshooting SQL Server performance issues. Collected performance data is displayed graphically.
Permissions to use Activity Monitor
To be able to see data on Activity Monitor, you must have the following permissions:
- VIEW SERVER STATE – to check the activity
- CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION with VIEW SERVER STATE – to check the Data File I/O pane
How to start Activity Monitor
There are several ways to launch Activity Monitor in SSMS:
- Way 1 – from the toolbar
- Way 2 – from the Object Explorer
- Way 3 – from the Options menu
Way 1 – from the toolbar
Just сlick the following icon:
Way 2 – from the Object Explorer
Right-click the SQL Server connection and click Activity Monitor:
Way 3 – from the Options menu
1. Navigate to Tools > Options:
2. Click Startup and select Open Object Explorer and Activity Monitor from the drop-down menu:
3. Finally, click OK.
SQL Server Activity Monitor panes
Activity Monitor has the following panes: Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries, and Active Expensive Queries. Each pane is expandable and collapsible.
If you expand a pane, it starts querying for real-time data. If you collapse a pane, it stops querying. You can expand several panes at the same time.
Activity Monitor Overview pane
The Overview pane provides a quick overview of the instance performance. In the pane, you can see four graphical displays:
- Processor Time is the percentage of time that processors spend on executing running threads
- Waiting Tasks is the number of tasks that will be performed when the processor is released
- Database I/O is data transmission speed from memory to disk, disk to memory, or disk to disk in MB/s
- Batch Requests is the number of batches that the SQL Server instance receives in a second
Activity Monitor Processes pane
The Processes pane displays a report on active users who run processes on the SQL Server instance. The report has the following columns:
- Session ID is an ID of a user’s connection
- User Process is a process identifier: 0 is for the system processes, 1 is for the user’s processes
- Login is a login name that runs a session on the SQL Server instance
- Database is a database where a process runs
- Task State is a state of a task. If a task is in a runnable or sleeping state, the state is blank
- Command is the current type of the command
- Application is the name of the application that created the connection
- Wait Time is time, in milliseconds, for the task that waits for resources
- Wait Type is the name of the current or last wait type
- Wait Resource is a resource name that the connection waits for
- Blocked By is a session ID that blocks a task
- Head Blocker is a session that causes the first blocking condition
- Memory Use is the amount of memory that a task uses
- Host Name displays the name of a computer that connects to the SQL Server instance
- Workload Group is the name of the Resource Governor workload group for the connection
Activity Monitor Resource Waits pane
The Resource Waits pane contains a report on waits for resources. There are the following columns in the report:
- Wait Category has a list of categories with closely related wait types
- Wait Time displays the time for tasks that wait for resources
- Recent Wait Time is the average time for tasks that wait for resources
- Average Waiter Count is a number of tasks that wait for resources at a specific moment in the last sample interval
- Cumulative Wait Time specifies the time that tasks have waited for resources since the last restart of the SQL Server instance
Activity Monitor Data File I/O pane
The Data File I/O pane provides information about database files on the SQL Server instance. In the pane, you can see the following columns:
- Database is a database name
- File Name is a file name of a database
- MB/sec Read displays recent read activity for a file
- MB/sec Written displays recent write activity for a file
- Response Time is the response time for the read-and-write activity
Activity Monitor Recent Expensive Queries pane
The Recent Expensive Queries pane helps you to identify queries that use much resources. You can get information from the following columns:
- Query is a monitored query statement
- Executions/min is the number of executions per minute for a query
- CPU (ms/sec) is the CPU usage rate by request
- Physical Reads/sec is a rate of physical reads by a query
- Logical Writes/sec is a rate of logical writes by a query
- Logical Reads/sec is a rate of logical reads by a query
- Average Duration (ms) is the average time of a query execution
- Plan Count is a number of cached query plans for a query
Monitor SQL Server queries via T-SQL commands
You can also use T-SQL queries to check SQL Server performance. In this section, we have listed three alternative ways of monitoring SQL Server health.
Query SQL Server performance via dynamic management views
Dynamic management views (DMVs) are related to SQL Server Operating System (SQLOS). SQLOS manages the operating system resources of SQL Server.
To start querying DMVs, you must have the following permissions:
- SELECT on object
- VIEW SERVER STATE or VIEW DATABASE STATE
All dynamic management views have names starting with sys.dm_ in the sys schema. You can check a list of available dynamic management views in the SQL Server Operating System Related Dynamic Management Views (Transact-SQL) document.
For example, to query memory information from SQL Server operating system, you can run the following query:
SELECT total_physical_memory_kb
FROM sys.dm_os_sys_memory;And you will get the result, for example:
| total_physical_memory_kb | |
| 1 | 16449800 |
Query general maintenance of SQL Server via system stored procedures
System stored procedures are an alternative way to monitor SQL Server metrics. You can check a list of supported system stored procedures in the “Database Engine Stored Procedures (Transact-SQL)” document. Pay attention to the fact that each system stored procedure requires having some permissions.
For example, to check all active processes on the instance, run the following system stored procedure:
USE master;
GO
EXEC sp_who 'active';
GOYou will see data similar to this:
| spid | ecid | status | loginame | hostname | blk | dbname | cmd | request_id | |
| 1 | 1 | 0 | background | sa | 0 | NULL | XIO_RETRY_Worker | 0 | |
| 2 | 2 | 0 | background | sa | 0 | NULL | XIO_LEASE_RENEWAL_WORKER | 0 | |
| 3 | 3 | 0 | background | sa | 0 | NULL | RECOVERY WRITER | 0 |
Query system data of SQL Server via built-in functions
SQL Server also supports a set of built-in functions for you to be able to monitor system information. You can check a list of all available functions in the Monitor SQL Server Performance and Activity with Built-In Functions document.
For example, to query CPU processing time in milliseconds for SQL Server activity, run the following built-in function:
SELECT @@cpu_busy AS "CPU Busy";
GOThe query will return the result as follows :
| CPU Busy | |
| 1 | 381964 |
Gather SQL Server metrics via System Data collection sets
If you want to store and generate reports on metrics gathered from the SQL Server instance, you can use System Data collections sets. System Data collection sets include the following components:
- Disk Usage that collects disk data and logs usage for all the databases in the system
- Server Activity that collects statistics of resource usage and performance data from SQL Server
- Query Statistics that collects query statistics, query plans, specific queries, and individual query text
You can read more about System Data collection in the System Data Collection Sets document.
A collection set retrieves data from dynamic management views and reporting commands, then sends the collected data to the management data warehouse.
Enhanced real-time SQL Server monitoring tool
Monitoring the SQL Server performance and activity is very important in order to avoid system failures. That is why Activity Monitor in SQL Server Management Studio includes such critical and crucial tracking parameters.
Additionally, you can use dbForge Monitor to stay informed about the capacity of databases and servers in real-time.
dbForge Monitor comes as a free add-in for SSMS and as a part of dbForge Studio for SQL Server.
Among all the tracking parameters, we would like to pay your attention to the one that keeps track of queries, which consume much resources. In most cases, such queries are the reason why SQL Server has low performance. dbForge Monitor comes with Query Profiler that helps to be aware of slow running queries:
The tool aims to set and analyze SQL query performance and fix issues. Query Profiler contains the following information:
- Date and time of the query
- Total execution and planning time
- Graphical plan diagram
- Plan tree
- Top operations within the query plan
- Table input/output
- XML version of the query plan
Conclusion
In this article, we have found out what SQL Activity Monitor is and what panes it consists of. Also, we have reviewed alternative ways to monitor SQL Server performance. As you can see, an effective monitoring tool is a must-have. You can download dbForge Monitor absolutely for free or dbForge Studio for SQL Server for a free 30-day trial and try Activity Monitor to understand how powerful this tool is. And feel free to watch this video to get more information about dbForge SQL Monitor:
