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 and sql 2008 installation) to collect data for SQL Nexus to analyze. It can collect profiler trace, blocking and DMV output.
Where to download?
Download the PerfStatsScript2005.zip package for sql server 2005, PerfStats2008.zip for sql server 2008 and PerfStatsScript2008R2.zip for SQL Server 2008 R2. To download this file, click on the Page Info
link at the top this page and download from File Attachment
section (right hand portion of the page).
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?
- 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)
- Windows event logs (system, security and application logs)
- 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"....