RML Utilities for SQL Server工具

    今天学习了一下RML Utilities工具，觉得这个工具确实比较好的跟SQL Server Profiler工具结合起来使用，确实挺好的。工具的下载地址
        SQL Server 的重播标记语言 （RML） 实用程序： 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

    我下载的是x64版本的，在使用该先要有个trc文件，让用用该工具进行分析trc文件，下面是操作步骤

STEP1:生成trc文件
    开启SQL SERVER PROFILER, 对服务起进行跟踪一段时间，生成 a.trc 文件，将文件放在D盘根目录下面： D:\a.trc

STEP2：打开RML Utilities Command Prompt工具
     该工具在：开始--&gt所有程序--&gtRML Utilities for SQL Server--&gtRML Cmd Prompt" 或者进入安装目录（我的安装目录是：D:\Program Files\Microsoft Corporation\RMLUtils），打开的界面如图：
     

STEP3：开启本地SQL server 服务
     我装的是SQL SERVER 2012, 当然也可以通过参数配置指定远程服务器，只要指定的用户有响应的权限。

STEP4：运行导入命名       
     先要创建输出目录 D:\trace，

1. [RML] D:\\Program Files\\Microsoft Corporation\\RMLUtils>ReadTrace -I"D:\a.trc" -o"D:\trace" -S"192.168.6.56" -d"MID" -E

    具体参数说明（-I：输入文件，-o：输出文件，-S：服务器，-d：数据库，-E：windows认证），下面会介绍， 输出结果如下

