Application performance degraded after upgrading from SQL 2005 32 Bit to SQL 2008 64 Bit


I have just configured my C# multi-threaded application from 32 bit to 64 bit platform for getting more memory support. But my application runs up to 40 - 50% slower as compared to 32 bit platform.

The overall idea about the multithreaded application is, we do recalculation for server hundred thousand records available in the database. Fetch the records from the table and do the recalculation finally it will update it into the table.

Earlier we had the following sever configuration as bellow

OS- Windows 2003 server 32 bit version
SQL - SQL Server 2005 Enterprise Edition for 32 bit version

Now we changed to the latest version

OS- Windows 2003 server 64 bit version
SQL - SQL Server 2008 Standard Edition for 64 bit version

The application was working fine and recalculated 3000 records per minute. But now after changing to 64 bit version it is calculating only 1200 records per minute.

I have tried with the following scenarios.

Changed to enterprise edition 64 bit version and tested - no improvement (memory utilization is high)
Page lock in memory in enterprise version and tested - no improvement (memory utilization is high).

Can you suggest where I need to concentrate to improve the performance? (OS Level / SQL Level /Application Level)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SQl*Profiler-trace + Perfmon
=> see what's takening a lot of time/resource

Could be as simple as bad statistics -> too many reads -> more records in memeory needed ....
But it can be a lot more difficult.
Mark WillsTopic AdvisorCommented:
you will have to configure SQL server to use memory - and recalc those statistics and rebuild indexes - sometimes best to do a full backup with the various optimise options (a -la maintenance plans).

And page lock in memory is a bit of a misnomer, you might end up allocating memory blocks which could otherwise be used for general queries - need to be a little bit careful about that.
pvinesAuthor Commented:
Turns out that the test was complicated by a change in the raid configuration. We moved from a raid 1 to a raid 5 drive setup. Raid 5 has poor performance in random r/w and this is we think causing the problem. Going to change to RAID 10 and see if that resolves the issue.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Mark WillsTopic AdvisorCommented:
Definitely NOT raid 5 please !!! It will make a difference - and can account for the high CPU in calculating parity bits.

Best to stay with mirroring (raid 1), or move to raid10.
Tuned queries, super statistics, super CP, super memory .... and bad disktype for the job. It only needs one week part.
And moving to a whole new server any part is new so don't forget to check afther your disk-change that also the other components perform as they should. Monitor! And if anything is good, you have a baseline to check after a while if everything still is ok.
How came the author to find it was a disk-problem?  Do think I helped him to how/where to look

"SQl*Profiler-trace + Perfmon
=> see what's takening a lot of time/resource "
pvinesAuthor Commented:
We didn't run sql profiler or perfmon. We did have the app running on a less powerful development server with non-raid disks and it ran more quickly. We concluded that there might be a server configuration issue and compared the two servers using the Passmark Performance Test suite. Turned out our production server had very poor random r/w performance. We then did a bit of research and decided we'd made a mistake configuring our production server with RAID 5 and switched to RAID 10.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.