SSIS Performance 32 Bit V 64 Bit

SSIS Performance 32 Bit V 64 Bit

• Pascal_Mtl

  Sutha Thiru wrote:

  This issue has been resolved by hotfix from Microsoft. Thanks Sutha

  Superb work Sutha. You've done a great job!!!

  -Jamie

  
  
  

• James81

  What is the spec of your machine Memory, CPU, Disk drives

  Are you running the 32 bit or 64 bit version of SSIS I havent' got a 64 bit box but my understanding is that there is a different program files folder that contains the 64 bit binaries.

  i you have any drivers that you only have a 32 bit version for you have to run the 32 version of DTExec

  What does your package do Read files, sort, merge, send emails, load a DB

  
  
  

• tpbjs2006

  Well that way my thoughts too. I am sure some settings are wrong on my packages or something. I have got "Run64BitRunTime" set to "True". I dont know any other settings I should watch out when executing on 64 Bit.

  My laptop(dual core) has 2 GB of memory. This server has 16GB of memory. Server is 4 way Dual Core box.

  I have few things running on my laptop, where server is absolutely dead, nothing is happening on it. Only difference is that the source files, databases are on C: of my machine, where source files are on C: on the server, but the databases are on different disk and one of the source database (ODS) is on the different server all together.

  I am running exactly same packages on my laptop and dev server.

  My packages uses various componts such as OLE DB Source, OLE DB Destination, SQL Server Destination, Lookup, Merge, Sort, Aggregate, Script Task, SQL Task, Execute Package Task, For Each Loop, Data Conversion, Derived Column, RowCount, Union All, OLE DB Command, Conditional Split, Multicast and CHECKSUM.

  I audit my packages via event handler. I execute SQL Task to insert record into a table on PreExecute, PostExecute and OnError. I also use Checksum from Darren & Allan. I don't think that's the problem as everything is slow in general.

  Thanks

  Sutha

  
  
  

• Joe Burke

  yes! I am seeing the same thing - thousands of processes not being released under 64 bit SSIS and the pkg slows to a crawl and excutes way slower than on 32 bit. Please! whats the hot fix
  

• PatrickAltman

  Sutha Thiru wrote:

  This issue has been resolved by hotfix from Microsoft. Thanks Sutha

  
  
  
  Any ideas what specific components in a package might cause this
  
  I have a package which runs in about 7 seconds on my 32 bit machine, but it takes 95 seconds on our 64 bit server. Watching the output, it says Started: 1:46 PM, then nothing happens for a long time, then at the very end, all the status messages appear. It seems like the actual execution is very fast, but for some reason, there is an extremely long pause before anything happens. Is this similar to your experience
  
  

• Sahand

  Update

  I have written a simple package which moves series of files from source directory to working directory with 3 connections (in the package config file), which executes under 20 seconds.

  If I write exactly same package and have around 100 connections (in the another package config file) it takes over 5 minutes. This behaviour seems to be happening only on a 64 bit environment.

  I have logged a call and they have reproduced it and this may need to go to dev team.

  Thanks

  Sutha

  
  
  

• OktaEndy

  Brian

  Sorry for the delayed response. I was executing 64 bit dtexec from command from in Program Files not in Program Files (x86).

  I have logged a call with MS and I have got a simple project which re-produces the same scenario. If you wish to test, you can get the sample project from here.

  Thanks

  Sutha

  
  
  

• Mike Wasson - MSFT

  Simon

  My laptop is not as powerful as the server i am testing it on.

  I am running the 64 Bit version as I have only 64 bit installed on that machine. I am running the DTEXEC from the directory "C:\Program Files\Microsoft SQL Server\90\DTS\Binn". If you install 32 bit on 64 bit machine you will get the following directory structure "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn".

  I am executing a master package, which executes end to end daily load of the warehouse from various sources.

  Thanks

  Sutha

  
  
  

• Jit

  What type of 64 box are you on (x64 or Ia64) I have seen this same behavior myself. The main reason I saw the problem was I was previously using the 32 bit version of dtexec.exe. When you run the package, do you see it say in the header of the program, dtexec - 64 bit from the console window Both are installed on your box as you've probably found out. Sorry for the simple question but we have to start somewhere :).

  Other 64 bit gotchas here in this post:

  http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/05/12/165.aspx

  Brian

  
  
  

• admng

  What is the hotfix number What is the KB number I need to reference some documentation on this from Microsoft before my OPS team will let me intentionally call the 32-bit vice 64-bit DTExec.exe.

  thanks,
  Matt

  
  

• joy4u

  I wish do any trial like that. We are finishing to prepare 2005 production x64 server and we can to compare timing with the current one 2005 x32
  
  

• OdilonS

  My understanding is that 64 bit should run faster, so there must be contention somewhere.

  How much memory is on your laptop, in your server

  What else is running on your laptop and server

  Are they using the same data source and destinations

  What components does you package use

  Is our 64 bit server a multi proc server With a multi processor box the execution of a package is very very different to a single processor box due to the way threading can work. This means you can end up with many more active flows, each contending for resource. Imagine 10 people reading a book, on a single proc box the book is read by one person, then passed to the next who finds the page they are reading and reads, then passes to the next who finds their page etc. On a multi proc box they all try and read the same book at once with the possibility of conflict occuring. I have often seen things work quicker on a single proc box than on a multi proc box. i.e OPTION (MAXDOP1 ) in TSQL.

  
  
  

• schmidtl_dd

  This issue has been resolved by hotfix from Microsoft.

  Thanks

  Sutha

  
  
  

• batman____

  > If I rerun it on a 64 Bit machine with 64 Bit Win 2003, it takes between 21 and 24 mins.

  Try running it on same machine under 64-bit Windows, once using 64-bit dtexec.exe from "C:\Program Files\Microsoft SQL Server\90\DTS\Binn" and once using 32-bit dtexec from "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn". Is there a difference

   

  If 32-bit dtexec performs better, it is probably a performance issue with 64-bit drivers or 64-bit SSIS. If both perform slow on this machine, it is more likely an issue with machine hardware or software configuration.

   

  Also note that 64-bit execution is expected to perform somewhat slower (surprise, surprise) since the pointer size is twice as big and the processor has to move more data. 64-bit only has performance benefits (sometimes huge) if you need to process very large amounts of data that don't fit 32-bit address space. If 32-bit is enough for your the data, it might be reasonable to use 32-bit SSIS execution on 64-bit machines.

   

  > one of the source database (ODS) is on the different server all together

   

  Is it local in cases when the package is "fast"

   

   

  What are the CPU load and memory usage during the execution of this package In Performance Monitor, what is the "Average Disk Queue Length" for disks involved

  
  
  
• SSIS Performance 32 Bit V 64 Bit