Odd Blocking Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Odd Blocking Issue

Author

Topic

sureshot
Yak Posting Veteran

72 Posts

Posted - 2007-09-04 : 12:25:12

I don't understand why a CREATE PROC from 3.5 hours ago is the head blocker. The SP wasn't changed since in months so this must be a compile/recompile? It's not a COMPILE lock though but a PAGE lock. It's certainly not the initial creation. What else can be done to debug this? It's happening and usually resolving itself in 4-6 hours or if I kill the head blocker myself. It doesn't happen every day but almost every day.

I've retrieved this info about the blocking from DMVStats and found similar info using my regular blocking info script also pulling info from DMV's.

I'm running SQL 2005 SP2 Enterprise.

statement started 6:35AM
waiting statement (PAGE lock):

insert into grades (blah,blah)
select blah blah from homework join blah blah

statement started at 3:01AM
blocking statement: =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
@homeworkResultId int,
@NumDeleted int output
AS

delete homeworksessions where homeworkresult_id = @homeworkresultId

set @NumDeleted=@@ROWCOUNT

delete homeworkresults where homeworkresult_id=@homeworkresultId

set @NumDeleted=@NumDeleted + @@ROWCOUNT

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-09-04 : 12:47:00

Ahh, the SQL Server tools. What confusion, they can sow. No, what this means is someone is running this procedure. Since it is a delete procedure, it is taking out exclusive locks on the affected tables, and likely blocking any other process. Is there an index on homeworkresult_id?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-04 : 12:48:30

quote:
Originally posted by mcrowley

.... Is there an index on homeworkresult_id?

Is there an index on homeworkresult_id in both tables?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sureshot
Yak Posting Veteran

72 Posts

Posted - 2007-10-12 : 22:06:08

Yes there is an index on both tables. The issue turned out to be a transaction calling ResultsHomeworkDelete that was stuck in limbo and not rolled back.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources