Share via

SQL Server 2005 Performance Statistics Script

  • Article

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

Comments

  • Anonymous
    July 19, 2007
    The missing index DMVs are a really cool new feature in SQL Server 2005 that, in my opinion, have been
  • Anonymous
    July 19, 2007
    The missing index DMVs are a really cool new feature in SQL Server 2005 that, in my opinion, have been
  • Anonymous
    January 22, 2008
    SQL Server 2005 has some DMVs that will help you tune the system. The missing index DMVs track recent queries that could have benefited from an index that didn't exist. ...
  • Anonymous
    April 09, 2008
    Last week Graham was guest blogging on my blog about PSSDiag and how to gather data with PSS Diag. In
  • Anonymous
    April 14, 2008
    If you ever talk to me or any of my colleagues in the SQL Server PSS support team, and you have a suspected
  • Anonymous
    May 25, 2008
    The comment has been removed
  • Anonymous
    June 06, 2008
    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 virtua
  • Anonymous
    January 22, 2009
    Often times, our team is confronted with SQL Server performance issues and in most of those times, the
  • Anonymous
    June 02, 2009
    Anyone has a detailed maunual for SQLDIAG and PSS?if so,gave me a copy,please,thanks.sqysl@sohu.com
  • Anonymous
    June 11, 2009
    I refer to this many times in my other posts, so here’s a direct link to the download location of the
  • Anonymous
    November 27, 2017
    The comment has been removed