SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Costly update trigger -70,000,000 logical reads for 30,000 rows updated!


Costly update trigger -70,000,000 logical reads for 30,000 rows updated!

Author
Message
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12902 Visits: 3766
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?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Attachments
UpdTable_Upd_Trigger.txt (85 views, 46.00 KB)
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12902 Visits: 3766
Here is the correct exec plan (attached)

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Attachments
UpdTable_Upd_Trigger_2.zip (26 views, 4.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217803 Visits: 41995
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?


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35514 Visits: 9518
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!

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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217803 Visits: 41995
RBarryYoung (1/31/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!

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


Heh... the force begins to flow in the other direction. Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35514 Visits: 9518
Jeff Moden (1/31/2009)
RBarryYoung (1/31/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!

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


Heh... the force begins to flow in the other direction. Wink


But, an "occasion" can be either an event or a point in time. It's a matter worth clarifying...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217803 Visits: 41995
Ah... true enough.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12902 Visits: 3766
RBarryYoung (1/31/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!

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


It's a snapshot in time. Here is the query I use to capture the info:

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;



__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12902 Visits: 3766
Thanks 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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227839 Visits: 46339
Can you perhaps post the trigger code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search