sql local data: locks

Showing posts with label locks. Show all posts

Friday, March 30, 2012

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:
> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:
>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>> Mike
>> The optimiser escalates to a full table lock when it thinks it will be
>> more
>> efficient way of processing the data than using page locks. There are a
>> number of things you can do if you think it should not be performing a
>> table
>> lock.
>> Are your statistics up to date? If you don't regularly update statistics,
>> the optimiser is working with at least one hand tied behind it's back. If
>> the
>> statistics are not up tp date it may be making incorrect choices.
>> Do you have suitable indexes? If you have indexes on the data you are
>> updating (especially a clustered index) it will be easier for the
>> optimiser
>> to use page locks rather than table locks, if suitable.
>> It may be you are updating enough of the table that a table lock is the
>> correct option. In this case you can split up your updates into smaller
>> transactions, small enough that the optimiser uses page locks.
>> Hope this helps
>> John
>> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>
>
--
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:
> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> > Mike
> >
> > The optimiser escalates to a full table lock when it thinks it will be
> > more
> > efficient way of processing the data than using page locks. There are a
> > number of things you can do if you think it should not be performing a
> > table
> > lock.
> >
> > Are your statistics up to date? If you don't regularly update statistics,
> > the optimiser is working with at least one hand tied behind it's back. If
> > the
> > statistics are not up tp date it may be making incorrect choices.
> >
> > Do you have suitable indexes? If you have indexes on the data you are
> > updating (especially a clustered index) it will be easier for the
> > optimiser
> > to use page locks rather than table locks, if suitable.
> >
> > It may be you are updating enough of the table that a table lock is the
> > correct option. In this case you can split up your updates into smaller
> > transactions, small enough that the optimiser uses page locks.
> >
> > Hope this helps
> >
> > John
> >
> > "Mike" wrote:
> >
> >> It appears that when we issue an UPDATE statement on a table, it locks
> >> the
> >> WHOLE table so that any other users can not complete SELECT queries on
> >> the
> >> table (without using "WITH (nolock)" in the SELECT statement)
> >>
> >> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
> >> an
> >> update, so that maybe it takes longer, but it only locks 1 row or page at
> >> a
> >> time?
> >>
> >>
> >>
>
>

Locking

Wednesday, March 28, 2012

Lock that reads original data BUT does not honour exclusive locks?

Hi all,
Is there a lock that is similar to NOLOCK hint by not honouring exclusive
locks, BUT reads original (committed) data (no dirty reads) instead?
Pls kindly advise. Help is much appreciated. TQ.Not in SQL Server 2000, but there will be in SQL Server 2005.
Jacco Schalkwijk
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:42536077-EBBC-4025-9035-952D44E556BB@.microsoft.com...
> Hi all,
> Is there a lock that is similar to NOLOCK hint by not honouring exclusive
> locks, BUT reads original (committed) data (no dirty reads) instead?
> Pls kindly advise. Help is much appreciated. TQ.

Lock resources in Bpool ??

I have read that the memory allocated for locks is finite. I wanted to know
what that finite number is and in which compartment of the SQL Server memory
portion does it reside in..BPool or MemToLeave ? Also what about user
connection info ..where is that stored ?Both lock memory and user connection memory come from the buffer pool.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OXrSsnLBFHA.3588@.TK2MSFTNGP11.phx.gbl...
>I have read that the memory allocated for locks is finite. I wanted to know
> what that finite number is and in which compartment of the SQL Server
> memory
> portion does it reside in..BPool or MemToLeave ? Also what about user
> connection info ..where is that stored ?
>|||And whats the finite amount of memory allocated for locks ?
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:ePRsJ9QBFHA.608@.TK2MSFTNGP15.phx.gbl...
> Both lock memory and user connection memory come from the buffer pool.
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OXrSsnLBFHA.3588@.TK2MSFTNGP11.phx.gbl...
> >I have read that the memory allocated for locks is finite. I wanted to
know
> > what that finite number is and in which compartment of the SQL Server
> > memory
> > portion does it reside in..BPool or MemToLeave ? Also what about user
> > connection info ..where is that stored ?
> >
> >
>|||It is a moving value based on a percentage of buffer pool resources. I
wouldn't be concerned with the actual value, as it could change from release
to release. "Inside SQL Server 2000" has a good chapter on lock manager
internals.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OKHfoARBFHA.3592@.TK2MSFTNGP11.phx.gbl...
> And whats the finite amount of memory allocated for locks ?
> "Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
> news:ePRsJ9QBFHA.608@.TK2MSFTNGP15.phx.gbl...
>> Both lock memory and user connection memory come from the buffer pool.
>> --
>> Ryan Stonecipher
>> Microsoft Sql Server Storage Engine, DBCC
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:OXrSsnLBFHA.3588@.TK2MSFTNGP11.phx.gbl...
>> >I have read that the memory allocated for locks is finite. I wanted to
> know
>> > what that finite number is and in which compartment of the SQL Server
>> > memory
>> > portion does it reside in..BPool or MemToLeave ? Also what about user
>> > connection info ..where is that stored ?
>> >
>> >
>>
>

