SQL Server Activity monitor- An underrated SSMS diagnostic tool.

In SQL Server Management Studio (SSMS), there is a very useful diagnostic tool called "Activity Monitor". I have observed while taking interviews, people usually never mention Activity Monitor as a primary tool which always gathers useful data to start troubleshooting easily. I will try to explain why I consider this very important to have a first look at Activity Monitor here. 

To open Activity Monitor , right click on your server instance in SSMS and to go to Activity Monitor. 

It opens up a new tab having 5 important sections along with an Overview of your server current status. This overview provides real-time insights into SQL Server performance. It displays information such as active processes, resource waits, data file I/O, and expensive queries. Lets deep dive in each section in details.

Overview:  This tab gives the current CPU %, Waiting Tasks, Database I/O (in MB/Sec) and Batch Requests/Sec. By having a look you can directly access that if your system is under CPU pressure or Waiting on some resources or Disk system is creating a bottleneck or your server system is experiencing heavy work load. Actually some of these metrices are gathered by system performance monitor counters as % Processor Time is obtained from "Processor" category where as "Batch Requests/Sec" is fetched from "SQLServer:SQL Statistics". So taking a glance of this actually can be the first step in right approach. 

Processes: This section gives details of all currently running processes in the SQL Server system. 

You can filter these details to get more relevant data such as, select your database name from "Database" dropdown column if you see some issue with an individual Database.

You can even sort the table from column name, e.g. if you sort the table from column "Wait Time" in descending order, it will give the detail of SessionID which is currently having Maximum Wait time. 

Any blocked and blocking session ids can also be find here directly, it will be visible under "Blocked By" column section and you can even kill the blocking SessionID from this table by right clicking on the SessionID through "Kill Process". Though in Production always double verify before killing any session. 

Resource Waits: This table gives details of all Waiting Categories with Wait Time. You can sort the table with "Cumulative Wait Time" in descending order to get the highest Wait Category at that moment to get an idea whether it is due to system memory or disk sub system or some other reason. Then from here you can move in correct direction for further diagnostic. 

Data File I/O: This table gives the details of Disk IO sub-system related to SQL server. It actually gives the Read/Write response for all the DB files hosted on OS disk system and a consistent higher values of "Response Time" might be an indication of underperforming disk system. 

Recent Expensive Queries: Here comes the main part which is very important for query level troubleshooting. This table can give you all the details of recently problematic queries, their execution count, where it was read intensive or write intensive or CPU intensive query along with the Database in which it was executing. Doing a right click on "Query" will also give you full query text and Execution Plan which be further drill down to find the exact problem. 

Active Expensive Queries: It provides a near Realtime expensive queries which is very handy for troubleshooting live scenarios. Apart from Query text, Execution plan. It also provides Live Execution Plan which is very useful for live data movement and tracking live query analysis. 

Now as you have gone through all sections , try it in your Prod or Non-Prod environment whenever you encounter an issue and do let me know if wasn't it helpful to immediately start your investigation from Activity Monitor? Do let me know if anything is missing here and I will try to incorporate it for community help. 

Also comment your inputs if any for further improvement.