InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:19:05 GMT20http://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote]They will embed the update code directly in the sprocs where the base table is itself updated. [/quote]WISE decision!Tue, 03 Feb 2009 12:15:10 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]TheSQLGuru (2/3/2009)[/b][hr][quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!There are two puzzling aspects about this:(1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)(2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).I have captured the number of logical reads and the execution plan by polling the [i]sys.dm_db_exec_requests [/i]DMV once a minute.How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?[/quote]Given the ACTUAL plan, I can't see what your issues are with the number of reads: 1) The clustered index update is 1.46M rows, NC index update 2.93M rows.2) You are doing a table scan of 1.46M row inserted and 39.9M row updtable3) You are hash matching the above4) then doing a sort of the hash resultNo idea where your statement of only updating 30K rows is coming from, but it is irrelevant. THAT query you gave the plan for is a beast and that is all there is to it. :)[/quote]It is a beast, no doubt.The 30k-record number came from the estimated-plan info, which - clearly - is hugely inaccurate.The developers have had enough of my whining, and they are eliminating the trigger alltogether.They will embed the update code directly in the sprocs where the base table is itself updated.Tue, 03 Feb 2009 08:47:56 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!There are two puzzling aspects about this:(1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)(2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).I have captured the number of logical reads and the execution plan by polling the [i]sys.dm_db_exec_requests [/i]DMV once a minute.How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?[/quote]Given the ACTUAL plan, I can't see what your issues are with the number of reads: 1) The clustered index update is 1.46M rows, NC index update 2.93M rows.2) You are doing a table scan of 1.46M row inserted and 39.9M row updtable3) You are hash matching the above4) then doing a sort of the hash resultNo idea where your statement of only updating 30K rows is coming from, but it is irrelevant. THAT query you gave the plan for is a beast and that is all there is to it. :)Tue, 03 Feb 2009 08:42:56 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxHere is the "actual" exec plan obtained from a server-side trace (see attached).Tue, 03 Feb 2009 04:23:59 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]TheSQLGuru (2/2/2009)[/b][hr]Other than the ol' trigger-firing-itself-due-to-update-inside-trigger-ploy, 2 other questions:1) any OTHER triggers on this table? 2) is the join column a primary key or unique index value? Looks like cartesian product if not.[/quote]Yes, that's the only trigger on this table.The join column is a unique clustered primary key.Mon, 02 Feb 2009 13:10:41 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]TheSQLGuru (2/2/2009)[/b][hr]2) is the join column a primary key or unique index value? Looks like cartesian product if not.[/quote]Agreed. That's one reason I want see the actual exec plan, see what the row counts really are at various points of the query.Mon, 02 Feb 2009 12:50:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxOther than the ol' trigger-firing-itself-due-to-update-inside-trigger-ploy, 2 other questions:1) any OTHER triggers on this table? 2) is the join column a primary key or unique index value? Looks like cartesian product if not.Mon, 02 Feb 2009 12:45:17 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]GilaMonster (2/2/2009)[/b][hr]Nested triggers means that an update to a table in a trigger can fire triggers on that table, providing it's not the same table. So, say you have Table1 that has an update trigger that inserts into table2. Table2 has an insert trigger that nserts into table3. If nested triggers is on and you update table1, then the trigger on table1 will insert into table2, will fire that trigger and will insert into tbl3. If it's off, the insert trigger on table2 won't fire.Recursive trigger controls if a trigger may fire itself due to data changes. If it's off, your trigger will not be called due to the updates that it does.[/quote]Thanks, so that means I'm OK from that aspect, ie. this trigger will not fire itself repeatedly, since my [b]db [/b]setting is off.That cleared it up.From [url]http://msdn.microsoft.com/en-us/library/ms190739(SQL.90).aspx[/url]An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set.Mon, 02 Feb 2009 10:59:56 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxNested triggers means that an update to a table in a trigger can fire triggers on that table, providing it's not the same table. So, say you have Table1 that has an update trigger that inserts into table2. Table2 has an insert trigger that nserts into table3. If nested triggers is on and you update table1, then the trigger on table1 will insert into table2, will fire that trigger and will insert into tbl3. If it's off, the insert trigger on table2 won't fire.Recursive trigger controls if a trigger may fire itself due to data changes. If it's off, your trigger will not be called due to the updates that it does.Mon, 02 Feb 2009 10:56:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]GilaMonster (2/2/2009)[/b][hr][quote][b]Marios Philippopoulos (2/2/2009)[/b][hr]Wow, would the trigger feed on itself that way? I hadn't thought of that...[/quote]That's only possible if recursive triggers are enabled. It's a database-level option and it's disabled by default.[/quote]I ran the following on the db in which the trigger resides and confirmed that recursive triggers is indeed turned off at the database level[code]EXEC sp_dboption 'myDB', 'recursive triggers'[/code]However, the nested triggers setting is turned on at the server instance level. What is the difference between the 2 settings, apart from the scope in which they operate?[code]EXEC sp_configure 'nested triggers'[/code]Returns:config_value: 1run_value: 1Mon, 02 Feb 2009 10:48:22 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (2/2/2009)[/b][hr]Wow, would the trigger feed on itself that way? I hadn't thought of that...[/quote]That's only possible if recursive triggers are enabled. It's a database-level option and it's disabled by default.Mon, 02 Feb 2009 10:23:33 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxNormally the SQL Server thinks 32 is the limit and stops execution with an error.See Books Online for Nested Triggers and Recursive Triggers.Mon, 02 Feb 2009 09:50:16 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Peso (2/2/2009)[/b][hr]The trigger is updating the same base table on where the trigger is resided.So when the trigger updates the two columns, the same trigger is fired again!And again.. And again...[/quote]Wow, would the trigger feed on itself that way? I hadn't thought of that...How many iterations would that involve?Mon, 02 Feb 2009 08:45:49 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxThe trigger is updating the same base table on where the trigger is resided.So when the trigger updates the two columns, the same trigger is fired again!And again.. And again...Mon, 02 Feb 2009 08:28:45 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Peso (2/2/2009)[/b][hr]No need to check for UPDATE, DELETE or INSERT.Trigger is designed for UPDATE.Also, the trigger is nesting itself by updating the table for which the trigger ís triggering on...Use thisCREATE TRIGGER [dbo].[TG_U_UpdTable]ON [dbo].[UpdTable]AFTER UPDATEASIF UPDATE(db_updateDate) OR UPDATE(db_updateBy) RETURNUPDATE dSET d.db_updateDate = GETDATE(), d.db_updateBy = SYSTEM_USERFROM dbo.UpdTable AS dINNER JOIN inserted AS i ON i.UpdTableOID = d.UpdTableOID[/quote]Thanks for the suggestion, yes, the code that checks for type of DML is redundant.Can you explain what the following does though, it's not clear to me:[code]IF UPDATE(db_updateDate) OR UPDATE(db_updateBy) RETURN[/code]Mon, 02 Feb 2009 08:22:20 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxNo need to check for UPDATE, DELETE or INSERT.Trigger is designed for UPDATE.Also, the trigger is nesting itself by updating the table for which the trigger ís triggering on...Use thisCREATE TRIGGER [dbo].[TG_U_UpdTable]ON [dbo].[UpdTable]AFTER UPDATEASIF UPDATE(db_updateDate) OR UPDATE(db_updateBy) RETURNUPDATE dSET d.db_updateDate = GETDATE(), d.db_updateBy = SYSTEM_USERFROM dbo.UpdTable AS dINNER JOIN inserted AS i ON i.UpdTableOID = d.UpdTableOIDMon, 02 Feb 2009 08:03:31 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]GilaMonster (2/2/2009)[/b][hr][quote][b]Marios Philippopoulos (2/2/2009)[/b][hr]Wow, that was something I didn't know, but makes sense the way you put it.Not that I don't believe you, but can you post a link that mentions this?[/quote]I don't have one offhand (and I'm sitting at the airport), but it's easy to check. Open the exec plan, find any joi, seek or scan operator and look at the tooltips. If you see things like 'Actual rows', 'Actual IO cost', etc then it's an 'actual' plan with run-time information. If you don't then it's essentially an estimated plan.Not on this exactly, but - [url]http://sqlinthewild.co.za/index.php/2007/09/04/execution-plans-estimated-vs-actual/[/url][/quote]I just looked at the Index-seek operator that accounts for 86% of the total workload of the exec plan I posted (2nd post in this thread) and it only lists [i]Estimated[/i] values. I'm sold! ;)I will set up the trace and post my findings.Mon, 02 Feb 2009 07:57:41 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (2/2/2009)[/b][hr]Wow, that was something I didn't know, but makes sense the way you put it.Not that I don't believe you, but can you post a link that mentions this?[/quote]I don't have one offhand (and I'm sitting at the airport), but it's easy to check. Open the exec plan, find any joi, seek or scan operator and look at the tooltips. If you see things like 'Actual rows', 'Actual IO cost', etc then it's an 'actual' plan with run-time information. If you don't then it's essentially an estimated plan.Not on this exactly, but - [url]http://sqlinthewild.co.za/index.php/2007/09/04/execution-plans-estimated-vs-actual/[/url]Mon, 02 Feb 2009 06:17:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]GilaMonster (2/1/2009)[/b][hr][quote][b]RBarryYoung (2/1/2009)[/b][hr]But Marios should be able to get the Actual from Profiler or tracing, right?[/quote]Yes, absolutely. Providing he traces the correct event. I seem to recall there are about 6 events relating to the exec plan and I can't recall offhand which produces what. BoL should say.[/quote]Looks like running a trace will get me what I'm looking for, ie. the [i]actual[/i] exec plan.I will do so and post my findings.Thanks guys!Mon, 02 Feb 2009 04:24:27 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]GilaMonster (2/1/2009)[/b][hr][quote][b]Marios Philippopoulos (2/1/2009)[/b][hr]I don't think it's the estimated exec plan, I obtained it from sys.dm_db_exec_requests. It was retrieved when the statement was actually executed...[/quote]It's the same as the 'estimated' plan. Both the plans produced by the "Estimated Execution Plan" and the ones retrieved from the plan cache have only the compile-time information. That means just the estimated row counts, data sizes, etc. Neither will have the actual values, which are very important for any form of performance tuning. The actual values won't be available with the "Estimated Execution plan" and they're not stored in the plan cache.[/quote]Wow, that was something I didn't know, but makes sense the way you put it.Not that I don't believe you, but can you post a link that mentions this?Mon, 02 Feb 2009 04:22:32 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]RBarryYoung (2/1/2009)[/b][hr]But Marios should be able to get the Actual from Profiler or tracing, right?[/quote]Yes, absolutely. Providing he traces the correct event. I seem to recall there are about 6 events relating to the exec plan and I can't recall offhand which produces what. BoL should say.Sun, 01 Feb 2009 23:34:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (2/1/2009)[/b][hr]The SELECT query is for capturing the system info; has nothing to do with the trigger.[/quote]I know that... I'm suggesting that it's giving you bad information.Sun, 01 Feb 2009 18:51:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]GilaMonster (2/1/2009)[/b][hr][quote][b]Marios Philippopoulos (2/1/2009)[/b][hr]I don't think it's the estimated exec plan, I obtained it from sys.dm_db_exec_requests. It was retrieved when the statement was actually executed...[/quote]It's the same as the 'estimated' plan. Both the plans produced by the "Estimated Execution Plan" and the ones retrieved from the plan cache have only the compile-time information. That means just the estimated row counts, data sizes, etc. Neither will have the actual values, which are very important for any form of performance tuning. The actual values won't be available with the "Estimated Execution plan" and they're not stored in the plan cache.[/quote]But Marios should be able to get the Actual from Profiler or tracing, right?Sun, 01 Feb 2009 18:38:28 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (2/1/2009)[/b][hr]I don't think it's the estimated exec plan, I obtained it from sys.dm_db_exec_requests. It was retrieved when the statement was actually executed...[/quote]It's the same as the 'estimated' plan. Both the plans produced by the "Estimated Execution Plan" and the ones retrieved from the plan cache have only the compile-time information. That means just the estimated row counts, data sizes, etc. Neither will have the actual values, which are very important for any form of performance tuning. The actual values won't be available with the "Estimated Execution plan" and they're not stored in the plan cache.Sun, 01 Feb 2009 14:06:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Jeff Moden (2/1/2009)[/b][hr][quote][b]Marios Philippopoulos (2/1/2009)[/b][hr][quote][b]RBarryYoung (1/31/2009)[/b][hr][quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads![/quote]Is this for one execution of the trigger? Or is it a total over a period of time?[/quote]It's a snapshot in time. Here is the query I use to capture the info:[code]SELECT R.start_time, R.status, R.command, R.database_id, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.wait_type, R.wait_time, R.last_wait_type, R.wait_resource , R.cpu_time , R.total_elapsed_time, R.reads , R.writes , R.logical_reads, T.text, P.query_planFROM sys.dm_exec_requests RINNER JOIN sys.dm_exec_sessions SON R.session_id = S.session_idOUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS TOUTER APPLY sys.dm_exec_query_plan(R.plan_handle) AS PWHERE R.session_id > 50;[/code][/quote]I think this may be where the cross-joins are... what is the ACTUAL row count from the TRIGGER itself. Add a rowcount to the trigger and see because I don't see anything in the trigger that should cause this.[/quote]The SELECT query is for capturing the system info; has nothing to do with the trigger.Sun, 01 Feb 2009 13:12:00 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]RBarryYoung (2/1/2009)[/b][hr]Marios, I think that what you gav eus before was the estimated execution plan. Can you get us the actual execution plan? IT should also be avaialable through tracing/profiler.[/quote]I don't think it's the estimated exec plan, I obtained it from sys.dm_db_exec_requests. It was retrieved when the statement was actually executed...Sun, 01 Feb 2009 13:08:40 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (2/1/2009)[/b][hr][quote][b]RBarryYoung (1/31/2009)[/b][hr][quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads![/quote]Is this for one execution of the trigger? Or is it a total over a period of time?[/quote]It's a snapshot in time. Here is the query I use to capture the info:[code]SELECT R.start_time, R.status, R.command, R.database_id, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.wait_type, R.wait_time, R.last_wait_type, R.wait_resource , R.cpu_time , R.total_elapsed_time, R.reads , R.writes , R.logical_reads, T.text, P.query_planFROM sys.dm_exec_requests RINNER JOIN sys.dm_exec_sessions SON R.session_id = S.session_idOUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS TOUTER APPLY sys.dm_exec_query_plan(R.plan_handle) AS PWHERE R.session_id > 50;[/code][/quote]I think this may be where the cross-joins are... what is the ACTUAL row count from the TRIGGER itself. Add a rowcount to the trigger and see because I don't see anything in the trigger that should cause this.Sun, 01 Feb 2009 08:19:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxMarios, I think that what you gav eus before was the estimated execution plan. Can you get us the actual execution plan? IT should also be avaialable through tracing/profiler.Sun, 01 Feb 2009 06:42:02 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]GilaMonster (2/1/2009)[/b][hr]Can you perhaps post the trigger code?[/quote]Sure, here it is. I had posted this code in a previous posting ([url]http://www.sqlservercentral.com/Forums/Topic630749-360-4.aspx[/url]), and you had correctly pointed out that some of the code at the start of the trigger body is not needed.Practically all of the work is done by the last UPDATE statement, as can also be seen by the exec plan (pls see my 2nd posting in this thread).My concern in the previous posting was on the effect the trigger had on tempdb. Here I'm focusing on the discrepancy between the huge number of reads and the modest number of rows updated in the base table.One of the responders in the previous post had this to say on the subject (highlighting my own), but this angle was not explored further:[quote][b]TheSQLGuru (1/8/2009)[/b][hr]It is my belief that even if you rewrite the trigger to only do the audit update on rows where something is different between inserted and deleted [b]the trigger will still have to hit 2+M rows in the base table to gather the comparison data thus 20M IOs and 3GB of tempdb space will still be used, even if it winds up that you are only truly modifying 1 row of actual data[/b]. That doesn't even make much sense to me at the moment since why affect 2M+ rows if you only modify 1?[/quote][b]I guess the point in the above is that an update of even a small number of rows in a large table will still have a huge impact on the total I/O (and memory use) involved, if a trigger like the one here is used.[/b] If this is the case, why is this not reflected in the exec plan?[code] CREATE TRIGGER [dbo].[TG_U_UpdTable]ON [dbo].[UpdTable]FOR UPDATEAS SET NOCOUNT ON DECLARE @ActionType char(1) SELECT @ActionType = CASE WHEN EXISTS( SELECT null FROM inserted ) THEN 'U' WHEN EXISTS( SELECT null FROM deleted ) THEN 'D' ELSE 'X' END IF @ActionType = 'X'BEGIN RETURNEND UPDATE DSET D.[db_updateDate] = getdate(), D.[db_updateBy] = system_userFROM [inserted] AS i INNER JOIN [dbo].[UpdTable] AS D ON i.[UpdTableOID] = D.[UpdTableOID] [/code]Sun, 01 Feb 2009 05:51:07 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxCan you perhaps post the trigger code?Sun, 01 Feb 2009 05:16:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxThanks guys for your responses.I should clarify that this is not a one-off event. Whenever the trigger is executed, the number of reads is always in the millions, while the number of rows updated in the base table is typically in the 30,000-row ballpark.Looking at the execution plan has not raised any flags (in my mind).I'm thinking, maybe the query I use to capture the info is not correct? (see my previous posting right above)Perhaps I should run a server-side trace zeroing in on the trigger execution to get an independent confirmation on the number of reads.Sun, 01 Feb 2009 05:15:51 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]RBarryYoung (1/31/2009)[/b][hr][quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads![/quote]Is this for one execution of the trigger? Or is it a total over a period of time?[/quote]It's a snapshot in time. Here is the query I use to capture the info:[code]SELECT R.start_time, R.status, R.command, R.database_id, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.wait_type, R.wait_time, R.last_wait_type, R.wait_resource , R.cpu_time , R.total_elapsed_time, R.reads , R.writes , R.logical_reads, T.text, P.query_planFROM sys.dm_exec_requests RINNER JOIN sys.dm_exec_sessions SON R.session_id = S.session_idOUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS TOUTER APPLY sys.dm_exec_query_plan(R.plan_handle) AS PWHERE R.session_id > 50;[/code]Sun, 01 Feb 2009 05:09:07 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxAh... true enough.Sat, 31 Jan 2009 21:22:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Jeff Moden (1/31/2009)[/b][hr][quote][b]RBarryYoung (1/31/2009)[/b][hr][quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; [font="Arial Black"][color="RED"]on one occasion [/color][/font]it has hit 73,421,728 logical reads![/quote]Is this for one execution of the trigger? Or is it a total over a period of time?[/quote]Heh... the force begins to flow in the other direction. ;)[/quote]But, an "occasion" can be either an event [i]or[/i] a point in time. It's a matter worth clarifying...Sat, 31 Jan 2009 21:21:01 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]RBarryYoung (1/31/2009)[/b][hr][quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; [font="Arial Black"][color="RED"]on one occasion [/color][/font]it has hit 73,421,728 logical reads![/quote]Is this for one execution of the trigger? Or is it a total over a period of time?[/quote]Heh... the force begins to flow in the other direction. ;)Sat, 31 Jan 2009 21:03:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads![/quote]Is this for one execution of the trigger? Or is it a total over a period of time?Sat, 31 Jan 2009 20:47:33 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspx[quote][b]Marios Philippopoulos (1/31/2009)[/b][hr]I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!There are two puzzling aspects about this:(1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)(2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).I have captured the number of logical reads and the execution plan by polling the [i]sys.dm_db_exec_requests [/i]DMV once a minute.How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?[/quote]This sounds like an accidental cross join or triangular join between two tables... you need to double check and see how many rows are in each condtion that will satisfy the join. You may be missing a join column or simply have not anticipated a possible many to many relationship.Sat, 31 Jan 2009 20:26:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxHere is the correct exec plan (attached)Sat, 31 Jan 2009 19:04:51 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic647604-360-1.aspxI have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!There are two puzzling aspects about this:(1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)(2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).I have captured the number of logical reads and the execution plan by polling the [i]sys.dm_db_exec_requests [/i]DMV once a minute.How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?Sat, 31 Jan 2009 18:54:45 GMTMarios Philippopoulos