DTSX Package Running Slow

Posted on 2010-03-24
Medium Priority
Last Modified: 2012-05-09
I am a sys admin with what appears to be an app problem. I want to start with that is is NOT a resource problem (well actually it is, but there are plenty of resources the app just isn't using them). It is SSIS running on Windows and SQL 2008 server. The problem lies in only one package for this one app. When the dtsx package is run on the server it takes forever to run compared to how the same package runs on the 2003 box. What it boils down to is that it appears the package is not taking the resources it needs to run so even validation is very slow. It barely takes 100k of memory and chugs. So I am wondering if something different needs to be done to the dtsx package to run on Windows 2008 64-bit. The BI app person is convinced it is the OS. The SQL team doesn't think it is their problem. I am an OS person, not an app person or a DBA. Any thoughts would be appreciated.
Question by:susantoo
LVL 30

Expert Comment

by:Reza Rad
ID: 28516415
It's better to attach screenshots of package, or upload the package.
LVL 22

Expert Comment

ID: 28571035
I'm not sure what having the package would do for us with regard to assisting the OP.  If we can't simulate the server and database, how can we really even look at the package and get anything meaningful from it?
  • What else is running at the same time as the package?  
  • Is the WnServer 2003 box a development box and, therefore, the database being hit a development database?
This could be the result of settings in the SQL Server Instance that constrain how much memory and other resources SQL Server 2008 can consume.  
Also, someone might want to check the query plans for any queies the package is using but do it on the server where the package runs slowly . . . doing it on another box will give you what it would be on that box and not the server.

Expert Comment

ID: 28659089
If validation is slow maybe it's a problem with connecting to data sources used in the package?

best regards
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why


Author Comment

ID: 28704852
I agree about the package, it won't do you any good, plus it is proprietary and I can't post it. This package runs fine on the server this new server is replacing. The package is run locally on the server and it is the only thing this server has going on about 0% utilization across the board on all critical systems with the exception of 14% utilization on memory. When this package runs there is hardly a blip in resource consumption. There are no network errors. I pointed back to the known good server and tried to run it, thinking the package might be corrupt, but received the same result with the known good package. I turned off SAV and no change. The package is the same on both servers, pointing to the same datasources. It continues to be slow even after validation. It is a quad CPU, quad core with 32GB of memory running Windows 2008 SP1 64-bit.

I asked about those SQL questions and they said they are fine, but I have the app person checking again. I asked her about SQL patch level and she said the SQL team did not let them put CU3 on it, while it is on the other server, she doesn’t seem to think that is an issue. I also asked her that maybe the package needs to be recompiled, so she is checking into that. I asked the SAN team if the drives are configured the same on the backend, but I haven’t heard back yet. The app person’s connection to MS will not be back until Tuesday.

Expert Comment

ID: 29370631
Windows 2008 has TCP auto tuning enabled by default to adjust the data size this server receives depending on the size of data transferred by TCP. Disable it by the following command:
netsh interface tcp set global autotuninglevel=disabled

Accepted Solution

susantoo earned 0 total points
ID: 29398586
This was solved yesterday with workaround #1 from this  KB

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

OfficeMate Freezes on login or does not load after login credentials are input.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Suggested Courses
Course of the Month3 days, 4 hours left to enroll

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question