Problem using linked servers and distributed trans

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem using linked servers and distributed trans

Author

Topic

dpreziosi
Starting Member

4 Posts

Posted - 2007-01-08 : 10:42:03

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 10:48:35

The second time the query runs, SQL Server reuses the execution plan and runs faster because there is no need to create a new.

Peter Larsson
Helsingborg, Sweden

dpreziosi
Starting Member

4 Posts

Posted - 2007-01-08 : 11:09:53

Thanks for the help. I agree with the fact that the execution plan is new, but the 50 seconds difference is much larger then I believe it should be. Another lesser horsepower server runs the same initial SP in 5~8 sec. Also the entire transaction is only moving about 80 rows of data between the DB's.

dpreziosi
Starting Member

4 Posts

Posted - 2007-01-08 : 16:10:46

If I do a SQL Trace in Profiler there are 2 commands that the get 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.

Any suggestions on how to speed these 2 up or ideas on something I may be doing that might slow these down.

I've also included the SP I’m calling, maybe something in the SQL may help. (The print statements were to help with debuging)

Thank in advance

---------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_TransferRecipe]
@p_EquipID as tinyint,
@p_RecipeID as int
AS

PRINT 'DBG: Start SP'

-- Declare internal values
DECLARE @ServerName varchar(40)
DECLARE @LocalName varchar(40)
DECLARE @tmpRows int

-- Get name of HMI server to transfer to
SELECT @LocalName = NodeName FROM xxEquipmentInfo
WHERE EquipID = @p_EquipID

PRINT 'DBG: Select Local Node Name (Done)'

-- Check if recipe ID exists on server
SELECT @tmpRows = COUNT(*) FROM prRecipeHeader
WHERE RecipeID = @p_RecipeID

PRINT 'DBG: Check if recipe exists (Done)'

-- If no rows exist exit function
IF @tmpRows = 0
BEGIN
-- Send back error code and text
PRINT 'MSG: Recipe does not exist on server'
RETURN 1001
END

PRINT 'DBG: Distributed Tran start'

-- Setup transaction variables
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN TR1
BEGIN
-- Declare SQL command parameters
DECLARE @SQLComm nvarchar(1000)
DECLARE @SQLPara nvarchar(500)

-- Create SQL statement for Recipe Header insert
SET @SQLComm = N''
+ N'INSERT INTO [' + @LocalName + '].[DP2000].[dbo].[prRecipeHeader] ('
+ N' RecipeID, GroupID, Status, RcpDist_EquipIDs,'
+ N' RecipeName, Version, SaveDateTime, SaveUser,'
+ N' ApprovalDateTime, ApprovalUser, CheckedOutID)'
+ N' SELECT * FROM prRecipeHeader'
+ N' WHERE RecipeID = @ComRecipeID'
SET @SQLPara = N''
+ N' @ComRecipeID tinyint'
EXEC sp_executesql @SQLComm, @SQLPara, @p_RecipeID

-- If there was an error, return error number and exit SP
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN TR1
RETURN @@ERROR
END

PRINT 'DBG: Insert to RecipeHeader done'

-- Create SQL statement for Recipe Globals insert
SET @SQLComm = N''
+ N'INSERT INTO [' + @LocalName + '].[DP2000].[dbo].[prRecipeGlobals] ('
+ N' RecipeID, Global1, Global2, Global3, Global4, Global5,'
+ N' Global6, Global7, Global8, Global9, Global10,'
+ N' Global11, Global12, Global13, Global14, Global15,'
+ N' Global16, Global17, Global18, Global19, Global20)'
+ N' SELECT * FROM prRecipeGlobals'
+ N' WHERE RecipeID = @ComRecipeID'
SET @SQLPara = N''
+ N' @ComRecipeID tinyint'
EXEC sp_executesql @SQLComm, @SQLPara, @p_RecipeID

-- If there was an error, return error number and exit SP
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN TR1
RETURN @@ERROR
END

PRINT 'DBG: Insert to RecipeGlobals done'

-- Create SQL statement for Recipe PID insert
SET @SQLComm = N''
+ N'INSERT INTO [' + @LocalName + '].[DP2000].[dbo].[prRecipePID] ('
+ N' RecipeID, PIDNumber, Gain, Reset, Rate)'
+ N' SELECT * FROM prRecipePID'
+ N' WHERE RecipeID = @ComRecipeID'
SET @SQLPara = N''
+ N' @ComRecipeID tinyint'
EXEC sp_executesql @SQLComm, @SQLPara, @p_RecipeID

-- If there was an error, return error number and exit SP
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN TR1
RETURN @@ERROR
END

PRINT 'DBG: Insert to RecipePID done'

-- Create SQL statement for Recipe Alarms insert
SET @SQLComm = N''
+ N'INSERT INTO [' + @LocalName + '].[DP2000].[dbo].[prRecipeAlarms] ('
+ N' RecipeID, AlmNumber, AlmHi, AlmLo, AlmDB, AlmSD)'
+ N' SELECT * FROM prRecipeAlarms'
+ N' WHERE RecipeID = @ComRecipeID'
SET @SQLPara = N''
+ N' @ComRecipeID tinyint'
EXEC sp_executesql @SQLComm, @SQLPara, @p_RecipeID

-- If there was an error, return error number and exit SP
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN TR1
RETURN @@ERROR
END

PRINT 'DBG: Insert to RecipeAlarms done'

-- Create SQL statement for Recipe Steps insert
SET @SQLComm = N''
+ N'INSERT INTO [' + @LocalName + '].[DP2000].[dbo].[prRecipeSteps] ('
+ N' RecipeID, StepNumber, StepType, StepTypeText,'
+ N' Setpoint1, Setpoint2, Setpoint3, Setpoint4, Setpoint5,'
+ N' Setpoint6, Setpoint7, Setpoint8, Setpoint9, Setpoint10,'
+ N' Setpoint11, Setpoint12, Setpoint13, Setpoint14, Setpoint15,'
+ N' Setpoint16, Setpoint17, Setpoint18, Setpoint19, Setpoint20,'
+ N' Setpoint21, Setpoint22, Setpoint23, Setpoint24, Setpoint25,'
+ N' EndType, EndTypeText, EndTime,'
+ N' EndSetpoint1, EndSetpoint2, EndSetpoint3, EndSetpoint4)'
+ N' SELECT * FROM prRecipeSteps'
+ N' WHERE RecipeID = @ComRecipeID'
SET @SQLPara = N''
+ N' @ComRecipeID tinyint'
EXEC sp_executesql @SQLComm, @SQLPara, @p_RecipeID

-- If there was an error, return error number and exit SP
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN TR1
RETURN @@ERROR
END

PRINT 'DBG: Insert to RecipeSteps done'

END

COMMIT TRAN TR1
SET XACT_ABORT OFF

dpreziosi
Starting Member

4 Posts

Posted - 2007-01-09 : 12:00:41

I have found the problem after a suggustion from another message board.

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. I hope this info helps someone else.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources