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

[Spacecadet]

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

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

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]

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