FIX: The fn_get_sql function returns SQL text for handle in the Sysprocesses system table

   Date         Time      Version    Size              File name
   -----------------------------------------------------------------

   07/01/2002   4:50 PM   8.00.652   7,285 kilobytes   Sqlservr.exe
				

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

Here is a definition and the syntax for the fn_get_sql system table-valued function.

fn_get_sql

Returns the SQL text referred to for the specified handle.

Syntax

fn_get_sql ([@SqlHandle = ]SqlHandle)

Arguments

[@SqlHandle = ] SqlHandle

The binary handle value. SqlHandle is binary(20) with no default.

Tables returned

Remarks

Fn_get_sql is a system table-valued function that returns the SQL text for the specified SQLHANDLE. You can obtain a valid SQLHANDLE from the sql_handle column of the sysprocesses system table.

If you pass a handle that no longer exists in cache, fn_get_sql will return an empty result set. If you pass an invalid handle, the batch will abort, and you receive the following error message: SQL Server cannot cache some Transact-SQL statements, such as bulk operation statements and statements with string literals larger than 8 KB. Handles to those statements are not retrievable through the fn_get_sql function.

The text column is filtered for text that may contain passwords. Review the "Limiting Traces" topic in SQL Server Books Online for details about the security-related stored procedures that are not monitored.

Permissions

Only members of the sysadmin fixed server role can run the fn_get_sql function.

Examples

The information returned by the fn_get_sql function is similar to the DBCC INPUTBUFFER command. Use the fn_get_sql function in situations where DBCC INPUTBUFFER is limited, such as:

• When events have more than 255 characters.
• When you have to return the highest current nesting level of a stored procedure. For example, you have two stored procedures that are named sp_1 and sp_2. If the sp_1 stored procedure calls the sp_2 stored procedure, and you get the handle from the sysprocesses system table while sp_2 is running, the fn_get_sql function will return information about sp_2. Additionally, the fn_get_sql function returns the whole stored procedure at the highest current nesting level.

Database administrators can use the fn_get_sql function to help diagnose problem processes. After an administrator identifies a problem server process ID (SPID), the administrator can retrieve the SQLHANDLE for that SPID, and then call the fn_get_sql function with the handle and use the start and end offsets to determine the SQL text of the problem SPID. For example: You can also use the fn_get_sql function to continuously monitor the server. For example, a client tool that periodically retrieves the SQLHANDLE and the statement start and end offsets from the sysprocesses system table. The tool maintains a cache of SQL text, with the SQL handle as the unique key and the results of the fn_get_sql function as the value. For each row in the sysprocesses system table rowset, the tool looks up the text in it's cache based on the SQLHANDLE. If the text is not in the tool's cache, the tool then calls the fn_get_sql function to get the text and save it in its cache.

Trace flag 2861

Trace flag 2861 instructs SQL Server to keep zero cost plans in cache, which SQL Server would typically not cache (such as simple ad-hoc queries, set statements, commit transaction and others).

• If trace flag 2861 is turned on, the fn_get_sql function can return the SQL text for activities that have zero cost plans. If trace flag 2861 is turned off, the fn_get_sql function cannot return the SQL text for the activities with zero cost plans.
• By default, trace flag 2861 is turned off when you apply this fix.

Remarks

The number of objects in the procedure cache increase when trace flag 2861 is turned on. Because the additional objects are so small, you will see a small increase in memory, which is taken up by the procedure cache.

SQL Server 2000 has an efficient algorithm to find any existing execution plan for any specified SQL statement. However, because of the increased number of objects stored in the procedure cache, it is possible that the time it takes for the relational engine to search for an existing plan may degrade and may adversely affect the performance of your system.

Typically, on systems where the database size is much larger than the memory size, the system is under some expected memory pressure. If the memory pressure is such that memory is required for other objects, the lazywriter process will deallocate objects in the procedure cache. This will bind the size of the procedure cache and will minimize the potential adverse affect of this change.

However, on systems where the memory size is larger than the database size, the system is generally not under memory pressure. Therefore, objects are not deallocated from the procedure cache because of memory needs, and the procedure cache size can grow to a point where it will adversely affect performance.

If you note an adverse affect on system performance, follow these steps:

1. Turn trace flag 2861 off.
2. Run the DBCC FREEPROCCACHE command from Query Analyzer. You do not have to restart SQL Server.