SSIS Performance testing is one grey area is MS BI, as per my views. I had worked on a project that contained almost 100+ ETL packages, and we were badly struggling with performance issues. Most of it had to do with connection usage methodology and architectural mistakes, but we had no way to figure out where really is the performance issue.
Logging data is one of the ways by which the performance bottlenecks can be figured out. But just by looking at the logs, it's not that crystal clear that one can make out the exact cause of bottleneck.
We had a load test environment for testing of SSIS packages. Voluminous data can be created using DB Pro data generators, and the statistics can be collected from perfmon windows tool for execution analysis. Still I believe that this is not the correct tool to measure SSIS performance. Microsoft doesn't provide any out of the box tools for load or performance testing of packages. I believe that using WMI, package execution can be instrumented in a more precise manner than using perfmon.
Recently I came across a tool from the community blogs for this very purpose. It's called DTLoggedExec. BTW, DTExec and DTExecUI is the tool that is used to executed packages. DTExecUI tool gives you a UI to configure all the options that are availabe with this tool.
DTLoggedExec tool can be downloaded from Codeplex along with the source code. On the face value, by and large the tool seems quite interesting. But honestly speaking, I have not found time enough to test and evaluate the potential of this tool. If anyone has used this tool, please feel free to post comments on this post so that the community can benefit from the feedback and user reviews.
Below are some of the screen excerpts that the tool offers:
Article 
No comments:
Post a Comment