Tracking functions supported by SQL Server 2000 system (2)
Baya Pavliashvili and Kevin Kline
Http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentId=28000409
These functions can be used to retrieve a tracking metadata (the meaning of these functions is blurred if you are not familiar with the logo of the column of the column of the tracking event, you can check out the online SQL Server Help "sp_trace_setevent")
These two functions are the only parameters of the tracking flag. FN_Trace_GetFilterInfo returns the information after the specified tracking. Example: Suppose we limit with the Pubs database, because we try to resolve a failure of a long query in this database We can run the function as follows:
Select * from :: Fn_Trace_GetfilterInfo (1)
--Results:
Columnid Logical_operator Comparison_operator value
---------- ------------------------------------- ----
35 0 6 PUBS
This output tells us that our specified tracking is listed as 35 (Database Name) and no logical operator ("and" or ") because there is only one condition. Compare operators are" Liee "(= 6) The filtering value is "PUBS".
Depending on your application, you can change the comparison operator. In many cases, you can use Like or Not Like. And if you limit the specified processed or specified database, then you can specify "=", " > "," <> "," <","> = ", or" <= "and other logic operators.
Remarks: If you create a track by the SQL Server Profiler tool, this track will automatically add a filter, which does not include tracked filtering statements that are generated by PROFILER ourselves. If you like to know the Profiler tool, just make filter settings For OFF.
FN_TraceGetEventInfo can return a tracking event message, there are many tracking events you can specify. The more information you specify, there will be more information to be organized, so you have to carefully. I advise you to understand each event's meaning and Select those events closely related to you. Fn_TraceGetEventInfo is a very useful function that simulates multiple tracking because of different purposes.
To get the flag of the tracking event we build, we can run the Fn_TraceGetEventInfo function as follows:
Select Distinct EventID
From :: fn_trace_geteventinfo (1)
--Results:
EventID
-----------
12
37
40
41
42
43
This result tells us that this track is to detect the following events:
· 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, we can run the same function, you can get a tracking all data columns.
Select Distinct Columnid from :: Fn_Trace_geteventInfo (1)
--Results:
Columnid
-----------
1
10
11
12
13
14
16
In one
18
Here we collect typical data to perform performance adjustments, including a query: body data, program name, login name, SPID, duration, start time, read, write, and CPU occupation.
UDFS provided using the system
Now you specify the UDFS provided by some systems, you can build your own custom functions through these system functions. One of the main restrictions is that the custom function cannot call the stored procedure. But the custom function can call other custom functions. Below UDF initialization fn_trace_geteventinfo function, make the result more convenient to read:
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 Ten 'DatabaseId'
When 4 Ten 'TransactionID'
When 6 Ten '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)
summary
In this article, I introduced very useful system-defined functions provided by SQL Server 2000, I hope everyone can like and study other custom functions provided by other systems.
Download: Traceudfs.sql
Reference resources:
· 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
