Tuesday, June 23, 2020

What is SQL Server Activity Monitor ?

What is SQL Server Activity Monitor?
SQL Server Activity Monitor is a feature in SQL Server Management Studio that displays information about the SQL Server processes and their effect on SQL Server performance.
Activity Monitor panes
Activity Monitor consists of several panes – Overview, Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. Panes can be expanded and collapsed. The activities are queried only when the specific pane is expanded
“Activity Monitor runs queries on the monitored instance to obtain information for the Activity Monitor display panes. When the refresh interval is set to less than 10 seconds, the time that is used to run these queries can affect server performance”.
The Overview pane
The Processes pane
Task State – the task state, blank for tasks in the runnable and sleeping state. The value can also be obtained using the sys.dm_os_tasks view, as the task_state column. The states returned can be:
“PENDING: Waiting for a worker thread.
RUNNABLE: Runnable, but waiting to receive a quantum.
RUNNING: Currently running on the scheduler.
SUSPENDED: Has a worker, but is waiting for an event.
DONE: Completed.
SPINLOOP: Stuck in a spinlock.”
The Resource Waits pane
The Data File I/O pane
The Recent Expensive Queries pane
Requirements to use Activity Monitor
How to start Activity Monitor
There are several ways to start Activity Monitor – in the SQL Server Management Studio toolbar click the activity Monitor icon, use the keyboard shortcut Ctrl+Alt+A, or in Object Explorer right-click the SQL Server instance and select Activity Monitor
One more option is to set Activity Monitor to be opened when SQL Server Management Studio is started
1.     In the SQL Server Management Studio menu click Tools and then Options
2.     Open the Environment | Start Up tab
3.     Select the Open Object Explorer and Activity Monitor option
On the next SQL Server Management Studio start up, Object Explorer will be shown on the left and Activity Monitor on the right
Activity Monitor is a SQL Server Management Studio feature that tracks some of the most important metrics that affect performance. It shows the metrics in real time, without the out-of-the-box solution to save them for later analysis. Filtering by a specific database/parameter is easy, but excluding a specific value is not possible. Monitoring additional metrics is also not possible. Due to its limited set of features and monitored metrics, it’s not recommended for in-depth performance monitoring.

No comments:

Post a Comment