PAL and SQLDiag with SQL Server 2012

Published 24 May 12 11:56 PM | MartinBell

We’ve had a great week at SQL Relay so far I have organised one event and helped run a second and spoken at 2 others. I still have presentations at the final legs in London to go and I'm looking forward to them. As part of my presentation I talk about running PAL with SQL Server 2012. The SQL 2005/2008 threshold file can be used against a server running SQL Server 2012, so your Performance Data Collection Set that is imported from the PAL threshold file is still ok.

If you don’t like using logman to start your Performance Counter collection (real DBA don’t use the Perfmon GUI Smile), then you may look towards SQLDiag to collect your SQL Trace and Performance Counter data. Anyone brave enough to hand craft their own SQLDiag configuration files are hopefully already seeking counselling. I use the SQLDiag Configuration Tool (SDCT) available on codeplex. Unfortunately the last time the project was updated was 20 May 2011 and does not list SQL Server 2012 as a supported option. Luckily the only issue I have found with the generated configuration files is the need to change the SQL Server version number for (say) 10.50 to 11. i.e. the line like:

<Instance name="*" windowsauth="true" ssver="10.50" user="">

changes to:

<Instance name="*" windowsauth="true" ssver="11" user="">

PAL will collect around 71 performance counters for a single instance of SQL Server. Configuring these with SDCT is still quite a tedious job, so I created a template which includes all the counters in the PAL Threshold file. A copy of a template for SQL Server 2008 _ss10_PAL.xml can be found here. Copy this file into the appropriate perform template directory e.g. C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\PerfmonTemplates\10 and the file will appear in the list of templates available in the drop down box.



Unfortunately I don’t know of a way to add additional buttons to the front screen, but you can change the underlying configuration file e.g. SQLDiag1050.XML for SQL Server 2008 R2 in C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\Configurations. If you wish to generate a SQL Server 2012 configuration files without having edit the configuration file afterwards you will need to replace one of the existing configuration files. A default SQL Server 2012 configuration file can be found here, this contains all the new Perfmon counters for SQL Server 2012.

Because this is a new version you will need to create two template directories e.g.:
      
C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\PerfmonTemplates\11
C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\TraceTemplates\11

You may just want to copy the corresponding …\10 directories. A SQL Server 2012 specific Perfmon template (_ss11_PAL.xml) can be found here.

If you get the following error dialog, then you need to create the appropriate template directories:



This configuration will also collect the SQL Trace completion events which are useful when aggregating information from trace files when pinpointing poorly performing code.



It's interesting that the new configuration file is recognised as being for SQL Server Denali.

The profiler template (_Performance_Aggregation.xml) to collect this information can be found here
 place it in the appropriate TraceTemplates directory.

Filed under: , ,

Comments

# PAL and SQLDiag with SQL Server 2012 | My Love with SQL Server | Scoop.it said on May 25, 2012 05:44 AM:

Pingback from  PAL and SQLDiag with SQL Server 2012 | My Love with SQL Server | Scoop.it

# Establish the baseline for SQL Server Performance tuning using Perfmon and PAL | My Blog said on October 2, 2014 10:05 PM:

Pingback from  Establish the baseline for SQL Server Performance tuning using Perfmon and PAL | My Blog

This Blog

SQL Blogs

Syndication