Activity Monitor In SQL Server

Gagan Sharma
8y
5.9k
0
2
100
Article

In SQL Server 2014 we have Activity Monitor window tool, it is useful to provide analytical statistics information about SQL Server processes and affect the various processes on current instance of the SQL Server that is in use.

Database Administrators and Database developers often use it to know about performances and workloads.

Various ways to open Activity Monitor in SQL Server Management Studio (SSMS) 2014:

Through Object Explorer : Right click on Server expand it and then click on Activity Monitor.
Through Tool panel:
Activity Monitor can be seen while opening SSMS through Tool Tab options: Startup means whenever you will login into SSMS, Activity Manager will open by default.

Activity Monitor Dashboard in SQL Server 2014:

Activity Monitor Dashboard Expandable Panes:

Functionality under Panes:

Overview: It consists of Graphical information about Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second. It acts like a SNAPSHOT to view.

Refresh Interval Timing Selection:
Processes: It consists of active users list, which are connected to SQL Server Database Engine.
You can Edit Processes: To see Details or Kill Process or Trace Process in SQL Server Profiler:
Resource Waits: It shows various tasks that are waiting for processor, I/O, or memory resources. This pane is helpful to identify threads in waiting that are associated in terms of respect to Memory, CPU, Backup, buffer and Network I/O, etc.
Data File I/O: It provides the information of transfer rate in MB/Sec, of data from memory to disk, disk to memory, or disk to disk about various log files and databases. Through this Identifying bad performance database is possible.
Recent Expensive Queries: It is helpful for database administrators to quickly identify poor performance queries in an SQL Server Instance.