BIDS 10x faster than DTExec!?
Hello, When I run a package on my local computer with BIDS, it runs in 50 seconds. After deploying this package to our SQL Server 2008, the same package runs for up to 10 minutes! This is with all logging available. I can post this if requested (+2000 lines). When I check these logs, I notice that all transformations take more time. However, a lot of extra time is spend in a Pivot transformation. Logextract for server run: The component "PVT_params by carrier" (25) spent 0 milliseconds in PostExecute. The component "PVT_params by carrier" (25) spent 0 milliseconds in PreExecute. The component "PVT_params by carrier" (25) spent 0 milliseconds in Validate. The component "PVT_params by carrier" (25) spent 0 milliseconds in Validate. The component "PVT_params by carrier" (25) spent 33504 milliseconds in ProcessInput. The component "PVT_params by carrier" (25) spent 84465 milliseconds in PrimeOutput filling buffers on output "Pivot Default Output" (27). Logextract for local run: The component "PVT_params by carrier" (25) spent 0 milliseconds in PostExecute. The component "PVT_params by carrier" (25) spent 0 milliseconds in PreExecute. The component "PVT_params by carrier" (25) spent 0 milliseconds in Validate. The component "PVT_params by carrier" (25) spent 0 milliseconds in Validate. The component "PVT_params by carrier" (25) spent 6580 milliseconds in ProcessInput. The component "PVT_params by carrier" (25) spent 9310 milliseconds in PrimeOutput filling buffers on output "Pivot Default Output" (27). I also notice messages like this for my server run: Buffer manager is throttling allocations to keep in-memory buffers around 214MB Memory pressure was alleviated, buffer manager is not throttling allocations anymore During last execution the pipeline suspended output "OLE DB Source Output" (11) of component "OLE_SRC_MWare_TCARRIERPARAM + TPROTOCARRIERPARAM" (1) for 28687 milliseconds to limit the number of in-memory buffers. My desktop has 2Gb of memory. Our server has 3Gb of which max 2Gb is allocated to SQL Server. It is a virtual server. BTW: does anyone know of/have an analysis module on top of the standard SQL Server logtable? Any idea what might cause this? Tjomme
February 15th, 2011 3:46am

http://blogs.msdn.com/b/mattm/archive/2011/01/15/why-does-my-package-run-slower-in-bids-than-dtexec.aspx
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2011 4:10am

Thats an interesting observation. What are the tasks and transforms used in your package. And what are the resources you are uing? How loaded is the Server on which you are running thePackage? Could there be any network issue(I feel this could be the cause)??My Blog | Ask Me
February 15th, 2011 4:45am

Its most likely a memory issue - SSIS will be competing with SQL Server and any other server process. It may also be a simple fact of processing power. Your virtual server may simply be underpowered compared to your desktop. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2011 11:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics