CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Filtering Trace Data Without Loading In Profiler or Database Table

Filtering Trace Data Without Loading In Profiler or Database Table

  • Comments 1

We are often asked how to extend the filtering of RML (ReadTrace/OStress) or Profiler.  Filtering during trace production is the preferred method because it typically reduces the overall impact of the Trace on the SQL Server throughput.  

For late filtering you can generally use the Profiler filtering or database table (fn_trace_gettable and save to .TRC using Profiler).  However, the SMO object model provides TraceFile implementations that make this very easy.  The TraceFile object do not require the entire trace to be loaded in order to process the records so in comparison they can be memory friendly.

I have included the C# (x86) sample below that uses the Management.Trace classes (Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.ConnectionInfoExtended) to read an trace and filter events using the WriteNotifyHandler.

Note: Be sure to build for the x86 platform as the SMO objects are x86 based.
==========================================================================
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;


namespace FilterTrace
{
    class Program
    {
      
        //      Shows how we can change event before we right it
        //      This can be helpful for generating scenarios as
        //      we apply our own filter
        static void WriteNotifyHandler(object sender, TraceEventArgs args)
        {
            string textData;
            string eventClass;
            bool bSkip = false;

            eventClass = args.CurrentRecord.GetString(args.CurrentRecord.GetOrdinal("EventClass"));

            //      We only want to skip those we DONT'T want included
            //      Leave the rest for replay
            if (true == eventClass.StartsWith("RPC:", StringComparison.OrdinalIgnoreCase)
                || true == eventClass.StartsWith("SQL:Batch", StringComparison.OrdinalIgnoreCase))
            {
                //      For an RPC the textData is built out from the BINARY column so you don't
                //      need to collect the text at trace time 
                textData = args.CurrentRecord.GetString(args.CurrentRecord.GetOrdinal("TextData"));
                if (null != textData && textData.Length > 0)
                {
                    if (true == textData.Contains("sp_executesql"))
                    {

                    }
                    else
                    {
                        //      This shows how to change a record
                        //      Set skip to true and the write will not occur
                        args.CurrentRecord.SetValue(args.CurrentRecord.GetOrdinal("TextData"), "FILTERED");
                        //bSkip = true;        
                    }
                }
            }

            args.SkipRecord = bSkip;
        }

        static void Main(string[] args)
        {
            long loop = -1;

            //      Initialze the readers
            //      Error handles left out for clarity in this sample
            Console.WriteLine("Opening source");
            TraceFile source = new TraceFile();
            source.InitializeAsReader(@"c:\temp\Input10.trc");

            Console.WriteLine("Opening destination");
            TraceFile dest = new TraceFile();
            dest.InitializeAsWriter(source, @"c:\temp\Filtered.trc");

            dest.WriteNotify += new WriteNotifyEventHandler(WriteNotifyHandler);

            while(true == dest.Write());

            Console.WriteLine("");
            Console.WriteLine("Closing files");
            source.Close();
            dest.Close();

        }
    }
}

Bob Dorr
SQL Server Principal Escalation Engineer

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post