We have a Server which houses Database Engine,Integration Server,Reporting Server,Sharepoint,(Planning for Analysis Server as well) This is a new server and nobody has yet complained except for few Sharepoint users(Dont know what was the issue but was not of big deal). There is a SSIS package which runs for around 10 mins every hour. This package simply extracts the data from many sources including SQL Databases,Flat Files and Webservices Finds if the record is inserted,updated or deleted and flags the record. It also performs some file processing tasks. I have been assigned to do the performance analysis on this server as we are going to deploy some reports on it.I was concerned with the SSIS Package running on it.Based on your experience how would you evaluate the SSIS package performance wise.Is it good/bad/OK ? At this time when I did the performance monitor only the SSIS Package was the major application running, nobody was running report or running any queries. When SSIS Package not running RAM : 48 GB Target Server Memory : 32 GB Total Server Memory : 23.6 GB Page Fault/sec (Average) = 445 Pages Output/sec (Average) = 0 Pages/sec (Average) = 0 Buffer cache hit ratio = 100 Avg.Disk Queue Length = 1 %Disk Time (Average) = 35 Current Disk Queue Length (Average) = .9 %Processor Time (Average) = 9 Buffers in use = 0 Flat Buffers in use = 0 Private Buffers in use = 0 Rows read = 0 Rows Written = 0 Buffers spooled = 0 Bytes Total/sec = 4800000 Bytes Total/sec = 23900000 Bytes sent/sec = 4600000 Bytes sent/sec = 12000000 Current Bandwidth = 1,000,000,000 Current Bandwidth = 1,000,000,000 Private Bytes = 3.116e+010 Working Set = 3.0566e+010 Available Bytes = 1.8291e + 010 When SSIS Package is running RAM : 48 GB Target Server Memory : 32 GB Total Server Memory : 23.6 GB 24781312 Page Fault/sec (Average) = 10000 Pages Output/sec (Average) = 0 Pages/sec (Average) = 101 Buffer cache hit ratio = 100 Avg.Disk Queue Length = 2.4 %Disk Time (Average) = 75 Current Disk Queue Length (Average) = 2.3 %Processor Time (Average) = 33 Buffers in use = 0 Flat Buffers in use = 0 Private Buffers in use = 0 Rows read = 0 Rows Written = 0 Buffers spooled = 0 Bytes Total/sec = 30800000 Bytes Total/sec = 39000000 Bytes sent/sec = 8000000 Bytes sent/sec = 19000000 Current Bandwidth = 1,000,000,000 Current Bandwidth = 1,000,000,000 Private Bytes = 3.1757e+010 Working Set = 3.1074e+010 Available Bytes = 1.8291e + 010 Any suggestion would be highly appreciated. Thanks,

Basically, when you have SQL Server by default it grabs all the memory available, I would limit this to somewhat lower value. To assess the performance of a SSIS package I can suggest DTLoggedExec fromhttp://dtloggedexec.codeplex.com/ It can help create perf reports like: Arthur My Blog

Thanks alot for the link. I will work on DTLoggedExec. Before that is it possible to make a approx. judgement on how the package is performing based on the counters I provided. Max Server memory has been set to 32GB. So, as per my understanding SQL Server wont take more than that. As the Total Server memory is less than that I think 32GB is more than enough for sqlserver.exe. Thanks,

Just for info... Here are some simple (to implement) package performance improvers.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

@SSISJoost -- Thanks for the info. I looked through the package and figured out that the package is following all the best practices that your link provides. I am just concerned if the performance counters that I provided above seems to be good/bad/OK ? I am doing it for the first time in my life, so needed some opinion from experts whether the numbers seems ok ? Or, shall i be looking at completely different counters and these counters doesnt make any sense for the SSIS Packages? Thanks,

As a general guide line if the performances are at or below 75% of your server resources all is good (this is what I remember from one of the certifications). Please use this as your guide. I believe also that due to the nature of ETL to handle different volumes of data and operate in tandem with SQL Server and other applications it is really hard to tell what suffocates what and what thus what would be acceptable and/or not. At my workplace I benchmark a machine and then compare later to find out if a degradation started to occur.Arthur My Blog