Have you ever wondered what SQL statements are currently executing in your SQL Server database? The built-in utility sp_who2 provides some information, but it has limitations. It doesn’t show the actual underlying SQL executing, which can be crucial for debugging slow queries or identifying blocking issues. In this article, we will introduce a utility that overcomes these limitations and allows you to identify the SQL statements currently executing in your SQL Server database.

The ‘What SQL Statements Are Currently Executing’ Utility

The utility we will discuss in this article is called ‘dba_WhatSQLIsExecuting’. It makes use of Dynamic Management Views (DMVs) and can be used in SQL Server 2005 or later versions. The utility retrieves information from the sys.db_exec_requests DMV, which shows the requests currently executing, including the handle to the SQL text and offsets relating to the section of SQL within the batch that is currently executing.

To determine the current section of SQL executing, the utility calls the sys.dm_exec_sql_text Dynamic Management Function (DMF) and applies the relevant offsets. By combining the sys.db_exec_requests DMV with the sys.sysprocesses system view, the utility provides additional information such as the user executing the query, the machine they are running from, and the name of the database.

Running the Utility

To run the ‘What SQL Statements Are Currently Executing’ utility, execute the following stored procedure:

EXEC YourServerName.master.dbo.dba_WhatSQLIsExecuting

The utility will return the Parent Query that is running, typically a stored procedure, along with the Individual Query within the Parent Query that is currently executing. It also provides useful information such as the database name, user name, and program name. The output can help you observe the progress of a stored procedure or SQL batch and identify the cause of long-running or blocking queries.

Conclusion

The ‘What SQL Statements Are Currently Executing’ utility described in this article is a valuable tool for SQL Server DBAs and developers. It allows you to identify the SQL statements currently executing, which can be crucial for debugging slow queries, identifying blocking issues, and monitoring the progress of stored procedures. By leveraging the power of Dynamic Management Views (DMVs), the utility provides detailed information about the executing queries without significantly impacting performance.

Give the ‘What SQL Statements Are Currently Executing’ utility a try in your SQL Server environment and see how it can enhance your troubleshooting and monitoring capabilities.

Author: John Doe