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.
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail