In my previous two columns, I told you about several system tables that I find most useful and examined some columns in one of my favorite system tables: sysprocesses. I also described how you can use columns from sysprocesses to troubleshoot locking and blocking problems. (See "Inside Sysprocesses," July 2003, InstantDoc ID 38674, and "Track Down Troublemakers," August 2003, InstantDoc ID 39453, for details.) This month, I finish discussing the usefulness of sysprocesses by looking at some columns that Microsoft added to that table in SQL Server 2000 Service Pack 3 (SP3).
Microsoft originally added these columns for a customer who was developing a tool for SQL Server monitoring. But these changes are now part of the released product; if you've installed SP3 or the newer SP3a, the product enhancements are available to you as well.
Microsoft released SP3a in May. Because most of the changes in SP3a involve the installation process, you don't need to apply SP3a to any instances of SQL Server 2000—or Microsoft SQL Server Desktop Engine (MSDE) 2000—that you've already upgraded to SP3. For instances that you haven't upgraded to SP3, Microsoft recommends upgrading them directly to SP3a. Now, let's look at the following SP3 product enhancements:
- The new table-valued system function fn_get_sql()
- Three new sysprocesses columns: sql_handle (binary), stmt_start (int), and stmt_end (int)
- The new trace flag 2861
These three features can assist you when you use the sysprocesses table for troubleshooting.
Retrieving Statements
Fn_get_sql() is a table-valued system function that returns the text of the T-SQL batch that's currently running on a specific connection. This function is useful because knowing the current command text can be an important part of troubleshooting a poorly performing or unresponsive query. To use the fn_get_sql() function, you must first know the server process ID (SPID) of the connection you're troubleshooting; then, you can use that SPID in the WHERE clause of a query that retrieves data from sysprocesses. The function also needs you to pass it a SQLHANDLE (a type of pointer) for the connection you're investigating. You can find the SQLHANDLE in that connection's row in the sysprocesses table.
After you retrieve the data, including the SQLHANDLE, from sysprocesses, you can pass the SQLHANDLE as an argument to the fn_get_sql() function. If you pass it a handle that no longer exists in cache, fn_get_sql() returns an empty result set. Because the function returns a table, it can't return a NULL. If you pass an invalid SQLHANDLE parameter, the batch aborts and you get an error message.
Let's look at the details of how to use this function. The fn_get_sql() function returns four columns of information in addition to a column containing the text of the current batch. The updated SQL Server 2000 SP3 Books Online (BOL) shows the definitions of all five columns, but in this discussion, I look only at the column called text.
Prev. page
[1]
2
next page 
