Today, I learned a little bit about the RML utilities tool, and I think this tool is really good to use with SQL Server Profiler tools. Tool for
The Replay Markup Language (RML) utility for SQL Server: https://support.microsoft.com/zh-cn/kb/944837
RML Utilities for SQL Server (x86) –https://www.microsoft.com/en-us/download/details.aspx?id=8161
RML Utilities for SQL Server (x64) –https://www.microsoft.com/en-us/download/details.aspx?id=4511
Some machines will prompt for installation: Microsoft reportviewer--https://www.microsoft.com/zh-cn/download/confirmation.aspx?id=35747
I downloaded the x64 version, In the use of the first to have a TRC file, let use the tool to analyze the TRC file, the following is the procedure
STEP1: Generate TRC file
Open SQL SERVER PROFILER, keep track of the service for a while, generate the a.trc file, and place the file under the D Packing directory: G:\A.TRC
STEP2: Open RML Utilities Command prompt tool
The tool at: Start-All Programs--& Gt RML Utilities for SQL server-->rml Cmd Prompt "or go to the installation directory (my installation directory is: C:\Program Files\Microsoft Corporation\rmlutils), Open Interface
STEP3: Turn on local SQL Server service
I am loading SQL Server 2012, and of course can also specify the remote server through the parameter configuration, as long as the specified user has the response permission.
STEP4: Run import naming
First to create output directory D:\trace ,
- [RML] D:\\Program Files\\Microsoft Corporation\\rmlutils>readtrace-i "G:\A.TRC"-O "G:\trace"-S "127.0.0.1\rtwo2"-D "CRM "-e-u" sa "-p" ****** "
Specific parameter description (-I: Input file,-o: Output file,-S: Server,-D: Database,-e:windows authentication), the following will be introduced, the output is as follows
[RML] D:\\Program Files\\Microsoft Corporation\\rmlutils>readtrace-i "G:\A.TRC"-O "G:\trace"-S "127.0.0.1\rtwo2"-D "CRM "-e-u" sa "-p" ****** "
06/05/16 15:44:12.525 [0x000007a4] I/O completion manager started
06/05/16 15:44:12.525 [0x00001304] attempting DOD5015 removal of [G:\trace\Read
06/05/16 15:44:12.527 [0x00001304] readtrace a SQL Server trace processing util
Version 9.04.0051 built for x64.
Copyright? 1997-2014 Microsoft. All Rights Reserved
06/05/16 15:44:12.527 [0x00001304] computer:127.0.0.1
06/05/16 15:44:12.527 [0x00001304] Base Module:c:\program Files\micro
06/05/16 15:44:12.527 [0x00001304] Process id:7296
06/05/16 15:44:12.527 [0x00001304] Active proc Mask (0): 0x000000ff
06/05/16 15:44:12.527 [0x00001304] Architecture:9
06/05/16 15:44:12.527 [0x00001304] Page size:4096
06/05/16 15:44:12.527 [0x00001304] Cpus:8
06/05/16 15:44:12.527 [0x00001304] Processor groups:1
06/05/16 15:44:12.527 [0x00001304] Highest node:0
06/05/16 15:44:12.528 [0x00001304] proximity:00 node:00
06/05/16 15:44:12.528 [0x00001304]---------------------------------------
06/05/16 15:44:12.528 [0x00001304] group:0
06/05/16 15:44:12.528 [0x00001304]---------------------------------------
06/05/16 15:44:12.528 [0x00001304] Processor (s): 0x00000003 Function UN
06/05/16 15:44:12.528 [0x00001304] Processor (s): 0x0000000c Function UN
06/05/16 15:44:12.528 [0x00001304] Processor (s): 0x00000030 Function UN
06/05/16 15:44:12.528 [0x00001304] Package mask:0x000000ff
06/05/16 15:44:12.528 [0x00001304] Processor (s): 0x000000c0 Function UN
06/05/16 15:44:12.528 [0x00001304] Processor (s): 0X000000FF assigned to
06/05/16 15:44:12.529 [0x00001304] Current time bias: -480 minutes-8.00 hours
06/05/16 15:44:12.529 [0x00001304]-ig:\a.trc
06/05/16 15:44:12.533 [0x00001304]-og:\trace
06/05/16 15:44:12.535 [0x00001304]-s127.0.0.1\rtwo2
06/05/16 15:44:12.541 [0x00001304]-dcrm
06/05/16 15:44:12.544 [0x00001304]-E
06/05/16 15:44:12.546 [0x00001304]-usa
06/05/16 15:44:12.549 [0x00001304]-p******
06/05/16 15:44:12.551 [0x00001304] Using language ID (LCID): 1024x768 [Chinese (Sim
06/05/16 15:44:12.557 [0x00001304] Attempting to cleanup existing RML files fro
06/05/16 15:44:12.560 [0x00001304] Using extended rowsetfastload Synchronizatio
06/05/16 15:44:12.562 [0x00001304] establishing initial database connection
06/05/16 15:44:12.564 [0x00001304] Server:127.0.0.1\rtwo2
06/05/16 15:44:12.567 [0x00001304] Database:crm
06/05/16 15:44:12.569 [0x00001304] Authentication:sql
06/05/16 15:44:12.604 [0x00001304] Using SQLOLEDB version 11.0.2100.60
06/05/16 15:44:12.605 [0x00001304] Connected to SQL Server Version, Major:10,
06/05/16 15:44:12.606 [0x00001304] Creating or clearing the performance Databas
06/05/16 15:44:14.878 [0x00001304] processing file:g:\a.trc (
06/05/16 15:44:14.884 [0x00001304] Validating core events exist
06/05/16 15:44:14.886 [0x00001304] warning:the ' eventsequence ' column for even
06/05/16 15:44:14.888 [0x00001304] warning:the ' eventsequence ' column for even
06/05/16 15:44:14.891 [0x00001304] warning:the ' eventsequence ' column for even
06/05/16 15:44:14.894 [0x00001304] warning:the ' eventsequence ' column for even
06/05/16 15:44:14.898 [0x00001304] warning:the ' eventsequence ' column for even
06/05/16 15:44:14.902 [0x00001304] warning:the ' eventsequence ' column for even
06/05/16 15:44:14.904 [0x00001304] warning:the ' eventsequence ' column is Missi
06/05/16 15:44:14.907 [0x00001304] Validating necessary events exist for Analys
06/05/16 15:44:14.910 [0x00001304] warning:the following trace events were not
06/05/16 15:44:14.912 [0x00001304] warning:event (post_rpc_event_class) Doe
06/05/16 15:44:14.915 [0x00001304] Warning:event (post_lang_event_class) do
06/05/16 15:44:14.917 [0x00001304] warning:event (pre_lang_event_class) Doe
06/05/16 15:44:14.920 [0x00001304] warning:the quality of the performance anal
06/05/16 15:44:14.925 [0x00001304] Validating necessary events exist for RML BR
06/05/16 15:44:14.927 [0x00001304] warning:event [server:server Memory change
06/05/16 15:44:14.930 [0x00001304] WARNING replay:the following trace events W
Arnings:attention, Transactions:sqltransaction, Transactions:dtctransaction, Tr
ompleted, Transactions:TM:Save Tran starting, Transactions:TM:Save Tran complet
Replay needs. Lack of these events can leads to severe replay problems.
06/05/16 15:44:14.934 [0x00001304] ERROR:RML output has been disabled because Trace Flag-t28 disables the check allowing RML output processing. Use with
06/05/16 15:44:15.212 [0x00001304] Events read:1000 queued:346 processed/sec:
06/05/16 15:44:15.215 [0x00001304] ********************************************
06/05/16 15:44:15.215 [0x00001304] warning:found [trace_pause_event] in TRACE
06/05/16 15:44:15.215 [0x00001304] to process further events extract them with
06/05/16 15:44:15.215 [0x00001304] ********************************************
06/05/16 15:44:15.241 [0x00001304] Reads completed
06/05/16 15:44:15.241 [0x00001304] signaling worker threads to complete final A
06/05/16 15:44:15.243 [0x00001304] Waiting for the worker threads to complete F
06/05/16 15:44:15.250 [0x00001304] performing general cleanup actions.
06/05/16 15:44:15.250 [0x00001304] reducing cached memory in background
06/05/16 15:44:15.251 [0x00001304] Total Events processed:1088
06/05/16 15:44:15.251 [0x00001304] Total Events filtered:0
06/05/16 15:44:15.262 [0x00001304] Table ReadTrace.tblUniqueBatches:loaded ~13
06/05/16 15:44:15.262 [0x00001304] Table ReadTrace.tblUniqueStatements:loaded
06/05/16 15:44:15.263 [0x00001304] Table ReadTrace.tblUniquePlans:loaded ~0 ro
06/05/16 15:44:15.265 [0x00001304] Table ReadTrace.tblUniquePlanRows:loaded ~0
06/05/16 15:44:15.272 [0x00001304] Table ReadTrace.tblBatches:loaded ~571 rows
06/05/16 15:44:15.273 [0x00001304] Table ReadTrace.tblStatements:loaded ~0 row
06/05/16 15:44:15.276 [0x00001304] Table ReadTrace.tblPlans:loaded ~0 rows
06/05/16 15:44:15.278 [0x00001304] Table ReadTrace.tblPlanRows:loaded ~0 rows
06/05/16 15:44:15.281 [0x00001304] Table ReadTrace.tblInterestingEvents:loaded
06/05/16 15:44:15.286 [0x00001304] Table ReadTrace.tblConnections:loaded ~336
06/05/16 15:44:15.295 [0x00001304] warning:one or more WARNING conditions Exis
06/05/16 15:44:15.298 [0x00001304] info:cleaning up unique batch hash table
06/05/16 15:44:15.299 [0x00001304] info:cleaning up unique statement hash tabl
06/05/16 15:44:15.300 [0x00001304] info:cleaning up unique plan hash table
06/05/16 15:44:15.301 [0x00001304] info:cleaning up unique procedure hash tabl
06/05/16 15:44:15.303 [0x00001304] Indexing tables ...
06/05/16 15:44:15.937 [0x00001304] Doing post-load data Cleanup ...
06/05/16 15:44:16.413 [0x00001304] Computing partial aggregates ...
06/05/16 15:44:16.826 [0x00001304] Building Analysis Indexes ...
06/05/16 15:44:16.842 [0x00001304] Data load completed.
06/05/16 15:44:16.843 [0x00001304] Using execution Path:c:\program Files\micro
06/05/16 15:44:16.846 [0x00001304] launching [C:\Program Files\Microsoft Corpor
06/05/16 15:44:16.885 [0x00001304] Launch failure exit code:259
06/05/16 15:44:16.885 [0x00001304] attempt to launch Reporter failed. Check th
06/05/16 15:44:16.887 [0x00001304] ********************************************
* Readtrace encountered one or more ERRORS. An error condition typically *
* Stops processing early and the readtrace output may unusable. *
* Review the log file for details. *
*******************************************************************************
06/05/16 15:44:16.890 [0x00001304] * * * * Readtrace Exit code:-23
06/05/16 15:44:16.931 [0x00001304]
06/05/16 15:44:16.933 [0x00001304] info:cleaning up unique batch hash table
06/05/16 15:44:16.933 [0x00001304] info:cleaning up unique statement hash tabl
06/05/16 15:44:16.934 [0x00001304] info:cleaning up unique plan hash table
06/05/16 15:44:16.935 [0x00001304] info:cleaning up unique procedure hash tabl
06/05/16 15:44:16.935 [0x00001304] info:cleaning up connection INFO hash table |
STEP5: Viewing Reports
When the 4th step above is complete, the reporter application is opened automatically
Through the report, you can see your tracking during this period of time to track the relative pressure of the server, the specific interface is not one.
STEP6: Local Database MID objects are added to the
When you perform the 4th step command, some objects are created in the mid database configured with the-D parameter
Here are some of the parameter descriptions in Readtrace:
USAGE:
Note:all command line arguments is case sensitive
--Input file
-I File name of the first. TRC or. XEL file to process [REQUIRED]. Note:xel
Processing is ' BETA ' and limited.
--File within compressed file
-I If specified, indicates that. TRC file (s) to process is present inside
A Cab/zip/rar file with the This file name
--Output file (to full path), default is the current directory
-O full path of the directory to place output files [default was current directory]
--The database server (the resulting database object is stored on the server), the default local
-S Name of SQL Server 2005 server to connect to when loading performance Analys
is data [default is (local)]
--Specify the database (with data stored for analysis), do not specify to create the Perfanalysis database
-D loading performance data [default is Perfanalysis].
User specified below must have CREATE DATABASE permission (if DB doesn ' t
exist) or be part of the db_owner role if the database already exists.
--Log on to the database server for Windows licensing mode
-e Connect to SQL using Windows authentication [default]
--User logging on to the database server
-U Connect to SQL using the This user name
--Log on to the database server password
-P Password for the user specified in-u option
--Disable Execution analysis
-A Disable performance analysis
-F do not produce. RML output files for each Session and Request
-Q do normalization parse using QUOTED_IDENTIFIER OFF symantics. (Default is O
N
-r# Read at most this # of files (including the first) [Default was all files
Until a rollover file sequence is detected].
-M Mirror trace events by Session to the specified output directory (all Sessio
Ns
Would be output even if Session filter is specified)
-MF Mirror trace Events by Session to the specified output directory (only Sessi
Ons
Matching filter parameters'll be output)
-ms Mirror trace events to a single. TRC file in the specified output directory.
-M[FS] OR-M[SF] Combine mirror filtering with single. TRC file output
Note:the mirroring option may overwritten because the source Metadat
A information indicates such a change. ***
-A ' INCLUDE or EXCLUDE ' events with application Names
-C ' INCLUDE or EXCLUDE ' events with Database Names when possible
-h ' INCLUDE or EXCLUDE ' events with Host Names
-s# ' INCLUDE or EXCLUDE ' events from specified Sessions
-x# ' INCLUDE or EXCLUDE ' events based on Event Class value
-x# ' INCLUDE or EXCLUDE ' events based on subclass value
-b### time bias:adjusts the start and end times, as read by (+) # # minutes.
-B provide a designated start time in required format 2000-05-25 11:46:20:060
-e provide a designated stop time in required format 2000-05-25 11:46:20:060
-D Skip date on log file output
-L Integer value representing the language ID
-Y Optional Scratch path used by Expander when handling compressed files. If y
ou processing lots of files from a nested archive this can increase performance.
-? Show usage of command line parameters
EXAMPLES:
Readtrace-iserver__sp_trace.trc-ic:\temp\traces.cab-oc:\temp\output-f
Readtrace-i "C:\My Traces\80allevents.trc"-o "C:\My Output"
Readtrace-ioutput\sqlsrv1__sp_trace_20.trc-ic:\temp\pssdiag.zip-oc:\temp\brea
Kout-f-R2
Note:command line parameters must start with hyphen or slash and must not has
A space between the switch and parameter value. |
RML Utilities for SQL Server Tools
