5

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.

2
  • 5
    Have you experimented with creating an in Memory OLTP version and running a typical workload against that? If so how did that get on? Jul 28 '16 at 7:16
  • 1
    In addition to exploring Martin's suggestion, have you root caused the deadlocks? In the vast majority of cases, deadlocks occur by design whether the dev knows it or not. If you can rewrite the queries to prevent deadlock, you may be able to get away from the coarse grain locking. Also, if you change nothing, what exactly is causing the bottleneck on the table? Lots of requests getting blocked because of high concurrency? IO not being able to keep up? A bit more detail will be helpful.
    – SQLmojoe
    Jul 28 '16 at 17:28
1

In my experience, Sql Server sometimes wants to scan small tables (even when they have indexes that you'd think would be used) which can lead to deadlocks on a hot table.

It might be a kludge solution, but I've had tables like this and was able to solve my problem by actually adding lots of 'bogus' rows to 'widen' the table and force Sql Server (optimizer) to seek instead of scan. I've also had to resort to using the ROWLOCK hint to get around the deadlock problem (your mileage may vary)

6
  • Adding bogus rows with meaningless values is a bad idea. If it generated a plan that seeks instead of scan, it's pure luck and not a consistent behavior. The QO relies on stats and histograms to build the best plan it can, bogus rows just provides bogus stats. Besides, if the table is small, there's probably little to no difference in perf between scan and seek, in some cases the scan might be faster.
    – SQLmojoe
    Jul 28 '16 at 17:22
  • As I said, it's a bit of a kludge, but the bogus rows were there to discourage the optimizer from choosing a scan because of the number of rows - scans can cause deadlocks regardless of how quick they are. Jul 28 '16 at 17:31
  • 1
    I get that but you're missing my point. Even with the bogus rows, the QO can still choose to scan because the stats are bad. If the bogus rows results in a histogram that is heavily skewed towards the same rows you're trying to retrieve, the QO may cost that scan lower than having to seek specific pages. Also, scans and seeks both can cause deadlocks. Great that it worked for you before but that's pure luck. It's not something that is consistently repeatable. In general, larger locks will have bigger chances of running into deadlocks faster but while true, that's a really generic statement.
    – SQLmojoe
    Jul 28 '16 at 17:39
  • I merely offered it as a suggestion because it has worked for me many times on small hot tables. In my case, it was consistently repeatable and so I stuck with it. Jul 28 '16 at 17:46
  • 1
    With all due respect, instead of bogus rows, you can change the fill factor to a low value, to force fewer rows per page. That's a kludge. Adding bogus rows is a bad habit. Aug 1 '16 at 0:58
1

I had a similar issue some time ago, I resolve it by disabling the lock escalation.

use tempdb

create table my_test(
  ind int not null,
  book varchar(50) not null,
  constraint pkmy_test primary key clustered (ind))

  select * from sys.tables
  where name ='my_test'

ALTER TABLE dbo.my_test SET (LOCK_ESCALATION=DISABLE)

ALTER TABLE dbo.my_test REBUILD
WITH (ALLOW_ROW_LOCKS = ON, ONLINE = ON, DATA_COMPRESSION = ROW);


  select * from sys.tables
  where name ='my_test'

How to disable locking on the table in SQL Server?

You can disable lock escalation at the table level via

ALTER TABLE mytable SET(LOCK_ESCALATION=DISABLE)

See https://msdn.microsoft.com/en-us/library/ms190273.aspx for details.

Important: lock escalation is a rudimentary resource governance method. Disabling it can result in significantly higher memory consumption which in turn can have all kinds of downstream impact so test thoroughly before turning this loose. This was a major issue with 32-bit platforms that limits VAS of any app to 4GB regardless of how much RAM was installed on the server. Anything above that was usable for data cache only. With 64-bit platforms, SQL Server is able to use whatever memory you have onboard for any purpose.

This one is a great article about this too: Kalen Delaney Did You Know? Turning Off Locking

After I have done that though, I had some problems that I have not seen anyone mentioning, (maybe it just happened to me???) but I will share it with you here.

When the lock escalation is disabled I had to twick something on my rebuild and reorganize of the indexes of that particular table, can't remember what it is now.

I suggest you test this before implementing it in live, and test also your routine that defrags your indexes.

2
  • 1
    How is this relevant to the situation described in the post? As the OP noted, it's a tiny table, so lock escalation won't even kick in here. Besides, the existing code already hints with coarse grain locks (TABLOCK?). There is no more escalation.
    – SQLmojoe
    Jul 28 '16 at 17:14
  • well, I had once this system that many stored procedure would go to a single table to get their next count, and update it, there were about 50 rows in that table and lots of deadlocks, and the way I described it above is how I solved that problem and it worked. regarding the TABLOCK and deadlocks, we should try to minimise the area locked not extend it and amplify concurrence., hence the restriction to lock only rows, the table is small anyways. Jul 28 '16 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.