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

[ChrisPackit]

Thanks for your help SC.

What makes me think is if you could imagine there are 250 SQL Statements run from the Access client and we keep a log after each event is run in a SQL table. When comparing the log from Prod to the log from Test, every event is taking 4 or 5 times longer. If there was a specific point where it was halting, then I guess it would be easier to diagnose. Am I correct in thinking that the parts of the process which use certain distributed queries would be slow, but the rest should perform OK?

Is there a simple way via SQL to check which ones may be looking at another server, or just by looking at the properties?

I've just checked the Server specs for my own sanity, and the Prod box which runs OK is only a 4 CPU box with moderate OS Memory and the Test box runs 24 CPU's and is way slower...

[Spacecadet]

Pardon my ignorance on this...I'm not a DBA. Is there a simple SQL statement I can run to find any distributed queries?

Would these queries cause an issue even if they weren't being called?

Thanks

Distributed queries are those which use tables from more than one server... e.g.
(Assuming there is a linked server SOURCE defined)
select *
from SOURCE.ProdDB.table T1
inner join LocalTable T2
on T1.ID = T2.ID
where T2.date > 20120101

So if the source table has 10 years of history but the query only needs the past week, the entire table still needs to be dragged across the network for the local server to work with it.
This can make a difference between something running in 10 seconds or 10 hours
These types of queries are also quite common in migration/ETL scripts as they make for an easy check that already migrated data isn't being reimported.
It's a simple mistake which I've seen every single client make.

Trace logs might be able to help find if there are any of these running

[ChrisPackit]

you checked for any distributed queries?
Are you running a trace on both source and target instances we can can look at?

Pardon my ignorance on this...I'm not a DBA. Is there a simple SQL statement I can run to find any distributed queries?

Would these queries cause an issue even if they weren't being called?

Thanks

[Spacecadet]

you checked for any distributed queries?
Are you running a trace on both source and target instances we can can look at?

[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

[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).

[stek]

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

[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.

[rsingh]

Have you updated statistics on all the tables?

[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!

[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?

[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]

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

[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