March 20, 2015 at 9:09 am
We have 2 SQL servers I will call them SQLA and SQLB.
SQLB uses OSQL to execute an SP on SQLA and for some reason in the last couple weeks the execution time has gone from 10 minutes to anywhere between 10 and 20 hours.
If I run the SP manually on SQLA it runs in minutes...
Any ideas where I can start looking?
March 20, 2015 at 10:31 am
What does the sp on SQLA do? Just a SELECT? INSERTS? ETC?
Also, what version of SQL?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 20, 2015 at 11:39 am
The SP on SQLA Pulls data from SQLB into a production database using both tables and databases on both servers.
We are running SQL2008 R2
March 20, 2015 at 12:14 pm
Have you done any index de-fragmentation?
March 20, 2015 at 1:17 pm
No, and I wasn't going in that direction since it runs very quickly via SSMS...
March 20, 2015 at 2:32 pm
How large is the dataset being returned? Not saying the network is to blame but returning data locally will always be faster than retrieving it through a linked server (well, most times). Have you tried running sys.dm_os_wait_stats a few times while the query is executing on the server to see which waits are prominent?
Also, are you running the EXACT same procedure, using the EXACT same parameters when you are testing the execution on SQLA and SQLB (want to rule out parameter sniffing)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply