Skip to content

Data Collection Templates

Jump to bottom
Shannon Barrett edited this page Mar 17, 2017 · 3 revisions

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 SQL_2005_Perf_Stats.sql or SQL_2008_Perf_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)
  1. 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 and 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>
Clone this wiki locally