Lock Escalation in Sql Server 2014

Question

Lock Escalation in Sql Server 2014

skavijay

Old Hand

Points: 301
More actions
September 5, 2014 at 12:19 pm

#397306

Hi Experts
I got a Sql server database 2005. Now I am in a plan to upgrade to 2014.
In the 2005 database I have one monster generic table. Which is being used by entire application. The application sends data in big string. I have a function that parses string put that in to that table. And related SPs uses that data process it based on business rules and finally it clears the data from that generic table. But the transaction is created and controlled by application. I do experiencing dead-locks in this process and resolving it by handling it as and when it comes. Now the main doubt is without changing much code I would want to fix this by using sql server 2014 lock escalation option.
My doubt is if I set Lock escalation to this monster table disabled. Whether it solves it or not.? I don't want this table continue experience dead lock issues again after upgrading to sql server 2014.
Appreciate in advance

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

As far as I'm aware, there are no changes to the lock escalation mechanisms in SQL 2014.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

skavijay Old Hand Points: 301 More actions · Answer 2

Hi

Thank you for reply.

So the table still be part of transaction and dead lock might occur is that what would you say even after disabling it?

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

skavijay (9/5/2014)
Hi
Thank you for reply.
So the table still be part of transaction and dead lock might occur is that what would you say even after disabling it?

Which feature or option, the "it" in your question above, are you thinking you can disable in SQL 2014 that you think might help your situation?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Nothing you've said has suggested that lock escalation is the cause, and there's no deadlock graph or query details provided, so no way anyone can say anything useful

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 5

skavijay (9/5/2014)
I do experiencing dead-locks in this process and resolving it by handling it as and when it comes.

I assume that you are talking about blocking not dead-locks, because you don't need to resolve dead-lock manually, it's done by the server automatically.

skavijay (9/5/2014)
My doubt is if I set Lock escalation to this monster table disabled. Whether it solves it or not.? I don't want this table continue experience dead lock issues again after upgrading to sql server 2014.

There is absolutely no guarantees that it will resolve blocking issue. Server may decide to start locking on a page level instead of a row, and this has nothing to do with Lock escalation, so 2 processes can still block each other. If you disable Lock escalation AND disable page lock on a table then the memory consumption can rise drastically. And as far as I know after the memory is reserved for locking mechanism, server will never release it back so other memory clerks can use it.

It looks like that the best way to fix the issue is to redesign the app to use temp tables.

Alex Suprun

skavijay Old Hand Points: 301 More actions · Answer 6

"It" means the lock_Escalation option and would want to set Disable for my new database and for the table that is being used by all modules.

skavijay Old Hand Points: 301 More actions · Answer 7

Hi after been experiencing good number of times I did handling in C# code when the dead lock error number comes then aborting and retrying again 🙂

But after knowing this disable option just want to know expert opinion whether that might helpful to me or not?

If it helps then I would want to set. But based on the experts opinion and based on more technical details about the lock_escalation option.

I am going to leave that as default. Let application code handle it as usual. 🙂

skavijay Old Hand Points: 301 More actions · Answer 8

Alexander Suprun (9/8/2014)
skavijay (9/5/2014)
I do experiencing dead-locks in this process and resolving it by handling it as and when it comes.
I assume that you are talking about blocking not dead-locks, because you don't need to resolve dead-lock manually, it's done by the server automatically.
skavijay (9/5/2014)
My doubt is if I set Lock escalation to this monster table disabled. Whether it solves it or not.? I don't want this table continue experience dead lock issues again after upgrading to sql server 2014.
There is absolutely no guarantees that it will resolve blocking issue. Server may decide to start locking on a page level instead of a row, and this has nothing to do with Lock escalation, so 2 processes can still block each other. If you disable Lock escalation AND disable page lock on a table then the memory consumption can rise drastically. And as far as I know after the memory is reserved for locking mechanism, server will never release it back so other memory clerks can use it.
It looks like that the best way to fix the issue is to redesign the app to use temp tables.

Thank You very much with. As I said above I am not going to disable this instead let application code handle as usual.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

skavijay (9/8/2014)
"It" means the lock_Escalation option and would want to set Disable for my new database and for the table that is being used by all modules.

Can attach one or a few of your deadlock graphs to a new post in this thread?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

skavijay (9/8/2014)
But after knowing this disable option just want to know expert opinion whether that might helpful to me or not?

No idea at all, because you haven't given any information about the table, query or deadlock. Hence no one can give any useful, relevant information at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass