Trace functions supported by SQL SERVER 2000 system

server| function

Most of you may have set up your own user-defined functions (UDF) in SQL Server, but you know what? Microsoft has already integrated a large number of its own UDFs, especially in the latest release of SP3. In this article, Baya Pavliashvili and Kevin Kline systematically studied the UDF about the SQL Server Tracking section. Some of you may want to read an article about traditional UDFs in the previous SQL Server professional, such as the September 2000 column of Andrew Zanevsky's ( "Granting Wishes with UDF"), Andrew Zanevsky and Anton jiline ' s October 2001 article ("UDF performance ... or Lack of It"), or Jimmy N Ilsson ' s July 2003 article ("Another Udf:global Constants").

UDFs is an additional feature of SQL Server No. 2000, UDFs typically used by DBAs and developers to modularize code and sometimes to improve performance. In this article, we will start from scratch to understand the UDFs provided by SQL Server System, You can allow DBAs to track management.

Although the user-defined functions provided by the system may sound a bit contradictory, Microsoft still integrates a large number of internal UDFs (read-only, system-supplied). At the same time, although the UDFs feature was originally released in SQL SERVER 2000, it was provided However, we find that only in SP3, Microsoft is used for its own purposes, and all the UDFs functions provided by the system are started with ' fn_ ' and stored in the master database.

Compare system-provided and standard UDF

If you are familiar with UDFs, you may know that UDF is not able to modify the record of the fixed table, the typical application is: Read the data, modify the data of the table variable, Returns data. and UDFs can run extended stored procedures and system-supplied custom functions. In fact, there are a lot of systems that provide custom functions that simply invoke an extended stored procedure. [Extended stored procedures are usually written in C + + DLL files, you can see Paul Storer-martin's article in July 2002 and August, "Playing the ODS"], so it's better to read code that uses the same functionality as a UDF written in T-SQL? System-supplied custom functions and user-defined functions are slightly different at run time: A typical custom Function (UDFs) can be invoked like this:

SELECT column_list

From Owner_name. Udf_name (@parameter1, ... @parameterN)

The system-supplied custom function needs to be preceded by a two colon (::), and you do not have to specify the owner of the feature:

SELECT column_list

From:: fn_systemsuppliedudf

(@parameter1, ... @parameterN)

For example, the system-supplied custom Function fn_helpcollations () can return all of the character sets supported by SQL SERVER 2000, which we can do:

SELECT * FROM:: Fn_helpcollations ()

Custom functions for tracing (UDFs)

A trace-trapped T-SQL statement is sent (or run a stored procedure in) the specified SQL Server's real column and saved as a *. TRC files. SQL Server tracing can be established by profiler tools or stored procedures running the system sp_trace_create and can specify a number of filtering criteria to limit output files. In this article, we mainly provide custom functions for systems that track functionality.

Fn_trace_gettable
Fn_trace_gettable () requires two parameters: the initialization trace file name (. TRC) and the number of trace files. When you build a trace, you can configure SQL Server to limit the size of the trace file. When the trace file reaches the specified size, the SQL Server field produces a new "scrolling" trace file. The second parameter of the fn_trace_gettable () function is the number of "scrolling" trace files, which begin when you specify the first argument.

If you like to keep your tracked New year in the database, you can simply run a query and save the trace file as a datasheet by fn_trace_gettable, for example:

SELECT *

Into dbo.my_trace_table

From:: fn_trace_gettable

(' C:\TRACE_FILE.TRC ', default)

Furthermore, it is very convenient to query directly and search for strings with special meanings. In our test environment, all user-defined stored procedures start with "USP," so we can run a query that searches for records that last longer than 3000ms:

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 consistently running slowly or only at peak times.

Fn_trace_getinfo
This system provides a custom function to get a trace of high-level information or all running traces running on a SQL Server. This function has only one parameter-the number of the trace (trace ID)

In order to limit the information of a trace, you must specify a trace marker. You can also specify default or "0" as the trace designator so that you can get all the running trace information. SQL Server assigns a trace flag to each trace when it establishes a trace, and if you do not specify the trace identifier you want to query, simply run the system function with the parameter "0", and then you can limit the trace output to the content you are interested in. The output description of the fn_trace_getinfo system function is shown in table one:

Table 1. F fn_trace_getinfo output.

Column Name	Describe
Traceid	The ID of this trace . Can be used to manage traces through system stored procedures
Property	The properties of the trace, represented by the following integer: The sp_trace_create tracking option (see @optionsin the) 2–filename 3–maxsize 4–stoptime 5– Current trace status
Value	Information about the properties of the specified trace.

The tracking options can be specified through system stored procedure sp_trace_create (see table 2)

Table 2. The tracking options can be specified through system stored procedure sp_trace_create

Option name	Option value	Describe
Trace_produce_rowset	1	Trace will produce a rowset
Trace_file_rollover	2	When max_file_size is reached, the current trace file is closed and a new file is created . SQL SERVER automatically adds a sequence number to each file (1,2,3 ...)
Shutdown_on_error	4	If the trace cannot be written to a file, SQL Server shuts down.
Trace_produce_blackbox	8	If this option is selected , SQL server's last 5 MB trace information record will be saved by the server

Let's look at a real column to see how fn_trace_getinfo works. Imagine that we build a trace through 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 the tracking information.

SELECT * FROM:: fn_trace_getinfo (2)

The results of the query are in table 3.

Table 3. Fn_trace_getinfo the results of the 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 trace that automatically grows to 5MB and automatically generates another file. The trace stop time is not specified (property = 4), so the trace runs until the SQL Server service stops or passes through the system stored procedure Sp_trace_ SetStatus Stop tracing.