October 16, 2008 at 1:31 pm
We are running multiple instances of SQL2005 on different machines. We are experiencing strange problem where we have trigger on box1.instance1.db1 which is inserting and updating data on box2.instance2.db2, box3.instance3.db3. We have observed that trigger execution is taking hell amount of time. More suprisingly, if we update data individually through query analyzer of box1.instance1.db1 on box2.instance2.db2 and box3.instance3.db3 then it is not taking much time.
Any help in this regard would be highly appreciated.
Thanks in advance.
October 16, 2008 at 2:07 pm
Can you post the trigger?
Is the insert fast when done on the server, but slow when done across a linked server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2008 at 3:38 am
yes it is slow when done across linked server. please find trigger code as below. Also as i mentioned earilier if i execute same DML statement (i.e. in trigger code) through query analyser from box1.instance1.db1 which is updating data on box2.instance2.db2 and box3.instance3db3 then it is fast.
USE [asite]
GO
/****** Object: Trigger [dbo].[Updt_UsersEmail_dmsdb_master] Script Date: 10/17/2008 10:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Updt_UsersEmail_dmsdb_master] ON [dbo].[tbl_TPDAddress]
FOR INSERT, UPDATE
AS
BEGIN
SET XACT_ABORT ON
declare @v_tpdorgid varchar(200)
declare @v_tpduserid varchar(200)
declare @v_orgid int
declare @v_userid int
declare @v_OwnerType nvarchar(100), @v_email nvarchar(400)
declare @v_address1 nvarchar(50), @v_city nvarchar(50), @v_postcode nvarchar(50), @country nvarchar(50),@tel_number nvarchar(50)
declare @v_n_userid bigint
select @v_tpdorgid = OrgID,
@v_tpduserid = UserID ,
@v_OwnerType = OwnerType,
@v_email = substring(Email,1,50) ,
@v_address1 = substring(Street,1,50) ,
@v_city = substring(City,1,50) ,
@v_postcode = substring(Postalcode,1,50) ,
@country = substring(CountryOfOrigin,1,50) ,
@tel_number = substring(Telephone,1,50)
from inserted
if @v_OwnerType = 'USER'
Begin
select @v_n_userid = u.user_id
FROM [ASTDB01\DDMASTER].dmsdb_master.dbo.users U
INNER JOIN [ASTDB01\DDMASTER].dmsdb_master.dbo.organisation O on ( O.org_id = U.org_id )
where u.tpd_user_id = @v_tpduserid and O.tpd_org_id = @v_tpdorgid
-- Asite Collab2.1
UPDATE [ASTDB01\DDMASTER].dmsdb_master.dbo.users SET email = @v_email where user_id = @v_n_userid
UPDATE [ASTDB03\MISC01].tenderdb.dbo.users SET email = @v_email where user_id = @v_n_userid
UPDATE [ASTDB04\DDBAA].dmsdb_master.dbo.users SET email = @v_email where user_id = @v_n_userid
UPDATE [ASTDB04\DDBAA].tenderdb.dbo.users SET email = @v_email where user_id = @v_n_userid
UPDATE [ASTDB01\DDMASTER].moodle.dbo.mdl_user SET email = @v_email where username = convert(varchar,@v_n_userid)
End
if @v_OwnerType = 'TP'
Begin
-- Asite Collab2.1
UPDATE [ASTDB01\DDMASTER].dmsdb_master.dbo.organisation
SET address1 = @v_address1,
city = @v_city,
postcode = @v_postcode,
country = @country,
tel_number = @tel_number
WHERE tpd_org_id = @v_tpdorgid
-- Collab2.1 BAA
UPDATE [ASTDB04\DDBAA].dmsdb_master.dbo.organisation
SET address1 = @v_address1,
city = @v_city,
postcode = @v_postcode,
country = @country,
tel_number = @tel_number
WHERE tpd_org_id = @v_tpdorgid
End
IF @@ERROR != 0
begin
ROLLBACK TRAN
end
END
October 21, 2008 at 1:53 am
This probably has to do with the boxes taking part in the transaction.
If you start the DML via SSMS on box1, it is only the three boxes box1, box2 and box3
which are probably situated in the same lan segment with fast connection.
If you start the DML via a remote client or a webserver behind a firewall,
management of the transaction might involve the client-box or the webservers box
and thus take a lot more time to coordinate.
Trace the difference on transaction handling on the three SQL Server boxes
between the two call variants. You'll probably see a totally different timing.
devloping robust and performant databaseapplications with Microsoft SQL-Server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply