Get Current Running Queries in SQL Server with fn_get_sql


By:   |   Updated: 2020-08-14   |   Comments   |   Related: More > Performance Tuning


Problem

If you have ever had the need to troubleshoot a process that is currently running in SQL Server there are a few different ways to see what query statement is being run.  These options include:

  1. using SQL Server Management Studio and Activity Monitor
  2. using the system stored procedures sp_who2
  3. querying the system tables directly

Using any of these methods allows you to see which connections are currently active as well part of the statement that is currently running, but they don't all show the entire contents of the statement.

Solution

Another option is to use the "::fn_get_sql" statement.  This was introduced with SQL Server 2000, but still works.

Using fn_get_sql

This allows you to see the entire command that is running.  Instead of limiting the output when using DBCC INPUTBUFFER.

To use this command, all you need is the SPID that you want to collect the information for and run the command. 

In the following examples the SPID that we are investigating is 55, therefore this is what is used for both statements.  The SPID can be retrieved by getting the Process ID from SSMS or by running the stored procedure sp_who2.

DECLARE @Handle binary(20) 
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55 SELECT * FROM ::fn_get_sql(@Handle)

Here is the output we get from running the above.

SELECT *, Orders.OrderID AS Expr1, Orders.CustomerID AS Expr2, Orders.EmployeeID AS Expr3, Orders.OrderDate AS Expr4, Orders.ShipPostalCode AS Expr5, Orders.ShipCountry AS Expr6, Orders.ShipRegion AS Expr7, Orders.ShipCity AS Expr8, Orders.ShipAddress AS Expr9, Orders.ShipName AS Expr10, Orders.Freight AS Expr11, Orders.ShipVia AS Expr12, Orders.ShippedDate AS Expr13, Orders.RequiredDate AS Expr14, Customers.CustomerID AS Expr15, Customers.CompanyName AS Expr16, Customers.ContactName AS Expr17, Customers.ContactTitle AS Expr18, Customers.Fax AS Expr19, Customers.Phone AS Expr20, Customers.Country AS Expr21, Customers.PostalCode AS Expr22, Customers.Region AS Expr23, Customers.Address AS Expr24, Customers.City AS Expr25, [Order Details].OrderID AS Expr26, [Order Details].ProductID AS Expr27, [Order Details].UnitPrice AS Expr28, [Order Details].Quantity AS Expr29, [Order Details].Discount AS Expr30 FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Using DBCC INPUTBUFFER

If we use DBCC INPUTBUFFER as shown below, this only returns part of the command.

DBCC INPUTBUFFER (55)
SELECT *, Orders.OrderID AS Expr1, Orders.CustomerID AS Expr2, Orders.EmployeeID AS Expr3, Orders.OrderDate AS Expr4, Orders.ShipPostalCode AS Expr5, Orders.ShipCountry AS Expr6, Orders.ShipRegion AS Expr7, Orders.ShipCity AS Expr8, Orders.ShipAddress AS

Using SSMS Activity Monitor

If you use Activity Monitor in SSMS, it will give you the same output as DBCC INPUTBUFFER and doesn't return the entire command.  To do so, right click on one of the processes and select Details.  This will show you the statement.

Conclusion

As you can see using this function can help return more of the query statement.  In later versions of SQL Server the output of DBCC INPUTBUFFER has been increased, but it still cuts off some of the text.

Next Steps


Last Updated: 2020-08-14


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

How to find out how much CPU a SQL Server process is really using

SQL Server stored procedure runs fast in SSMS and slow in application

Different Ways to Flush or Clear SQL Server Cache

UPDATE Statement Performance in SQL Server

Fastest way to Delete Large Number of Records in SQL Server








get free sql tips
agree to terms


Learn more about SQL Server tools