Hi, In our Production server, lot of Pakcages/Cubes are running 24/7. Now, my assignment is to find out CRU (Computer Resource Utilization) of the server, such as which package and which task of the package is running for more time, which one is taking much memory and all related details. I guess we can get this from Control Panel > Performace and Monitoring > Administrative Tools > Using this I am planning to create a batch file which will be sceduled for every 1 hour. I really appreciate if you guys give inputs on the appraoches for capturing those utilization and after that what can we do with those details like optimization and other stuffs? Thanks in advance!!!

Hi, Please refer to this whitepaper first: "Integration Services Performance Tuning Techniques". Kind regards, Zoli-- Zoltn Horvth -- MCITP SQL Server Business Intelligence Developer 2005, 2008 -- MCITP Database Developer 2008, Database Administrator 2008 -- Please mark posts as answered or helpful where appropriate.

http://msdn.microsoft.com/en-us/library/ms141687(v=SQL.90).aspx . Also did you look at this BIDS helper : http://bidshelper.codeplex.com/wikipage?title=SSIS%20Performance%20Visualization&ProjectName=bidshelperHappy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

you need to put some custom logging in your SIS package to see the execution details or you can achieve the same through the built in SSIS logging through OnPreExecute and OnPostExecute event handler. and for memory consumption you need to monitor your packages through performance monitor tool.following are the peformance counters which you should monitor while your package is running: •Buffers in use •Flat buffers in use •Private buffers in use •Buffers spooled •Rows read •Rows written for custom logging see this article (http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx) for memory consumption see this article(http://blogs.msdn.com/b/sqlperf/archive/2007/05/01/something-about-ssis-performance-counters.aspx) Cheers, Zaim Raza.

Hi, Thanks for your inputs.However, please understand that there are lot of packages which are running in PROD and in NO WAY i can touch it. I just have to log what is happening in Control Panel > Performance and Maintenance > Administrative tools > Performance ( Actually it is the full version of what we normally see in Windows Task Manager tabs 1) Applications 2) Processes 3) Performace and 4) Networking) . I agree i can not optimize anything by seeing this but I can just see what are the tasks/Packages are taking much time..thats it!!!

Hi Revathy Menon, I am afraid that you have mentioned may diffcult to achieved, please refer to the following links, and then check if they could help you. SSIS – Monitoring Performance by Using Performance Counters: http://bisherryli.wordpress.com/2010/08/13/ssis-monitoring-performance-by-using-performance-counters/ SSIS Performance Visualization: http://bidshelper.codeplex.com/wikipage?title=SSIS%20Performance%20Visualization&ProjectName=bidshelper Hope that helps. Thanks, Eileen

thanks Eileen :)