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?
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