RE: Linked Server Lockes, mass testing
From: kirk1880 (kirk1880_at_discussions.microsoft.com)
Date: 06/14/04
- Next message: Gert-Jan Strik: "Re: Version Comparision"
- Previous message: kirk1880: "RE: easy cursor problem"
- In reply to: Ali Salem: "Linked Server Lockes, mass testing"
- Next in thread: Ali Salem: "RE: Linked Server Lockes, mass testing"
- Reply: Ali Salem: "RE: Linked Server Lockes, mass testing"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 14 Jun 2004 13:27:01 -0700
your biggest proble is that distributed queries always have an excusive lock and that lock is usually a table level lock. Also there is higher IO because a distributed query cannot use indexes because the query plan is built on the source server and only retrieves the schema from the target server. To run a successfull distributed query you need to fake it out with a simple trick. from the source server call your procedure. On the target server create another proc that will do the processing needed on the target server then from your source starting procedure call the procedure on the target server. You will get much better results.
"Ali Salem" wrote:
> Hi,
>
> I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
> I call a stored procedure defined on the linked server as part of a transaction and it works properly.
>
>
> However, my problem appeared when I tried to stress test my application, the connections on the linked server
> started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
> the applied scenario:
> 1) A transaction is opened on Server A
> 2) Server A executes a stored procedure on Server B (Linked Server)
> 3) The Linked Server SP performs the following:
> a) Selects a number from a certain row in table X.
> Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
> b) Updates the same Row queries in previous step.
> c) Returns the read number to caller.
> 4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
> 5) The transaction is committed.
>
> The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
> The following was noticed on the Locks/Process ID list on EM:
> 1) One SPID (SPID 100) was displayed with status blocking.
> 2) SPID 101 was blocked by SPID 100.
> 3) All other SPIDs were blocked by SPID 101.
>
>
> Please note that:
> 1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
> 2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
>
> I do highly appreciate any help or support on this.
>
> Regards,
> Ali Salem
- Next message: Gert-Jan Strik: "Re: Version Comparision"
- Previous message: kirk1880: "RE: easy cursor problem"
- In reply to: Ali Salem: "Linked Server Lockes, mass testing"
- Next in thread: Ali Salem: "RE: Linked Server Lockes, mass testing"
- Reply: Ali Salem: "RE: Linked Server Lockes, mass testing"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
