Lock escalation not working

I occassionally see one of the spid holding around 10-20 million locks in
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>|||underprocessable|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>