Consolidating Data Capture with SQLdiag - SQL Server 2012 Internals and Troubleshooting - page 310

Adding Trace Filters to a SQLdiag Confi guration
Normally, Proi ler trace i lters are added to a SQL Server data collection to reduce the amount of
diagnostic data that is collected by SQLdiag. For example, you could add a i lter on SPID to collect
only trace events which pertain to a particular Session ID. Note that the text i lters add CPU
overhead to the data collection as there is a signii cant CPU overhead for evaluating text-based
i lters as opposed to integer i lters. However, if you collect a Proi ler trace using a SQLdiag
coni guration XML i le, then you need to follow these steps:

1.

Start SQLdiag on the server.The options to start SQLdiag can be found in the “Using
SQLdiag as a Command-line Application” section of this chapter.

2.

Find out the Trace ID of the Proi ler trace running using fn_trace_getinfo function or
sys.traces view.

3.

Stop the trace using sp_trace_setstatus without deleting the dei nition.

4.

Use the Trace ID obtained from step 2, and use the sp_trace_setfilter stored procedure
to set the i lter. Refer to “SQL Proi ler Data Columns” in SQL Server Books Online for the
Data Column numbers.

5. To verify that the i lter is active, use the fn_trace_filterinfo function.

6. When you are satisi ed that the i lter is active, start the trace data collection using

sp_trace_setstatus .

The following T-SQL Commands can be used toSet a Filter for SPID = 52 for TraceID = 2 once the
SQLdiag has been initialized:

select * from sys.traces — To get the trace id
EXEC sp_trace_setstatus 2,0 — Stops the trace but doesn't delete the trace definition
from the server
EXEC sp_trace_setfilter 2, 12, 0, 0, 52 — Add a filter for SPID = 52
EXEC sp_trace_setstatus 2,1 — Start the trace again
select * from fn_trace_getfilterinfo(2) — Get information about the filters set for the
trace

Understanding the Custom Diagnostics in SQLdiag
This section looks at the different categories of custom diagnostics available out-of-the-box in the
Diag Manager. The extensibility of SQLdiag using Diag Manager is a very compelling reason for
all SQL Server professionals to add this tool to their repertoire. The T-SQL, VBScript and DOS
commands used by the custom diagnostics coni gurations are available in the C:\Program Files\
Microsoft\Pssdiag\CustomDiagnostics folder.

NOTE If you have installed Diag Manager on a 64-bit machine, then instead of
Programs Files you will be looking for the Program Files (x86) folder.