Lock resources in Bpool ??

Friday, March 23, 2012

Lock Information

Looking under Locks/Object I noticed the following items:
master.dbo.spt_values
tempdb.dbo.##lockinfo75
The lock mode is X (Exclusive Insert/delete or update)
Process details show 'sa' as the user
The Databases are all working okay (with 80+ users)
Is there any way of checking what this is (presumably a temp table was created as part of the process) ?
Also what is spt_values used for ?
thanks
Gerry
This is a temp table used by one of the stored procedures EM calls when you
look up information such as locks. It's nothing to worry about.

Andrew J. Kelly
SQL Server MVP
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:6F9059D0-29EF-4B05-B8FF-92388071AC37@.microsoft.com...
> Looking under Locks/Object I noticed the following items:
> master.dbo.spt_values
> tempdb.dbo.##lockinfo75
> The lock mode is X (Exclusive Insert/delete or update)
> Process details show 'sa' as the user
> The Databases are all working okay (with 80+ users)
> Is there any way of checking what this is (presumably a temp table was
created as part of the process) ?
> Also what is spt_values used for ?
> thanks
> Gerry

Lock Information

I need some help in interpreting the information displayed under
Management -> Current Activity -> Locks / Process ID in Enterprise
Manager.
There seems to be a number of locks against the "Water.dbo.sched_lock"
object. The values in the Index column are different for the different
rows: "Gazetteer", "Employees" and "pk_workbank...".
The strange thing is that these are not indexes of the Sched_Lock
table. Gazetteer and Employees refer to 2 different tables within the
database.
Why are they being displayed against a lock which is supposed to be
held against the "sched_lock" table?No ideas, anyone?|||I never use that to monitor locks. A few years ago there
were posts like yours so I played around with viewing locks
from Enterprise Manager and found that it's pretty easy to
get bogus, useless info. I specifically remember seeing the
wrong table references. And then if you look at the stored
procedure used by Enterprise Manager,
sp_MSset_currency_activity, you can see how the results
wouldn't be too reliable.
Use sp_lock, sp_who2 to monitor things. Or even better is
the stored procedure Erland wrote:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
-Sue
On 7 Sep 2006 09:39:26 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>I need some help in interpreting the information displayed under
>Management -> Current Activity -> Locks / Process ID in Enterprise
>Manager.
>There seems to be a number of locks against the "Water.dbo.sched_lock"
>object. The values in the Index column are different for the different
>rows: "Gazetteer", "Employees" and "pk_workbank...".
>The strange thing is that these are not indexes of the Sched_Lock
>table. Gazetteer and Employees refer to 2 different tables within the
>database.
>Why are they being displayed against a lock which is supposed to be
>held against the "sched_lock" table?|||Thanks very much Sue - it's a pain that a part of Enterprise Manager
itself would be unreliable.
I've had a look at sp_MSset_current_activity, and I've seen where the
Index column is likely to get mixed up. I can't see why the Table
column would be unreliable though - it seems like a fair bet to me that
the data in the table column is correct, and the locks are being held
against the "sched_lock" table. Would you agree?|||In your case, yes.
In terms of the other areas of unreliability in that stored
procedure, look at the isolation levels being used.
Many GUI tools for databases are have issues - not just
Microsoft's. You're often better off just knowing the SQL
commands to execute. It generally gives you more flexibility
in writing some of your own scripts to retrieve the data you
want, with the information you need or care about.
-Sue
On 11 Sep 2006 08:21:22 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>Thanks very much Sue - it's a pain that a part of Enterprise Manager
>itself would be unreliable.
>I've had a look at sp_MSset_current_activity, and I've seen where the
>Index column is likely to get mixed up. I can't see why the Table
>column would be unreliable though - it seems like a fair bet to me that
>the data in the table column is correct, and the locks are being held
>against the "sched_lock" table. Would you agree?|||Indeed.
Thanks very much.sql

