Problems with Linked Servers and distributed trans | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problems with Linked Servers and distributed trans

Hello All, I’m experiencing a problem with 2 linked servers where the first time I call a stored procedure, which executes a distributed transaction, it takes about 50 sec. If I execute the same SP again it will take less then a second. It seems like the first time it runs it is establishing a connection with the other linked server and something is timing out during that process. Once the connection is established all is good until the connection itself times out. I have a 3rd server with the same setup which does not have this issue, its initial SP execution is about 5~8 sec. All servers are Win 2003 and SQL 2005. I’m using Linked Servers with the "Be made using this security context" option set with a common user setup on all 3 machines with dbo rights to the database which is being accessed. Server options are set to: "Data Access" = True, "RPC" = True, "RPC Out" = True, "Use Remote Collation" = true. MSDTC security settings are "Network DTC Access", "Allow Inbound", "Allow Outbound", "No Auth Required", "Enable XA Trans" are all true. Any help or ideas would be greatly appreciated.
Are all servers configure to use same network protocols?
Run the sql trace on source and destination servers to see how much time it is taking to execute and returned data…
Check the NIC configuration too… Mohammed U.
I had done a SQL Trace in Profiler and there were 2 commands that the got hung during the query. Both are in the EventClass "OLEDB Call Event" the first is IDBInitialize::Initialize, duration about 13500ms; the second is ITransactionJoin::JoinTransaction, duration about 38000ms. I did not have any good reason for the amount of time either was taking. But after reading your reply I went and checked the network card settings. I realized the server I was using has 2 network cards, but currently only 1 was connected to the network during development. The network card itself was configured and enabled, just unplugged. So I disabled the unplugged card and ran the SP, times went to a respectable 5-ish seconds. My assumption is that the OLEDB provider was trying to use/bind to the unplugged network card first and once it failed and timed out, moved to the other network connection which would complete the transaction. Thanks for your help, sometimes all you need is another set of eyes.
Good to know it is working<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Mohammed U.