Understanding the Code Examples

Using the sys.dm_exec_requests Dynamic Management Function (DMF):

• This DMF provides real-time information about all active queries on the server.
• To list queries, execute the following query:

SELECT
    session_id,
    request_id,
    status,
    start_time,
    command,
    text
FROM
    sys.dm_exec_requests;

• The columns in the result set provide details such as session ID, request ID, status, start time, command type, and the actual query text.

Using the sp_who Stored Procedure:

• While older, sp_who is still a useful option for quickly getting a snapshot of running queries.
• Execute the following stored procedure:

EXEC sp_who;

• The output will display information about active sessions, including session ID, user name, database, and the running query.

Using SQL Server Profiler:

• SQL Server Profiler is a graphical tool that can capture and analyze events occurring on the server.
• To trace running queries, create a trace profile and start tracing. The trace output will contain detailed information about each query, including its execution plan.

Using the EXEC sp_trace_results Stored Procedure:

• If you have already created a trace, you can use this stored procedure to view the results.

EXEC sp_trace_results @traceid = <trace_id>;

• Replace <trace_id> with the actual ID of your trace. The results will display information about captured events, including query text.

Additional Tips:

• To filter the results based on specific criteria (e.g., session ID, status, command type), you can use the WHERE clause in your query.
• For more advanced monitoring and analysis, consider using tools like SQL Server Management Studio or third-party performance monitoring software.

Understanding the Code Examples

Note: These code examples demonstrate how to list currently running queries on a SQL Server instance.

SELECT
    session_id,
    request_id,
    status,
    start_time,
    command,
    text
FROM
    sys.dm_exec_requests;

Explanation:

• Columns:
  • session_id: The unique identifier of the session.
  • request_id: The unique identifier of the request within the session.
  • status: The current status of the request.
  • start_time: The time the request started.
  • command: The type of command being executed.
  • text: The actual SQL text of the query.

EXEC sp_who;

• sp_who: This stored procedure provides a summary of active sessions on the server.
• Output:

• Steps:
  1. Create a trace profile.
  2. Start tracing.
  3. View the trace results to see information about running queries.

EXEC sp_trace_results @traceid = <trace_id>;

• sp_trace_results: Used to view the results of a previously created trace.
• @traceid: The ID of the trace you want to view.

• For more granular control and filtering, you can use WHERE clauses with the sys.dm_exec_requests query.
• Consider using tools like SQL Server Management Studio for a more user-friendly interface and additional features.

Alternative Methods for Listing Running SQL Server Queries

Beyond the standard methods discussed earlier, here are some additional approaches:

Extended Events (EE)

• Advantages:
  • More granular control and flexibility compared to Profiler.
  • Can capture events beyond queries, such as locks, waits, and errors.
  • Can be configured to capture events asynchronously, reducing performance impact.
• Example:

  CREATE EVENT SESSION [RunningQueries]
  ADD EVENT [sql_server.query_execution]
  ACTION (
      ADD TARGET [event_file] (
          SET FILENAME = 'RunningQueries.xel'
      )
  )
  GO
  CREATE EVENT SESSION [RunningQueries] STATE ON;
  

  This creates an Extended Events session to capture query execution events, saving them to a file. You can then use tools like SQL Server Management Studio to view the events.

Performance Monitor (PerfMon)

• Advantages:
  • Provides a real-time view of server performance metrics, including query activity.
  • Can be used to create alerts and notifications based on specific thresholds.
• Steps:
  1. Open Performance Monitor.
  2. Add the "SQL Server" category.
  3. Select counters related to query activity, such as "Batch Requests/sec" or "SQL Server: Locks/sec".

Third-Party Monitoring Tools

• Advantages:
  • Often provide more advanced features and integrations than built-in tools.
  • Can offer specialized capabilities for performance tuning, troubleshooting, and reporting.
  • Examples include Red Gate SQL Monitor, SolarWinds Database Performance Analyzer, and DB Engine Tuning Advisor.

Dynamic Management Views (DMVs)

• Advantages:
  • Provide a programmatic way to query server information.
  • Can be combined with other DMVs to get a more comprehensive view of query activity.
• Example:

  SELECT
      session_id,
      request_id,
      status,
      start_time,
      command,
      text
  FROM
      sys.dm_exec_requests;
  

  This is the same example used earlier, but it demonstrates the use of a DMV to retrieve query information.