你們大部分人可能已經(jīng)在SQL SERVER中建立自己的用戶定義函數(shù)(UDF),但是你知道么?微軟公司已經(jīng)集成了大量自己的UDFs,特別是在最新發(fā)布的SP3中.在這篇文章中 Baya Pavliashvili和Kevin Kline系統(tǒng)地研究了關于SQL SERVER跟蹤部分的UDF.你們中的一些人也許想閱讀以前SQL Server Professional的一篇關于傳統(tǒng)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和開發(fā)者用來模塊化代碼和間或用來提高性能.在這篇文章中,我們將從零開始了解SQL SERVER系統(tǒng)提供的UDFs,可以允許DBA進行跟蹤管理.

雖然系統(tǒng)提供的用戶定義函數(shù)聽起來有一點矛盾,但微軟還是集成大量的內(nèi)部的UDFs(只讀,系統(tǒng)提供).同時,盡管UDFs這個特性在SQL SERVER 2000最初發(fā)布是就提供了,不過我們發(fā)現(xiàn)只是在SP3中微軟才因為自己的目的而大量使用,所有系統(tǒng)提供的UDFs函數(shù)都是以’fn_’開始并且保存在master數(shù)據(jù)庫中.

比較系統(tǒng)提供的和標準的UDF

如果你熟悉UDFs,你也許知道UDF是不能修改固定表的記錄,典型的應用是:讀取數(shù)據(jù),修改表變量的數(shù)據(jù),返回數(shù)據(jù).而且UDFs可以運行擴展存儲過程和系統(tǒng)提供的自定義函數(shù).事實上有很多的系統(tǒng)提供的自定義函數(shù)只是簡單的調(diào)用一個擴展存儲過程.[ 擴展存儲過程通常是有C++寫的DLL文件,你可以看Paul Storer-Martin's在2002年7月和8月的文章'Playing the ODS'],因此閱讀用T-SQL寫的UDF相同功能的代碼不是更好么? 系統(tǒng)提供的自定義函數(shù)和用戶的自定義函數(shù)在運行時有輕微的差別:典型的自定義函數(shù)(UDFs)可以向這樣調(diào)用:

SELECT column_list

FROM owner_name.UDF_name (@parameter1, … @parameterN)

系統(tǒng)提供的自定義函數(shù)需要在FROM后面加二個冒號(::),同時你不必指定該功能的所有者:

SELECT column_list;

FROM; :: fn_SystemSuppliedUDF

(@parameter1, … @parameterN)

比如: 系統(tǒng)提供的自定義函數(shù)fn_helpcollations()可以返回SQL SERVER 2000支持的所有字符集,我們可以這樣執(zhí)行:

SELECT * FROM :: fn_helpcollations()

用于跟蹤的自定義函數(shù)(UDFs)

一條跟蹤捕獲的T-SQL語句發(fā)給(或運行一個存儲過程在)指定的SQL SERVER的實列并且保存為一個*.TRC的文件.SQL SERVER的跟蹤可以通過Profiler工具或運行系統(tǒng)的存儲過程sp_trace_create建立并且可以指定許多過慮的標準來限制輸出文件.在這篇文章中,我們主要針對跟蹤功能的系統(tǒng)提供的自定義函數(shù).

fn_trace_gettablefn_trace_gettable() 需要二個參數(shù): 初始化的跟蹤文件名(.TRC)和跟蹤文件的個數(shù).當你建立一個跟蹤,你可以配置SQL SERVER限制跟蹤文件的大小.當跟蹤文件到達指定的大小,SQL SERVER會字段產(chǎn)生一個新的”滾動的”跟蹤文件. fn_trace_gettable()函數(shù)的第二個參數(shù)是”滾動的”跟蹤文件的個數(shù),這是在指定第一個參數(shù)時開始的.

如果你喜歡將跟蹤的新年保存在數(shù)據(jù)庫中,你可以簡單地運行一個查詢,通過fn_trace_gettable 將跟蹤文件保存為一個數(shù)據(jù)表,比如:

SELECT *

INTO dbo.my_trace_table

FROM :: fn_trace_gettable

('c:trace_file.trc', default)

而且,可以非常方便直接查詢,搜索一些特殊含義的字符串.在我們的測試環(huán)境中,所有的用戶定義的存儲過程以”USP”開始,因此我們可以運行一個查詢,搜索持續(xù)時間超過3000ms的記錄:

SELECT TextData, duration

FROM ::

fn_trace_gettable('c:trace_file.trc', default)

WHERE TextData LIKE '%usp%'

AND duration > 3000

通過更加復雜的查詢,我們可以精練SELECT語句來確定哪些查詢一致運行地比較慢還是只在高峰期.

fn_trace_getinfo這個系統(tǒng)提供的自定義函數(shù)可以得到一個跟蹤的高級別信息或在一個SQL SERVER上運行的所有正在運行的跟蹤.這個函數(shù)只有一個參數(shù)—跟蹤的編號(TRACE ID)

為了限制一個跟蹤的信息,你必須指定跟蹤標志符.你也可以指定DEFAULT或”0”,作為跟蹤標志符,這樣可以獲得所有的運行的跟蹤信息.SQL SERVER在建立跟蹤時給每一個跟蹤分配一個跟蹤標志符,如果你不指定你要查詢的跟蹤標志符,簡單的以參數(shù)”0”運行該系統(tǒng)函數(shù),然后你可以限制跟蹤輸出你感興趣的內(nèi)容. fn_trace_getinfo系統(tǒng)函數(shù)的輸出描述如表一:

表 1. f fn_trace_getinfo的輸出.

列名

描述

TraceID

此跟蹤的 ID.可以被用來通過系統(tǒng)存儲過程來管理跟蹤

Property

跟蹤的屬性，由下列整數(shù)表示：

1 – 跟蹤選項（請參見 sp_trace_create 中的 @options）2 – FileName3 – MaxSize4 – StopTime5 – 當前跟蹤狀態(tài)

Value

有關指定跟蹤的屬性的信息。

跟蹤的選項可以通過系統(tǒng)存儲過程sp_trace_create來指定(看表2)

表 2. 跟蹤的選項可以通過系統(tǒng)存儲過程sp_trace_create來指定

選項名

選項值

描述

Trace_produce_rowset

1

跟蹤將產(chǎn)生一個行集

Trace_file_rollover

2

當達到 max_file_size 時，將關閉當前跟蹤文件并創(chuàng)建新文件.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

現(xiàn)在我們可以用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后會自動產(chǎn)生另外一個文件.沒有指定跟蹤停止時間( property = 4 ) ,因此該跟蹤會運行直到SQL SERVER服務停止或通過系統(tǒng)存儲過程sp_trace_setstatus停止跟蹤。