Activity Monitor In SQL Server

Overview

Has anyone seen on Database connect there is an option called activity Monitor?



Well we will see in detail what activity monitor is is all about in SQL Server.

Introduction

If you are trying on server level you need to have permission from DBA to access Activity Monitor. If you do have this our task becomes much simpler. Just with the help of normal GUI right click and click on Activity monitor. Or else press ctrl + Alt + A.

• When you click on Activity Monitor you will see an overview and various tabs such as:
  
  
  
  
• Overview Pane
• Processes
• Resources Waits
• Data File I/O
• Recent Expensive Queries

So let’s see one by one,

Overview Pane

• Processor Time: Is the time taken by processor to execute the thread.
• Waiting Tasks: Tasks that are waiting for the processor, here memory to be released so that it can perform tasks.
• Database I/O: Data transfer rate.
• Batch request/Sec: No of SQL Server batches received.

On server Level you will see a hike graph going up and down. As I am showing you in local PC try this Activity Monitor on Server Level.

Processor Pane

The processor pane gives you an idea of which processes are running currently,



As you can see those process are running in process pane and it gives an idea which database is connected.

If there are multiple databases under a single instance:

• Session ID: Here a unique value is assigned everytime when an connection is made to database.
  
• Login: Under which login its running
  
• Database: The actual Database Name where its connecting
  
• Task State: These are obtained from sys.dm_os_tasks view the task state are generally pending,runnable,running and suspended.
  
• Command: The actuall SQL command which its running.
  
• Application: Name of Application.

Wait type, enitre source, and wait tasks are from sys.dm,

• HostName: The hostname from where the connection is made .
  
  

Here you can see the task state and command whether its updated, selected, inserted,  and so on



HostName or Servername is shown in process Pane,



Resource Waits



Here in Resource waits it shows wait category.

• Wait time: How much time it has to wait in /sec.
• Recent Wait time: The time tasks are waiting for more than one resource.
• Average waiter counts: It is calculated in terms of interval samples that process that has to wait for resources.
• Cumulative Wait Time: Cumulative wait time is calculated when the SQL sever instance is restarted.

Data File I/O



Here it shows the path of MDF,LDF and NDF files.

Recent Expensive Queries



It Shows the queries that are hit on database in the last 50 seconds.

There are things like physical read, execution time, and so on --  you can see the details here as:



Click on Show Execution Plan.



You will see a detailed plan here.

Conclusion

That’s all from SQL Server Activity monitor. If you have any doubts regarding this topic feel free to ask.