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

The following list describes each of these options:
➤

Output trace i les (.trc) by SPID to %TEMP%\RML — This is a helpful option when
you want to extract the events associated with each SPID/Session ID into individual SQL
Trace i les.

➤

Output RML i les (.rml) to %TEMP%\RML — This option is useful when you want to
generate Replay Markup Language (RML) i les. RML is an XML-like format that is used
for replay and data exchange with the OSTRESS utility (which is discussed later in the
chapter). The . TRC events are broken down into sequences of RML nodes. This enables
OSTRESS to replay the activity.

➤

Assume QUOTED_IDENTIFIER ON — RML Utilities performs normalization of the
queries submitted to the database engine to generate Hash IDs for unique normalized SQL
Server texts. If this option is set to OFF, then the normalization parsing is done by using
QUOTED IDENTIFIER OFF semantics. The default setting is ON.

➤

Ignore events associated with PSSDIAG activity — This is another useful option for
reducing the noise that results from capturing T-SQL queries executed by the PSSDIAG
executable during the diagnostic data capture. This is accomplished using the
HOSTNAME i lter parameter for ReadTrace and can be further coni gured if ReadTrace is
run separately.

➤

Disable event requirement checks — RML Utilities requires the capture of starting and
completed events in order to create accurate aggregation data. If the necessary events are
not captured, then the SQL Trace import will fail to import any data into the ReadTrace
schema tables as the default behavior. This can be overridden for trace i les so that they can
be imported into a SQL Nexus database.

➤

Enabled -T35 to support MARS — Note that this trace l ag is for the ReadTrace executable
and is not a SQL Server instance trace l ag. RML Utilities provides partial support for
MARS (Multiple Active Result Sets) connections wherein a MARS session is made to
appear as separate sessions. The formula used is [ (Session Id * 10) + BatchId ]. This
enables basic performance analysis capabilities. Enabling this option also disables RML
output processing. When this option is enabled, SQL Nexus calls ReadTrace with the
parameters -T35 and -f .

WARNING There is a caveat when generating RML i les or TRC i les for
individual Sesison IDs/SPIDs with SQL Nexus, as the default output location is
the TEMP folder on your machine, which invariably points to your system drive.
Unless you have edited the TEMP environment variable to point to a different
location, you could quickly run out of disk space when analyzing large amounts
of diagnostic data. You can overcome this, however, by using ReadTrace.exe
separately to load the SQL Trace i les into the SQL Nexus database.

The destination for the imported data is by default a database called sqlnexus , which is created
when SQL Nexus connects to a SQL Server instance. This database can store diagnostic data
collected from only one instance at a time, so it is re-created each time you run an import. If you