Microsoft KB Archive/325607

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

Article ID: 325607

Article Last Modified on 9/27/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q325607





SUMMARY
This article discusses a functional hotfix for SQL Server 2000 that implements the following changes: The hotfix creates a new fn_get_sql system table-valued function. The hotfix adds these three new columns to the end of the master..sysprocesses system table:
 * sql_handle(binary)
 * stmt_start(int)
 * stmt_end(int)

The hotfix creates a new trace flag, trace flag 2861.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 How to obtain the latest SQL Server 2000 service pack

Note The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

Hotfix file
There is a hotfix for SQL Server 2000 that implements the fn_get_sql function.

Contact Microsoft Product Support Services to obtain the fix. The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date         Time      Version    Size              File name -

07/01/2002  4:50 PM   8.00.652   7,285 kilobytes   Sqlservr.exe Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

Important If you want the functionality of this fix, you must apply a Sqlservr.exe build later than or equal to 8.00.652, and, you must run the Sp2_qfe_serv_uni.sql file that is included in this fix. For more information, see the Readme.txt file that is included in the fix files. 

STATUS
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.



MORE INFORMATION
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.

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:

Server: Msg 569, Level 16, State 1, Procedure fn_get_sql, Line 12 The handle passed to fn_get_sql was invalid.

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 &quot;Limiting Traces&quot; 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: DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 SELECT * FROM ::fn_get_sql(@Handle) 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.

Keywords: kbinfo kbfix kbsqlserv2000presp3fix kbqfe kbsqlserv2000sp3fix kbhotfixserver KB325607

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.