By David Pless – firstname.lastname@example.org
In this article we are going to build on what we discussed in the ‘Utilizing the SQLDiag Tool’ article and discuss an add-in which can provide some very useful information called the ‘SQL Server 2005/2008 Performance Statistics’ scripts. SQLTWIST simply refers to it as the ‘PerfStats scripts’.
The ‘PerfStats scripts’ collects data for SQL Nexus to analyze. It also produces a text output with some very useful information. We will discuss the text output in this article. First we must download the ‘PerfStats script’ and discuss how it works. We will cover SQLNexus in a future article.
Where to download the ‘SQL Server 2005/2008 Performance Statistics’ script
You can download the PerfStatsScript2005.zip package for SQL Server 2005, the PerfStats2008.zip for SQL Server 2008, or the PerfStatsScript2008R2.zip for SQL Server 2008 R2 respectively.
To download click on the link below:
Once it is downloaded, all you need to do is unzip the download to a local disk on a SQL Server 2005, 2008 or 2008 R2 machine and run ‘StartSQLDiagTrace.cmd‘, ‘StartSQLDiagTrace2008.cmd‘, ‘StartSQLDiagTrace2008R2.cmd‘ or any other batch file depending on what what you need.
How does it Work?
The batch scripts call SQLDiag with a custom template. In the custom templates, there is a section called CustomDiagnostics where some SQL scripts are called to produce the Perfstats script output. The contents of the ‘PerfStats Script’ for 2008 R2is shown below. The XML documents replace the SQLDiag templates found in the following directory: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\
Below is the PerfStats download contents before executing one of the batch files
Just as we discussed in the previous SQLDiag article, the general XML file does not capture statement level data and the SHOWPLAN output that the detailed template does. The detailed template has slightly higher overhead than general profiler trace events. But it is useful if you want to identify statement level events. For example, if you have identified a stored procedure consuming high CPU you may want to focus on individual statements within that stored procedure.
The SQL Replay template is used to create a SQL trace output to replay it again.
“SQL Server Profiler features a multi-threaded playback engine that can simulate user connections and SQL Server Authentication. Replay is useful to troubleshoot an application or process problem. When you identify the problem and implement corrections, run the trace that found the potential problem against the corrected application or process. Then, replay the original trace and compare results.”
Below is a comparison of the PerfStats detail and the replay templates.
Below is the CustomDiagnostics section which all of the templates have:
<CustomGroup name=”MSInfo” enabled=”true” />
<CustomTask enabled=”true” groupname=”MsInfo” taskname=”MSINFO32″ type=”Utility” point=”Startup” wait=”OnlyOnShutdown” cmd=”get_msinfo.cmd %server% "%output_path%"” />
<CustomTask enabled=”true” groupname=”MsInfo” taskname=”FibreChannelInfo” type=”Utility” point=”Startup” wait=”No” cmd=”get_fcinfo.cmd > "%output_name%.txt" 2>&1″ />
<CustomTask enabled=”true” groupname=”MsInfo” taskname=”RegHardwareDesc” type=”Reg_Query” point=”Startup” wait=”OnlyOnShutdown” cmd=”HKLM\HARDWARE\DESCRIPTION” />
<CustomGroup name=”SQL 2008 Perf Stats” enabled=”true” />
<CustomTask enabled=”true” groupname=”SQL 2008 Perf Stats” taskname=”SQL 2008 Perf Stats Script” type=”TSQL_Script” point=”Startup” wait=”No” cmd=”SQL_2008_Perf_Stats.sql” pollinginterval=”0″ />
<CustomTask enabled=”true” groupname=”SQL 2008 Perf Stats” taskname=”RDTSCTest” type=”Utility” point=”Startup” wait=”No” cmd=”RDTSCTestLoop.cmd > "%output_name%.OUT" 2>&1″ pollinginterval=”0″ />
<CustomTask enabled=”true” groupname=”SQL 2008 Perf Stats” taskname=”SQL 2008 Perf Stats Snapshot” type=”TSQL_Script” point=”Startup” wait=”No” cmd=”SQL_2008_Perf_Stats_Snapshot.sql” pollinginterval=”0″ />
<CustomTask enabled=”true” groupname=”SQL 2008 Perf Stats” taskname=”SQL 2008 Perf Stats Snapshot” type=”TSQL_Script” point=”Shutdown” wait=”No” cmd=”SQL_2008_Perf_Stats_Snapshot.sql” pollinginterval=”0″ />
The batch files execute the SQL statements and SQLDiag with the new templates. Below is an example of the StartSQLDiagDetailed_Trace2008R2 batch file.
@REM To register the collector as a service, open a command prompt, change to this
@REM directory, and run:
@REM SQLDIAG /R /I “%cd%\SQLDiagPerfStats_Trace.XML” /O “%cd%\SQLDiagOutput” /P
@REM You can then start collection by running “SQLDIAG START” from Start->Run, and
@REM stop collection by running “SQLDIAG STOP”.
@rem the command below sets sqldiag.exe path. if your installation is different, adjust accordingly
@rem sql 2008 sqldiag.exe will be able to capture multiple platforms.
@rem any sqldiag will be able to detect 32 bit or 64 bit instances
set SQLDIAGCMD=C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLdiag.exe
“%SQLDIAGCMD%” /I “%cd%\SQLDiagPerfStats_Detailed_Trace2008.XML” /O “%cd%\SQLDiagOutput” /P
All we need to do to begin a capture is to execute one of the PerfStats batch scripts.
Similar to SQLDiag, the PerfStats script will collect blocking information, profiler trace data (each batch file will capture different events and StartSQLDiagNoTrace.cmd doesn’t capture any trace data), Perfmon, msinfo32, Windows event logs (system, security and application logs), and more. It is important to remember that the different batch files capture Profiler trace events differently, though everything else mentioned above will be captured exactly the same.
What makes the PerfStats scripts unique is the performance DMV data collected via the SQL2005PerfStats.sql and SQL2008PerfStats.sqlscripts. These scripts harvest DMV data startup, since SQL Server has been running, and shutdown which will show performance data during the capture. The 2008 version also captures procedure and trigger data with query_hash and query_plan_hash grouping.
When you start the batch file to collect the PerfStats data you will notice some new files created and a new directory called SQLDiagOutput. In this directory the usual SQLDiag data will be captured along with the PerfStats output.
In my example, I kicked off the StartSQLDiagDetailed_Trace2008R2.cmdbatch file. You will know that the PerfStats script is working when you see the following listed in the SQLDiag collection process:
Once you have reproduced your issue if working on a reactive problem or captured enough data for a proactive baseline click CTRL + Cto shutdown SQLDiag gracefully. If you close the command prompt window any other way, you will lose valuable data.
In this output directory look for the __SQL_2008_Perf_Stats_Snapshot_Startup.OUT and __SQL_2008_Perf_Stats_Snapshot_Shutdown.OUT files. In these files there is the Top N Query Plan Statisticssection which gives a ranking of the most expensive SQL Server statements by CpuRank, PhysicalReadsRank, and DurationRank. There are also columns for usecounts, size_in_kb, tot_cpu_ms, tot_duration_ms, total_physical_reads, total_logical_writes, and total_logical_reads.
There is a section for Missing Indexes which lists the Missing Indexes based on the improvement measurement. In these files there is the the current database options conviently documented. There is also statistics date and rowmodctr for indexes in all databases. This statistics information can give you some idea of which indexes have a high amount data change which might require special statistics maintenance.
There is the Resource Governor output new to SQL Server 2008. There is also sys.dm_database_encryption_keys, sys.dm_os_loaded_modules, and sys.dm_server_audit_status output.
At the end of the file is more SQL Server performance information:
- Top 10 CPU consuming procedures
- Top 10 CPU consuming triggers
- Top 10 CPU by query_hash
- Top 10 logical reads by query_hash
- Top 10 elapsed time by query_hash
- Top 10 CPU by query_plan_hash and query_hash
- Top 10 logical reads by query_plan_hash and query_hash
- Top 10 elapsed time by query_plan_hash and query_hash
Again, the query_hash and query_hash_plan information is only for SQL Server 2008 and above. The PerfStats script is a very beneficial addition to SQLDiag, it does not replace using DMVs, but can make ease of SQLDiag via templates even more powerful for reactive efforts as well as capturing a baseline of your SQL Server environment.
SQL Server 2005/2008 Performance Statistics collection scripts
Utilizing the SQLDiag Tool
UPDATE:The SQLDiag Configuration Tool