[RML] D:\Program Files\Microsoft Corporation\RMLUtils>ReadTrace -I"D:\a.trc" -o" D:\trace" -S"192.168.6.56" -d"MID" -E 04/20/15 09:37:46.123 [0X00022A30] I/O Completion manager started 04/20/15 09:37:46.124 [0X000237E4] Readtrace a SQL Server trace processing utili ty. Version 9.04.0051 built for x64. Copyright ? 1997-2014 Microsoft. All Rights Reserved 04/20/15 09:37:46.124 [0X000237E4]             Computer: ******-** 04/20/15 09:37:46.124 [0X000237E4]          Base Module: D:\Program Files\Micros oft Corporation\RMLUtils\ReadTrace.exe 04/20/15 09:37:46.125 [0X000237E4]           Process Id: 8344 04/20/15 09:37:46.125 [0X000237E4]  Active proc mask(0): 0x0000000F 04/20/15 09:37:46.125 [0X000237E4]         Architecture: 9 04/20/15 09:37:46.125 [0X000237E4]            Page size: 4096 04/20/15 09:37:46.126 [0X000237E4]                 CPUs: 4 04/20/15 09:37:46.126 [0X000237E4]     Processor groups: 1 04/20/15 09:37:46.126 [0X000237E4]         Highest node: 0 04/20/15 09:37:46.126 [0X000237E4]   Proximity: 00  Node: 00 04/20/15 09:37:46.127 [0X000237E4] --------------------------------------- 04/20/15 09:37:46.127 [0X000237E4]                Group: 0 04/20/15 09:37:46.127 [0X000237E4] --------------------------------------- 04/20/15 09:37:46.127 [0X000237E4]         Processor(s): 0x00000001 Function uni ts: Separated 04/20/15 09:37:46.127 [0X000237E4]         Processor(s): 0x00000002 Function uni ts: Separated 04/20/15 09:37:46.128 [0X000237E4]         Processor(s): 0x00000004 Function uni ts: Separated 04/20/15 09:37:46.128 [0X000237E4]         Package mask: 0x0000000F 04/20/15 09:37:46.128 [0X000237E4]         Processor(s): 0x00000008 Function uni ts: Separated 04/20/15 09:37:46.128 [0X000237E4]         Processor(s): 0x0000000F assigned to Numa node: 0 04/20/15 09:37:46.130 [0X000237E4] Current time bias: -480 minutes -8.00 hours D ST not used 04/20/15 09:37:46.130 [0X000237E4] -ID:\a.trc 04/20/15 09:37:46.130 [0X000237E4] -oD:\trace 04/20/15 09:37:46.130 [0X000237E4] -S192.168.6.56 04/20/15 09:37:46.130 [0X000237E4] -dMID 04/20/15 09:37:46.131 [0X000237E4] -E 04/20/15 09:37:46.131 [0X000237E4] Using language id (LCID): 1024 [Chinese (Simp lified)_People's Republic of China.936] for character formatting with NLS: 0x000 60101 and Defined: 0x00060101 04/20/15 09:37:46.131 [0X000237E4] Attempting to cleanup existing RML files from  previous execution 04/20/15 09:37:46.132 [0X000237E4] Using extended RowsetFastload synchronization 04/20/15 09:37:46.132 [0X000237E4] Establishing initial database connection 04/20/15 09:37:46.133 [0X000237E4] Server: 192.168.6.56 04/20/15 09:37:46.134 [0X000237E4] Database: MID 04/20/15 09:37:46.134 [0X000237E4] Authentication: Windows 04/20/15 09:37:46.406 [0X000237E4] Using SQLOLEDB version 11.0.3000.0 04/20/15 09:37:46.474 [0X000237E4] Connected to SQL Server Version, Major: 11, M inor: 0, Build: 2218 04/20/15 09:37:46.475 [0X000237E4] Creating or clearing the performance database 04/20/15 09:37:50.241 [0X000237E4] Processing file: D:\a.trc (SQL 2008 / SQL 200 8 R2 / SQL Azure*) 04/20/15 09:37:50.241 [0X000237E4] Validating core events exist 04/20/15 09:37:50.242 [0X000237E4] WARNING: The 'EventSequence' column for event  id=10 [POST_RPC_EVENT_CLASS] is missing from the trace. 04/20/15 09:37:50.242 [0X000237E4] WARNING: The 'EventSequence' column for event  id=12 [POST_LANG_EVENT_CLASS] is missing from the trace. 04/20/15 09:37:50.242 [0X000237E4] WARNING: The 'EventSequence' column for event  id=13 [PRE_LANG_EVENT_CLASS] is missing from the trace. 04/20/15 09:37:50.242 [0X000237E4] WARNING: The 'EventSequence' column for event  id=17 [ACTIVE_EVENT_CLASS] is missing from the trace. 04/20/15 09:37:50.243 [0X000237E4] WARNING: The 'EventSequence' column is missin g from the trace.  Physical file order will be used and this can lead to inaccur ate results. 04/20/15 09:37:50.243 [0X000237E4] Validating necessary events exist for analysi s 04/20/15 09:37:50.244 [0X000237E4] WARNING: The following trace events were not captured: [RPC:Starting, SP:StmtStarting, SP:StmtCompleted, Audit:Login, Audit:L ogout, Showplan Statistics]. Review the help file to ensure that you have collec ted the appropriate set of events and columns for your intended analysis. 04/20/15 09:37:50.244 [0X000237E4] WARNING: Event 10 (POST_RPC_EVENT_CLASS) does  not contain the following recommended column(s): DBID 04/20/15 09:37:50.245 [0X000237E4] WARNING: Event 12 (POST_LANG_EVENT_CLASS) doe s not contain the following recommended column(s): DBID 04/20/15 09:37:50.245 [0X000237E4] WARNING: Event 13 (PRE_LANG_EVENT_CLASS) does  not contain the following recommended column(s): DBID 04/20/15 09:37:50.245 [0X000237E4] WARNING: The quality of the performance analy sis may be substantially affected due to the lack of recommended columns.  Consu lt the help file for the recommended set of trace events and columns. 04/20/15 09:37:50.246 [0X000237E4] Validating necessary events exist for RML bre akout 04/20/15 09:37:50.246 [0X000237E4] WARNING: Event [Server: Server Memory Change]  missing from trace 04/20/15 09:37:50.246 [0X000237E4] WARNING REPLAY: The following trace events we re not captured: [Security Audit: Audit Login, Security Audit: Audit Logout, Sto red Procedures: RPC Output Parameter, Cursors:Unprepare, Cursors:CursorClose, Cu rsors:CursorPrepare, Cursors:CursorCreated, Cursors:CursorExecute, TSQL: Prepare  SQL, TSQL: Unprepare SQL, Errors and Warnings:Attention, Transactions:SQLTransa ction, Transactions:DTCTransaction, Transactions:TM:Begin Tran starting, Transac tions:TM:Begin Tran completed, Transactions:TM:Commit Tran starting, Transaction s:TM:Commit Tran completed, Transactions:TM:Rollback Tran starting, Transactions :TM:Rollback Tran completed, Transactions:TM:Save Tran starting, Transactions:TM :Save Tran completed, Transactions:TM:Promote Tran starting, Transactions:TM:Pro mote Tran completed, RPC:Starting and RPC:Completed]. Review the help file to en sure that you have collected the appropriate set of events and columns for RML r eplay needs.  Lack of these events can lead to severe replay problems. 04/20/15 09:37:50.259 [0X000237E4] ERROR: RML output has been disabled because t he required events or columns were not captured. Consult the log and help file f or the recommended set of trace events and necessary columns. Trace flag -T28 disables the check allowing RML output processing. *** Use with caution as the output and behavior could be unpredictable. 04/20/15 09:37:50.285 [0X000237E4] Events Read: 1000 Queued: 967 Processed/sec: 1320 04/20/15 09:37:50.355 [0X000237E4] Events Read: 2000 Queued: 1961 Processed/sec:  410 04/20/15 09:37:50.370 [0X000237E4] Events Read: 3000 Queued: 2955 Processed/sec:  409 04/20/15 09:37:50.382 [0X000237E4] Events Read: 4000 Queued: 3950 Processed/sec:  409 04/20/15 09:37:50.396 [0X000237E4] Events Read: 5000 Queued: 4948 Processed/sec:  382 04/20/15 09:37:50.409 [0X000237E4] Events Read: 6000 Queued: 5942 Processed/sec:  389 04/20/15 09:37:50.420 [0X000237E4] Events Read: 7000 Queued: 6942 Processed/sec:  362 04/20/15 09:37:50.431 [0X000237E4] Events Read: 8000 Queued: 7938 Processed/sec:  362 04/20/15 09:37:50.510 [0X000237E4] Reads completed 04/20/15 09:37:50.511 [0X000237E4] Signaling worker threads to complete final ac tions. 04/20/15 09:37:50.511 [0X000237E4] Waiting for the worker threads to complete fi nal actions. 04/20/15 09:37:51.871 [0X000237E4] Performing general cleanup actions. 04/20/15 09:37:51.872 [0X000237E4] Reducing cached memory in background 04/20/15 09:37:51.873 [0X000237E4] Total Events Processed: 8411 04/20/15 09:37:51.873 [0X000237E4]  Total Events Filtered: 0 04/20/15 09:37:52.034 [0X000237E4] Table ReadTrace.tblUniqueBatches: loaded ~150  rows 04/20/15 09:37:52.034 [0X000237E4] Table ReadTrace.tblUniqueStatements: loaded ~ 0 rows 04/20/15 09:37:52.035 [0X000237E4] Table ReadTrace.tblUniquePlans: loaded ~0 row s 04/20/15 09:37:52.036 [0X000237E4] Table ReadTrace.tblUniquePlanRows: loaded ~0 rows 04/20/15 09:37:52.041 [0X000237E4] Table ReadTrace.tblBatches: loaded ~8103 rows 04/20/15 09:37:52.042 [0X000237E4] Table ReadTrace.tblStatements: loaded ~0 rows 04/20/15 09:37:52.042 [0X000237E4] Table ReadTrace.tblPlans: loaded ~0 rows 04/20/15 09:37:52.043 [0X000237E4] Table ReadTrace.tblPlanRows: loaded ~0 rows 04/20/15 09:37:52.043 [0X000237E4] Table ReadTrace.tblInterestingEvents: loaded ~0 rows 04/20/15 09:37:52.049 [0X000237E4] Table ReadTrace.tblConnections: loaded ~74 ro ws 04/20/15 09:37:52.066 [0X000237E4] WARNING: One or more warning conditions exist  that may affect the quality of the analysis data.  See MID.ReadTrace.tblWarning s table and the ReadTrace log for complete details. 04/20/15 09:37:52.069 [0X000237E4] INFO: Cleaning up unique batch hash table 04/20/15 09:37:52.071 [0X000237E4] INFO: Cleaning up unique statement hash table 04/20/15 09:37:52.072 [0X000237E4] INFO: Cleaning up unique plan hash table 04/20/15 09:37:52.073 [0X000237E4] INFO: Cleaning up unique procedure hash table 04/20/15 09:37:52.074 [0X000237E4] Indexing tables... 04/20/15 09:37:54.385 [0X000237E4] Doing post-load data cleanup... 04/20/15 09:37:56.084 [0X000237E4] Computing partial aggregates... 04/20/15 09:37:57.093 [0X000237E4] Building analysis indexes ... 04/20/15 09:37:57.493 [0X000237E4] Data load completed. 04/20/15 09:37:57.494 [0X000237E4] Using execution path: D:\Program Files\Micros oft Corporation\RMLUtils\ 04/20/15 09:37:57.495 [0X000237E4] Launching [D:\Program Files\Microsoft Corpora tion\RMLUtils\Reporter.exe] 04/20/15 09:37:57.561 [0X000237E4] Launch failure exit code: 259 04/20/15 09:37:57.562 [0X000237E4] Attempt to launch Reporter failed.  Check the  error log and your RML installation. 04/20/15 09:37:57.562 [0X000237E4] ********************************************* ********************************** * ReadTrace encountered one or more ERRORS. An error condition typically      * * stops processing early and the ReadTrace output may be unusable.            * * Review the log file for details.                                            * ******************************************************************************* 04/20/15 09:37:57.563 [0X000237E4] ***** ReadTrace exit code: -23 04/20/15 09:37:57.631 [0X000237E4] 04/20/15 09:37:57.635 [0X000237E4] INFO: Cleaning up unique batch hash table 04/20/15 09:37:57.635 [0X000237E4] INFO: Cleaning up unique statement hash table 04/20/15 09:37:57.635 [0X000237E4] INFO: Cleaning up unique plan hash table 04/20/15 09:37:57.635 [0X000237E4] INFO: Cleaning up unique procedure hash table 04/20/15 09:37:57.637 [0X000237E4] INFO: Cleaning up connection info hash table [RML] D:\Program Files\Microsoft Corporation\RMLUtils>

STEP5：查看报表
     上面的第4步完成后，会自动打开Reporter应用程序


通过上面的报表，可以看到你的跟踪在跟踪这段时间内服务器的相关压力情况，具体每个界面就不一一截图了。

STEP6: 本地数据库 MID 中增加了对象
     执行第4步的命令时，会在参数-d配置的 MID数据库中创建一些对象




下面是ReadTrace中的一些参数说明：

USAGE:         NOTE: All command line arguments are case sensitive -- 输入文件 -I  File name of the first .TRC or .XEL file to process [REQUIRED].  Note:  XEL processing is 'BETA' and limited. -- 文件在压缩文件内 -i  If specified, indicates that the .TRC file(s) to process are present inside a CAB/ZIP/RAR file with this file name -- 输出文件（要全路径的），默认是当前目录 -o  Full path of directory to place output files [default is current directory] --数据库服务器（生成的数据库对象保存在该服务器上），默认local -S  Name of SQL Server 2005 server to connect to when loading performance analys is data [default is (local)] --指定数据库（用与存放分析的数据），未指定会创建PerfAnalysis数据库 -d  Database to use when 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. -- 登录数据库服务器为windows授权模式 -E  Connect to SQL using Windows Authentication [default] --登录数据库服务器的用户 -U  Connect to SQL using this user name --登录数据库服务器的密码 -P  Password for the user specified in -U option -- 禁用执行分析 -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 is all files     until a break in the rollover file sequence is detected]. -M  Mirror trace events by Session to the specified output directory (All Sessio ns     will 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 will 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 be 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 have a space between the switch and parameter value.

参考文档：http://blog.csdn.net/gzh0222/article/details/6924164
http://haicang.blog.51cto.com/2590303/1132462

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30150152/viewspace-1586885/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30150152/viewspace-1586885/