SQL Server Business Intelligence

SQL SERVER 2005 & 2008 IS THE MAIN THEME OF THIS BLOG

Friday, June 23, 2006

SSIS Performance on 64 Bit Sucks....

I have been working on a project last few months. I have developed the project on my laptop, which is resonably powerful. It runs through fine within 9 mins with sample data set. If I replicate the same environment on a 64 Bit machine with 32 Bit Win 2003 and SP1, it takes just over 7 mins. If I rerun it on a 64 Bit machine with 64 Bit Win 2003, it takes between 21 and 24 mins. Executing the packages via dtexec on a command prompt.

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.

We are trying various options and we are speaking to Microsoft as well. As we uncover more things I will update my blog.

Thanks
Sutha

0 Comments:

Post a Comment

<< Home