Any SQL Server DBA's on here, able to help with a problem...

[ChrisPackit]

Chaps

I'm from a Dev background so hope you can help with a SQL Server performance issue. I am experiencing performance issues with a migration from SQL EE 2005 to 2008. Various steps of the process which runs ODBC passthroughs from an MS Access client have increased significantly from 20 minutes to 2 hours. The upgrade was performed by offshore bobs and a Test and Dev environment has been set up. The collation was wrong on the DB's when they were created, so had the DB collation changed to match that of the Server. The boxes are meant to be fairly decent spec, as it is a different box to the current Prod DB. I beleive a Copy Database was done, so all the settings should be the same.

Can any DBA's out there fire some ideas at me why this may be taking so much longer? I was under the impression that 2005 to 2008 upgrade is fairly simple to do, unlike say 2000 to 2005.

Any help very much appreciated...

Thanks

[Spacecadet]

are you migrating the data from a 2005 instance to 2008?
check for any queries which join tables on different servers , they kill performance and should be avoided at all costs

[DimPrawn]

The boxes are meant to be fairly decent spec, as it is a different box to the current Prod DB.


There's your starting point. How are the disk arrays configured, where is the transaction log, are the db settings the same (autoshrink, autogrow, statistics updates, memory allocation). RAM, CPU between the boxes? How big are the DB files between the two servers?

[Spacecadet]

The boxes are meant to be fairly decent spec, as it is a different box to the current Prod DB.


There's your starting point. How are the disk arrays configured, where is the transaction log, are the db settings the same (autoshrink, autogrow, statistics updates, memory allocation). RAM, CPU between the boxes? How big are the DB files between the two servers?

Given that its the data migration itself which is the problem I reckon its more of a development issue in the migration scripts, one or more of the following are very common mistakes:
Distributed queries (these can be fine, thena tiny change to the query can make it take 1000 times longer)
Non bulk operations
cursors or other row by row operation

Is there a reason that Access is being used for the migration and not SSIS?

[ChrisPackit]

Latest is that I can't get the permission required to do anything, run the Tuning Advisor even, so the bobs in India will now have to sort it out and I'll guarantee that won't be this year.

Access is used as it pulls data from a DB2 system, does some processing and exports to to SQL Server. It could propbaly be done solely in SQL Server but this is a legacy system that's been around 10 years, and the schemas have approximately 1500 tables, so by starting to migrate it to SQL exclusively is not an option....and I wouldn't want to do that job anyway!

[rsingh]

Have you updated statistics on all the tables?

[platforminc]

Having done a lot of these migrations in the past, rebuild the indexes on all your tables, that should get you a stats update for free as well.

Also clear out the procedure cache before you start your tests.

Without seeing it, its hard to say.

[stek]

Have you tried turning it off and turning it on again?

[KentPhilip]

I beleive a Copy Database was done

It may be the case that only the data tables have been migrated to the new servers, rather than all the objects in the database, most notably the indexes.

Get them to run a SQL Profiler trace of similar operations on the old and new systems to try to identify individual queries or stored procedure calls that are taking significantly different amounts of time to run. Look at the figure under the "reads" column - if this is much higher it does suggest missing indexes or table statistics that are either missing or need to be updated (refreshed).

[ChrisPackit]

On going saga.... any help appreciated...

Chaps

This on going saga still continues....

We have rebuilt the SQL databases with an On Shore (Non Bob) DBA, and any discrepancies are now resolved. All the indexes and Stats seem to match exactly on both Prod and Test environments. I have checked the other suggestions posted so far and all seem fine.

The processing is performed from an MS Access client which runs 2 versions of the same app, 1 connected to Prod and 1 to Test and on the same spec machines. The Prod process takes 2 hours E2E and the Test one takes 6 hours ++!!

So there is still an issue in what should be a simple upgrade, the only difference is one is SQL 2005 and one is SQL 2008. Both servers are physically located on site too. I can't see the issue being at the client (Access) side, so any further SQL Server advice would be appreciated...

TIA