May 28, 2019 at 2:24 am
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
May 28, 2019 at 11:14 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 29, 2019 at 6:59 am
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.
May 29, 2019 at 12:00 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 30, 2019 at 12:07 am
Thanks
Looks like the option to mark your reply as the answer is gone.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply