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.
.gif)
0 Comments