1

We have a After Insert and Update trigger on one of our tables. The trigger basically makes a json payload and enqueues it in a RabbitMQ system.

Today a large insert script was run on the table (over 50,000 inserts). This scenario had not been tested or accounted for and now we are having performance problems on that database.

We notice that the RabbitMQ is having the records trickle in slowly over a long period of time. Even though the data has been in place for a while (because it is an After Trigger).

It seems as if the After triggers have been queued up somehow and are very slowly working through the system.

How are the After Trigger events kept track of for execution? Are they queued somewhere? Is there a way I can clear them out?

Improve this question

1 Answer 1

Reset to default
3

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out:

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

So my guess is either your original INSERT is still executing (which can then be aborted and rolled back) or the issue is somewhere between after your Trigger runs and the mechanism you're using to dump the data into RabbitMQ.

You can use sp_WhoIsActive to determine if your INSERT statement is still running and to get the SPID of the process so you can abort it and rollback. To abort you'd have to run KILL 123 (where 123 = the SPID of your INSERT).

Side note, if by 50,000 inserts, you mean 50,000 records in one INSERT statement, then that's small and should be performant. If you actually mean 50,000 separate INSERT statements then that's a different story that can take much longer to complete the INSERT.

Improve this answer
5
  • Thank you for the information. sp_WhoIsActive shows that the insert statement is done. (And a query shows that the data is all there.) That trigger calls a SQL CLR stored procedure. We are still getting data sent by that CLR Sproc to RabbitMQ. I am guessing that there is some buffering process where that sproc has queued the calls and is working through them. (Though really, I have no idea what could be causing what I am seeing.) I am finding it hard to get any kind of metrics on the SQL CLR Sproc.
    – Vaccano
    Commented Apr 28, 2021 at 0:01
  • @Vaccano Interesting, yea so that would fall more into the latter of my two guesses of what you got going on. You should see multiple processes for the CLR Stored Procedure running too though with sp_WhoIsActive. If you don't see the CLR processes running, then I'd assume even that part is done. Not sure what code your CLR is doing but if it's executing something either asynchronously or external like a web service to hit RabbitMQ, then likely at this point your data is already outside the domain of your SQL Server Instance and the trickling is occuring via that outside...
    – J.D.
    Commented Apr 28, 2021 at 1:42
  • ...process / web service, or even potentially at the point of insertion into RabbitMQ by their own governance (though I'd find that unlikely given that RabbitMQ is supposed to be able to handle queuing large amounts of data).
    – J.D.
    Commented Apr 28, 2021 at 1:43
  • 1
    Thank you for the tips and information! It was helpful in figuring out what was going on!
    – Vaccano
    Commented Apr 28, 2021 at 2:11
  • @Vaccano Absolutely, glad to be of help! Best of luck! 🙂
    – J.D.
    Commented Apr 28, 2021 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.