Lock Information

I need some help in interpreting the information displayed under
Management -> Current Activity -> Locks / Process ID in Enterprise
Manager.
There seems to be a number of locks against the "Water.dbo.sched_lock"
object. The values in the Index column are different for the different
rows: "Gazetteer", "Employees" and "pk_workbank...".
The strange thing is that these are not indexes of the Sched_Lock
table. Gazetteer and Employees refer to 2 different tables within the
database.
Why are they being displayed against a lock which is supposed to be
held against the "sched_lock" table?No ideas, anyone?|||I never use that to monitor locks. A few years ago there
were posts like yours so I played around with viewing locks
from Enterprise Manager and found that it's pretty easy to
get bogus, useless info. I specifically remember seeing the
wrong table references. And then if you look at the stored
procedure used by Enterprise Manager,
sp_MSset_currency_activity, you can see how the results
wouldn't be too reliable.
Use sp_lock, sp_who2 to monitor things. Or even better is
the stored procedure Erland wrote:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
-Sue
On 7 Sep 2006 09:39:26 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:

>I need some help in interpreting the information displayed under
>Management -> Current Activity -> Locks / Process ID in Enterprise
>Manager.
>There seems to be a number of locks against the "Water.dbo.sched_lock"
>object. The values in the Index column are different for the different
>rows: "Gazetteer", "Employees" and "pk_workbank...".
>The strange thing is that these are not indexes of the Sched_Lock
>table. Gazetteer and Employees refer to 2 different tables within the
>database.
>Why are they being displayed against a lock which is supposed to be
>held against the "sched_lock" table?|||Thanks very much Sue - it's a pain that a part of Enterprise Manager
itself would be unreliable.
I've had a look at sp_MSset_current_activity, and I've seen where the
Index column is likely to get mixed up. I can't see why the Table
column would be unreliable though - it seems like a fair bet to me that
the data in the table column is correct, and the locks are being held
against the "sched_lock" table. Would you agree?|||In your case, yes.
In terms of the other areas of unreliability in that stored
procedure, look at the isolation levels being used.
Many GUI tools for databases are have issues - not just
Microsoft's. You're often better off just knowing the SQL
commands to execute. It generally gives you more flexibility
in writing some of your own scripts to retrieve the data you
want, with the information you need or care about.
-Sue
On 11 Sep 2006 08:21:22 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:

>Thanks very much Sue - it's a pain that a part of Enterprise Manager
>itself would be unreliable.
>I've had a look at sp_MSset_current_activity, and I've seen where the
>Index column is likely to get mixed up. I can't see why the Table
>column would be unreliable though - it seems like a fair bet to me that
>the data in the table column is correct, and the locks are being held
>against the "sched_lock" table. Would you agree?|||Indeed.
Thanks very much.

Lock Information

Looking under Locks/Object I noticed the following items:
master.dbo.spt_values
tempdb.dbo.##lockinfo75
The lock mode is X (Exclusive Insert/delete or update)
Process details show 'sa' as the user
The Databases are all working okay (with 80+ users)
Is there any way of checking what this is (presumably a temp table was creat
ed as part of the process) ?
Also what is spt_values used for ?
thanks
GerryThis is a temp table used by one of the stored procedures EM calls when you
look up information such as locks. It's nothing to worry about.
Andrew J. Kelly
SQL Server MVP
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:6F9059D0-29EF-4B05-B8FF-92388071AC37@.microsoft.com...
> Looking under Locks/Object I noticed the following items:
> master.dbo.spt_values
> tempdb.dbo.##lockinfo75
> The lock mode is X (Exclusive Insert/delete or update)
> Process details show 'sa' as the user
> The Databases are all working okay (with 80+ users)
> Is there any way of checking what this is (presumably a temp table was
created as part of the process) ?
> Also what is spt_values used for ?
> thanks
> Gerry

