PLEASE HELP !>> 2145 Locks, displayed from SP_LOCK 143

HI, Can someone PLEASE HELP ME to better understand what is happening in the
SQL2000 SP4 database when someone runs a posting process that creates 2145
locks. There are about 300 users connected to this database, entering
transactions all day and some through the night, and most of the time
everything runs along smoothly, I've seen 300 - 600 locks on normal OPs, but
when a posting is run (I know little about that actual application) it
creates thousands of locks and transactions grind to a halt. I've been
monitoring the locks while writing this and that one SPID has a death grip
on 2145 locks, this has been for at least 15 minutes. I've been the SLQ DBA
here for just under a year, and this is one of many issues that needs
attention. IS THIS AN APPLICATION THING, IS THIS SOMETHING THAT I NEED TO
CHANGE ON THE SQL CONFIGURATION, >>> WHAT CAN I DO? IS THIS AN
ENGINEERING QUESTION?
Check the Query from that particular SPID (user) via Profiler.
to immideatly resolve the issue: kill the SPId that is Blocking:
(Carefully examine that this particular SPID for other blocking
scenarios)
Read more about NOLOCK and ISOLATION LEVELS.
Get the developers to use this often if there are heavy transactions,
specially for "select" stuff.
Get a nolock on all SPs that this application or the Posting Process
uses.
More about blocking in BOL:
Maninder
MCDBA
|||THANK YOU!! It took about 20 minutes for this process to complete, and then
all locks cleared. The Developers are another Company and discussing how
there app works, is not easy. Would you have any ideas as to why a process
that effects maybe 15,000 entries, would lock 2145 records (am I correct to
assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
that process?) and takes 20 minutes.
Server is Win2003 x64, 4g Ram, Xeon 3.0 GHz Dual Proc, with HyperThreading
On(shows 4 Proc's in TaskManager), Dual gigabit NICs, SQL2000 SP4.
==========================================
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1170345674.343261.97400@.a75g2000cwd.googlegro ups.com...
> Check the Query from that particular SPID (user) via Profiler.
> to immideatly resolve the issue: kill the SPId that is Blocking:
> (Carefully examine that this particular SPID for other blocking
> scenarios)
> Read more about NOLOCK and ISOLATION LEVELS.
> Get the developers to use this often if there are heavy transactions,
> specially for "select" stuff.
> Get a nolock on all SPs that this application or the Posting Process
> uses.
> More about blocking in BOL:
> Maninder
> MCDBA
>
|||WANNABE wrote:
> THANK YOU!! It took about 20 minutes for this process to complete, and then
> all locks cleared. The Developers are another Company and discussing how
> there app works, is not easy. Would you have any ideas as to why a process
> that effects maybe 15,000 entries, would lock 2145 records (am I correct to
> assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
> that process?) and takes 20 minutes.
>
No, it shows you the number of LOCKS that the process has in place. You
need to look at the lock type to determine if they are rows, pages,
tables, etc...
My guess is that there are missing or inadequate indexes on some of the
tables involved in this posting process, which result in table locks. I
would pursue this before going with NOLOCK. Using NOLOCK is an
often-abused band-aid for poorly designed systems. It exposes you to
possible reading "dirty" data, and should be used carefully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

PLEASE HELP !>> 2145 Locks, displayed from SP_LOCK 143

HI, Can someone PLEASE HELP ME to better understand what is happening in the
SQL2000 SP4 database when someone runs a posting process that creates 2145
locks. There are about 300 users connected to this database, entering
transactions all day and some through the night, and most of the time
everything runs along smoothly, I've seen 300 - 600 locks on normal OPs, but
when a posting is run (I know little about that actual application) it
creates thousands of locks and transactions grind to a halt. I've been
monitoring the locks while writing this and that one SPID has a death grip
on 2145 locks, this has been for at least 15 minutes. I've been the SLQ DBA
here for just under a year, and this is one of many issues that needs
attention. IS THIS AN APPLICATION THING, IS THIS SOMETHING THAT I NEED TO
CHANGE ON THE SQL CONFIGURATION, >> WHAT CAN I DO? IS THIS AN
ENGINEERING QUESTION'Check the Query from that particular SPID (user) via Profiler.
to immideatly resolve the issue: kill the SPId that is Blocking:
(Carefully examine that this particular SPID for other blocking
scenarios)
Read more about NOLOCK and ISOLATION LEVELS.
Get the developers to use this often if there are heavy transactions,
specially for "select" stuff.
Get a nolock on all SPs that this application or the Posting Process
uses.
More about blocking in BOL:
Maninder
MCDBA|||THANK YOU!! It took about 20 minutes for this process to complete, and then
all locks cleared. The Developers are another Company and discussing how
there app works, is not easy. Would you have any ideas as to why a process
that effects maybe 15,000 entries, would lock 2145 records (am I correct to
assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
that process') and takes 20 minutes.
Server is Win2003 x64, 4g Ram, Xeon 3.0 GHz Dual Proc, with HyperThreading
On(shows 4 Proc's in TaskManager), Dual gigabit NICs, SQL2000 SP4.
=========================================="Maninder" <msdhanjal@.gmail.com> wrote in message
news:1170345674.343261.97400@.a75g2000cwd.googlegroups.com...
> Check the Query from that particular SPID (user) via Profiler.
> to immideatly resolve the issue: kill the SPId that is Blocking:
> (Carefully examine that this particular SPID for other blocking
> scenarios)
> Read more about NOLOCK and ISOLATION LEVELS.
> Get the developers to use this often if there are heavy transactions,
> specially for "select" stuff.
> Get a nolock on all SPs that this application or the Posting Process
> uses.
> More about blocking in BOL:
> Maninder
> MCDBA
>|||WANNABE wrote:
> THANK YOU!! It took about 20 minutes for this process to complete, and then
> all locks cleared. The Developers are another Company and discussing how
> there app works, is not easy. Would you have any ideas as to why a process
> that effects maybe 15,000 entries, would lock 2145 records (am I correct to
> assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
> that process') and takes 20 minutes.
>
No, it shows you the number of LOCKS that the process has in place. You
need to look at the lock type to determine if they are rows, pages,
tables, etc...
My guess is that there are missing or inadequate indexes on some of the
tables involved in this posting process, which result in table locks. I
would pursue this before going with NOLOCK. Using NOLOCK is an
often-abused band-aid for poorly designed systems. It exposes you to
possible reading "dirty" data, and should be used carefully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

pk_dtproperties Question

I'm having some weird locking in SQL Server 2000 and I look the locks with a
modified sp_locks it shows thousands of the locks below. But I can't find
any code or SQL scripts that use that table and the table has 0 rows.
Has anyone seen this?
thanks
Johh
PrePressAutomationpk_dtpropertiesDBS
PrePressAutomationpk_dtpropertiesKEYRange
PrePressAutomationpk_dtpropertiesPAGIS
PrePressAutomationpk_dtpropertiesPAGIX
PrePressAutomationpk_dtpropertiesTABIS
PrePressAutomationpk_dtpropertiesTABSch-S
Hi
pk_dtproperties is the primary key on dtproperties which is used for
database diagrams
http://groups.google.com/groups?hl=e...dt properties
John
"John" wrote:

> I'm having some weird locking in SQL Server 2000 and I look the locks with a
> modified sp_locks it shows thousands of the locks below. But I can't find
> any code or SQL scripts that use that table and the table has 0 rows.
> Has anyone seen this?
> thanks
> Johh
> PrePressAutomationpk_dtpropertiesDBS
> PrePressAutomationpk_dtpropertiesKEYRange
> PrePressAutomationpk_dtpropertiesPAGIS
> PrePressAutomationpk_dtpropertiesPAGIX
> PrePressAutomationpk_dtpropertiesTABIS
> PrePressAutomationpk_dtpropertiesTABSch-S
>
|||Thank you John, but I don't have any database diagrams on that server. I
also tried to delete the table, but in SQL 2000 sp3a It's a system table and
will not let me delete it. Do you have any clues I'm getting locks on this
table?
Thank you
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> pk_dtproperties is the primary key on dtproperties which is used for
> database diagrams
> http://groups.google.com/groups?hl=e...dt properties
> John
> "John" wrote:
|||Hi John
I don't know why this should be. You may want to check the inputbuffer
for that spid http://msdn.microsoft.com/library/de..._dbcc_8v8y.asp
You may also want to check what SQLAgent and NT jobs are scheduled,
and if there are any services.
Failing that you may want to see if stopping/starting the SQL Server
service
removes the problem.
John
"John" <John@.discussions.microsoft.com> wrote in message news:<2143B280-2453-4BB4-81F0-DAF461E1050A@.microsoft.com>...[vbcol=seagreen]
> Thank you John, but I don't have any database diagrams on that server. I
> also tried to delete the table, but in SQL 2000 sp3a It's a system table and
> will not let me delete it. Do you have any clues I'm getting locks on this
> table?
> Thank you
> John
> "John Bell" wrote:

pk_dtproperties Question

I'm having some weird locking in SQL Server 2000 and I look the locks with a
modified sp_locks it shows thousands of the locks below. But I can't find
any code or SQL scripts that use that table and the table has 0 rows.
Has anyone seen this?
thanks
Johh
PrePressAutomation pk_dtproperties DB S
PrePressAutomation pk_dtproperties KEY Range
PrePressAutomation pk_dtproperties PAG IS
PrePressAutomation pk_dtproperties PAG IX
PrePressAutomation pk_dtproperties TAB IS
PrePressAutomation pk_dtproperties TAB Sch-SHi
pk_dtproperties is the primary key on dtproperties which is used for
database diagrams
http://groups.google.com/groups?hl=en&lr=&threadm=eyjuHEShEHA.2620%40TK2MSFTNGP10.phx.gbl&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26scoring%3Dd%26q%3Dpk_dtproperties
John
"John" wrote:
> I'm having some weird locking in SQL Server 2000 and I look the locks with a
> modified sp_locks it shows thousands of the locks below. But I can't find
> any code or SQL scripts that use that table and the table has 0 rows.
> Has anyone seen this?
> thanks
> Johh
> PrePressAutomation pk_dtproperties DB S
> PrePressAutomation pk_dtproperties KEY Range
> PrePressAutomation pk_dtproperties PAG IS
> PrePressAutomation pk_dtproperties PAG IX
> PrePressAutomation pk_dtproperties TAB IS
> PrePressAutomation pk_dtproperties TAB Sch-S
>|||Thank you John, but I don't have any database diagrams on that server. I
also tried to delete the table, but in SQL 2000 sp3a It's a system table and
will not let me delete it. Do you have any clues I'm getting locks on this
table?
Thank you
John
"John Bell" wrote:
> Hi
> pk_dtproperties is the primary key on dtproperties which is used for
> database diagrams
> http://groups.google.com/groups?hl=en&lr=&threadm=eyjuHEShEHA.2620%40TK2MSFTNGP10.phx.gbl&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26scoring%3Dd%26q%3Dpk_dtproperties
> John
> "John" wrote:
> > I'm having some weird locking in SQL Server 2000 and I look the locks with a
> > modified sp_locks it shows thousands of the locks below. But I can't find
> > any code or SQL scripts that use that table and the table has 0 rows.
> >
> > Has anyone seen this?
> > thanks
> > Johh
> >
> > PrePressAutomation pk_dtproperties DB S
> > PrePressAutomation pk_dtproperties KEY Range
> > PrePressAutomation pk_dtproperties PAG IS
> > PrePressAutomation pk_dtproperties PAG IX
> > PrePressAutomation pk_dtproperties TAB IS
> > PrePressAutomation pk_dtproperties TAB Sch-S
> >|||Hi John
I don't know why this should be. You may want to check the inputbuffer
for that spid http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8v8y.asp
You may also want to check what SQLAgent and NT jobs are scheduled,
and if there are any services.
Failing that you may want to see if stopping/starting the SQL Server
service
removes the problem.
John
"John" <John@.discussions.microsoft.com> wrote in message news:<2143B280-2453-4BB4-81F0-DAF461E1050A@.microsoft.com>...
> Thank you John, but I don't have any database diagrams on that server. I
> also tried to delete the table, but in SQL 2000 sp3a It's a system table and
> will not let me delete it. Do you have any clues I'm getting locks on this
> table?
> Thank you
> John
> "John Bell" wrote:
> > Hi
> >
> > pk_dtproperties is the primary key on dtproperties which is used for
> > database diagrams
> >
> > http://groups.google.com/groups?hl=en&lr=&threadm=eyjuHEShEHA.2620%40TK2MSFTNGP10.phx.gbl&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26scoring%3Dd%26q%3Dpk_dtproperties
> >
> > John
> >
> > "John" wrote:
> >
> > > I'm having some weird locking in SQL Server 2000 and I look the locks with a
> > > modified sp_locks it shows thousands of the locks below. But I can't find
> > > any code or SQL scripts that use that table and the table has 0 rows.
> > >
> > > Has anyone seen this?
> > > thanks
> > > Johh
> > >
> > > PrePressAutomation pk_dtproperties DB S
> > > PrePressAutomation pk_dtproperties KEY Range
> > > PrePressAutomation pk_dtproperties PAG IS
> > > PrePressAutomation pk_dtproperties PAG IX
> > > PrePressAutomation pk_dtproperties TAB IS
> > > PrePressAutomation pk_dtproperties TAB Sch-S
> > >