Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Profiler-Specific Functions

The other set of functions classified as security functions are specific to Profiler traces. If you're not familiar with a SQL Server Profiler and you're aspiring to be a DBA, you should definitely learn how to use this tool. In simple terms the Profiler helps you monitor the activity on your server by capturing SQL statements as they are executed. In addition, the Profiler can be used to capture logon events, monitor performance, and more. You can use the Profiler to create traces; alternatively, you can use system procedures and extended procedures to do the same.

SQL Server 2000 provides numerous functions that start with fn_. These are system-supplied UDFs, which can be used for troubleshooting or monitoring your system. Don't bother trying to read the code of these functions—SQL Server won't let you. Security functions specific to Profiler are such system-supplied UDFs.

Notice that system-supplied UDFs are executed slightly differently from typical UDFs and system functions. You must use a colon (:) prior to specifying the name of the system-supplied UDF, as in the following:

SELECT * FROM :: fn_system_function

Perhaps the most useful Profiler function for beginners is fn_trace_gettable. If you have a trace file saved on a hard drive, you can bring the results into the Query Analyzer using this function. For example, the following query returns a few columns from a trace file e:\my_trace.trc:

SELECT TextData, NTUserName, ClientProcessID, ApplicationName
FROM ::fn_trace_gettable('e:\my_race.trc', default)

Results (abbreviated):

TextData

NTUserName

ClientProcessID

ApplicationName

SET NOEXEC OFF SET PARSEONLY OFF SET ROWCOUNT 0

Administrator

1720

SQL Query Analyzer

DBCC USEROPTIONS

Administrator

1720

SQL Query Analyzer

master.dbo.sp_Mshasdbaccess

Administrator

1720

SQL Query Analyzer

set showplan_text off

Administrator

1720

SQL Query Analyzer

SET NOEXEC OFF SET PARSEONLY OFF

Administrator

1720

SQL Query Analyzer

set showplan_all off

Administrator

1720

SQL Query Analyzer

use [master]

Administrator

1720

SQL Query Analyzer

SELECT COUNT(*) FROM sysperfinfo

Administrator

1720

SQL Query Analyzer

SELECT * FROM sysperfinfo

Administrator

1720

SQL Query Analyzer

SELECT * FROM sysperfinfo

Administrator

1720

SQL Query Analyzer

SELECT name FROM sysobjects WHERE type = 'u' ORDER BY 1

Administrator

1720

SQL Query Analyzer

SELECT COUNT(*) FROM spt_monitor

Administrator

1720

SQL Query Analyzer


If there is enough interest, the rest of the Profiler-related functions will be discussed in a future article.

  • + Share This
  • 🔖 Save To Your Account