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?

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!

RBarryYoung (1/31/2009)

---

Is this for one execution of the trigger? Or is it a total over a period of time?

Jeff Moden (1/31/2009)

---

Heh... the force begins to flow in the other direction. ;)

RBarryYoung (1/31/2009)

---

Is this for one execution of the trigger? Or is it a total over a period of time?

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_plan
FROM
	sys.dm_exec_requests R
INNER JOIN
	sys.dm_exec_sessions S
ON 
	R.session_id = S.session_id
OUTER APPLY 
	sys.dm_exec_sql_text(R.sql_handle) AS T
OUTER APPLY 
	sys.dm_exec_query_plan(R.plan_handle) AS P
WHERE
	R.session_id > 50;