October 19, 2005 at 12:44 pm
Folks
thanks in advance for your help.
We recently applied SP4 on one of our sql server.
Now we have job that uses linked server to update and insert based on a query using linked server. Now comes the problem.
The query which used to take around 1 min(before SP4) is now running 31/2hrs and still doesnt complete.
Situation-Server A (has SP4) Server B(has SP3)
Table_a in server A has around 65000 rows
Tbl_b in server B has around 8708695 rows
We do a delete which is followed by an insert in serverA with a join across the linked server to ServerB
query goes like this
Delete table_A FROM table_A A, LinkServerB.DB_NameB_dbo.table_B, table_C C, LinkServerB.DB_NameB_dbo.table_D D where a.edfoid = C.edfoid and d.oname = b.oid and a.IsLInk = 0 and b.oname = c.LookupLinkName
NOTE: When we noticed this issue, we installed SP4 on couple of our development serves and we were able to reproduce this issue.
We did some more test and found that we still can run the query in less than a minute, if we use 2 servers with SP3.
Any help is much appreciated,
We also contacted MS support, not much progress so far
October 19, 2005 at 1:34 pm
Can you upgrade ServerB to SP4?
-SQLBill
October 19, 2005 at 1:38 pm
Rewrote you query a little to use join syntax for readability+performance
Delete dbo.table_A
FROM dbo.table_A A /*SERVER A*/
INNER JOIN dbo.table_C C /*SERVER A*/
ON a.edfoid = C.edfoid
AND a.IsLInk = convert(bit,0) /*in case of bit value*/
INNER JOIN LinkServerB.DB_NameB.dbo.table_B B /*SERVER B*/
ON b.oname = c.LookupLinkName
INNER JOIN LinkServerB.DB_NameB.dbo.table_D D /*SERVER B*/
ON d.oname = b.oid
1)Is there a different query plan for SP4-SP3 vs SP3-SP3?
(any noticible locking behaviour?)
2) All indexes are present?
a.edfoid ,C.edfoid,d.oname,b.oid?
3) Is there any referential integrity set (cascading update/delete?)
(any triggers)
4) Can you post the full statement? (only an insert posted)
October 20, 2005 at 5:45 am
Check this out may or may not help.
FIX: A query that uses a view that contains a correlated subquery and an aggregate runs slowly
http://support.microsoft.com/kb/826906/
Rick
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply