How to check which queries are running in SQL Server Management Studio (SSMS)

To check which queries are currently running in SQL Server Management Studio (SSMS), you can use the following methods:

1. Using Activity Monitor

Activity Monitor provides a real-time view of the queries running on your SQL Server instance.

  1. Open SQL Server Management Studio (SSMS).
  2. In the Object Explorer, right-click on the server name and choose Activity Monitor.
  3. In the Activity Monitor window, you can see a variety of information about SQL Server's current activity, including:
  4. Processes: Displays current running queries.
  5. Recent Expensive Queries: Shows queries that consume a lot of resources.
  6. SQL Server Performance: Gives you more general performance data.

You can find the running queries under the Processes pane. Right-click on a specific process to get more details about it.

2. Using Dynamic Management Views (DMVs)

You can run queries to get information about active sessions and queries in SQL Server. Here's an example query using DMVs:

SELECT
ses.session_id,
ses.login_name,
req.status,
req.start_time,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.sql_text
FROM
sys.dm_exec_sessions AS ses
JOIN
sys.dm_exec_requests AS req
ON ses.session_id = req.session_id
CROSS APPLY
sys.dm_exec_sql_text(req.sql_handle) AS sqltext
WHERE
req.status = 'running';

This query will return:

  1. session_id: The session ID for the running query.
  2. login_name: The login name of the user executing the query.
  3. status: The status of the query (e.g., running).
  4. start_time: When the query started.
  5. command: The type of query being executed.
  6. cpu_time: The amount of CPU time consumed by the query.
  7. total_elapsed_time: The total time the query has been running.
  8. sql_text: The SQL text of the running query.

3. Using sp_who2

sp_who2 is a system stored procedure that shows information about current users, sessions, and processes on SQL Server.

To check active queries:

EXEC sp_who2;

This will return a list of all active sessions, with columns like:

  1. SPID (Session ID)
  2. Status (whether the process is running, sleeping, etc.)
  3. Login (the user who initiated the session)
  4. CPUTime (CPU time used)
  5. BlkBy (if a session is being blocked by another)
  6. Command (the type of command running)

You can filter out specific sessions by using:

EXEC sp_who2 'active';

4. Using sys.dm_exec_requests

You can use the sys.dm_exec_requests DMV directly to see running queries:

SELECT
session_id,
status,
command,
start_time,
total_elapsed_time,
percent_complete,
wait_type,
wait_time,
wait_resource
FROM
sys.dm_exec_requests
WHERE
status = 'running';

This will give you details about the queries that are in the running state, including information about wait times, resources, and how long the query has been running.

Conclusion

The best method depends on your needs:

  1. Activity Monitor is a graphical way to view activity.
  2. DMVs provide powerful, customizable querying for detailed information.
  3. sp_who2 is a simple and quick way to get active session information.

Related Posts