I've inherited a very volatile table which is a map of who holds what resource in the system. At any given moment, there could be a dozen inserts/deletes/reads going against that table. However, there are never any more than 30-40 rows in the system.
The system was written in the SQL 2000 era and the access to the table is serialized via sp_getapplock/sp_releaseapplock system sprocs, so that only 1 request is modifying the table. In addition, the INSERT & DELETE statements execute
WITH (TABLOCK). Reading the notes from a decade ago, it states that without these restrictions, the system would experience non-stop deadlocks.
I've ported the database to SQL Server 2016 Enterprise Edition. Now that the throughput of the system has increased 10 fold, this table is easily the biggest bottleneck.
What are my options for a table as volatile as this with SQL 2016?
I am looking for fast (hopefully concurrent) access and no deadlocks.