SQL Server Performance Analysis Tool

References

Monitoring with SQL profiler

Readtrace

There is a profiler tool in SQL Server to track the execution process of SQL Server and monitor all the processes of SQL Server. If SSMs is used to operate SQL Server, the commands corresponding to the SSMs GUI are reflected in profiler. When performing troubleshooting, we usually require customers to capture the final trace file of this profiler. the filename suffix is. TRC. However, the trace file we get may be large and usually contains tens of thousands or even hundreds of thousands of records. How can we analyze this trace? At this time, we need to introduce some tools. Here we use the readtrace tool, which is rml utilities for SQL Server.

After the installation is complete, there will be two tools named readtrace.exeand reporter.exe in the \ Program Files \ microsoft corporation \ rmlutils \ directory. If you want to analyze the trace file in 'd: \ sqltrace \ input \ sp_trace.trc ', the output is also generated in 'd: \ sqltrace \ output. The command can be:

D:\Program Files\Microsoft Corporation\RMLUtils>ReadTrace -I"D:\sqltrace\input\sp_trace.trc" -o"D:\sqltrace\output" -f

Note that the parameters here are case sensitive. Another thing to note is that the directory of the output file cannot be in the same directory as that of the input file. Otherwise, an error will be reported, so I will distinguish it from output here. After performing the preceding operations, a database called perfanalysis is automatically created in SQL Server. Click reporter.exe to view the report.

Common statistics include the following:

1. The report tool will make statistics based on the start time, end time, CPU, reads, writes, duration, and so on in the trace record, and draw a chart based on the time period. It helps administrators understand how busy SQL Server is in different time periods.
2. Sort by application name, Database ID, and login name to find the program/database/user that causes the maximum SQL workload.
3. Calculate the most expensive statements based on the same type of statements. In unique batch and unique statement

SQL nexus

Http://sqlnexus.codeplex.com/

Pssdiag data collection Utility

Microsoft's introduction to pssdiag is as follows:

Pssdiag is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. pssdiag can natively collect performance monitor logs, SQL profiler traces, SQL Server blocking Script output, Windows event logs, and sqldiag output.

Pssdiag has two versions. One version supports SQL Server 7.0 and SQL Server 2000. The latest version supports SQL Server 2005 and SQL Server 2008, but you do not know how to support SQL Server 2012. This article takes SQL Server 2008 R2 as an example. After installing pssdiag, open the diagconfig.exe file to configure the trace to be crawled. There are many items that can be configured, but we have already selected many items by default. We can capture the trace according to the default configuration when we are not very clear about how to configure it. Shows the pssdiag configuration interface:

At the bottom, we can see the following description:

Select the diagnostics you want to collect and click the Save button to save your configuration. during the Save process,. cab file is created that can then be used for collecting diagnostic data after extracting the contents. this package will contain in the configuration file you 've just created as well as the scripts, utilities, and other files necessary to collect the diagnostic data that you need to modify.

This means that after we have configured the content to be captured, we click the Save button in the upper left corner and will ask where the configuration file is saved, as shown in:

We can see that the configuration file is saved in the build file, and another cab file is saved in the customer folder. This is because if we ask the customer to capture the trace, we do not need to send the entire pssdiag installation file to the customer, just need to pssd. the cab file is sent to the customer, asking the customer to decompress the file and then run the pssdiag. cmd. The pssdiag. CMD page of star is shown in:

After executing the above Batch Processing Command, we will see an output folder in the directory pssdiag \ customer \ pssd, we only need to let the customer package the Output Folder and send it to us. The main files in the output folder are as follows:

Sqldiag. BlG is opened using Performance Monitor, and the. TRC file is opened using SQL profiler.