你們大部分人可能已經在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_gettablefn_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 – FileName3 – MaxSize4 – StopTime5 – 當前跟蹤狀態

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停止跟蹤。