quote:

---

Originally posted by sodeep

You need to carefully read this article:

http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

---

quote:

---

Originally posted by GilaMonster

Can you show us us the update that''s on line 88 of uspFacilitySchedulerUpdateAssign and the select that's on line 8 of uspFacilitySchedulerGetAssignBySchedule. Also the table structure and indexes would be very useful.

--
Gail Shaw
SQL Server MVP

---

UPDATE dbo.fsAssign SET
	fsDeptID = @fsDeptID,
	fsStaffID = @fsStaffID,
	Charge = @Charge,
	UnscheduledAbsence = @UnscheduledAbsence,
	fsCancelReasonID = @fsCancelReasonID,
	CancelComment = @CancelComment,
	OvertimeShift = @OvertimeShift,
	CreateDateUTC = @CreateDateUTC,
	fsNeedID = @fsNeedID,
	fsNeedStatusID = @fsNeedStatusID,
	[Date] = @Date,
	fsDeptShiftID = @fsDeptShiftID,
	StartTime = @StartTime,
	EndTime = @EndTime,
	fsCountTypeID = @fsCountTypeID,
	fsProductiveID = @fsProductiveID,
	Approved = @Approved
WHERE
	fsAssignID = @fsAssignID
	AND version = @version


SELECT
	A.fsAssignID,
	A.fsDeptID,
	A.fsStaffID,
	A.Charge,
	A.UnscheduledAbsence,
	A.fsCancelReasonID,
	A.CancelComment,
	A.OvertimeShift,
	A.CreateDateUTC,
	A.version,
	A.fsNeedID,
	A.fsNeedStatusID,
	A.[Date],
	A.fsDeptShiftID,
	A.StartTime,
	A.EndTime,
	A.fsCountTypeID,
	A.fsProductiveID,
	A.ProviderConfirmed,
	A.Approved
FROM
	dbo.fsAssign A
WHERE
	A.fsDeptID = @fsDeptID
	AND A.StartTime < @EndTime
	AND A.EndTime > @StartTime

