Can a simple Alter Trigger statement cause serious blocking?

Most of the time it would not as it is a extremely quick operation but it certainly has the potential to. I ran across this the other day: an admin user was running a simple, “innocent”, Alter Trigger statement on a production SQL Server, he had done this many a times without any trouble so he had no reason to worry. Unfortunately, this time something went wrong – the Alter Trigger statement which usually executes instantaneously wasn’t completing and all of a sudden blocking alerts started firing left and right! He killed the Alter Trigger and all went back to normal, but he was puzzled: what had just happened?

After a quick investigation here is what I found: the Alter Trigger had blocked virtually everyone (it was a trigger on a critical, heavily used table) but it wasn’t the head blocker! Instead, a “long” query that appeared to be doing some sort of data aggregation had blocked the Alter Trigger statement. But wait how can that be, the query was written with the NOLOCK option - apparently the person who wrote it knew that this query could potentially wreck-havoc on the production system so he tried to make sure he wouldn’t block anyone. And yes, most of the time his query would not cause any trouble to “others” except… while the nolock directive ensures that the query takes no data locks the query still takes what’s called a schema stability lock (in other words it’s saying: I will read un-committed data but don’t change the schema on me while I am reading) which prevents the Alter Trigger from taking the schema modification lock it needs. So, while the Alter Trigger is patiently waiting in line to get the schema modification lock it needs the rest of the processes that desperately need access to that table start piling up!