Bringing It All Together with SQL Nexus - SQL Server 2012 Internals and Troubleshooting - page 341

➤

Importing SQL Trace i les using ReadTrace.exe

➤

Building custom reports for SQL Nexus

➤

Running SQL Nexus using command-line options

➤

Writing your own T-SQL query to facilitate quicker data analysis

➤

Running stress tests on your SQL Server instance using OSTRESS.EXE

Using ReadTrace.exe
SQL Nexus provides you with the option to break down the activity of each SPID into individual
.trc i les, but they can only be directed to the %TEMP%\RML folder on your machine. You have
two options: Change the %TEMP% environment path on your machine to a different drive or use
the ReadTrace.exe to generate the .trc i les in the required path. The i rst option is a bit drastic
as a change in the temp environment variable will affect all applications running under the user's
context. Sometimes, the free disk space of your system drive may not be sufi cient to accommodate
all the session specii c Trace i les generated by ReadTrace.exe . This warrants for the use of the
command-line options that the executable provides. A third option, available with RML Utilities,
is the capability to add SPID, Hostname, and Application i lters while importing the data to reduce
the size of the session specii c Trace i les generated by ReadTrace.exe . The ReadTrace.exe can
be accessed from the RML Utilities command prompt. Some of the default values for the common
parameters required for ReadTrace to import a SQL Trace i le are mentioned below:

➤

-o — Output i les to be generated in the current directory unless otherwise specii ed

➤

-S — If the -S parameter is not specii ed, the default option is to connect to the default SQL
Server instance installed on the machine.

➤

-d — Loads SQL Trace data into a database called PerfAnalysis unless a database name is
explicitly mentioned for this parameter value

➤

-E — Uses Windows authentication while connecting unless otherwise specii ed

One of the situations in which you might need to use ReadTrace.exe separately to import SQL Trace
data into a SQL Nexus database is when you are analyzing a deadlock. When analyzing deadlocks,
the deadlock graph is not sufi cient to determine why a lock is still held by that session and
why that session acquired the lock on the object in the i rst place. At that point, you need to track
down the sequence of statements executed by the session. To do so, you can use the following
command to import the data for the sessions involved in the deadlock. This command imports the SQL
Trace data into a database called dbRMLUtilDemo by i ltering only on activities for SPID 53 and 55:

readtrace -S. -E -ddbRMLUtilDemo
-I"D:\RML\RMLUtil_demo.trc" -A"!SQLCMD" -s53 -s55 -MF -o"D:\RML\output" -f

The preceding command also excludes events generated by queries executed through the SQLCMD
utility, which is what PSSDIAG or SQLDIAG uses to execute the T-SQL queries to collect the
diagnostic data. Additionally, individual trace i les for each SPID/Session ID will be generated in the
D:\RML\output folder. You can also add hostname i lters using the -H parameter.