sqldiag-configuration file-profilercollector

In the previous article, we described the Perfmoncollector elements related to performance counters in the configuration file, which we will cover the PROFILERCOLLECTOR elements related to tracking data.
In the previous article using the Sd_detailed.xml configuration file in my local collection of 5 minutes trace data file 7Mb, then did not do anything else, imagine if in a busy production environment, the resulting trace file size will be larger.
The tracking data is related to the added tracking events and the system's busy level. SQLdiag the size of the data collection expands disproportionately or captures diagnostic data when the target SQL Server performance degrades primarily because a large number of redundant or irrelevant profiler events are added to the data collection configuration. Profiler tracking collects the data collection components that are by far the most consumed resource in sqldiag.
Profilercollector
This section will be tested in the following steps:
1> disables Eventlogcollector, Blockingcollector, customdiagnostics element collectors; Perfmoncollector, Sqldiagcollector, Profilercollector element remains enabled
2> enable the following event (class) in the Profilercollector element, all other events are disabled

<EventTypename= "Database"enabled= "true">  <Eventname= "Data File Auto Grow"enabled= "true"ID= "the" />  <Eventname= "Log File Auto Grow"enabled= "true"ID= " the" /><EventTypename= "Stored Procedures">  <Eventname= "rpc:completed"enabled= "true"ID= "Ten" /><EventTypename= "TSQL">  <Eventname= "sql:batchcompleted"enabled= "true"ID= " a" />

Event Category Add policy refer to SQL Server 2012 in-depth parsing and performance optimization (3rd edition) 11.5.2 filter noise, analysis problem can not be once and for all, to be appropriate to enable/disable the event for the specific situation.
We collect 5 minutes of data using the Sqldiagperfstats_detailed_trace2008new.xml configuration file:

CD D:\Program Files\Microsoft SQL Server\/I F:\TroubleShooting\SQLdiag\input\SQLDiagPerfStats_ detailed_trace2008new.xml/o F:\TroubleShooting\SQLdiag\LocalOutput/  e +xx:xx

After it appears in the green font "Collection started", refer to RML Utilities for SQL server to parse a. trc file

 readtrace-i f:\troubleshooting\trace\ HIGHDURATION20151212.TRC  -o"  Span style= "color: #800000;" >f:\troubleshooting\trace\output  -s"  127.0.0.1,7777   "-d " perfanalysis_cl  " -e 

Due to the small size of the existing HIGHDURATION20151212.TRC files, RML is quickly analyzed. In the auto-open reporter, we just click to view. Just to simulate the case where the application is using the database, SQLdiag collection will automatically end after 5 minutes.
We recall that in the Profilercollector element of the configuration file, we can only enable/disable events (classes) and cannot manipulate event columns! By default, all available event columns for the event should be added, and column filters will not be edited!
We can open the trace file at the end of the collection and view the trace file properties. As expected, most event columns are checked, there are no column filters, and the number of records in the 5-minute trace file is 833 lines:

But in a production environment, we may expect to add the necessary screening. We may just want to monitor a program, or a login name, or textdata a keyword, or cpu/duration/reads a statement that satisfies a certain condition.
Add a trace filter for the SQLDIAG configuration
By case, the profiler tracking filter is added to SQL Server data collection to reduce the amount of data collected by SQLDIAG for diagnostic data. Note that the text filter increases the CPU overhead for data collection because there is significant CPU overhead for evaluating file-based filters, and integer filters do not. If you collect profiler traces using SQLdiag's XML configuration file, you need to follow these steps:
1> starting the sqldiag in the server
2> using the Fn_trace_getinfo function or sys.traces view to find the Tracking ID of the profiler trace
3> use Sp_trace_setstatus to stop tracing without deleting the definition
4> uses the Tracking ID obtained from step 2nd and uses the Sp_trace_setfiler stored procedure to set the filter
5> using the Fn_trace_getfilterinfo function to verify that the filter is active
6> use Sp_trace_setstatus to start tracking data collection When you are satisfied that the filter is active
To test, we re-open sqldiag and use the following script to add a trace cpu>10 statement:

--gets the trace ID of the trace fileDeclare @TraceID intSelect @TraceID=Id fromSys.traceswhere [Path]  like '%SP_TRACE.TRC'--Stop functionexecSp_trace_setstatus@TraceID,0--Set the FiltersDeclare @intfilter intDeclare @bigintfilter bigint--Get trace_column_id--SELECT * FROM Sys.trace_columns TC--Set @bigintfilter = 1000000--exec sp_trace_setfilter @TraceID, 0, 4, @bigintfilter--duration>=1s--Set @bigintfilter = NULL--exec sp_trace_setfilter @TraceID, 0, 1, @bigintfilter-excludes rows that do not contain valuesSet @intfilter = TenexecSp_trace_setfilter@TraceID, -,0,4,@intfilter--cpu>10Set @intfilter = NULLexecSp_trace_setfilter@TraceID, -,0,1,@intfilter--exclude rows that do not contain values--Startup functionexecSp_trace_setstatus@TraceID,1

View Code

We also analyze A. TRC trace file and view reporter data, open Profiler trace data after 5 minutes:

We can see that there is trace Stop/start in the trace file, there are Cpu=0 records before stop, and only cpu>=10 (MS) after stop is tracked. Also note that the number of records in the 5-minute trace file is only 81 lines. In practice, we can adjust the filter filter value according to the situation, in fact, we can save it to a file named Sql_2008_setfilters.sql, and then add it to the custom collector, so that we do not have to manually add filters for the tracking data each time we start sqldiag.
Tracking data reports under the SQL Nexus
We import the collected sqldiag data using the SQL Nexus, at which time the Perfmon Summary (performance counter), Readtrace Reports (trace file) of the main interface are available. Click Readtrace Reports is the page that appears familiar? Yes, it's the interface we see in RML.
First we look at the total Interface Performance Overview:

Then click Application Name and sort by the total number of duration:

We can directly click on the application Name, jump to the relevant appname under the top Unique Batches, or return to the Performance Overview page, click on Unique Batches:

Scroll the mouse wheel, here is the statement information of the top Unique batches by CPU:

The actual operation felt that the Readtrace reports in the SQL Nexus did not use RML fully, parameters in the SQL Nexus seemingly does not work, do not know whether their own computer problems or other reasons.

sqldiag-configuration file-profilercollector