By: Greg Robidoux | Updated: 2006-06-27 | Comments | Performance Tuning
If you have ever had the need to troubleshoot current processing that is occurring on your SQL Server there are a few different ways to see what is going on. These options include:
- using Enterprise Manager and looking at current activity
- using the system stored procedures sp_who2 or sp_who
- or maybe 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. With Enterprise Manager you can double click on process and a window will pop up with the current batch or last batch that was run or with options 2 and 3 you can use DBCC INPUTBUFFER from Query Analyzer to see the same information, but both of these options only allow you to see the first 255 characters of the statement.
If you are running a stored procedure with a few parameters, 255 characters is probably not a problem, but if a long statement is running most of the statement will not be displayed and therefore it makes it much tougher to troubleshoot the problem.
Another option is to use the "::fn_get_sql" statement. This was introduced with SQL Server 2000 and allows you to see the entire command that is running. Instead of limiting the output to 255 characters, the output is a Text data type and therefore the entire statement can be seen.
To use this command it is pretty similar to using DBCC INPUTBUFFER. All you need is the SPID that you want to collect the information on for the currently running process and run the command using Query Analyzer.
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 retreived by getting the Process ID from Enterprise Manager or by running sp_who2 in Query Analyzer.
|DECLARE @Handle binary(20) |
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@Handle)
|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|
|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|
|Enterprise Manager Output|
As you can see the output using ::fn_get_sql provides a lot more detail then DBCC INPUTBUFFER and what Enterprise Manager offers.
- When you are troubleshooting a SQL Server issue or a current running processes use this function to provide all the details
- Add this tool to your SQL Server toolbox
Last Updated: 2006-06-27
About the author
View all my tips