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:
The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.
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
tab in the Date and Time tool in Control Panel.
Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.
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.
Returns the SQL text referred to for the specified handle.
= ] SqlHandle
The binary handle value. SqlHandle is binary(20)
with no default.
|Column name||Data type||Description|
|dbid||smallint||Database ID. NULL in the case of ad-hoc SQL statements. |
|objectid||int||ID of the database object. NULL in the case of ad-hoc SQL statements.|
|number||smallint||Number in procedure grouping if grouped. 0 for entries that are not procedures. NULL in the case of ad-hoc SQL statements.|
|encrypted||bit||Indicates if the object is encrypted:|
0 = Not encrypted
1 = Encrypted
|Text||text||SQL Text. NULL in the case of encrypted objects.|
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
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
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.
Only members of the sysadmin
fixed server role can run the fn_get_sql
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 = 52SELECT * 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.
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:
- Turn trace flag 2861 off.
- Run the DBCC FREEPROCCACHE command from Query Analyzer. You do not have to restart SQL Server.