大部分人可能已經(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)提供).同時,盡管UDFs這個特性在SQL SERVER 2000最初發(fā)布是就提供了,不過我們發(fā)現(xiàn)只是在SP3中微軟才因?yàn)樽约旱哪康亩�大量使�?所有系統(tǒng)提供的UDFs函數(shù)都是以’fn_’開始并且保存在master數(shù)據(jù)庫中.

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

如果你熟悉UDFs,你也許知道UDF是不能修改固定表的記錄,典型的應(yīng)用是:讀取數(shù)據(jù),修改表變量的數(shù)據(jù),返回?cái)?shù)據(jù).而且UDFs可以運(yùn)行擴(kuò)展存儲過程和系統(tǒng)提供的自定義函數(shù).事實(shí)上有很多的系統(tǒng)提供的自定義函數(shù)只是簡單的調(diào)用一個擴(kuò)展存儲過程.[ 擴(kuò)展存儲過程通常是有C++寫的DLL文件,你可以看Paul Storer-Martin's在2002年7月和8月的文章"Playing the ODS"],因此閱讀用T-SQL寫的UDF相同功能的代碼不是更好么? 系統(tǒng)提供的自定義函數(shù)和用戶的自定義函數(shù)在運(yùn)行時有輕微的差別:典型的自定義函數(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ā)給(或運(yùn)行一個存儲過程在)指定的SQL SERVER的實(shí)列并且保存為一個*.TRC的文件.SQL SERVER的跟蹤可以通過Profiler工具或運(yùn)行系統(tǒng)的存儲過程sp_trace_create建立并且可以指定許多過慮的標(biāo)準(zhǔn)來限制輸出文件.在這篇文章中,我們主要針對跟蹤功能的系統(tǒng)提供的自定義函數(shù).

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

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

SELECT *

INTO dbo.my_trace_table

FROM :: fn_trace_gettable

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

而且,可以非常方便直接查詢,搜索一些特殊含義的字符串.在我們的測試環(huán)境中,所有的用戶定義的存儲過程以”USP”開始,因此我們可以運(yùn)行一個查詢,搜索持續(xù)時間超過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
這個系統(tǒng)提供的自定義函數(shù)可以得到一個跟蹤的高級別信息或在一個SQL SERVER上運(yùn)行的所有正在運(yùn)行的跟蹤.這個函數(shù)只有一個參數(shù)—跟蹤的編號(TRACE ID)

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

表 1. f fn_trace_getinfo的輸出.

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

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

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

/* 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é)果.

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

關(guān)鍵字：SQL SERVER、數(shù)據(jù)庫、跟蹤函數(shù)