September 28, 2010 at 5:20 am
Hi,
I have 2 servers each on SQL2008. These servers link to a SQL 2000 instance (on which I've applied the required service packs to get remote queries working).
The setup has worked fine for months. The 2000 server was rebooted on Monday, and since then the performance of queries running against it has degraded significantly. <1 sec increased to 120 sec.
Even simple queries(not in an SP but in Man. studio) such as :
SELECT COUNT(*) FROM Remote_Table
take up to 25 seconds when querying a table with only 64 rows. (although if run almost immediatley again the reuslt is < 1 sec. Wait a couple of hours and the time for such a query returns to taking 25 secs)
I've examined the actual execution plans on the calling servers and they show that the queries are being executed remotely so this doesn't seem to be about volumes of data being trawled across the network. Testing the linked server connection in management studio returns imediately so suggesting that there isn't a delay making the connection.
(Runing the sme queries from SQL Query Analyzer on a client PC the queries all run as per the original timescale < 1sec.)
Any ideas anyone - getting very desperate here?
Thanks,
Andrew
September 28, 2010 at 11:21 am
Check this out, could be related to the memory leak
September 29, 2010 at 3:25 am
Thanks for the suggestion, but the symptoms don't quite match what I'm experiencing. Just as a further update, when navigating the linked server folder on the local server (in Management Studio) and looking at the remote server, the tree is very slow to refresh and populate - so even the easiest queries are taking forever. When I look at other remote servers on the local server they refresh almost immediately, as I'd expect
(One further detail I'm connecting using SQL Server authentication)
October 1, 2010 at 10:34 am
I am running into the exact same issue!... If you find a resolve, PLEASE post it. Still looking myself...
October 6, 2010 at 3:10 am
I'm still not having any luck resolving this.
If you find a solution please let me know (Did yours happen after a reboot - what was the trigger for your performance loss? Perhaps if we find some common symptoms we may edge closer to a solution)
October 6, 2010 at 4:57 am
andysfreeemail (10/6/2010)
I'm still not having any luck resolving this.If you find a solution please let me know (Did yours happen after a reboot - what was the trigger for your performance loss? Perhaps if we find some common symptoms we may edge closer to a solution)
Two questions...
1- Any network related changes at the time server was reboot? Do a trace route to verify what's in between the two servers in terms of networking.
2- Have you tried to drop/recreate linked server?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 6, 2010 at 9:23 am
Hi Pablo
Thanks for your interest:
1) Unfortuantely Tracert shows nothing of any concern, and our network guys have also given things the once over
2) The linked server has been dropped and recreated (a number of times!)
Neither of these things have had any effect - the linked server query is still performing very slowly.
Any other suggestions gratefully received
Andrew
October 6, 2010 at 11:50 am
No, mine wasn't after a reboot of the client (linked server) box. My issue is between an x64 SQL Server 2008 install / environ, and a SQL Server 2005 box with it's databases in 2000 compatibility mode.
I have another SQL Server 2008 server (same x64 disk for install, but x86 core, SP1) that connects to the linked server with no performance problems at all.
It only seems to be the x64 server that has the perfermance problems connecting and throwing simple selects (8 minutes versus 2 seconds from anywhere else on 15K record table) to that one SQL Server 2005 server (w/DBs in compatibility mode or not). Have tried on the x64 server to patch up to SP2, use an alias via TCP for alternate connection, and ODBC linked server to no avail... Getting pretty desperate here... :ermm:
October 7, 2010 at 9:17 am
Interesting(?) observation on my problem.
When query is run from Query Analyzer on a client PC it retuns in a second, and the wait attributable to the query is all CXPACKET
When query is run from a linked server, the wait is much greater and is all LATCH_EX wait.
The execution plan for both show that all work is done on my remote server - so why should it do it so differently when the query comes from another server?
October 20, 2010 at 2:50 am
Just to close this thread...
We restored the performance of the query. It is unclear when the server had its previous reboot and so the suspicion is that the recent reboot finished the install of a previous service pack.
We took the server offline to try to add more memory and realised that it already had 16gb. A boot.ini setting was changed to make the additional memeory visible (sorry I'm not a hardware person so don't know what it was). Some settings were also changed on DEP(Data Execution Prevention). Finally they stuck a bit more memory in.
Unfortuantely we couldn't do these changes one at a time so we don't know what fixed it. Hope it gives someone some ideas in future though
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply