SQL Server Management Studio Activity Monitor

The activity monitor in SQL Server Management studio is a powerful tool to help you identify and understand long running queries. Here's a simple guide to using it.

1. Open the Activity Monitor in SQL Management Studio. This can be found by right-clicking the SQL Server instance in the Object Explorer.

2. In the Activity Monitor, you will see a list of tabs.

- Overview

-Processes

-Resource Waits

-Data File I/O

-Recent Expensive Queries

-Active Expensive Queries

3. To find long running queries, click on the Recent Expensive Queries tab. This will show you all of the queries that have been running for more than a certain amount of time, which can be set in the Options menu.

5. From this tab, you can view the query text, execution plan, and other details about the query that can help you identify Venio jobs or searches that are using up the most SQL resources.

6. The Activity Monitor can also be used to monitor the performance of other processes, such as backups, restores, index rebuilds, and more. You can view the current running processes, view their details, and even cancel them if necessary. Additionally, you can view the server performance, such as CPU and memory usage, to ensure that your server is running optimally.