Activity Monitor is an inbuilt monitoring tool which comes with SQL Server Management Studio. This monitoring tool provides real-time information about processes, resource waits, data file I/O and recent expensive queries etc. If you are a DBA or have to monitor database instance but do not have any third party tool, in that case it is very useful inbuilt tool which can be helpful to monitor the health of system and instance. Kindly note that to access activity monitor you need VIEW SERVER STATE permission.
In earlier version of SQL Server like SQL Server 2005 and SQL Server 2000, this monitoring tool was available at different location in instance and in SQL Server 2012, you can find it by right click on server name or instance name and select activity monitor as shown below.
Once activity monitor window will open, you will find different categories in it like.
1. Overview
2. Processes
3. Resource Waits
4. Data File I/O
5. Recent Expensive Queries
2. Processes
3. Resource Waits
4. Data File I/O
5. Recent Expensive Queries
1. Overview
In overview section, we can monitor graphical information about processor time, waiting tasks, database I/O details, batch request per second.
In overview section, we can monitor graphical information about processor time, waiting tasks, database I/O details, batch request per second.
2. Processes
In processes section, you will find information about processes, login details, database, application details, currently running SQL commands, blocking details etc. You can right click on process and kill the blocking queries or you can trace the processes with SQL Server Profiler directly.
In processes section, you will find information about processes, login details, database, application details, currently running SQL commands, blocking details etc. You can right click on process and kill the blocking queries or you can trace the processes with SQL Server Profiler directly.
3. Resource Waits
Resource wait section provides detail about processes which are waiting for resources, and different wait time information.
Resource wait section provides detail about processes which are waiting for resources, and different wait time information.
4. Data File I/O
Data file I/O provides detail about database and physical database files currently in use and read write information.
Data file I/O provides detail about database and physical database files currently in use and read write information.
5. Recent Expensive Queries
Most expensive recent queries detail and resources utilized by those queries are available in recent expensive queries section. You can find the execution plan and view the query by right click on the available query.
Most expensive recent queries detail and resources utilized by those queries are available in recent expensive queries section. You can find the execution plan and view the query by right click on the available query.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
