InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:58:18 GMT20http://www.sqlservercentral.com/Forums/Topic520387-146-1.aspxThere is a procedure call to get some kind of ServiceLogID returned. So, this procedure is being called for every update made in your database, what is it doing?Since this trigger is only able to handle single-row updates, I would suspect much of the code in the database was designed in the same RBAR fashion. It is pretty likely that your performance issues are not the result of the triggers so much as a result of programming that is in general conflict with what SQL Server handles well.Fri, 20 Jun 2008 05:20:32 GMTMichael Earl-395764http://www.sqlservercentral.com/Forums/Topic520387-146-1.aspx[quote][b]sram24_mca (6/20/2008)[/b][hr][code]SELECT @ServiceID = I.ServiceID, @Name = I.[Name], @LocationID = I.LocationID, @ModifiedBy = I.CreatedBy, @Deleted = I.Deleted FROM INSERTED I[/code] [/quote]One problem with your trigger straight off, ignoring performance problems...It only caters for single row updates.The inserted table contains all the rows affected by the update. If the update affects more than 1 row, then your select there will return the values for 1 row. No guarentees which one.On the performance side, what problems are you seeing?Fri, 20 Jun 2008 01:20:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic520387-146-1.aspxALTER TRIGGER [dbo].[Tg_Update_Service] ON [dbo].[Table_name] AFTER UPDATEAS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @ServiceLogID NVARCHAR(50) DECLARE @ServiceID NVARCHAR(30) DECLARE @Name NVARCHAR(100) DECLARE @Deleted BIT DECLARE @LocationID NVARCHAR(30) DECLARE @ModifiedDt DATETIME DECLARE @ModifiedBy VARCHAR(50) SELECT @ServiceID = I.ServiceID, @Name = I.[Name], @LocationID = I.LocationID, @ModifiedBy = I.CreatedBy, @Deleted = I.Deleted FROM INSERTED I EXEC Procedure_Name,'Table_Name', @LocationID, @ServiceLogID OUT SET @ModifiedDt = GETDATE() IF (@DELETED = 0) BEGIN INSERT INTO Table_Name(ServiceLogID, ServiceID, [Name], Deleted, LocationID, ModifiedDt, ModifiedBy) VALUES (@ServiceLogID, @ServiceID, @Name, @Deleted, @LocationID, @ModifiedDt, @ModifiedBy) ENDELSE BEGIN SET @ModifiedBy = @ModifiedBy + '/Deleted' INSERT INTO Table_Name(ServiceLogID, ServiceID, [Name], Deleted, LocationID, ModifiedDt, ModifiedBy) VALUES (@ServiceLogID, @ServiceID, @Name, @Deleted, @LocationID, @ModifiedDt, @ModifiedBy) ENDENDFri, 20 Jun 2008 01:09:19 GMTsram24_mcahttp://www.sqlservercentral.com/Forums/Topic520387-146-1.aspxCould you post an example of one of those triggers please? It's hard to talk about increasing performance without some code.Fri, 20 Jun 2008 01:04:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic520387-146-1.aspxHi Guys, We have the database triggers for audit trial,now we are facing performance problem because of those triggers.How to monitor the Database Performance on sqlserver 2005 if the database will have the triggersTell me,how to increase the database performance with triggersThnx,RamFri, 20 Jun 2008 00:57:02 GMTsram24_mca