How to use MSSQL Activity Monitor to find the most expensive query?

A slow and long-running SQL query execution consumes many processors, memory, and disk of your server, so finding and optimizing them for performance tuning is crucial. Such queries eventually delay the quick processing of queries and hold them in the queue.

There are certain ways to identify and troubleshoot long-running queries. This prevents potential performance issues.

SQL Activity Monitor is the easiest and most rich UI tool in SQL Server Management Studio. It provides information about processor time, waiting tasks, database I/O, batch requests, and recent expensive queries.


The following steps describe how to identify the recent expensive queries.

1. Login into your Windows VPS via RDP.

2. Open MSSQL Management Studio and log in as the “sa” user. Click on the activity monitor icon shown in the below image.

MSSQL Activity Monitor

3. The information about the CPU, RAM, disk, and execution time of each query can be analyzed on the SQL activity monitor. The queries with the highest resource usage and maximum time execution queries are displayed in “Recent Expensive Queries”.

SQL Recent Most Expensive Query

4. Hover to the query column to read the complete query. You need to optimize the query as much as possible to reduce the execution time and resource usage. You can directly edit the query in a text editor. Right-click the query and click on “Edit Query Text.”

Edit Query On Activity Monitor

The CPU(ms/sec) column shows how much processor time has been used by the query since the last compilation. It’s the ratio of processor time used by the query per second.

Was this answer helpful?

« Back