Rambling on about 64 bit issue I had...

I have done various tests. I developed a simple package (which moves files from source directory to working directory) to test timing against RTM, and the same process against SP1 and then Hotfix. The differences were negligible, between different builds. So I opened my original package and disabled all the tasks except the one I have built in the simple package and executed it and the difference was huge.

Simple package took around ~ 18 seconds to moves files etc where my master package took around ~ 270 seconds. Infact it was 15 times slower.

Then I went back drawing board as they say and looked at what was going on, specially what are the differences between simple package against the master package originally developed. My first instinct was event handlers and I disabled them and the performance was negligible . Second thought was transaction and that didn't make any difference. Next thing came to my mind was checkpoint and that wasn't an issue either. At this point I was going mad.....!!!!!!

While checking both packages visually I noticed something quite different. The simple package has only 2 connections where the master package had quite a few (close to 100 connections). Then I reproduced it and it seems to be the problem.

So, my initial conclusion is that Execute Package Task seems to be resolving package configurations each time it loads / execute a package on a 64 Bit environment. The behaviour seems to be totally different on a 32 bit environment.

We have logged a call with MS about this.

Keep you posted.

Thanks
Sutha

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

UK BI UserGroup Meeting

Last night we had a successful first BI user group meeting at Microsoft Reading. Turn out for the meeting was excellent. Most of the BI consultancy representatives were there.

At last I have met people like Jamie Thomson and Christian Wade face to face after speaking to them for a long time on the web.

Chris Webb, Mark Hill and Simon Sabin presentations were excellent. Hopefully we can have more sessions like this.

Slides and sample codes from this session will be available soon @ SQL Server User Group site.

Thanks
Sutha

Upgrade to SQL Server 2005

If you are going upgrade from SQL Server 7 / 2000 to SQL Sercer 2005 you are highly recommended to use the following tools before you upgrade.

You have to run the Microsoft Upgrade Advisor which can be downloaded free. Microsoft SQL Server 2005 Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

Scalability Experts have developed a great tool, which helps you during upgrade process. You can download the SQL Server Upgrade Assistant for free. This tool analyses the impact associated with upgrading SQL Server 2000 installation to SQL Server 2005.

Thanks
Sutha

Cross database ownership chaining

One of the Gotcha when you migrate a database from SQL 2000 to SQL 2005 "Cross database ownership chaining" is turned off by default.

You would need to enable it manually once you have restored the database.

This option is available under "Server Properties" in "Security" tab.

You can either Execute the SQL below or you can change the server property using Management Studio.
EXEC SP_DBOPTION "(DB Name)","DB CHAINING", "TRUE"

If you wish to check is the database chaining is ON / OFF you can using the following statement
EXEC SP_DBOPTION "(DB Name)","DB CHAINING"

Thanks
Sutha

SQL Server 2005 Directory Structure

Nothing too serious, it is something everyone need to be aware.

Generally if you have full / default installation you will get the directory structure like shown below:










When you conduct default installation SQL Server (data / backup etc) will be installed MSSQL.1 directory. Analysis Services related stuff in MSSQL.2 dorectory and Reporting Services related stuff in MSSQL.3 directory.

What you have to be aware is that for example if you just install Analysis Services on a new server it will install under MSSQL.1 directory. On the other hand if you install another instance of SQL Server, Analysis Server and Reporting Server in the above scenario, they will get MSSQL.4, MSSQL.5 and MSSQL.6 directory as 1,2 and 3 already exists as shown above.

Specially you have to be aware if you use within your application / reports where Reporting Services sits etc.

The directory names would differ between servers depending on installation.

Thanks
Sutha