你们大部分人可能已经在SQL SERVER中建立自己的用户定义函数(UDF),但是你知道么?微软公司已经集成了大量自己的UDFs,特别是在最新发布的SP3中.在这篇文章中 Baya Pavliashvili和Kevin Kline系统地研究了关于SQL SERVER跟踪部分的UDF.你们中的一些人也许想阅读以前SQL Server Professional的一篇关于传统UDFs的文章,比如Andrew Zanevsky's 2000年9月的专栏 ("Granting Wishes with UDF"), Andrew Zanevsky and Anton Jiline's; 2001年10月的文章 ("UDF Performance… or Lack of It"), 或 Jimmy Nilsson's; 2003年7月的文章("Another UDF: Global Constants").
UDFs是SQL Server 2000期待已久的附加功能, UDFs典型的应用是DBAs和开发者用来模块化代码和间或用来提高性能.在这篇文章中,我们将从零开始了解SQL SERVER系统提供的UDFs,可以允许DBA进行跟踪管理.
虽然系统提供的用户定义函数听起来有一点矛盾,但微软还是集成大量的内部的UDFs(只读,系统提供).同时,尽管UDFs这个特性在SQL SERVER 2000最初发布是就提供了,不过我们发现只是在SP3中微软才因为自己的目的而大量使用,所有系统提供的UDFs函数都是以’fn_’开始并且保存在master数据库中.
比较系统提供的和标准的UDF
如果你熟悉UDFs,你也许知道UDF是不能修改固定表的记录,典型的应用是:读取数据,修改表变量的数据,返回数据.而且UDFs可以运行扩展存储过程和系统提供的自定义函数.事实上有很多的系统提供的自定义函数只是简单的调用一个扩展存储过程.[ 扩展存储过程通常是有C++写的DLL文件,你可以看Paul Storer-Martin's在2002年7月和8月的文章"Playing the ODS"],因此阅读用T-SQL写的UDF相同功能的代码不是更好么? 系统提供的自定义函数和用户的自定义函数在运行时有轻微的差别:典型的自定义函数(UDFs)可以向这样调用:
SELECT column_list
FROM owner_name.UDF_name (@parameter1, … @parameterN)
系统提供的自定义函数需要在FROM后面加二个冒号(::),同时你不必指定该功能的所有者:
SELECT column_list;
FROM; :: fn_SystemSuppliedUDF
(@parameter1, … @parameterN)
比如: 系统提供的自定义函数fn_helpcollations()可以返回SQL SERVER 2000支持的所有字符集,我们可以这样执行:
SELECT * FROM :: fn_helpcollations()
用于跟踪的自定义函数(UDFs)
一条跟踪捕获的T-SQL语句发给(或运行一个存储过程在)指定的SQL SERVER的实列并且保存为一个*.TRC的文件.SQL SERVER的跟踪可以通过Profiler工具或运行系统的存储过程sp_trace_create建立并且可以指定许多过虑的标准来限制输出文件.在这篇文章中,我们主要针对跟踪功能的系统提供的自定义函数.
fn_trace_gettable
fn_trace_gettable() 需要二个参数: 初始化的跟踪文件名(.TRC)和跟踪文件的个数.当你建立一个跟踪,你可以配置SQL SERVER限制跟踪文件的大小.当跟踪文件到达指定的大小,SQL SERVER会字段产生一个新的”滚动的”跟踪文件. fn_trace_gettable()函数的第二个参数是”滚动的”跟踪文件的个数,这是在指定第一个参数时开始的.
如果你喜欢将跟踪的新年保存在数据库中,你可以简单地运行一个查询,通过fn_trace_gettable 将跟踪文件保存为一个数据表,比如:
SELECT *
INTO dbo.my_trace_table
FROM :: fn_trace_gettable
('c:trace_file.trc', default)
而且,可以非常方便直接查询,搜索一些特殊含义的字符串.在我们的测试环境中,所有的用户定义的存储过程以”USP”开始,因此我们可以运行一个查询,搜索持续时间超过3000ms的记录:
SELECT TextData, duration
FROM ::
fn_trace_gettable('c:trace_file.trc', default)
WHERE TextData LIKE '%usp%'
AND duration > 3000
通过更加复杂的查询,我们可以精练SELECT语句来确定哪些查询一致运行地比较慢还是只在高峰期.
fn_trace_getinfo
这个系统提供的自定义函数可以得到一个跟踪的高级别信息或在一个SQL SERVER上运行的所有正在运行的跟踪.这个函数只有一个参数—跟踪的编号(TRACE ID)
为了限制一个跟踪的信息,你必须指定跟踪标志符.你也可以指定DEFAULT或”0”,作为跟踪标志符,这样可以获得所有的运行的跟踪信息.SQL SERVER在建立跟踪时给每一个跟踪分配一个跟踪标志符,如果你不指定你要查询的跟踪标志符,简单的以参数”0”运行该系统函数,然后你可以限制跟踪输出你感兴趣的内容. fn_trace_getinfo系统函数的输出描述如表一:
表 1. f fn_trace_getinfo的输出.
|
列名 |
描述 |
|
TraceID |
此跟踪的 ID.可以被用来通过系统存储过程来管理跟踪 |
|
Property |
跟踪的属性，由下列整数表示： 1 – 跟踪选项（请参见 sp_trace_create 中的 @options） |
|
Value |
有关指定跟踪的属性的信息。 |
跟踪的选项可以通过系统存储过程sp_trace_create来指定(看表2)
表 2. 跟踪的选项可以通过系统存储过程sp_trace_create来指定
|
选项名 |
选项值 |
描述 |
|
Trace_produce_rowset |
1 |
跟踪将产生一个行集 |
|
Trace_file_rollover |
2 |
当达到 max_file_size 时，将关闭当前跟踪文件并创建新文件.SQL SERVER 会自动为每个文件增加序列编号(1,2,3….) |
|
Shutdown_on_error |
4 |
如果不能将跟踪写入文件，则 SQL Server 将关闭。 |
|
Trace_produce_blackbox |
8 |
如果这个选项被选中,SQL SERVER的最后 5 MB 跟踪信息记录将由服务器保存 |
下面我们看一个实列来了解fn_trace_getinfo是如何工作的.设想我们通过下面的查询建立一个跟踪:
/* 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
现在我们可以用fn_trace_getinfo 来获得相应跟踪的信息
SELECT * FROM :: fn_trace_getinfo(2)
查询的结果在表 3.
表3. fn_trace_getinfo查询的结果.
|
traceID |
Property |
Value |
|
2 |
1 |
2 |
|
2 |
2 |
e:trace_file.trc |
|
2 |
3 |
5 |
|
2 |
4 |
NULL |
|
2 |
5 |
1 |
这个输出告诉我们,有一个正在运行的跟踪,自动增长到5MB后会自动产生另外一个文件.没有指定跟踪停止时间( property = 4 ) ,因此该跟踪会运行直到SQL SERVER服务停止或通过系统存储过程sp_trace_setstatus停止跟踪。
