ASE16 - sp_add_resource_limit...lock_count - Review

[Former Member]

Just the last week we had a scenario where in we ran out of locks on one of our core servers. The worst part was it happened in a span of 16 seconds. We have configured for 2 mil locks. We have tons of monitoring in place and none of the monitors could pickup of what happened. We have a suspect but no clear reason of why when set with "set rowcount 2000" this specific update would grab 2 mil locks. Anyway, my intent is not to bore you with my operational issues. I am sure all old hands would have faced various problems with server running out of locks. We had a feature request years ago to have a resource limit for number of locks. Our prayers have been answered...at least partially.

The syntax for adding defining resource limits is as follows. The biggest problem that I see is at least username or appname have to be specified while defining this resource limit. If there are 10000 users it is going to be that many times we have to define and at the same time if there are 2000 applications then it is that many entries. Both of them are NOT something we can manage. It would have been ideal if we were given the max number of locks per SPID configuration param. As designed right now we cannot protect our servers from rouge sql statements blowing the configured number of locks.

sp_add_resource_limit <username>,<appname>, "at all times", "lock_count", 10000