I will continue to explain how to Troubleshoot Performance problems of SQL Server in this Article Series.
SQL Server Performance Troubleshooting
Read previous SQLDiag article before this.
Reporting SQLDIAG Performance Data via RML Utilities Tool
RML Utilities tool is not available in the SQL Server, so you need to install it. You can download the RML Utilities tool for free from this link.
After downloading installing the RML Utilities tool, you can open RML CMD Prompt as follows.
After opening RML Utilities Cmd Prompt command line will appear as follows.
With the Readtrace command below, give the first of the trace files (D1010984_SQLDIAG__sp_trace.trc) that we generated as a result of SQLDIAG in our previous article as follows.
After it read the first one, it automatically reads the others and prepare the report.
readtrace -I"D:\SQL_DIAG_OUTPUT\D1010984_SQLDIAG__sp_trace.trc" -o"D:\Result"
When you run the above command, this command creates custom tables in a database and insert all performance data into this database called PerfAnalysis on our default Instance.
To see the report prepared by RML Utility for us, open the Reporter tool as below.
When you open the Reporter tool, a screen like the following will appear.
Type the Instance and Database from which the Reporter tool above will receive the Performance Data. When you click OK button, RML Utilities tool will give you a visual report as below.
In this report, you can see the CPU, Reads, Writes, Duration values of the database as shown below. When you click on the Unique Batches link next to the arrow above to see the costly queries ( TOP SQL ) for the database, you will see the costly queries ( TOP SQL ) for our database as follows.
To see more detailed information about any query, click the Template of the query from the Query Template section and see the details as below.
I will explain how to solve these Performance problems in the SQL Server in the Next article.