CREATE TABLE [dbo].[fsAssign] (
	[fsAssignID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[fsDeptID] [int] NOT NULL ,
	[fsStaffID] [int] NOT NULL ,
	[Charge] [tinyint] NOT NULL ,
	[UnscheduledAbsence] [tinyint] NOT NULL ,
	[fsCancelReasonID] [int] NULL ,
	[CancelComment] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OvertimeShift] [tinyint] NOT NULL ,
	[CreateDateUTC] [datetime] NULL ,
	[appChanged] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[userChanged] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[version] [timestamp] NULL ,
	[dateChangedUTC] [datetime] NULL ,
	[fsNeedID] [uniqueidentifier] NOT NULL ,
	[fsNeedStatusID] [tinyint] NOT NULL ,
	[fsOrigDeptID] [int] NOT NULL ,
	[Date] [smalldatetime] NOT NULL ,
	[fsDeptShiftID] [int] NULL ,
	[StartTime] [smalldatetime] NOT NULL ,
	[EndTime] [smalldatetime] NOT NULL ,
	[fsCountTypeID] [tinyint] NOT NULL ,
	[fsProductiveID] [tinyint] NOT NULL ,
	[ProviderConfirmed] [tinyint] NOT NULL ,
	[Approved] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[fsAssign] WITH NOCHECK ADD 
	CONSTRAINT [PK_fsAssign_fsAssignID] PRIMARY KEY  CLUSTERED 
	(
		[fsAssignID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [DK_fsAssign_fsDeptIDStartTimeEndTime] ON [dbo].[fsAssign]([fsDeptID], [StartTime], [EndTime]) ON [PRIMARY]
GO

 CREATE  INDEX [DK_fsAssign_fsStaffIDStartTimeEndTimefsDeptID] ON [dbo].[fsAssign]([fsStaffID], [StartTime], [EndTime], [fsDeptID]) ON [PRIMARY]
GO

 CREATE  INDEX [DK_fsAssign_fsNeedID] ON [dbo].[fsAssign]([fsNeedID]) ON [PRIMARY]
GO

 CREATE  INDEX [DK_fsAssign_fsOrigDeptIDStartTimeEndTime] ON [dbo].[fsAssign]([fsOrigDeptID], [StartTime], [EndTime]) ON [PRIMARY]
GO

 CREATE  INDEX [DK_fsAssign_fsDeptShiftIDStartTimeEndTime] ON [dbo].[fsAssign]([fsDeptShiftID], [StartTime], [EndTime]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[fsAssign] ADD 
	CONSTRAINT [FK_fsAssign_fsCancelReason_fsCancelReasonID] FOREIGN KEY 
	(
		[fsCancelReasonID]
	) REFERENCES [dbo].[fsCancelReason] (
		[fsCancelReasonID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsCountType_fsCountTypeID] FOREIGN KEY 
	(
		[fsCountTypeID]
	) REFERENCES [dbo].[fsCountType] (
		[fsCountTypeID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsDept_fsDeptID] FOREIGN KEY 
	(
		[fsDeptID]
	) REFERENCES [dbo].[fsDept] (
		[fsDeptID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsDept_fsOrigDeptID] FOREIGN KEY 
	(
		[fsOrigDeptID]
	) REFERENCES [dbo].[fsDept] (
		[fsDeptID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsDeptShift_fsDeptShiftID] FOREIGN KEY 
	(
		[fsDeptShiftID]
	) REFERENCES [dbo].[fsDeptShift] (
		[fsDeptShiftID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsNeed_fsNeedID] FOREIGN KEY 
	(
		[fsNeedID]
	) REFERENCES [dbo].[fsNeed] (
		[fsNeedID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsNeedStatus_fsNeedStatusID] FOREIGN KEY 
	(
		[fsNeedStatusID]
	) REFERENCES [dbo].[fsNeedStatus] (
		[fsNeedStatusID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsProductive_fsProductiveID] FOREIGN KEY 
	(
		[fsProductiveID]
	) REFERENCES [dbo].[fsProductive] (
		[fsProductiveID]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_fsAssign_fsStaff_fsStaffID] FOREIGN KEY 
	(
		[fsStaffID]
	) REFERENCES [dbo].[fsStaff] (
		[fsStaffID]
	) NOT FOR REPLICATION 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  TRIGGER trfsAssign_InsUpd ON dbo.fsAssign
FOR INSERT, UPDATE NOT FOR REPLICATION
AS
	SET NOCOUNT ON

	UPDATE A SET
		A.appChanged = dbo.ufnContextApplication(),
		A.userChanged = dbo.ufnContextUser(),
		A.dateChangedUTC = GETUTCDATE()
	FROM
		dbo.fsAssign A
		INNER JOIN INSERTED I ON
			A.fsAssignID = I.fsAssignID

GO

CREATE TRIGGER trfsAssign_Pending_Upd ON [dbo].[fsAssign] 
FOR UPDATE
NOT FOR REPLICATION
AS
	SET NOCOUNT ON

	IF UPDATE([fsNeedStatusID])
	BEGIN

		UPDATE N SET
			N.Pending = 1
		FROM
			dbo.fsNeed N
			INNER JOIN inserted I ON
				N.fsNeedID = I.fsNeedID
			INNER JOIN deleted D ON
				N.fsNeedID = D.fsNeedID
			INNER JOIN dbo.fsAssignFromSrx Srx ON
				I.fsAssignID = Srx.fsAssignID
		WHERE
			I.fsNeedStatusID IN (3,4) -- New status is canceled or no-need
			AND I.fsNeedStatusID <> D.fsNeedStatusID

	END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

quote:

---

Originally posted by sodeep

You need to carefully read this article:

http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

---

quote:

---

Originally posted by GilaMonster

I'd guess they're both taking locks on parts of the NC index and then trying to get locks on other parts (or on the cluster).

--
Gail Shaw
SQL Server MVP

---