lock escalation on a small table (13 rows)

Question

lock escalation on a small table (13 rows)

Gamleur84

Default port

Points: 1475
More actions
May 28, 2019 at 2:24 am

#3647682

Hi
We have encountered performance issues in the application due to blocking and locking on a table in SQL. The performance team fixed the problem by disabling lock escalation on the said table. Since then, the performance are better.
My issue with this is that said table has only 13 rows. I understand that locks and blocks on this table was the cause of the performance issue, but how could that have been fixed by disabling lock escalation ?
I am missing knowledge. Would it be possible for SQL to escalate fine-grained locks to a table lock for a table with 13 rows ?
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

With such a tiny table, the engine thought that just locking the entire table would be easier than locking individual rows. Or, possibly, you're updating a significant number of those rows all at once, so, same result.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gamleur84 Default port Points: 1475 More actions · Answer 2

hi,

So it is possible that a lock escalation happens on a small table ? I thought lock escalation was essentially to prevent high memory consumption from many locks on big tables.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

It's to do the most efficient locking possible. Depending on what you're doing, a table lock may be more efficient. To be sure about all this, we'd need to gather a bunch more data. However, yes, it's possible.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gamleur84 Default port Points: 1475 More actions · Answer 4

Thanks

Looks like the option to mark your reply as the answer is gone.