lock info

hi,
from Activity Monitor, i found that there's a process (suspended) holding
over a thousand locks on a table named tbl_BusinessRecordDetailed.
i have 2 questions:
#1 - is there a way to find out which row it's locking?
#2 - what exactly is causing the locks? since the mode is S, which means
it's read only'
much appreciated.
from activity monitor:
resouce_type is all KEY
request_mode is all S
request_type is all LOCK
request_status is all GRANT
resouce_lock_partition is all 0
request_reference_count is all 0
request_exec_context_id is all 0
request_request_id is all 0
request_lifetime is all 1
request_owner_type is all TRANSACTION
but resource_description are pretty much all different, some examples:
1:210617
1:289260
(8800ee1b2515)
(b000d565e6d8)
(af008d6eefc0)
below is the table definition and the indexes:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
[recordID] [int] IDENTITY(1,1) NOT NULL,
[rowVersion] [timestamp] NOT NULL,
[name] [varchar](200) NULL,
[address] [varchar](200) NULL,
[crossstreet] [varchar](200) NULL,
[neighborhood] [varchar](200) NULL,
[borough] [varchar](200) NULL,
[city] [varchar](50) NULL,
[zip] [varchar](20) NULL,
[state] [varchar](2) NULL,
[telephone] [varchar](200) NULL,
[fax] [varchar](10) NULL,
[url] [varchar](500) NULL,
[bookingUrl] [varchar](500) NULL,
[bus] [varchar](200) NULL,
[subway] [varchar](300) NULL,
[description] [varchar](max) NULL,
[hours] [varchar](2000) NULL,
[admission] [varchar](2000) NULL,
[otherinfo] [varchar](1000) NULL,
[specialDirection] [varchar](max) NULL,
[sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
DEFAULT ((0)),
[dateCreated] [datetime] NOT NULL CONSTRAINT
[DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
[copyright] [varchar](100) NULL,
[mappedVarchar200_1] [varchar](200) NULL,
[mappedVarchar200_2] [varchar](200) NULL,
[mappedVarchar200_3] [varchar](200) NULL,
[mappedVarchar200_4] [varchar](200) NULL,
[mappedVarchar2000_1] [varchar](2000) NULL,
[mappedVarchar2000_2] [varchar](2000) NULL,
[mappedVarcharMax_1] [varchar](max) NULL,
[dateModified] [datetime] NULL,
[email] [varchar](50) NULL,
CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
(
[recordID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name = N'idx_biz_datecreated')
CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateCreated] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name = N'idx_biz_datemodifed')
CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
[dbo].[tbl_BusinessRecordDetailed]
(
[dateModified] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]Hi
Your lock is a shared lock so it will not be stopping other processes from
reading this information see
http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
Using DBCC INPUTBUFFER on the SID for the process should indicate the last
statement issued by the client.
John
"tin" wrote:
> hi,
> from Activity Monitor, i found that there's a process (suspended) holding
> over a thousand locks on a table named tbl_BusinessRecordDetailed.
> i have 2 questions:
> #1 - is there a way to find out which row it's locking?
> #2 - what exactly is causing the locks? since the mode is S, which means
> it's read only'
> much appreciated.
>
> from activity monitor:
> resouce_type is all KEY
> request_mode is all S
> request_type is all LOCK
> request_status is all GRANT
> resouce_lock_partition is all 0
> request_reference_count is all 0
> request_exec_context_id is all 0
> request_request_id is all 0
> request_lifetime is all 1
> request_owner_type is all TRANSACTION
> but resource_description are pretty much all different, some examples:
> 1:210617
>
> 1:289260
>
> (8800ee1b2515)
>
> (b000d565e6d8)
>
> (af008d6eefc0)
>
>
>
> below is the table definition and the indexes:
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> BEGIN
> CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> [recordID] [int] IDENTITY(1,1) NOT NULL,
> [rowVersion] [timestamp] NOT NULL,
> [name] [varchar](200) NULL,
> [address] [varchar](200) NULL,
> [crossstreet] [varchar](200) NULL,
> [neighborhood] [varchar](200) NULL,
> [borough] [varchar](200) NULL,
> [city] [varchar](50) NULL,
> [zip] [varchar](20) NULL,
> [state] [varchar](2) NULL,
> [telephone] [varchar](200) NULL,
> [fax] [varchar](10) NULL,
> [url] [varchar](500) NULL,
> [bookingUrl] [varchar](500) NULL,
> [bus] [varchar](200) NULL,
> [subway] [varchar](300) NULL,
> [description] [varchar](max) NULL,
> [hours] [varchar](2000) NULL,
> [admission] [varchar](2000) NULL,
> [otherinfo] [varchar](1000) NULL,
> [specialDirection] [varchar](max) NULL,
> [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> DEFAULT ((0)),
> [dateCreated] [datetime] NOT NULL CONSTRAINT
> [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> [copyright] [varchar](100) NULL,
> [mappedVarchar200_1] [varchar](200) NULL,
> [mappedVarchar200_2] [varchar](200) NULL,
> [mappedVarchar200_3] [varchar](200) NULL,
> [mappedVarchar200_4] [varchar](200) NULL,
> [mappedVarchar2000_1] [varchar](2000) NULL,
> [mappedVarchar2000_2] [varchar](2000) NULL,
> [mappedVarcharMax_1] [varchar](max) NULL,
> [dateModified] [datetime] NULL,
> [email] [varchar](50) NULL,
> CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> (
> [recordID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => N'idx_biz_datecreated')
> CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateCreated] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> GO
> IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => N'idx_biz_datemodifed')
> CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> [dbo].[tbl_BusinessRecordDetailed]
> (
> [dateModified] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>
>|||hmm, actually, there have been "somthing" that blocking some records in that
table that other process can't update them. as simple as an update like
"update tbl_businessrecorddetailed set name = 'whatever' where recordid =123" would just hang. just certain records are like that while most others
are fine. but after restarting the db service, it's fine. but the problem
would return after a while.
since the process is suspended, can i just kill it as a quick fix for now?
but i need to find out the cause going forward, any ideas'
thanks.
"John Bell" wrote:
> Hi
> Your lock is a shared lock so it will not be stopping other processes from
> reading this information see
> http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
> and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
> Using DBCC INPUTBUFFER on the SID for the process should indicate the last
> statement issued by the client.
>
> John
> "tin" wrote:
> > hi,
> >
> > from Activity Monitor, i found that there's a process (suspended) holding
> > over a thousand locks on a table named tbl_BusinessRecordDetailed.
> >
> > i have 2 questions:
> >
> > #1 - is there a way to find out which row it's locking?
> >
> > #2 - what exactly is causing the locks? since the mode is S, which means
> > it's read only'
> >
> > much appreciated.
> >
> >
> > from activity monitor:
> >
> > resouce_type is all KEY
> > request_mode is all S
> > request_type is all LOCK
> > request_status is all GRANT
> > resouce_lock_partition is all 0
> > request_reference_count is all 0
> > request_exec_context_id is all 0
> > request_request_id is all 0
> > request_lifetime is all 1
> > request_owner_type is all TRANSACTION
> >
> > but resource_description are pretty much all different, some examples:
> > 1:210617
> >
> >
> >
> > 1:289260
> >
> >
> >
> > (8800ee1b2515)
> >
> >
> >
> > (b000d565e6d8)
> >
> >
> >
> > (af008d6eefc0)
> >
> >
> >
> >
> >
> >
> > below is the table definition and the indexes:
> >
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> > BEGIN
> > CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> > [recordID] [int] IDENTITY(1,1) NOT NULL,
> > [rowVersion] [timestamp] NOT NULL,
> > [name] [varchar](200) NULL,
> > [address] [varchar](200) NULL,
> > [crossstreet] [varchar](200) NULL,
> > [neighborhood] [varchar](200) NULL,
> > [borough] [varchar](200) NULL,
> > [city] [varchar](50) NULL,
> > [zip] [varchar](20) NULL,
> > [state] [varchar](2) NULL,
> > [telephone] [varchar](200) NULL,
> > [fax] [varchar](10) NULL,
> > [url] [varchar](500) NULL,
> > [bookingUrl] [varchar](500) NULL,
> > [bus] [varchar](200) NULL,
> > [subway] [varchar](300) NULL,
> > [description] [varchar](max) NULL,
> > [hours] [varchar](2000) NULL,
> > [admission] [varchar](2000) NULL,
> > [otherinfo] [varchar](1000) NULL,
> > [specialDirection] [varchar](max) NULL,
> > [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> > DEFAULT ((0)),
> > [dateCreated] [datetime] NOT NULL CONSTRAINT
> > [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> > [copyright] [varchar](100) NULL,
> > [mappedVarchar200_1] [varchar](200) NULL,
> > [mappedVarchar200_2] [varchar](200) NULL,
> > [mappedVarchar200_3] [varchar](200) NULL,
> > [mappedVarchar200_4] [varchar](200) NULL,
> > [mappedVarchar2000_1] [varchar](2000) NULL,
> > [mappedVarchar2000_2] [varchar](2000) NULL,
> > [mappedVarcharMax_1] [varchar](max) NULL,
> > [dateModified] [datetime] NULL,
> > [email] [varchar](50) NULL,
> > CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> > (
> > [recordID] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > ) ON [PRIMARY]
> > END
> > GO
> >
> > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > N'idx_biz_datecreated')
> > CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> > [dbo].[tbl_BusinessRecordDetailed]
> > (
> > [dateCreated] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > GO
> >
> > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > N'idx_biz_datemodifed')
> > CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> > [dbo].[tbl_BusinessRecordDetailed]
> > (
> > [dateModified] ASC
> > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> >
> >
> >
> >|||Hi
sp_who2 has a blk column that may show what spid is blocking a process. You
may also want to look at http://support.microsoft.com/kb/271509 and run the
sp_blocker_pss80 procedure.
John
"tin" wrote:
> hmm, actually, there have been "somthing" that blocking some records in that
> table that other process can't update them. as simple as an update like
> "update tbl_businessrecorddetailed set name = 'whatever' where recordid => 123" would just hang. just certain records are like that while most others
> are fine. but after restarting the db service, it's fine. but the problem
> would return after a while.
> since the process is suspended, can i just kill it as a quick fix for now?
> but i need to find out the cause going forward, any ideas'
> thanks.
>
>
> "John Bell" wrote:
> > Hi
> >
> > Your lock is a shared lock so it will not be stopping other processes from
> > reading this information see
> > http://msdn2.microsoft.com/en-us/library/aa213039(sql.80).aspx
> > and http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx
> >
> > Using DBCC INPUTBUFFER on the SID for the process should indicate the last
> > statement issued by the client.
> >
> >
> > John
> >
> > "tin" wrote:
> >
> > > hi,
> > >
> > > from Activity Monitor, i found that there's a process (suspended) holding
> > > over a thousand locks on a table named tbl_BusinessRecordDetailed.
> > >
> > > i have 2 questions:
> > >
> > > #1 - is there a way to find out which row it's locking?
> > >
> > > #2 - what exactly is causing the locks? since the mode is S, which means
> > > it's read only'
> > >
> > > much appreciated.
> > >
> > >
> > > from activity monitor:
> > >
> > > resouce_type is all KEY
> > > request_mode is all S
> > > request_type is all LOCK
> > > request_status is all GRANT
> > > resouce_lock_partition is all 0
> > > request_reference_count is all 0
> > > request_exec_context_id is all 0
> > > request_request_id is all 0
> > > request_lifetime is all 1
> > > request_owner_type is all TRANSACTION
> > >
> > > but resource_description are pretty much all different, some examples:
> > > 1:210617
> > >
> > >
> > >
> > > 1:289260
> > >
> > >
> > >
> > > (8800ee1b2515)
> > >
> > >
> > >
> > > (b000d565e6d8)
> > >
> > >
> > >
> > > (af008d6eefc0)
> > >
> > >
> > >
> > >
> > >
> > >
> > > below is the table definition and the indexes:
> > >
> > >
> > > SET ANSI_NULLS ON
> > > GO
> > > SET QUOTED_IDENTIFIER ON
> > > GO
> > > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND type in (N'U'))
> > > BEGIN
> > > CREATE TABLE [dbo].[tbl_BusinessRecordDetailed](
> > > [recordID] [int] IDENTITY(1,1) NOT NULL,
> > > [rowVersion] [timestamp] NOT NULL,
> > > [name] [varchar](200) NULL,
> > > [address] [varchar](200) NULL,
> > > [crossstreet] [varchar](200) NULL,
> > > [neighborhood] [varchar](200) NULL,
> > > [borough] [varchar](200) NULL,
> > > [city] [varchar](50) NULL,
> > > [zip] [varchar](20) NULL,
> > > [state] [varchar](2) NULL,
> > > [telephone] [varchar](200) NULL,
> > > [fax] [varchar](10) NULL,
> > > [url] [varchar](500) NULL,
> > > [bookingUrl] [varchar](500) NULL,
> > > [bus] [varchar](200) NULL,
> > > [subway] [varchar](300) NULL,
> > > [description] [varchar](max) NULL,
> > > [hours] [varchar](2000) NULL,
> > > [admission] [varchar](2000) NULL,
> > > [otherinfo] [varchar](1000) NULL,
> > > [specialDirection] [varchar](max) NULL,
> > > [sortOrder] [int] NOT NULL CONSTRAINT [DF__tbl_Busin__sortO__7ABC33CD]
> > > DEFAULT ((0)),
> > > [dateCreated] [datetime] NOT NULL CONSTRAINT
> > > [DF__tbl_Busin__dateC__7BB05806] DEFAULT (getdate()),
> > > [copyright] [varchar](100) NULL,
> > > [mappedVarchar200_1] [varchar](200) NULL,
> > > [mappedVarchar200_2] [varchar](200) NULL,
> > > [mappedVarchar200_3] [varchar](200) NULL,
> > > [mappedVarchar200_4] [varchar](200) NULL,
> > > [mappedVarchar2000_1] [varchar](2000) NULL,
> > > [mappedVarchar2000_2] [varchar](2000) NULL,
> > > [mappedVarcharMax_1] [varchar](max) NULL,
> > > [dateModified] [datetime] NULL,
> > > [email] [varchar](50) NULL,
> > > CONSTRAINT [PK__tbl_BusinessReco__79C80F94] PRIMARY KEY CLUSTERED
> > > (
> > > [recordID] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > > ) ON [PRIMARY]
> > > END
> > > GO
> > >
> > > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > > N'idx_biz_datecreated')
> > > CREATE NONCLUSTERED INDEX [idx_biz_datecreated] ON
> > > [dbo].[tbl_BusinessRecordDetailed]
> > > (
> > > [dateCreated] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > > GO
> > >
> > > IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id => > > OBJECT_ID(N'[dbo].[tbl_BusinessRecordDetailed]') AND name => > > N'idx_biz_datemodifed')
> > > CREATE NONCLUSTERED INDEX [idx_biz_datemodifed] ON
> > > [dbo].[tbl_BusinessRecordDetailed]
> > > (
> > > [dateModified] ASC
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> > >
> > >
> > >
> > >

lock info

Lock identifying through SP code

We would have come across locks on rows, tables etc. How do we handle it
through code (SP)!!!

A job is executed at 12 pm midnight on a table. This table exists on an
24X7 accessed (users will be access the table anytime in the day/night)
database. We need to execute this job if this table is not locked at 12 pm.
Can we have the solution for the listed are two scenarios:

1. You have a normal user and do not have system privilege.
How would you design the database(Table)? Is it possible?
2. You have all the privileges on the system tables.
What would be the sql script to help us identify the lock? How will
it be used in your code? Or is there exception handling !!!You can see if a lock exists in syslockinfo table:

if exists (select 1 from master.dbo.syslockinfo (nolock) where rsc_objid=object_id('dbo.yourtable') print 'Someone is using the table'
else begin
print 'Table is not in use'
begin tran
select top 1 [lockit]=1 from dbo.yourtable
--do your thing
if @.@.error != 0 rollback tran
else commit tran
end

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.
>
|||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...
> 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...
>
|||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...
>
sql

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...
>

Friday, March 30, 2012

Locking

Locking

Wednesday, March 28, 2012

Lock that reads original data BUT does not honour exclusive locks?

Lock resources in Bpool ??

Lock resources in Bpool ??

Lock resources in Bpool ??

Friday, March 23, 2012

Lock Information

Lock Information

Lock Information

Lock Information

lock info

lock info

lock info

Lock identifying through SP code

Lock escalation not working

Lock escalation not working

sql local data

Blog Archive

About Me