Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
>> For some reason, one table in our database (SQL 2000) keeps getting locks
>> on it so attempts to insert records on it seems to time out frequently.
>> Any ideas on what I might look for to keep this from happening? The
>> table has only a unique int for a PK and no other indexes. Thanks.
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
>> I'm not sure if this helps, but when I go into EM and look at the
>> Locks/process ID and click on a couple of the spid records, I see the
>> following entry in the objects list on a couple of the spid records:
>> Object = tempdb.dbo.##lockinfo67
>> Lock Type = TAB
>> Mode = X
>> Status = GRANT
>> Owner = Xact
>> Index = ##lockinfo67
>> Not sure if this helps.
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com