What LK's statement will be summed up in the same type by RML

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.