SQL SERVER 2000系统支持的跟踪函数Baya Pavliashvili and Kevin Kline
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409作者： leimin (黄山光明顶) 你们大部分人可能已经在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 来获得相应跟踪的信息
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停止跟踪.
http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409作者： leimin (黄山光明顶) 你们大部分人可能已经在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 来获得相应跟踪的信息
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停止跟踪.
解决方案 »
- 同一个数据库备份文件恢复为两个数据库时出现的问题？
- SQL2000数据库权限问题
- Unsupported method: ResultSet.first问题,我已设Statement stmt
- 如何比较两个SQL查询语句查询出来的结果集，并把有差异的记录列出来。这个SQL语句如何写
- SQL server 2000网络访问的问题
- 求一个SQL语句,大家进来看看,谢谢大家
- 请各位大侠指点指点，谢谢!
- 怎样在同一表内更新自身记录?
- SQL数据库执行的原理与过程是什么？
- 能否通过odbc或其他途径得到不同数据库里的所有的表名，数据库的类型可能未知！
- 如何将一个表中的数据自动备份到另一个表中
- 请问怎么样把EXCEL表格导入SQL数据库指定的表中啊？
fn_trace_getfilterinfo 和 fn_trace_geteventinfo
这些函数可以用来检索一个跟踪的元数据(这些函数的输出的含义是模糊的,如果你不熟悉跟踪的事件标志符和过虑的列的标志符.你可以看看SQL SERVER的在线帮助 “sp_trace_setevent”)
这2个函数都是将跟踪标志符作为唯一的参数. fn_trace_getfilterinfo返回指定跟踪的过虑后的信息.举例:假设我们限制跟PUBS数据库,因为我们试图解决该数据库中一个运行很长时间的查询的故障,我们可以如下运行该函数:
SELECT * FROM :: fn_trace_getfilterinfo(1)
--Results:
columnID logical_operator comparison_operator value
---------- ---------------- ------------------- ---------
35 0 6 pubs
这个输出告诉我们,我们指定的跟踪过虑是在列为35(数据库名)和没有使用逻辑操作符("AND" or "OR") 因为只有一个条件.比较操作符是”LIEE”(=6)过滤的值是”PUBS”.
根据你应用过虑的类型,你可以改变比较操作符.在许多案例中,你可以使用 LIKE或 NOT LIKE.而且,如果你限制跟踪指定的处理或指定的数据库,那么你可以指定"=", ">", "< >", "<", "> =", or "< ="等逻辑操作符.
备注:如果你通过SQL SERVER Profiler工具建立一个跟踪,这个跟踪会自动增加一个过滤器,不包括通过Profiler自己产生给SQL SERVER的跟踪过滤语句.如果你喜欢了解Profiler工具,只要简单的将过滤器设置为off.
fn_tracegeteventinfo 可以返回一个跟踪的事件信息,有很多的跟踪事件你可以指定.你指定的事件越多,就会有更多的信息被整理,因此要仔细.我劝告大家要了解每一个事件的含义并且选择那些和你解决问题密切相关的事件. fn_tracegeteventinfo 是一个非常有用的函数,当你因为不同的目的要模拟运行多个跟踪.
要获得我们建立的跟踪事件的标志符,我们可以如下运行fn_tracegeteventinfo函数:
SELECT DISTINCT eventid
FROM :: fn_trace_geteventinfo(1)
--Results:
eventid
-----------
12
37
40
41
42
43
这个结果告诉我们,这个跟踪在检测以下事件:
• 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
同样的,我们可以运行相同的函数,进行很小的改动就可以获得一个跟踪的所有数据列
SELECT DISTINCT columnid FROM :: fn_trace_geteventinfo(1)
--Results:
columnid
-----------
1
10
11
12
13
14
16
17
18
这里我们收集的典型数据用来性能调整,包括一个查询的:正文数据,程序名,登陆名,SPID,持续时间,开始结束时间,读取,写入和CPU占用.
使用系统提供的UDFs
现在你指定一些系统提供的UDFs,你可以通过这些系统函数建立自己的自定义函数.其中有一个主要的限制是自定义函数无法调用存储过程.但是自定义函数可以调用其他的自定义函数.下面的UDF初始化fn_trace_geteventinfo函数,使结果更方便阅读:
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)
概要
在这篇文章中,我介绍了SQL SERVER 2000提供的非常有用的系统自定义函数,希望大家喜欢并且研究其他的系统提供的自定义函数.
下载: TRACEUDFS.SQL
参考资源:
• 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.