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.
- Login into your Windows VPS via RDP.
- Open MSSQL Management Studio and login as “sa” user. Click on the activity monitor icon shown in below image.
- 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”.
- 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.”