SQL Server 2005/2008/2008R2/2012 Performance Statistics collection scripts

The attached batch file, TSQL Scripts plus sqldiag configuration file (XML) can be used together with sqldiag.exe (shipped as part of SQL Server 2005/2008/2008R2/2012/2014 installation) to collect data for SQL Nexus to analyze. It can collect profiler trace, blocking and DMV output.

Where to download?

The batch files used to collect data is now part of the main download. Once unzipped, you will see "data collectors".

How to Use it?

Unzip to a local disk on a SQL 2005, 2008 or 2008 R2 machine and run SartSQLDiagTrace.cmd, SartSQLDiagTrace2008.cmd, SartSQLDiagTrace2008R2.cmd or any other batch file depending on what what you need. See Next Section on what to collect.

What will each batch file collect?

  1. All the batch files will collect these:
    • Performance scripts by SQL2005PerfStats.sql or SQL2008PerfStats.sql. it will capture DMVs and blocking information to help troubleshoot performance problems blocking, high CPU etc.
    • Profiler trace (each batch file will capture different events and StartSQLDiagNoTrace.cmd doesn't capture any trace)
    • Perfmon
    • msinfo32
    • Windows event logs (system, security and application logs)
  2. Different batch files capture profiler trace events differently. Everything else mentioned in the above section will be captured exactly the same.
    • StartSQLDiagTrace.cmd, StartSQLDiagTrace2008.cmd and StartSQLDiagTrace20082.cmd: they capture general profiler trace events. This is the most commonly used and is sufficient to do initial troubleshoot performance problems.
    • StartSQLDiagDetailed_Trace.cmd, StartSQLDiagTrace2008.cmd and StartSQLDiagTrace2008R2.cmd: They capture detailed profiler trace events. This has slightly higher overhead than general profiler trace events. But it is useful if you want to identify statement level events. For example, you have identified a stored procedure consuming high CPU. not you want to focus on individual statements within that stored procedure.
    • StartSQLDiagForReplay.cmd, StartSQLDiagForReplay2008.cmd and StartSQLDiagForReplay2008R2.cmd: They will capture profiler trace events needed for readtrace to do replay. Replay is used if you want to simulate production workload in your test system.
    • StartSQLDiagNoTrace.cmd: This one won't capture profiler trace. Since it's not used that often 2008 doesn't have one.

How to customize it?

  • In general, you don't need to customize it at all. You just run it on the machine that has sql server running. it will discover all instances on that machine or cluster and start collecting data.
  • If you want to capture for a specific machine or instance, edit " <Machine name="."> and <Instance name="*"" in the corresponding xml. Each batch file has a /I parameter that indicates which XML the batch file uses. for example, if you have a cluster Virtual Server SQLVIR and instance name instance1, you will put <Machine name="SQLVIR"> and <Instance name="instance1" ..... If you have a cluster default instance on virtual server SQLVIR, then you would use <Machine name="SQLVIR"> and <Instance name="MSSQLSERVER"....


Last edited May 14 at 7:37 PM by jackli, version 36

Comments

HotRats Jul 3 at 7:29 PM 
There are other useful SQLDiag arguments - see http://msdn.microsoft.com/en-us/library/ms162833.aspx.
For example, adding /L /E +00:200 /R /Q to your .cmd file registers (/R) sqldiag as a service (that way you don't have to keep the command window open), restarts SQLDIAG every 20 minutes (/L /E +00:20), and quietly (/Q) overwrites the old output (every 20 minutes). The arguments /L /E +00:200 /R /Q are useful when you don't know when the problem will start and/or a system may not have enough disk space (for a SQLDiag that traces). Also consider /N and /C.

One thing that a correctly configured pssdiag configuration manager's pssdiag will (that these scripts won't) create is DeleteOldTrcFiles.cmd.

nflash Nov 20, 2013 at 6:53 PM 
Ok, I've found the problem :) For some reason and for the first time I was trying to run this in a folder inside the Desktop folder... Once I move the folder to the C: root it worked. I think it might have something to do with the length of path (just guessing).

nflash Nov 20, 2013 at 6:37 PM 
Hi, I've already used the SQL 2008 R2 scripts several times, however today I tryed to use the SQL 2008 scritps for the first time and it doesn't create any .trc file. Can anyone help me?

Farshid1723 Mar 1, 2013 at 8:39 PM 
To download script files, Click Page Info on the navigation bar and go to the File Attachments section . Download the relevant version

deepaksql Jul 13, 2012 at 11:17 AM 
Can any point me the correct link to download the Perfstats script for sql 2008

karabela Jun 25, 2012 at 9:45 PM 
when will be the sql2012 perstatscript available?

flyingfdk11 May 28, 2012 at 10:07 AM 
Where to download sql server 2005 and PerfStats2008.zip ,this Page Link is lost

crossman May 11, 2011 at 6:30 PM 
How do I replay the trace when using SQLDiag with the replay trace XML input? This isn't creating a trc file from what I can tell.

peregrino Apr 27, 2011 at 11:20 PM 
How can I restrict the trace to record the activity of one or two databases (not all), using the templates that are downloaded from the site of SQL Nexus (SQLDiagTrace.xml, SQLDiagTrace2008.xml and SQLDiagTrace20082.xml)?

jackli Apr 6, 2011 at 2:57 PM 
We have various reports that using 2008 batch files fails to find SQL server 2008 R2 instance. I have put up a new zip file PerfStatsScript2008R2.zip that will work with SQL Server 2008 R2 instance. Click on PageInfo on this page and you will find a third zip file.

kppullin Jan 22, 2011 at 6:53 PM 
The batch files for the 2005 package are still appear to be broken (the extra path quotes need to be removed).

jackli Jan 13, 2011 at 3:14 PM 
A user kindly pointed out a bug in the batch files for SQL 2005. Fixed the issue and uploaded the new zip file for 2005.

Alkiris May 31, 2010 at 9:57 AM 
You should use PSSDIAG instead for SQL2000

qadave Mar 10, 2009 at 9:45 PM 
I need to run this on a SQL2000 box. What scripts can I use for SQL2000.