SQL Nexus and RML Utilities: A primer on the issues that I have dealt with last year
I have answered multiple queries last year regarding the tools SQL Nexus and RML Utilities via MSDN forums, Twitter and Codeplex last year. I thought now might be a good idea to collate the top issues that I answered and make a blog post on the same.
Issue #1
You cannot import a profiler traces using SQL Nexus and get the following exception in the SQL Nexus log:
"The system cannot find the file specified (System)"
—————————— Program Location: at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
at System.Diagnostics.Process.Start() at System.Diagnostics.Process.Start(ProcessStartInfo startInfo) at ReadTrace.ReadTraceNexusImporter.DoImport() in C:\jacklidocs3\cprsrc\main\src\sqlnexus_pass\ReadTraceNexusImporter\ReadTraceNexusImporter.cs:line 364 at sqlnexus.fmImport.DoImport() in C:\jacklidocs3\cprsrc\main\src\sqlnexus_pass\sqlnexus\fmImport.cs:line 557
I had blogged about the workarounds and reasons for the same here.
Issue #2
If you want to use Reporter.exe, which is the standalone report viewing tool that can be used to view Profiler aggregation done by RML Utilities, then you might run into errors while trying to view the data as the Reporter.exe connects to the PerfAnalysis database by default. You need to change the database name before viewing the reports in case you imported the Profiler Traces into a non-default database. I had blogged about this in the past.
Issue #3
Co-relation between the tables: Readtrace.tblbatches and Readtrace.tblstatements has been a big misconception for a number of users. I had blogged about this last April here.
Issue #4
You might get the following error while importing a profiler trace in the RML Utilities log:
12/17/10 10:45:22.498 [0X00007408] *** ERROR: Error encountered with operating system error 0x80004005 (Unspecified error)
12/17/10 10:45:22.498 [0X00007408] Reads completed - Global Error Status 0xffffffe7
12/17/10 10:45:22.514 [0X00007408] Shutting down the worker thread message queues.
12/17/10 10:45:22.514 [0X00007408] Waiting for the worker threads to complete final actions.
12/17/10 10:45:22.514 [0X00007408] Performing general cleanup actions.
12/17/10 10:45:22.514 [0X00007408] Total Events Processed: 82066
12/17/10 10:45:22.514 [0X00007408] Total Events Filtered: 0
12/17/10 10:45:22.514 [0X00007408] Parse errors: 0
12/17/10 10:45:22.545 [0X00007408] *******************************************************************************
* 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. *
*******************************************************************************
12/17/10 10:45:22.545 [0X00007408] ***** ReadTrace exit code: –25
OR
A serious error condition (true == pVariant->FIsUnknownType()) has been encountered.
Utility Error: Attempt to use an invalid variant type
File: .\rpcbinary.cpp
Line: 264
Return Address: 0x000000013FB650A2
Function: FConvertNCharStringFromRPC
If that is the case, then drop a mail to sstlbugs@microsoft.com with the RML Utilities log as an attachment and stating how the error occurred.
Issue #5
Unable to view the Blocking and Wait Statistics OR Bottleneck Analysis reports or getting the error “Database doesn't have the necessary data to run this report”.
The data for the above report is based on the SQL Server 2005/2008 Perf Stats script output depending on the version of the SQL instance that you are collecting data from. The file that normally has the data for the above reports to be generated will have the following naming convention: <ServerName>__SQL_2005_Perf_Stats_Startup. OUT or <ServerName>__SQL_2008_Perf_Stats_Startup.OUT. Ensure that this file has collected at least two snapshots of the outputs of the T-SQL queries present in the SQL Server 2005/2008 PerfStats script. The check that is done to report this error is to check if data is present in the following tables in the Nexus database:
- tbl_Requests
- tbl_blocking_chains
- tbl_notableActiveQueries
Issue #6
BLG file import using SQL Nexus. Currently SQL Nexus doesn’t have any reports for viewing data imported from BLG files imported using Nexus. There are also some minor known issues which prevent some .BLG files from successfully being loaded into a Nexus database. There are plans to address this in a future release. If you have imported .BLG files into a SQL Nexus database, then you would need to write T-SQL queries on your own to perform aggregation.
Issue #7
If you have sessions on your SQL instance which use MARS (Multiple Active Result Sets), then you need to use –T35 (trace flag) to import the profiler traces. This is documented here.
What can you do if you run into an issue with SQL Nexus that you are unable to resolve?
If you are stumped with a certain feature of SQL Nexus, then please feel to start a discussion on the SQL Nexus site at https://sqlnexus.codeplex.com/discussions. I get a notification about the new discussion and will respond to the same ASAP and also if a community member knows of a resolution, they will respond back to the same as well.
What can you do if you identify a bug with SQL Nexus or want to submit a design change request?
File a new item on the Issue Tracker section on the SQL Nexus site on CodePlex at https://sqlnexus.codeplex.com/workitem/list/basic. Based on the issue/request, we shall take the fix into consideration for the next release and provide a workaround for the time being.
Additional references
I had done a webcast a while back which talks about the following:
1. Overview of SQL Nexus as a tool.
2. A demo on performing root cause analysis using SQL Nexus for blocking scenarios
3. A demo on identifying TOP CPU consuming queries using SQL Nexus
Link: https://zeollar.cloudapp.net/Session/144
Happy troubleshooting!
Amit Banerjee
SEE, SQL Server Support