locked
Parallel trigger execution on multi-core single processor RRS feed

  • Question

  • If I have 6 tables with triggers on each, do they run in parallel or do they run serially?

    I would have thought they'd run on parallel but one of our staff is telling me otherwise.....

    Wednesday, September 3, 2014 8:44 AM

Answers

  • Hi Ciaran,

    AFTER triggers are executed after data modification statements, which, in your case is right after data has been inserted into the table. INSTEAD OF triggers are executed instead of data modification statement, e.g. at time when your insert statement is called.

    If you have the batch with 6 inserts running in the session, this session would execute those statements  sequentially - one after another. So your inserts and triggers would run one after another.

    If you have multiple sessions running separate INSERTs in parallel, those inserts and triggers would run in parallel. Those sessions/statements are independent from each other even though they can block each other if they deal with the same data.

    With all being said, triggers are not good from performance standpoint. Moreover, AFTER UPDATE and DELETE triggers can introduce index fragmentation. It is better to avoid them if it is all possible.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Uri DimantMVP Thursday, September 4, 2014 6:43 AM
    • Marked as answer by Ciaran Scolard Thursday, September 4, 2014 12:07 PM
    Wednesday, September 3, 2014 5:09 PM

All replies

  • Hello,

    Don't know what you mean with "parallel" here. In one statement you always can modify only data in one table and even if the table have ten triggers, they all will be executed in sequence one by one, so what could run here in parallel?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 3, 2014 9:56 AM
    Answerer
  • I mean that if I have inserts going into 6 tables and each table has it's own trigger, do they all execute at the same time or does one table have to wait until another tables trigger is finished.

    Seems highly unlikely to me.

    Wednesday, September 3, 2014 9:58 AM
  • Hi Ciaran,

    AFTER triggers are executed after data modification statements, which, in your case is right after data has been inserted into the table. INSTEAD OF triggers are executed instead of data modification statement, e.g. at time when your insert statement is called.

    If you have the batch with 6 inserts running in the session, this session would execute those statements  sequentially - one after another. So your inserts and triggers would run one after another.

    If you have multiple sessions running separate INSERTs in parallel, those inserts and triggers would run in parallel. Those sessions/statements are independent from each other even though they can block each other if they deal with the same data.

    With all being said, triggers are not good from performance standpoint. Moreover, AFTER UPDATE and DELETE triggers can introduce index fragmentation. It is better to avoid them if it is all possible.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Uri DimantMVP Thursday, September 4, 2014 6:43 AM
    • Marked as answer by Ciaran Scolard Thursday, September 4, 2014 12:07 PM
    Wednesday, September 3, 2014 5:09 PM
  • I already told you, one DML statement access always only one table; you can not insert with one statement into 6 different tables, so there nothing in parallel, they all run in sequence.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 3, 2014 5:12 PM
    Answerer
  • It's 6 separate inserts on 6 separate jobs, run in loops......
    Thursday, September 4, 2014 12:07 PM