If you have difficulty viewing this session, click here to view it in the original website.
Here is the recording of the webcast that I presented today! Feel free to contact me through this blog or post on the discussion forum on the SQL Nexus discussion forum.
In case you are not able to view the presentation, then you can view it directly on the Zeollar site.
While trying to import a profiler trace using SQL Nexus, you might get the following error in the SQL Nexus log file:
"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
The issue could be one of the two reasons:
I remember in the past month addressing at least three different queries regarding RML Utilities on MSDN w.r.t. the co-relation between the Batches and Statements. Yes, it is true that a Batch will have one or more T-SQL statements. But if the statements do not have SP:StmtStarting and SP:StmtCompleted, then the Batch will not have an entry in the ReadTrace.tblStatements. This is documented in the RML Utilities help file.
You can verify if the trace has any SP:StmtStarting or SP:StmtCompleted using the following query:
select count(*) from fn_trace_gettable ('<trace file with full path>',null) where eventclass in (44,45)
From the help file:
ReadTrace.tblStatements
This table contains one row for each SP:StmtStarting or SP:StmtCompleted event in the trace. Similar to the ReadTrace.tblBatches table, the completed event is stored when it is available. Otherwise, the starting event is saved so that you can tell that a query was running when the trace was stopped.
This is a tool that is used by the Microsoft PSS team to analyze PSSDiag data. This tool has the capability of loading the Profiler Traces collected (provided ReadTrace is installed on the box).
In addition to this, it also loads the other .OUT files and the PerfStats script output file into the Nexus database. I use this tool regularly to analyze PSSDIAG data due to the following reasons:
1. Saves me time from manually opening each file and looking through them
2. Based on the graphs provided in the reports, I can quickly drilldown to the major bottleneck
The SQL Nexus reports act as a brilliant guide when you are analyzing PSSDIAG for the following scenarios:
1. High CPU
2. Blocking
3. High IO
4. General Performance Problems
Since, the SQL Nexus Reports use Reporting Services client-side report viewer, you can create your own reports to work against the Nexus database and then deploy them to the %appdata%/SQL Nexus/Reports folder. Once, this is done, the new reports would show up along with the defult reports every time SQL Nexus is launched.
What are the advantages of this tool?
1. Shows aggregation reports for the Profiler Traces loaded into it
2. Blocking and wait statistics information based on the PerfStats script output. This helps in analyzing blocking on the server
3. Since, the data collected are loaded into tables, you can run queries against the database to draw inferences. If you use these queries frequently enough, you can create your own reports and deploy them to the above mentioned location
4. You can export the data from the SQL Nexus Reports into files (.XLS,.PDF,.JPG etc) and send them as reports
5. The reports let you narrow down to specific time frames which makes analysis easier
Download location: http://www.codeplex.com/sqlnexus
Addition: April 4, 2011
A list of known issues documented with SQL Nexus:
Addition: April 6, 2011
A very common issue that I worked on multiple times after which I realized that it warranted a blog post:
SQLDIAG: There are no instances of version 10 on this computer
The Microsoft Download site has a tool called RML Utilities which can be used to process the profiler traces collected from a SQL Server instance.
SQL Profiler Traces are very helpful when we need to perform a Bottleneck Analysis in any SQL environment or you need to find out the before & after picture. But the daunting task remains in analyzing the Profiler Traces because the size and volume of the trace files.
SQL Server provides an in-built function fn_trace_gettable() to load the collected profiler traces into a SQL Server database table.
Then you can run queries on this table to draw inferences from the profiler traces collected during that period. Lets say you wanted to find out all queries that had a duration above 10,000 and group the results by host name, then you could write a query in the following manner:
SELECT hostname, count(*) as counts
FROM tbl_trace
WHERE Duration > 10000
GROUP BY hostname
This is a tool that is used by Microsoft PSS to analyze SQL Server Profiler Traces collected by the PSSDIAG utility.
For more details, please refer:
Replay Markup Language
http://support.microsoft.com/?kbid=944837
RML Utilities (x86) Download
http://www.microsoft.com/downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en
RML Utilities (x64) Download
http://www.microsoft.com/downloads/details.aspx?familyid=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en
TroubleshootingSQL 