TheSQLGuru (2/3/2009)

---

Marios Philippopoulos (1/31/2009)

---

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 sys.dm_db_exec_requests 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?

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 updtable

3) You are hash matching the above

4) then doing a sort of the hash result

No 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. 🙂