0

I'm trouble-shooting some performance problems in a SQL Server database, and one of the things I found is that the stored procedures frequently disable and re-enable triggers. I'd love to have the authority and the time to redesign the thing so that it doesn't do this, but I don't.

As things stand at the moment, it's entirely possible for one stored procedure to disable a trigger, and then for another stored procedure to execute and want to disable the same trigger. Am I right in thinking that when this happens, the second stored procedure will have to wait for the first stored procedure to re-enable the trigger and release it's Sch-M lock on the table, so that the second stored procedure can acquire it's own lock and disable the trigger that the first stored procedure just re-enabled? If so, would it help at all if I modified the stored procedures to check if the trigger is already disabled before attempting to disable it?

Brendan Reynolds
  • 991
  • 2
  • 9
  • 19
  • Wow looks like you waded into a real mess here. Without knowing details, I don't think this particular change will make any difference to performance issues. You need to find which "module' yields the performance issue, then divide it down until you find the query with the issue. Or it could simply be that your SQL Server us underpowered... or a random bad query from an external user is stealing your processing power. – Nick.Mc Jul 07 '20 at 11:06
  • You have one answer - but beware that this is a very risky change. The SCH-M lock might have a similar effect to serializing access. Remember - you change it, you own it. – SMor Jul 07 '20 at 11:32
  • You could use [application locks](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15) to control access to the triggers. Good news: You have fairly fine control over how to handle locking, waiting and the like. Bad news: Application locks have no "meaning" to the DB. You can take a lock on a chicken dinner for all it cares. One SP, or user, who doesn't bother getting and holding the locks can make the whole thing unravel. – HABO Jul 07 '20 at 14:03

1 Answers1

1

Am I right in thinking that when this happens, the second stored procedure will have to wait for the first stored procedure to re-enable the trigger and release

Yes, as long as the first procedure is holding a transaction open.

If so, would it help at all if I modified the stored procedures to check if the trigger is already disabled before attempting to disable it?

You can do this by checking is_disabled on sys.triggers, but when doing so you would have to read with (nolock) (read uncommitted isolation level).

Nick is quite right, you have wandered into a minefield here. Note that you won't be able to modify data in the table while the first procedure runs anyway

allmhuran
  • 4,154
  • 1
  • 8
  • 27
  • Thank you for your answer. There's no explicit transaction, but from my research on the issue I understand that ALTER TABLE DISABLE TRIGGER requires a Sch-M lock on the table, and that SQL Server allows only one Sch-M lock on a table at a time, so if the first stored procedure has an Sch-M lock, the second stored procedure is going to have to wait until that lock is released, is that not correct? I know how to check if the trigger is already disabled, I just want to check that it will be worth doing so, as I will have to add this check to multiple stored procedures. – Brendan Reynolds Jul 07 '20 at 11:26
  • If there is no transaction, then as soon as the first procedure issues the disable trigger, the trigger is disabled, and no lock is being held. But this is obviously incredibly unsafe if other processes could be running that are disabling/enabling triggers, because by disabling the trigger the proc is indicating an expected behaviour, which will not be the actual behaviour if someone else comes in underneath it and changes it. – allmhuran Jul 07 '20 at 11:28
  • Also, minor point, the wait type will be LCK_M_SCH_S on the modification lock (for interests sake) – allmhuran Jul 07 '20 at 11:29