SELECT	[T_ID] = IDENTITY(int, 1, 1),
	[T_ThePK_ID] = ThePK_ID
INTO	#MyTempTable
FROM	dbo.MyTable WITH (NOLOCK)
WHERE	MyDateColumn < @MyStartDate -- 7 months ago

DECLARE	@intBatchStart int,
	@intBatchSize int,
	@intRowCount int,
	@dtStart datetime,
	@strMsg nvarchar(1000)

SELECT	@intBatchStart=MIN(T_ID), @intBatchSize=100, @intRowCount=1
FROM	#MyTempTable AS X
	JOIN dbo.MyTable AS D
		 ON D.ThePK_ID = X.ThePK_ID

WHILE @intRowCount >= 1
BEGIN
	SELECT	@dtStart = GetDate()
	DELETE D
	FROM	#MyTempTable AS X
		JOIN dbo.MyTable AS D
			 ON D.ThePK_ID = X.ThePK_ID
	WHERE	    T_ID BETWEEN @intBatchStart
		AND @intBatchStart + @intBatchSize
	SELECT	@intRowCount = @@ROWCOUNT,
		@intBatchStart = @intBatchStart + @intBatchSize
	SELECT	@strMsg = 'Loop:'
		+ CONVERT(varchar(20), @intBatchStart)
		+ ', Elapsed(ms)='
		+ CONVERT(varchar(20), DATEDIFF(Millisecond, @dtStart, GetDate()))
		+ ', Deleted=' + CONVERT(varchar(20), @intRowCount)
	RAISERROR (@strMsg, 10, 1) WITH NOWAIT
	WAITFOR DELAY '00:00:05'
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable]
(
	[ThePK_ID] [int] IDENTITY(10000,1) NOT NULL,
	[MyDateColumn] [datetime] NOT NULL,
	...
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
	[ThePK_ID] ASC
)WITH
(
	PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Index1] ON [dbo].[MyTable] 
(
	[SomeColumn1] ASC
)WITH
(
	PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Index2] ON [dbo].[MyTable] 
(
	[SomeColumn2] ASC
)WITH
(
	PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Index3] ON [dbo].[MyTable] 
(
	[MyDateColumn] ASC
)WITH
(
	PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Index4] ON [dbo].[MyTable] 
(
	[SomeColumn4] ASC
)WITH
(
	PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Index5] ON [dbo].[MyTable] 
(
	[SomeColumn5] ASC
)WITH
(
	PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Index6] ON [dbo].[MyTable] 
(
	[SomeColumn6] ASC
)WITH
(
	PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Index7] ON [dbo].[MyTable] 
(
	[SomeColumn7] ASC
)WITH
(
	PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
		ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_SomeTable4] FOREIGN KEY
(
	[sm_ysvl_SomeColumn4]
)
REFERENCES [dbo].[SomeTable4] ([SomeColumn4])
GO
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_SomeTable4]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_kk_SomeColumn3]  DEFAULT (0) FOR [SomeColumn3]
GO

quote:

---

Originally posted by lappin

Your problem is most likely due to deleting in a loop. Use a set based approach, i.e. don't delete each row in a loop delete every row at once using a WHERE statement. Also your temporary table is written to disk which can also slow the process - consider deleting directly from dbo.MyTable or using a table variable instead of temp table.

Most locking can be prevented if queries can be speeded up.

---

quote:

---

Originally posted by Kristen
Thanks for that but performance is NOT the key here, what I am after is minimum impact. The server is used by thousands of concurrent web users 24/7 who must not be disrupted by the deletion of some 20,000,000 rows.

---

quote:

---

Originally posted by lappin

Sorry didn't read the code properly. If locking is caused by index being rebuilt you could look at splitting up PK, i.e. have a unique constraint on your existing column but cluster on the date field - this should mean finding the date range / deleting date range /re-indexing etc - causes less fragmentation and faster disk reads. Also less table locking as data more likely stored together on same page so page locks are more likely to meet optimiser's needs than table lock.

---

DELETE D
FROM MyTable
WHERE     MyID BETWEEN @StartID and @EndID
      AND MyDate < @EndDate

quote:

---

Originally posted by russell

Nothing to add to the discussion other than Kristen's back!!!

---

quote:

---

Originally posted by Kristen

quote:

---

Originally posted by russell

Nothing to add to the discussion other than Kristen's back!!!

---

Yeah, but only 'coz I'm freeloading for some help ... :)

---

quote:

---

"Personally I'd prefer the deadlock graph output from the 1222 traceflag"

Sorry, jsut to clarify: Do you mean the "picture" output by SQL Profiler, or something you track yourself using the traceflag?

---

Index Delete(OBJECT:([MyDatabase].[dbo].[MyTable].[IX_MyDate]