Easing in the trace FILTER for SQLDIAG

Posted on by

Now that the Diag Manager is available online and you can use configured custom PSSDIAG/SQLDIAG configuration files to collect data from SQL Server instances, you might want to configure your data collection packages in such a manner that profiler traces are setup correctly with trace filters if needed.

Note: Trace filtering can dramatically reduce trace (.TRC file size) and the I/O cost of tracing, but you should be aware that it can actually increase the CPU burden of tracing. To minimize the extra CPU use, filtering should be performed on an integer column (dbid, duration, etc.) instead of a text column (database name, textdata, etc) whenever possible. If a filter doesn’t remove a significant portion of the trace events (example >10%), it probably isn’t worth it, and might actually introduce more overhead than it prevents. While configuring PSSDIAG/SQLDIAG for SQL Server, you cannot add Profiler Trace Filters. Even if you do so from the GUI, it would not be included in the configuration file so that the filters are honored when  the server side trace starts up.

imageTo set filters for profiler traces collected with PSSDIAG/SQLDIAG, you need to:

1. Initialize PSSDIAG/SQLDIAG on the server. By this I mean start the PSSDIAG/SQLDIAG.
2. Find out the Trace ID of the profiler trace running using fn_trace_getinfo function or sys.traces view.
3. Use the Trace ID obtained from the above step, and use the sp_trace_setfilter stored procedure to set the filter. Refer "SQL Profiler Data Columns" under SQL Server Books Online for the Data Column numbers and "sp_trace_setfilter" topic for finding out the values of the logical and comparison operators.
4. To verify that the filter is active, use the fn_trace_filterinfo function. 

I shall demonstrate in this blog post how this works. As you can see in the see in the screenshot that the TraceID 2 is what I want to customize. Now that I have trace id, I will stop the profiler trace using sp_trace_setstatus stored procedure. Status value 0 will stop the trace.

Once I stop the trace, I use the sp_trace_setfilter function with the appropriate Data Column ID for setting a filter on SPID 52 to set a filter on SPID 52. Then I start the trace again. When I look into the profiler trace, I find that before the trace was stopped it was collecting data for all SPIDs but after setting the filter data is being collected ONLY for SPID 52 (see highlighted sections screenshot below).

imageOnce the filter is set. You can use the fn_trace_getfilterinfo function to verify that the trace filter is active.

If you are not using SQLDIAG to collect profiler traces, then it is as simple as configuring a server side trace. The only additional thing that you need to in this case is add the column filters while configuring the profiler trace. The above exercise is required only when you are collecting traces using SQLDIAG/PSSDIAG.

Reference:
How to: Filter Events in a Trace (SQL Server Profiler)
http://msdn.microsoft.com/en-us/library/ms175520.aspx

/*T-SQL commands used*/

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

image

Hope the above information helps in setting up trace filter for a profiler trace collected using SQLDIAG.

11 thoughts on “Easing in the trace FILTER for SQLDIAG”

  1. Thanks Amit for another blog posting. Your are doing an Amazing job by bringing various troubleshooting techniques and ways to resolve it.Could you also post more on various performance issues (for example memory botlnecks and how to see memorystatus output) with couple of examples.and it will be helpful for the dba community.Memory bottlnecks i feel is always a grey area in sql server and explaning it with examples will be helpful

    -Kevin

    Like

  3. The best way is to filter trace manually after collecting then save it in another trc file. Because there are time between you run pssdiag and stop the trace, you can get lots of garbage. I was using it for Nexus, don’t forget that it pull all the trace files if output catalog, so to have a clear result delete or move old trc file.

    Like

  4. Sometimes this might not be possible if a lot of events are generated within a very short span of time. What you suggest Oleg is definitely an option for servers where filtering is feasible after capturing the data. I use this approach in most environments.

    Like

  5. Why do I get wrong parameter values being used in the performance Overview report when I apply filters after kicking off pssdiag?

    Like

  7. I start PSSDiag at 3/3/2016 09:05:51. I quickly execute a batch of T-sql statements to add filters to the trace (having determined that the trace ID to be 2):
    declare @duration bigint
    set @duration = 5000000
    EXEC sp_trace_setstatus 2,0 — pause trace
    EXEC sp_trace_setfilter 2,13,0,4,@duration — duartion > 5 secs
    EXEC sp_trace_setfilter 2,34,0,7,N’ap_log%’ –exclude objectname
    EXEC sp_trace_setfilter 2,35,0,6,N’Card360_BIZ_Perf%’ — databasename starting with
    EXEC sp_trace_setfilter 2,64,0,6,N’card360_imports’ — capture for only sessionlogname
    EXEC sp_trace_setstatus 2,1 — resume trace

    After 10 minutes I stop PSSDiag. I query the trace file and establish that:
    Min(StartTime)= 3/3/2016 09:56:37
    Max(EndTime) = 3/3/2016 10:08:51

    I fireup SQL Nexus and load the Output file into database SQLNexus (dropping the old database with same name). I click on “ReadTrace Reports” which takes me to report ”Perfromance Overview”

    Parameter Value
    Start Time 3/3/2016 9:57:06 AM
    End Time 3/3/2016 9:57:16 AM
    With a warning Icon

    The warning message in “One of more warning conditions exist that may affect the quality of the analysis data. Review tblWarnings and the ReadTrace log for details.” Which is the only entry in tblWarnings

    I have run this experiment several times getting the same result. I have problem when I apply the filters and no problems when I apply the filters..

    QUESTION:
    1. Why does the dration from start time to End time only a few Seconds?
    2. Why does the Parameters link not work in spite of it being enabled??

    Like

  8. I now suspect that the report does not function the way I expected it to because it is only valid for 2005,2008,2008R2 and NOT 2012 – I am using SQL 2012 – it should not be active for 2012.

    Like

  10. What version of SQL Nexus are you using? SQL Nexus 4.0 has SQL Perf Main which is the home page for the reports. You might be using an older version of the tool.

    Like

Comments are closed.