Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RdJ
Starting Member
4 Posts |
 Posted - 2008-01-24 : 07:52:21
|
| Hi,We recently installed service pack 2 on our server. Since then, the performance dramaticly decreassed.First example of the slow performance is an integration package. On SQL server 2005 without sp2 it took about 2 hours to complete. Now, when we run it, it's still not finished after 2 days! We also ran it in debug mode (visual studio). There we saw that it stopped at a point where it should sort about 8.000.000 records.Another example is when we execute a data driven subscription (of reporting services). This should generate about 1200 reports. Normally, before sp2, it took 3.5 hours to generate all reports.With sp2 it takes about 8 hours (sometimes less, depends on the other activities on the server.When the data driven subscription runs, the CPU usage is 100% almost the whole time.The server is a x64 and the SQL Server software is 32 bit. I don't know if this could be an issue, but without sp2 it worked fine.The SQL version is SQL Server 9.0.3054Thanks in advance for your help.Regards,Rob |
|
|
dewacorp.alliances
452 Posts |
Posted - 2008-01-24 : 14:02:05
|
| Hi thereDid you try to reindexing the database? |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-24 : 22:42:20
|
| Apply latest post sp2 fix. |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2008-01-25 : 03:22:45
|
| Have a look this one and apparently is up to 9.0.3161:http://support.microsoft.com/?kbid=934458 |
|
|
|
RdJ
Starting Member
4 Posts |
Posted - 2008-01-25 : 08:16:08
|
| Thank you very much for your replies.I reindexed the database. It did improve the performance of the data driven subscription. I took the same time to complete as before sp2. A few minutes ago I restarted the integration package. I guess that the problem with the package is not solved yet. The package deletes all records in the tables and refills them. So the reindex will be deleted as well. But maybe, for some reason, it will finish successfully. I downloaded the fix you suggested. I'm not able to install it. At the 3 step of the installation I'm supposed to check some boxes (to indicate which parts should be updated). For some reason it's not possible to check any of the boxes and therefore it's not possible to push the "next" button.Any other suggestions to get the integration package working, or how to install the update are more than welcome.Regards, Rob |
|
|
|
RdJ
Starting Member
4 Posts |
Posted - 2008-01-25 : 09:54:43
|
| I also noticed that in permormance monitor the pages/sec is 100% all the time (the CPU is at a low level from approx. 3%) when the integration package executes. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-26 : 00:24:20
|
| SSIS uses lot memory and will not release them, you can try restart ssis after running packages. |
|
|
|
RdJ
Starting Member
4 Posts |
Posted - 2008-01-26 : 02:26:21
|
| the problem is that the package not finishes. at a sort of 8.000.0000 records it hangs. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-26 : 21:43:53
|
| Try trace it in profiler. |
|
|
|
|
|
|
|
|
Slow SQL after sp2 install