How To Use SQL Server Activity Monitor

Recently I was asked,

If you approached a SQL Server with abnormal usage (slow applications, etc,) and have a limited amount of time, how or what tools would you use to diagnose the issue?

I replied by saying I would execute a great script like sp_whoisactiveby Adam Machanic, or sp_BlitzFirst by Brent Ozar’s team.

He quickly replied,

Let’s assume the client in charge of the SQL Server doesn’t allow ANY external scripts to be deployed/executed on their servers.

Hmmm…

I never really thought about a scenario where the client doesn’t allow third party scripts. I told him that I’d use “Activity Monitor.

What is Activity Monitor?

It is an instance-level tool built inside SQL Server Management Studio that allows you to get a quick “inside look” into key statistics like Processor Time, Waiting Tasks, Database I/O, Batch Requests/sec, Recent Expensive Queries, etc.

How To Use SQL Server Activity Monitor

There are 2 ways to bring up Activity Monitor in SSMS:

First way

Open SSMS, then press CTRL + ALT + A (this is the keyboard shortcut)

Second Way

Right-click the instance and click on Activity Monitor (see screenshot)

How to use SQL Server Activity Monitor

After you click “Activity Monitor” you will get the below screen (see screenshot. Click to enlarge). After a few seconds you will see live stats under the “Overview” window.

How to use SQL Server Activity Monitor 2

You will notice Activity Monitor has five “categories”, Overview, Processes, Resource Waits, Data File I/O and Recent Expensive Queries. Each category has a wealth of information that can help you easily determine why a certain SQL Server is under performing. Remember, you don’t always have the ability to use a thumb drive or access the internet to download a script. SQL Server’s Activity Monitor is a great first step into diagnosing a slow server.