Article ID: 325607 - View products that this article applies to.
This article was previously published under Q325607
This article discusses a functional hotfix for SQL Server 2000 that implements the following changes:
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:
290211Note The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.
(http://support.microsoft.com/kb/290211/ )How to obtain the latest SQL Server 2000 service pack
Hotfix fileThere 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.
Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.
Date Time Version Size File name ----------------------------------------------------------------- 07/01/2002 4:50 PM 8.00.652 7,285 kilobytes Sqlservr.exe
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.
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_sqlReturns the SQL text referred to for the specified handle.
Syntaxfn_get_sql ([@SqlHandle = ]SqlHandle)
Arguments[@SqlHandle = ] SqlHandle
The binary handle value. SqlHandle is binary(20) with no default.
Collapse this tableExpand this table
RemarksFn_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.
Server: Msg 569, Level 16, State 1, Procedure fn_get_sql, Line 12 The handle passed to fn_get_sql was invalid.
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.
PermissionsOnly members of the sysadmin fixed server role can run the fn_get_sql function.
ExamplesThe 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:
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 2861Trace 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).
RemarksThe 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: