We know that using the RML tool to analyze trace data (. TRC), a lot of stored procedure calls or dynamic statements are the same overall, but the parameters will be different, the tool will classify them.
is a trace file of the filter condition cpu>=30ms, after processing with the RML tool, the TOP Unique batches partial statement (here using the report statement in the Query window, the results are sorted by Normtext)
You can see that the **history_111_0 stored procedures are not fully merged, and this is related to the number of parameters and the order of parameters that the stored procedure executes.
We copy the first two normtext, and the cursor moves to the end of the statement:
They have different number of parameters, verify that the stored procedure defines a number of parameters, some with default values.
The following tests show whether the number of parameters and the order of parameters of the stored procedure execution will affect the classification of unique batches. First, you open the trace to collect the execution of the stored procedure and run the following statement in the query window:
UseTestDBGoCreate procCheckunique@parm1 int,@parm2 int=2,@parm3 int=3 asbegin Select @parm1,@parm2,@parm3End Go--Pass in a parameterexecCheckunique@parm1=1execCheckunique@parm1= One--two parameters passed inexecCheckunique@parm1=1,@parm2=2execCheckunique@parm1=1,@parm2= A--Swap parameter OrderexecCheckunique@parm2=2,@parm1=1--three parameters passed inexecCheckunique@parm1=1,@parm2=2,@parm3=3
View Code
At this point the profiler client sees:
Save as trace file to CHECKUNIQUE.TRC, using RML analysis:
Readtrace-i"F:\TroubleShooting\Trace\CheckUnique.trc" -o"f:\ Troubleshooting\trace\output" -S"127.0.0.1,7777" -D" Perfanalysis_checkunique" -E
Once processing is complete, click on "Unique Batches" on the report to get a statement-level report:
@parm1 belong to one category, @parm1, @parm2 belong to one category, @parm2, @parm1 belong to one category, @parm1, @parm2, @parm3 belong to one category. The number of visible arguments and the order of the parameters affect the collation of the statement.
What if it's an ordinary query statement?
--where conditions are differentSelect * fromsys.tablesSelect * fromSys.tableswhereType='U'Select * fromSys.tableswhereType='P'Select * fromSys.tableswhereType='U' andschema_id=5Select * fromSys.tableswhereType='P' andschema_id=1Select * fromSys.tableswhereschema_id=5 andType='U'--Select field is differentSelectName,create_date fromsys.tablesSelectCreate_date,name fromsys.tablesSelectNameobject_id, Create_date fromSys.tables
View Code
The profiler client sees:
RML Processing results:
Comparing the original and processed statements, it is easy to know that RML the same type of collation is affected by the following conditions:
For stored procedures, the number of parameters passed in and the Order of parameters
For a normal Select, it is related to the fields and order of the return field, order, and where condition (updtae, delete-like)
What LK's statement will be summed up in the same type by RML