MSDN Library
Servers and Enterprise Development
SQL Server
SQL Server 2000
Periodicals
Microsoft SQL Server Professional
August 2003

System-Supplied Trace UDFs

Baya Pavliashvili and Kevin Kline

Most of you have probably created your own SQL Server user-defined functions (UDFs), but did you know that Microsoft ships a fair number of its own UDFs, especially in the most recent SP3? In this article, Baya Pavliashvili and Kevin Kline explore a subset of them that are related to traces. Some of you may want to (re)read previous SQL Server Professional articles about traditional UDFs such as Andrew Zanevsky's September 2000 column ("Granting Wishes with UDF"), Andrew Zanevsky and Anton Jiline's October 2001 article ("UDF Performance… or Lack of It"), or Jimmy Nilsson's July 2003 article ("Another UDF: Global Constants").

User-defined functions (UDFs) were a long-awaited addition to SQL Server 2000. UDFs, which are typically written by DBAs and developers, can be used to modularize T-SQL code—and sometimes even improve performance. In this article, however, we're going to zero in on a particular subset, the Microsoft-supplied "system" UDFs, which allow DBAs to manage traces.

Although the notion of system-supplied UDFs may seem like an oxymoron, SQL Server does ship with a number of built-in (read, "system-supplied") UDFs. And, although UDFs were a feature of SQL Server 2000 in its initial release, it's only in Service Pack 3 (SP3) that we see Microsoft making heavy use of them for its own purposes. All of Microsoft's system-supplied UDFs begin with "fn_" and reside in the master database.

Comparing system-supplied and standard UDFs

If you're familiar with UDFs, you probably know that UDFs can't modify data in permanent tables and are typically used to read data, make changes to data in table variables, and display data to the user. UDFs can, however, execute extended stored procedures, and system-supplied UDFs function similarly. In fact, most of Microsoft's system-supplied UDFs simply execute an extended stored procedure [XP's are DLLs that are typically written in C++. See Paul Storer-Martin's features on "Playing the ODS" in the July and August 2002 issues.—Ed.], so reading T-SQL UDF code of such functions won't do you much good. One thing that differentiates system-supplied UDFs is that they're executed with a slightly different syntax. Typical UDFs can be called as follows:

SELECT column_list 
FROM owner_name.UDF_name (@parameter1, … @parameterN)

System-supplied UDFs, however, take two colons (::) after the FROM keyword, and you don't have to specify the function owner:

SELECT column_list  
FROM  :: fn_SystemSuppliedUDF 
(@parameter1, … @parameterN)

For example, the fn_helpcollations() function returns the list of all collations supported by SQL Server 2000 and can be executed as follows:

SELECT * FROM :: fn_helpcollations()

UDFs used for tracing

A trace captures T-SQL statements sent to (and stored procedures executed on) a given instance of SQL Server and saves them to a .trc file. Traces can be set up through the Profiler or by executing the system stored procedure sp_trace_create, and you can specify any number of filtering criteria to limit the output. In this article, we'll drill down on trace-specific system-supplied UDFs.
fn_trace_gettable

fn_trace_gettable() accepts two parameters: the initial trace (.trc) filename to be read, and the number of files. When you create a trace, you can advise SQL Server to limit the trace file to a particular size. When the trace file reaches this limit, SQL Server will automatically create a new "rollover" file. The second parameter of fn_trace_gettable specifies the number of rollover files to be read, starting at the file specified by the first parameter.

If you prefer to have trace information stored in a database, you could use fn_trace_gettable to save the trace file as a table by running a query similar to this:

SELECT * 
INTO dbo.my_trace_table
FROM :: fn_trace_gettable
('c:\trace_file.trc', default)

However, it's pretty easy to query the raw data directly, searching for some meaningful string. In our environment, all user-defined stored procs have the "usp" prefix, so we can run a query like this to determine their duration:

SELECT TextData, duration 
FROM :: 
  fn_trace_gettable('c:\trace_file.trc', default)
WHERE TextData LIKE '%usp%'
AND duration > 3000

Armed with a list of long queries, we might want to refine the SELECT to determine whether these queries are executing slowly each time or only during the peak user activity.

fn_trace_getinfo

This function lets you get high-level information about a particular trace or all active traces that are executing against a particular instance of SQL Server. This function accepts a single parameter—trace ID.

To limit the information about a particular trace, you must specify the trace identifier. Alternatively, you can specify DEFAULT, or "0", as the trace identifier to get information about all active traces. SQL Server assigns the trace identifier to each trace at creation; if you don't know which trace you're looking for, simply run this function specifying "0" as the parameter—then you can limit the output to the trace that interests you the most. The output of fn_trace_getinfo is described in Table 1.

Table 1. Output of fn_trace_getinfo.

Column name	Meaning
TraceID	Internal trace identifier.Can be used to manage traces through system stored procedures sp_trace_**.
Property	Trace properties, including: 1 — trace options (discussed shortly) 2 — filename 3 — max size for trace file 4 — stop time 5 — current status of the trace
Value	Value of each property returned.

Trace options can be specified through the sp_trace_create system procedure (see Table 2).

Table 2. Trace options you can use during sp_trace_create.

Option name	Value	Meaning
Trace_produce_rowset	1	Trace will produce a rowset.
Trace_file_rollover	2	Trace will create a new file when the first file will reach a maximum size specified at creation. SQL Server will append numbers to each file (1, 2, 3, and so on) in sequence.
Shutdown_on_error	4	Advises to shut down SQL Server if trace can't be written to the file.
Trace_produce_blackbox	8	If this option is chosen, SQL Server will save the last 5MB of trace output.

