Recently I had a situation where I was looking at a SQL Instance due to a contractor who controls the system wanting to drop some indexes and they couldn’t because they couldn’t get a lock request. Sadly, they were using the GUI to try to accomplish this. Always makes me sad when we aren’t using scripts. So, I took a deep drive into what was actually running on this system. I had them send over scripts for what they were trying to do. Meanwhile, I looked at the system and discovered some things. First, we had really had RESOURCE_SEMAPHORE wait types for two days which indicates it waiting on memory. So, I took a look at the memory clerks. The top memory clerk was for lock manager at 32 GBs and the system only has 64 GBs, which is abnormal.
Read on for the full story. This particular fix worked, I presume, because the ORM was sending all parameters as Unicode, whereas none of the strings in the database were Unicode—they were all VARCHAR rather than NVARCHAR.