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

A slow and long running SQL query execution consumes much processor, memory, and disk of your server so It is crucial to find and optimize them for performance tuning.such queries eventually delay the quick processing queries and hold them on 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 rich UI tool available in SQL Server Management Studio.It provides information about processor time, waiting tasks, database I/O, and batch requests, and recent expensive queries.

 


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

  1. Login into your Windows VPS via RDP.

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

    MSSQL Activity Monitor

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

    SQL Recent Most Expensive Query


  4. Mouse 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

  5. The CPU(ms/sec) column shows how much processor time is used by query since the last compilation.It’s the ratio of processor time used by the query per second.
  • 0 Users Found This Useful

Was this answer helpful?

Related Articles

How to change port for MSSQL Server 2008?

In Windows VPS, 1533 is the default port for MSSQL Server. Following steps will guide you to...

Comparison of MS SQL Server 2014 Express Edition V/s. MS SQL Server 2014 Web Edition

Following is a brief comparison between MSSQL Server 2014 Express Edition and MSSQL Server 2014...

vs shell installation has failed with exit code 1638.

You may receive the following error while installing MSSQL 2017 on Windows 2012 R2 Server. vs...

How to change Database Collation in MsSQL Server?

Collation is a configuration level setting in the MSSQL. It directs how the character data will...

How to check active connections on your MS-SQL Database ?

To check active database connection in the MS SQL server please follow the below steps. Open...