SQL SERVER 2000 系统支持的跟踪函数
你们大部分人可能已经在 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 ）
2 – FileName
3 – MaxSize
4 – StopTime
5 – 当前跟踪状态
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 来获得相应跟踪的信息
<p class="MsoNormal" style="MARGIN: 0cm 0cm 7.8pt; TEXT-INDENT: 21pt; mso-para-margin-bo