Unveiling Active Queries: Exploring Methods in SQL Server

There are two main methods:

1. Using Transact-SQL (T-SQL) queries:

   • T-SQL is the query language used in SQL Server.
   • You can write a T-SQL query that retrieves data from special system views like sys.dm_exec_requests and sys.dm_exec_sql_text.
   • These views provide information about currently running queries, including the database they are targeting, the user who submitted them, the actual query text, and how long they've been running.

2. Using SQL Server Management Studio (SSMS):

   • SSMS is a graphical user interface for managing SQL Server.
   • It has a built-in "Activity Monitor" tool that displays information about active sessions, queries, and resources.
   • This tool provides a user-friendly way to see what queries are running without writing any T-SQL code.

Using T-SQL Query:

This code retrieves information about currently running queries, including session ID (SPID), login name, start time, and the actual SQL text:

SELECT 
  request_id AS SPID,
  s.nt_username AS LoginName,
  request_start_time AS StartTime,
  CONVERT(nvarchar(max), query_string) AS SQLText
FROM sys.dm_exec_requests r
INNER JOIN sys.syslogins s ON r.session_id = s.sid
WHERE request_state NOT IN ('KILLED', 'COMPLETED')
ORDER BY request_id;

Explanation:

• sys.dm_exec_requests: This system view provides information about active requests in SQL Server.
• sys.syslogins: This system view contains information about logins to the SQL Server.
• The query joins these views to get login name associated with the running request.
• request_id: This column represents the session ID (SPID) of the running request.
• nt_username: This column shows the login name of the user who submitted the request.
• request_start_time: This column indicates when the query started execution.
• CONVERT(nvarchar(max), query_string): This converts the binary query string to readable text.
• The WHERE clause filters out queries that are already killed or completed.

Using SQL Server Management Studio (SSMS):

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. In the Object Explorer pane, navigate to your server name and right-click.
3. Select "Activity Monitor" from the context menu.
4. The Activity Monitor window will display information about active sessions, queries, and resources.
   • You can filter and sort the data to see specific running queries.

SQL Server Profiler:

• While less commonly used for real-time monitoring, SQL Server Profiler can be a powerful tool for capturing a trace of server activity over time.
• You can configure a trace to capture specific events related to query execution, allowing you to analyze the captured data later.
• This method is helpful if you need to track historical query execution or identify patterns in query usage.

Extended Stored Procedures (XSPs):

• For advanced users, there are a few Extended Stored Procedures (XSPs) available that can provide information about running queries.
• One such example is sp_whoIsActive (available as a free download). This XSP offers detailed information about active sessions and queries, similar to what you can achieve with T-SQL queries against system views.

Monitoring Tools:

• Several third-party monitoring tools integrate with SQL Server and offer functionalities like real-time query monitoring and performance analysis.
• These tools can provide a more comprehensive view of server health and performance, including insights into running queries.

Choosing the Right Method:

The best method for you depends on your specific needs:

• Real-time monitoring: Use T-SQL queries or SSMS Activity Monitor for immediate insights into currently running queries.
• Historical analysis: Utilize SQL Server Profiler to capture query execution data over time.
• Advanced features: Consider XSPs like sp_whoIsActive for detailed session and query information.
• Comprehensive monitoring: Explore third-party monitoring tools for a broader view of server performance.