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?
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.
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:
Open SSMS, then press CTRL + ALT + A (this is the keyboard shortcut)
Right-click the instance and click on Activity Monitor (see screenshot)
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.
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.