Everything slower with SP2?

I installed SP2 two days ago and it seems like my SSIS-packes now take longer time than before - the very opposite of what I was hoping for.

Anyway, here are some data from runs on our performance environment. No new data is added to the source database between the runs, but I do a full process of the cubes every time (time is in seconds):

Package...............................SP1...............................SP2

Load dimensions..................200.................................270

Load fact data.......................800...............................1600

Process cubes....................2100...............................2600

So, as you can see, everything is going slower with SP2. I have yet to look into if there are any specific steps in the packages that take longer time than before, but it's odd that all packages take a longer time to execute. Especially that cube processing is slower suprises me.

Has anyone experienced something similar? Thanks!

Are you using SSIS Logging? SP2 added some diagnostics messages for logging database connection and command information. If this causes performance issues, you may want to unselect logging information messages.

|||It's interesting that processing the cubes slows down. That would indicate to me that it might be the relational engine is running slower. Tough to imagine how any potential change in SSIS could impact that process. Are you able to separate processing time from package run time, to rule out a long validation phase or something?
|||

I did discover that there is a lot more logging going on in SP2 - at least for the User: Diagnostic, as described in "What's New in SQL Server 2005 SP2":

http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/WhatsNewSQL2005SP2.htm

"You can now troubleshoot a package's interaction with external data sources by enabling logging and selecting the package's Diagnostic event for logging. Many package failures occur during interaction with external data providers. However, those providers often do not return messages to Integration Services that provide enough information to begin troubleshooting. The Integration Services components listed below have now been instrumented to write a message to the log before and after every call to an external data provider. This message includes the name of the method being called; for example, the Open method of an OLE DB Connection object, or the ExecuteNonQuery method of a Command object. You can view these messages by enabling logging and selecting the package's Diagnostic event."

Since I am currently trying to improve the performance of our ETL I'm currenlty logging OnError, OnWarning, OnPreExecute, OnPostExecute, OnPipelineRowsSent and Diagnostic to the sysdtslog90 table. Apart from Diagnostic, the amount of events logged are roughly the same (# events logged):

Package...............................SP1...............................SP2...............................SP2 (without User: Diagnostic)

Load dimensions..................455............................26161...................................455

Load fact data.....................1581............................18527.................................1607

Process cubes........................41.................................137.....................................41

I have run the packages without any logging at all for SP2, and that only shortened the total execution time with a couple of seconds (less than one minute for all three).

I'll look into if there are any certain steps that now runs a lot slower than with SP1 and get back during the day. Thanks all!

|||

Ok, here are some more detailed info about the steps that differ between SP1 and SP2 (duration in seconds if not specified):

Load dimensions: http://happy.lanmust.org/SP1vsSP2/dimensions.Png

Load facts: http://happy.lanmust.org/SP1vsSP2/facts.Png

Cube processing: http://happy.lanmust.org/SP1vsSP2/cubes.Png

At least there are a couple of steps that are faster with SP2

tsk_c_s means data is loaded from an oracle db to sql server,

tsk_s_d means data is loaded from sql server to sql server (from staging to datamart).

Connection to Oracle uses the Oracle provider for Oracle (OraOLEDB.Oracle.1). The rest is SQL Native Client (SQLNCLI.1).

OT: Is it possible to add images in the forum post instead of linking? I tried using html mode and <img..> images but ends up blank.

|||

1) I was just thinking, are there any settings that defaults when you install SP2?

2) Is there anything you have to think about before installing SP2 (I reckon there was this blog post about it, but now when I need it I can't find it).

Also, installed build is 3042. Now there are some fixes: http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx I'll apply these and see if things work out better.

|||Nopes, going to build 3054 didn't help. Going back to SP1 on Monday for now.|||Try asking in Analysis Services forum - most likely the performance regression is in OLAP server; it is unlikely the SSIS is the bottleneck here. E.g. in case of Cube processing, SSIS just send the XMLA command to the OLAP server, which does the actual work.|||

Ok, I'll do that. Thanks!

By the way, version of SQL Server is Standard.

|||

Good news. Now SP2 seem to be on par with SP1. I uninstalled SQL Server, re-installed it and added SP1. Did run the packages again, and was fine (normal performance). Couldn't help to try installing SP2 again to see if there still was this performance difference - and what do you know - this time it performed as well as SP1.

I dunno why it worked better this time, but this time I did not install the hotfixes for SP1 before installing SP2 (cannot evaluate variables http://support.microsoft.com/kb/918091, http://support.microsoft.com/kb/918222/). In addition, this time I installed the SP2 built in March and not the very first release (Feb). Might also have to do with tempdb and/or transaction log, cause before I had run the ETL packages 20+ times instead of 5.

Anyway, here's the new comparison: http://happy.lanmust.org/SP1vsSP2/all.Png