Thursday, March 20, 2008

SSIS Performance Counters don't work on x64 (out of the box)

If you want to spend some time tuning performance of your SSIS solution, it is likely that at some point you will leverage the SSIS performance counters found in the SQL Server:SSIS Pipeline object.  However, if you are using 64-bit SQL, you will not find the performance counters listed in PerfMon.  That is because here is a known bug in SQL Server 2005 x64, which you can read about in KB 941154.

What is the problem?  The short version is: When SQL is installed, it accidentally points to the \Program Files (x86)\ directory for the SSIS PerfMon counters, except that they are actually in the \Program Files\ directory.

How do I fix this?  Its easy, but requires a reboot.  Here are the instructions, straight from the KB linked above:

To work around this problem, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.

2. In Registry Editor, locate the following registry entry:


3.Double-click the Library registry entry.

4.In the Edit String dialog box, type the following string value in the Value data box:

DriveLetter:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSPipelinePerf.dll

5.Restart the computer.

UPDATE: This fix does not work as described in the KB

Even though there is a DTSPipelinePerf.dll file at the path given in KB 941154, simply pointing to it in the registry setting and rebooting does not make it visible.  I was able to get it to work using Plan B: change the path back to \Program Files (x86)\ and use the 32-bit version of PerfMon.

There are two ways to use the 32-bit version of PerfMon:

1.) Start | Run | mmc.exe /32 perfmon.msc

2.) Execute c:\windows\sysWow64\Perfmon.exe

1 comment:

Anonymous said...

This was helpful but found a way to get everything working with another KB article. See