SQL Server 2005 Performance Statistics Script
Many of you have used the SQL Server 2000 blocker script (see KB 271509) to help troubleshoot performance issues in SQL 2000. If you haven't used it, you probably use a script of your own that takes a similar approach, polling sysprocesses and virtual tables to detect blocking and other resource contention issues. In PSS we’ve started using a new performance troubleshooting script for SQL 2005; we call this one our "Perf Stats Script", since it's useful for troubleshooting lots of issues in addition to blocking. The script takes advantage of the extremely rich set of data exposed by the new Dynamic Management Views in SQL Server 2005. We’ve found that the raw DMV output can be difficult for many people to interpret without a lot of help, so the script attempts to join the relevant DMVs together and present a simplified view of the most relevant data. If you like, feel free to check the script out and use it as a starting point when troubleshooting your own perf issues:
1. Download the PerfStatsScript.zip package attached to this blog post (there’s a link at the bottom of the post)
[update: The download link is removed. https://www.codeplex.com/sqlnexus has latested script and batch file. This is used as part of SQL Nexus tool for data analysis]
2. Unzip to a local disk on a SQL 2005 machine and run either StartSQLDiagNoTrace.cmd or StartSQLDiagTrace.cmd. The first of these will collect perfmon and perf stats script; the second one adds a basic Profiler trace.
3. Start this during a period of poor performance and let it run for at least 3 or 4 minutes.
These batch files run the perf stats script through SQLDIAG, which is installed on any SQL 2005 box. You can also run the script directly if you prefer.
Interpreting Perf Stats Script Output
The main script output will be called "server_instance_SQL_2005_Perf_Stats_Startup.OUT". Every 10 seconds the script will collect a fairly complete set of information about any active queries:
· The “Requests” resultset is a bit like sysprocesses, but on steroids. It will show quite a bit of detail (host name; app name; wait info; transaction details; last query start/end time; blocking relationships; CPU, I/O, and duration used by both the current query and the session; etc) for any session that has an active query.
· The “Notable Active Queries” resultset shows active query inputbuffers, plus historical execution statistics for the corresponding query plans.
· The “Head Blocker Summary” resultset is self-explanatory. You’ll only see this if you have blocking.
In addition, at a less frequent interval (60 seconds) a basic set of server-level performance statistics will be collected. This includes file-level I/O statistics, some key SQL perfmon counters, historical SQL and system CPU utilization, and global SQL resource wait statistics.
A second script will capture two snapshots (one at collection start, the second at collector shutdown) from the Missing Index DMVs (sys.dm_db_missing_index_group_stats, etc) and sys.dm_exec_query_stats. This script is nice in that it builds CREATE INDEX statments that you can run directly if you like. For certain workloads, it also is able to collect your top 50 most expensive queries (by CPU, physical I/O, and duration), without a profiler trace. For this data, look for an output file with the same name as the main script's output, but with a "_Snapshot" suffix.
Just to make sure that expectations are set properly, this script is just part of our internal PSS perf troubleshooting toolkit; it's not a supported part of the product.
Enjoy!
Bart Duncan