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

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

比較系統(tǒng)提供的和標(biāo)準(zhǔn)的UDF

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

SELECT column_list

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

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

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ā)給(或運(yùn)行一個(gè)存儲(chǔ)過程在)指定的SQL SERVER的實(shí)列并且保存為一個(gè)*.TRC的文件.SQL SERVER的跟蹤可以通過Profiler工具或運(yùn)行系統(tǒng)的存儲(chǔ)過程sp_trace_create建立并且可以指定許多過慮的標(biāo)準(zhǔn)來限制輸出文件.在這篇文章中,我們主要針對(duì)跟蹤功能的系統(tǒng)提供的自定義函數(shù).

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

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

SELECT *

INTO dbo.my_trace_table

FROM :: fn_trace_gettable

('c:trace_file.trc', default)

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

SELECT TextData, duration

FROM ::

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

WHERE TextData LIKE '%usp%'

AND duration > 3000

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

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

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

表 1. f fn_trace_getinfo的輸出.

列名

描述

TraceID

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

Property

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

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

Value

有關(guān)指定跟蹤的屬性的信息。

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

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

選項(xiàng)名

選項(xiàng)值

描述

Trace_produce_rowset

1

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

Trace_file_rollover

2

當(dāng)達(dá)到 max_file_size 時(shí)，將關(guān)閉當(dāng)前跟蹤文件并創(chuàng)建新文件.SQL SERVER 會(huì)自動(dòng)為每個(gè)文件增加序列編號(hào)(1,2,3….)

Shutdown_on_error

4

如果不能將跟蹤寫入文件，則 SQL Server 將關(guān)閉。

Trace_produce_blackbox

8

如果這個(gè)選項(xiàng)被選中,SQL SERVER的最后 5 MB 跟蹤信息記錄將由服務(wù)器保存

下面我們看一個(gè)實(shí)列來了解fn_trace_getinfo是如何工作的.設(shè)想我們通過下面的查詢建立一個(gè)跟蹤:

/* 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 來獲得相應(yīng)跟蹤的信息

SELECT * FROM :: fn_trace_getinfo(2)

查詢的結(jié)果在表 3.

表3. fn_trace_getinfo查詢的結(jié)果.

traceID

Property

Value

2

1

2

2

2

e:trace_file.trc

2

3

5

2

4

NULL

2

5

1

這個(gè)輸出告訴我們,有一個(gè)正在運(yùn)行的跟蹤,自動(dòng)增長(zhǎng)到5MB后會(huì)自動(dòng)產(chǎn)生另外一個(gè)文件.沒有指定跟蹤停止時(shí)間( property = 4 ) ,因此該跟蹤會(huì)運(yùn)行直到SQL SERVER服務(wù)停止或通過系統(tǒng)存儲(chǔ)過程sp_trace_setstatus停止跟蹤。