Èñïðàâëåíèå: Ôóíêöèÿ fn_get_sql âîçâðàùàåò SQL-òåêñò äëÿ äåñêðèïòîðà â ñèñòåìíîé òàáëèöå Sysprocesses

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

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

Äàííîå ïîâåäåíèå ÿâëÿåòñÿ ïîäòâåðæäåííîé îøèáêîé ïðîäóêòîâ Ìàéêðîñîôò, ïåðå÷èñëåííûõ â íà÷àëå äàííîé ñòàòüè..Âî-ïåðâûõ, ýòîé ïðîáëåìû ïîÿâèëîñü â SQL Server 2000 ñ ïàêåòîì îáíîâëåíèÿ 3.

Íèæå ïðèâîäèòñÿ îïèñàíèå è ñèíòàêñèñfn_get_sqlÒàáëè÷íàÿ ôóíêöèÿ ñèñòåìû.

fn_get_sql

Âîçâðàùàåò òåêñò SQL, êîòîðûé ññûëàåòñÿ çàäàííûé äåñêðèïòîð.

Ñèíòàêñèñ:

fn_get_sql ([@SqlHandle= ]SqlHandle)

Arguments

[@SqlHandle= ]SqlHandle

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

Tables returned

Remarks

Fn_get_sqlis a system table-valued function that returns the SQL text for the specified SQLHANDLE. You can obtain a valid SQLHANDLE from thesql_handleñòîëáåösysprocessesÑèñòåìíàÿ òàáëèöà.

If you pass a handle that no longer exists in cache,fn_get_sqlwill 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 thefn_get_sqlfunction:.

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 thesysadminfixed server role can run thefn_get_sqlfunction:.

ÏÐÈÌÅÐÛ

The information returned by thefn_get_sqlfunction is similar to the DBCC INPUTBUFFER command. Åñëè äàííûé ïîëüçîâàòåëü ÿâëÿåòñÿ ÷ëåíîì äîìåíà, òî ïðè ââîäå èìåíè ïîëüçîâàòåëÿ èñïîëüçóéòå ôîðìàòfn_get_sqlfunction 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 namedsp_1èsp_2. Åñëèsp_1stored procedure calls thesp_2stored procedure, and you get the handle from thesysprocessessystem table whilesp_2is running, thefn_get_sqlfunction will return information aboutsp_2. Êðîìå òîãîfn_get_sqlfunction returns the whole stored procedure at the highest current nesting level.

Database administrators can use thefn_get_sqlfunction 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 thefn_get_sqlfunction with the handle and use the start and end offsets to determine the SQL text of the problem SPID. Íàïðèìåð,:Ìîæíî òàêæå èñïîëüçîâàòüfn_get_sqlfunction to continuously monitor the server. For example, a client tool that periodically retrieves the SQLHANDLE and the statement start and end offsets from thesysprocessesÑèñòåìíàÿ òàáëèöà. The tool maintains a cache of SQL text, with the SQL handle as the unique key and the results of thefn_get_sqlfunction as the value. For each row in thesysprocessessystem 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 thefn_get_sqlfunction 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, thefn_get_sqlfunction can return the SQL text for activities that have zero cost plans. If trace flag 2861 is turned off, thefn_get_sqlfunction 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.

 ñèñòåìàõ, ãäå ðàçìåð áàçû äàííûõ — ãîðàçäî áîëüøå, ÷åì ðàçìåð ïàìÿòè, ñèñòåìà îáû÷íî íåêîòîðûå íåäîñòàòêå ñâîáîäíîé ïàìÿòè îæèäàåìîé.  ñëó÷àå íåõâàòêè ïàìÿòè, ïàìÿòü äëÿ äðóãèõ îáúåêòîâ â ïðîöåññå îòëîæåííîé çàïèñè áóäåò îñâîáîæäåíèÿ îáúåêòîâ â êýøå ïðîöåäóð. Ýòî áóäåò ïðèâÿçàòü ðàçìåð êýøà ïðîöåäóð è ñâåäåò ê ìèíèìóìó ïîòåíöèàëüíîãî îòðèöàòåëüíîãî âëèÿíèÿ ýòî èçìåíåíèå.

Îäíàêî â ñèñòåìàõ, ãäå îáúåì ïàìÿòè áîëüøå, ÷åì ðàçìåð áàçû äàííûõ, ñèñòåìà íå îáû÷íî íåäîñòàòêå ñâîáîäíîé ïàìÿòè. Òàêèì îáðàçîì îáúåêòû íå ñòàíîâÿòñÿ íåäîñòóïíû èç êýøà ïðîöåäóðû èç-çà ïîòðåáíîñòè â ïàìÿòè è ðàçìåðà êýøà ïðîöåäóð ìîãóò óâåëè÷èâàòüñÿ äî òî÷êè, ãäå îí áóäåò ñóùåñòâåííî ïîâëèÿòü íà ïðîèçâîäèòåëüíîñòü.

Îáðàòèòå âíèìàíèå, íåãàòèâíî âëèÿåò íà ïðîèçâîäèòåëüíîñòü ñèñòåìû, âûïîëíèòå ñëåäóþùèå äåéñòâèÿ:

1. Âûêëþ÷èòå ôëàã òðàññèðîâêè 2861.
2. Âûïîëíèòå êîìàíäó DBCC FREEPROCCACHE ñ ïîìîùüþ àíàëèçàòîðà çàïðîñîâ. Íåîáõîäèìî ïåðåçàïóñòèòü SQL Server.