Let's take a look at an example to see how fn_trace_getinfo works. Suppose we create a trace using 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 this particular trace:

SELECT * FROM :: fn_trace_getinfo(2)

The results are shown in Table 3.

Table 3. Sample results of using fn_trace_getinfo.

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 we have an active trace that can grow up to 5MB before rolling over to another file. There's no stop time (property = 4) specified for this trace, so it will run until SQL Server is stopped or the trace is stopped using a sp_trace_setstatus system procedure.

fn_trace_getfilterinfo and fn_trace_geteventinfo

These functions let you retrieve the metadata of a particular trace. (The output of these functions is rather cryptic if you aren't familiar with the identifiers of events that you can trace and the filter column identifiers; see the Books Online topic "sp_trace_setevent.")

Both of these functions take trace identifier as their only parameter. fn_trace_getfilterinfo returns the filter(s) applied to the specified trace. For instance, suppose we limit our trace to the pubs database, because we're trying to troubleshoot a long-running query in that database. We can execute the function as follows:

SELECT * FROM :: fn_trace_getfilterinfo(1) 

--Results: 
columnID   logical_operator comparison_operator value    
---------- ---------------- ------------------- ---------
35                0         6                   pubs

This output tells us that the trace we specified has a filter defined on column 35 (database name) and that the logical operator ("AND" or "OR") isn't used (0) since there's only one condition. The comparison operator is "LIKE" (= 6), and the value of filter is "pubs".

Depending on the type of filter you apply, your trace comparison operator can vary. In most cases, you'll use LIKE or NOT LIKE. However, if you're limiting the trace to a particular process or to a particular database ID, then you can specify "=", ">", "< >", "<", "> =", or "< =".

Note: If you set up a trace using SQL Server Profiler, the trace will automatically add a filter excluding the statements sent to SQL Server by the Profiler itself. If you'd like to spy on the Profiler, simply turn this filter off.

fn_tracegeteventinfo provides information about the events collected by a particular trace. There are numerous events you can specify within the trace. The more events you trace, the more information you'll have to plow through to digest, so be careful. We recommend learning the meaning of each event and only choosing those that matter most for your particular troubleshooting effort. fn_tracegeteventinfo is particularly useful when you're simultaneously running multiple traces for different purposes.

To get event identifiers included in our trace, we can execute fn_tracegeteventinfo as follows:

SELECT DISTINCT eventid 
FROM :: fn_trace_geteventinfo(1)

--Results: 

eventid     
----------- 
12
37
40
41
42
43

This tells us that we're examining the following events:

SQL: BatchCompleted—event id of 12
SP: Recompile—event id of 37
SQL: StatementStarting—event id of 40
SQL: StatementCompleted—event id of 41
SP: Starting—event id of 42
SP: Completed—event id of 43

Similarly, we could execute the same function with a slight change to get all the data columns that the trace is collecting with this statement:

SELECT DISTINCT columnid FROM :: fn_trace_geteventinfo(1)

--Results: 

columnid    
----------- 
1
10
11
12
13
14
16
17
18

Here, we're collecting typical data useful for tuning: text data, application name, SQL Server login, SPID, duration, start time and end time, reads, writes, and CPU utilized for query.

Using system-supplied UDFs

Now that you know a bit about system-supplied UDFs, you can use them in your own UDFs. One of the major limitations of UDFs is that they can't call stored procedures. UDFs can, however, call other UDFs. The following UDF utilizes fn_trace_geteventinfo to make the results more readable:

CREATE FUNCTION dbo.fn_GetTraceColumns (@trace_id INT)
RETURNS @TraceColumns TABLE (
  column_id INT, 
  column_name VARCHAR(155)
)
AS
BEGIN
INSERT @TraceColumns (
column_id)
SELECT DISTINCT columnid FROM :: 
fn_trace_geteventinfo(@trace_id)
UPDATE @TraceColumns 
SET column_name = 
  CASE column_id 
  WHEN 1 THEN 'TextData' 
  WHEN 3 THEN 'DatabaseID'  
  WHEN 4 THEN 'TransactionID' 
  WHEN 6 THEN 'NTUserName' 
  -- similar statements omitted here - see Source 
  ELSE 'other'
  END
RETURN
END

This function can be executed as follows:

SELECT * FROM dbo.fn_getTraceColumns(3)

Summary

In this article, we've introduced you to some powerful system-supplied UDFs available in SQL Server 2000. We hope you enjoyed it and will explore the other system-supplied UDFs.

Download TRACEUDFS.SQL

Sidebar: Additional Resources

284790 INF: How to Create a SQL Server 2000 Trace
283786 INF: How to Monitor SQL Server 2000 Traces
270599 BUG: fn_trace_gettable Function Cannot Read Rollover Files 273972 Generated by SQL
ProfilerHOW TO: Programmatically Load Trace Files into Tables
268591 PRB: ODBC Tracing to SQL.LOG Can Slow SQL Server or Consume All Disk Space
307786 INF: Tracing to Network Drive May Reduce SQL Server Throughput
286239 BUG: Replay Tool Uses LoginName Column for SETUSER Instead of DatabaseUserName Column
Andrew Novick's "Find Out What They're Doing with fn_get_sql"—www.databasejournal.com/features/mssql/article.php/2189761

—kw

To find out more about SQL Server Professionaland Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the August 2003 issue of Microsoft SQL Server Professional. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professionalis an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.