Performance profiling your custom extensions

First published on MSDN on Nov 12, 2008

The Team Development Edition and Team Suite versions of Visual Studio 2008 comes with a performance profiling feature that can used to find bottlenecks in your custom extensions (tasks, data flow components, etc) for SSIS. Although having your extension assemblies in the GAC make profiling a little more involved, it can still be done with a few additional steps, and I’ve found it to be a very effective way to improve the performance of your code.

In this post I’ll walk through how to use this tool on a custom Data Source Component (in this case, the source component for the SQL Data Services connectors on Codeplex ).

The basic steps are:

1. Configure your extension for debugging
2. Create a profile
3. Delay sign and disable strong name validation for your extension assembly(s)
4. Manually instrument your assembly(s) and add them to the GAC
5. Run the profile
6. Enjoy the results

Read on for more details.

Configure your extension for debugging

The first step is to setup your extension project to be easy to debug.

1. Create an SSIS package which makes use of your extension.

I’ve created a simple package which uses the SSDS Source, and pipes it to a row count transform.

2. Open the solution for your extension. Right click on the Project, and configure the debugging settings to launch the package using dtexec.exe.

Using dtexec to run your package makes it a lot easier to debug than it would be running it inside of BIDS. If you’re on a 64bit platform, be sure to select the 32bit version of dtexec (under Program Files (x86)), as the performance profiler has issues with 64bit executions.

Specify the path to your package using the /f switch. If you need to specify other command line parameters, you can use dtexecui to build your command line for you.

Once you have your project configured for debugging

Configuring the Performance Profiler

1. Launch the Performance Wizard (Analyze –> Launch Performance Wizard…)

2. Select your extension project, and click next.

3. On the next page you can select the type of profiling you’d like to do.

Both can be useful, but “Instrumentation” will give you a detailed report of which functions are being called most, and how much time is being spent in them. For SSIS extensions, this is probably what you’ll want to see.

After finishing the wizard, you’ll see the Performance Explorer window, with a new Target added for your project. If your extension relies on additional assemblies, you’d add them as additional targets by right clicking on the Targets folder, and selecting one of the Add options. In my case, the SSDS Source component makes heavy use of a Connectivity layer assembly. To get a full performance profile, I’ll instrument both of them.

Instrumenting your assemblies

If you tried to do a profiled run now (by clicking the Launch with Profiling button, or Analyze –> Profiler –> Launch with Profiling from the menu), you’d get the following warning dialog:

When running in Instrumentation mode, the profile will need to modify your assembly to insert the hooks it needs. This will invalidate the signature on your assembly. This becomes an issue if your assembly needs to be in the GAC (like with SSIS), as the GAC will only accept properly signed assemblies.

If you click ok and run anyways, dtexec will run without error, but no data will be collected because your extension assembly in the GAC wasn’t profiled.

To get around this, we’ll need to do a few things.

1. Delay sign our assemblies
2. Disable strong name validation for your assembly
3. Manually instrument the assembly(s) using the vsinstr.exe application
4. Add the instrumented assemblies to the GAC

To delay sign the assembly, check the “Delay sign only” box on the Signing tab of the project properties page for your extension project. Do the same for any other extensions required by your

To disable strong name verification for your assembly (which should only be done in your dev/test environment, and never in production) we’ll use the Strong Name Tool (sn.exe) which comes with the .NET framework SDK. If you’ve installed SQL Server 2008 on your machine, you’ll find it under C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin.

From the command line (must be elevated on Vista), run sn.exe -Vr <extension>.dll

C:\code\source\bin\release>sn -Vr Microsoft.Samples.DataServices.Source.dll

Microsoft (R) .NET Framework Strong Name Utility  Version 2.0.50727.1433
Copyright (c) Microsoft Corporation.  All rights reserved.

Verification entry added for assembly 'Microsoft.Samples.DataServices.Source,DA625E43F8E8D37E'

Next we’ll instrument our assembly(s) using the vsinstr.exe tool. From the command line, run vsinstr.exe <extension>.dll

C:\code\source\bin\release>vsinstr.exe Microsoft.Samples.DataServices.Source.dll
Microsoft (R) VSInstr Post-Link Instrumentation 9.0.30729 x86
Copyright (C) Microsoft Corp. All rights reserved.

File to Process:
C:\code\source\bin\release\Microsoft.Samples.DataServices.Source.dll -->
C:\code\source\bin\release\Microsoft.Samples.DataServices.Source.dll
Original file backed up to C:\code\source\bin\release\Microsoft.Samples.DataServices.Source.dll.orig

Warning VSP2001 : C:\code\source\bin\release\Microsoft.Samples.DataServices.
Source.dll is a strongly named assembly.  It will need to be re-signed before it can be executed.
Successfully instrumented file C:\code\source\bin\release\Microsoft.Samples.DataServices.Source.dll.
Warning VSP2013 : Instrumenting this image requires it to run as a 32-bit process.  The CLR header flags have been updated to reflect this.

Now add the instrumented assembly to the GAC. This can be done using the Global Assembly Cache Tool (gacutil.exe), which can be found in the same directory as sn.exe. From the command line, run gacutil.exe /i <extension>.dll

C:\code\source\bin\release>gacutil /i Microsoft.Samples.DataServices.Source.dll

Microsoft (R) .NET Global Assembly Cache Utility.  Version 2.0.50727.1433
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

These steps should be repeated for each assembly you’d like to profile.

Note: since you’ll most likely need to run the profiling more than once, I’d suggest you create a batch file which runs the VS instrumentation tool and adds the files to the GAC.

Launching with profiling

Once the instrumented assemblies have been placed in the GAC, you can run launch a profiled run (click the Launch with Profiling button, or Analyze –> Profiler –> Launch with Profiling from the menu). You’ll still get an instrumentation warning, but this time when you click ok, you’ll get the profiling results.

From the summary page you can drill down to see which functions are being called most, and which are taking the most time. From there you can begin to optimize your code and fix your bottlenecks.

Remember that you need to re-instrument and re-GAC your assembly anytime you modify it.

Be sure to check out the Performance Profiler MSDN article for more details on the features of the profiler.