Tracking functions supported by SQL Server 2000 system (1)
Baya Pavliashvili and Kevin Kline
Http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentId=28000409
Most of you may have already built your own user-defined functions (UDF) in SQL Server, but you know? Microsoft has integrated a large number of UDFS, especially in the latest release of SP3. Baya in this article Pavliashvili and Kevin Kline systematically studied UDF on SQL Server Tracking section. Some of you may want to read an article about traditional UDFS, such as Andrew Zanevsky's, September 2000 ("GRANTING WISHES) WITH UDF "), Andrew Zanevsky and Anton Jiline's article (" UDF Performance ... or lack of it "), or Jimmy Nilsson's article in July 2003 (" Another UDF: Global Constants).
UDFS is the SQL Server 2000 expecting additional feature. UDFS typical applications are DBAS and developers to modularize code and rooms or to improve performance. In this article, we will start from scratch to learn from the SQL Server system. UDFS can allow DBA to be tracked.
Although the user-defined function provided by the system sounds, Microsoft is also integrated with a large number of UDFS (read-only, system provision). At the same time, although the UDFS is originally released, but we discovered Just in SP3 is used in a lot of use because of its own purpose, the UDFS functions provided by all systems are started with 'FN_' and saved in the Master database.
Compare system and standard UDF
If you are familiar with UDFS, you may know that UDF is a record that cannot modify the fixed table. Typical applications are: read data, modify the data of the table variable, return data. And UDFS can run the custom function of the extended stored procedure and system. In fact, there are many custom functions provided by the system just simply call an extension stored procedure. [Extended stored procedures are usually written by C , you can see Paul Storer-Martin's article in July 2002 and August "Playing the ODS"], so the code that reads the same function as the T-SQL written is not better? The custom function of the system and the custom function of the user have slight differences at runtime: Typical custom functions (UDFS) can call this:
SELECT Column_List
From owner_name.udf_name (@ parameter1, ... @Parametern)
The custom function provided by the system needs to add two colons (: :) back after from FROM, and you don't have to specify the owner of this feature:
SELECT Column_List
From :: fn_systemsuppliedudf
(@ parameter1, ... @Parametern)
For example: the custom function FN_HELPCOLLLATIONS () provided by the system can return all the character sets supported by SQL Server 2000, we can do this:
Select * from :: fn_helpcollations ()
Custom function (UDFS) for tracking
A tracking captured T-SQL statement is sent (or running a stored procedure in the specified SQL Server listed, saving for a * .trc file. SQL Server tracking can via the PROFILER tool or the stored procedure sp_trace_create Establish and specify a lot of criteria to limit the output file. In this article, we mainly provide custom functions provided by the system of the tracking function.
Fn_Trace_gettablefn_trace_gettable () requires two parameters: initialization tracking file name (.TRC), and tracking files. When you create a track, you can configure the SQL Server limit trace file size. When the tracking file arrives at the specified size, SQL The Server will generate a new "scrolling" trace file. The second parameter of the FN_Trace_getTable () function is "scrolling" the number of trace files, which is starting when specifying the first parameter. If you like it Tracking New Year Save in the database, you can run a query simply, save the trace file as a data sheet with Fn_Trace_getTable, such as:
SELECT *
INTO DBO.MY_TRACE_TABLE
From :: fn_trace_gettable
('c: /trace_file.trc', default)
Moreover, it can be very convenient to directly query, search for some special meaning strings. In our test environment, all user-defined stored procedures start with "USP", so we can run a query, search for more than 3000ms records :
Select Textdata, Duration
From ::
Fn_Trace_getTable ('C: /Trace_File.trc', Default)
Where textdata like '% USP%'
And duration> 3000
With more complex queries, we can refine the SELECT statement to determine which queries are slower or only in the peak period.
FN_Trace_getinfo This system provides custom functions that you can get a tracking high-level information or all running tracks running on a SQL Server. This function has only one parameter - track number (Trace ID)
To limit the tracking information, you must specify the trace flag. You can also specify default or "0", as the trace flag, which can get all the running tracking information. SQL Server gives each track distribution when establishing tracking A tracking marker, if you don't specify the trace flag you want to query, simply run the system function in parameter "0", then you can limit the content you are interested in. The output of the FN_TRACE_GETInfo system function is described as :
Table 1. The output of F fn_trace_getinfo.
Column name
description
TRACEID
This tracking ID. Can be used to manage tracking through system stored procedures
Property
Tracking properties, represented by the following integers:
1 - Track option (see @Options in sp_trace_create) 2 - FileName3 - MaxSize4 - StopTime5 - Current tracking status
Value
Information about the attributes of the specified tracking.
The tracking option can be specified by system storage procedure sp_trace_create (see Table 2)
Table 2. The tracking option can be specified by system storage procedure sp_trace_create
Option name
Option value
description
TRACE_PRODUCE_ROWSET
1
Tracking will result in a rowset
TRACE_FILE_ROLLOVER
2
When Max_File_Size is reached, the current trace file will be closed. The new file is created. SQL Server will automatically add sequence numbers to each file (1, 2, 3 ....
Shutdown_on_error
4
If you cannot write a file, SQL Server will close.
TRACE_PRODUCE_BLACKBOX
8
If this option is selected, the last 5 MB tracking information record of SQL Server will be saved by the server.
Let's take a look at how Fn_Trace_GetInfo works. Imagine that we create a track by the following query:
/ * Declare a variable to hold trace id * /
Declare @Trace_id Int
/ * CREATE THE TRACE * /
EXEC SP_TRACE_CREATE
@TraceId = @trace_id output, @ Options = 2,
@Tracefile = n'e: /trace_file.trc ',
@MaxFileSize = 5,
@stoptime = NULL
/ * Start the TRACE We Just created.
By Default the TRACE IS Stopped At Creation
* /
EXEC SP_TRACE_SETSTATUS @Trace_ID, 1
/ * Return the TRACE IDENTIFIER * /
SELECT 'TRACE ID IS:' CAST (@trace_id as varchar (4))
--Result:
-------------------------
Trace ID IS: 2
Now we can use fn_trace_getinfo to get information about the corresponding tracking
Select * from :: Fn_Trace_GetInfo (2)
The result of the query is in Table 3.
Table 3. The result of the Fn_Trace_getinfo query.
TRACEID
Property
Value
2
1
2
2
2
E: /TRACE_FILE.TRC
2
3
5
2
4
NULL
2
5
1
This output tells us that there is a running track that automatically generates another file after 5MB. No specified tracking stop time (Property = 4), so the trace runs until the SQL Server service stops or passes the system stored procedure Sp_Trace_SetStatus stops tracking.
