Activity Monitor
Overview
The goal of monitoring databases is to assess how a server is performing. Effective monitoring involves taking periodic snapshots of current performance to isolate processes that are causing problems, and gathering data continuously over time to track performance trends. Microsoft SQL Server and the Microsoft Windows operating system provide utilities that let you view the current condition of the database and to track performance as conditions change.
To use SQL Server and Windows performance and activity monitoring tools contains the following topics (https://docs.microsoft.com/en-us/sql/relational-databases/performance/server-performance-and-activity-monitoring?view=sql-server-ver15):
To perform monitoring tasks with Windows tools
To create SQL Server database alerts with Windows tools
To perform monitoring tasks with Extended Events
To perform monitoring tasks with SQL Server Management Studio
In this blog, we focus on Activity Monitor. The first thing we need to know is that Activity Monitor (SQL server tool) is not Performance Monitor (Windows tool).
Performance Monitor, also known as PerfMon, is a tool provided with the Windows operating system that you can use to monitor operating system, application, and hardware performance in real time. You can even establish thresholds and receive alerts when thresholds are crossed. As an alternative, you can capture performance data in logs that you can review in the graphical interface or save to SQL Server tables for trend analysis.
If you are running Microsoft Windows server operating system, use the System Monitor graphical tool to measure the performance of SQL Server. You can view SQL Server objects, performance counters, and the behavior of other objects, such as processors, memory, cache, threads, and processes. Each of these objects has an associated set of counters that measure device usage, queue lengths, delays, and other indicators of throughput and internal congestion (https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-resource-usage-system-monitor?view=sql-server-ver15).
Taken together, PerfMon is used for looking into the resource usage of SQL Server and is not a query level tool.
Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server (https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/activity-monitor?view=sql-server-ver15).
Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries, and Active Expensive Queries. When any pane is expanded, Activity Monitor queries the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can expand one or more panes at the same time to view different kinds of activity on the instance.
Here is an excellent blog by Dan Sales, who discussed in-depth on Using Activity Monitor
There are two ways to open Activity Monitor (https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/open-activity-monitor-sql-server-management-studio?view=sql-server-ver15)
1. Keyboard shortcut
Type CTRL+ALT+A to open Activity Monitor at any time.
Hint! Hover over any icon in SSMS to learn what it is and what keyboard shortcut activates it!
2. Toolbar
From the Standard toolbar, click the Activity Monitor icon. It is in the middle, just to the right of the undo/redo buttons.
Complete the Connect to Server dialog box if you are not already connected to an instance of SQL Server you want to monitor.
Here is an excellent blog by Greg on Using Activity Monitor
Alternatively, you can use Extended Events to monitor performance. What's more, Extended Events can collect data for evaluation. In other words, Extended events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server (https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver15).
You can create an Extended Events session by using the Query Editor, or you can create a session in Object Explorer.
Create an Extended Events Session Using Query Editor
Create an Extended Events Session Using the Wizard (Object Explorer)
Create an Extended Events Session Using the New Session Dialog
While you could start from scratch and write a "CREATE EVENT SESSION …" statement, as we saw in the "New Session Wizard" there was an option at the end to generate a script of your session. I find it easiest to start using this and modify the script as necessary. You can also generate and TSQL script directly from within Object Explorer (https://www.mssqltips.com/sqlservertutorial/9200/create-sql-server-extended-events-session-using-ssms-query-editor).
In Object Explorer, Extended Events provides two user interfaces you can use to create, modify, and view event session data - a wizard that guides you through the event session creation process, and a New Session UI that provides more advanced configuration options. You can create Extended Events sessions to diagnose SQL Server tracing, which enables you to resolve issues such as the following (https://docs.microsoft.com/en-us/sql/database-engine/create-an-extended-events-session?view=sql-server-2014):
Find your most expensive queries
Find root causes of latch contention
Find a query that is blocking other queries
Troubleshoot excessive CPU usage caused by query recompilation
Troubleshoot deadlocks
Ben Snaidero provided an excellent tutorial for learning Extended Events.
Example:
You are experiencing performance issues with the database server. You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?
A. a System Monitor report
B. a sys.dm_tran_database_transaction dynamic management view query
C. an Extended Events session that uses Query Editor
D. an Activity Monitor session in Microsoft SQL Management Studio.
E. a sys.dm_exec_session_wait_stats dynamic management view query
Hints:
sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.
SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has "signaled" that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting for a specific resource to become available, such as waiting for the release of a lock on a specific record.
Extended Events: considered as "the best way" by the SQL Server purists. You can configure Extended Events to find Locking Issues in SQL Server.
SQL Trace and SQL Server Profiler are deprecated. The Microsoft SqlServer Management Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.