我正在运行以下命令从大表（1.5亿行）中批量删除行：
DECLARE @RowCount int
WHILE 1=1
BEGIN
DELETE TOP (10000) t1
FROM table t1
INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
WHERE t1.YearProcessed <= 2007
SET @RowCount = @@ROWCOUNT
IF (@RowCount < 10000) BREAK
END
此表格很常用。但是，它正在删除记录，但它也会导致某些记录的锁定，从而给用户带来错误（这在我们所处的环境中是不可接受的）。
如何在不导致锁定的情况下删除旧记录？我应该将批量的大小从10000记录减少到1000吗？这将如何影响日志大小（我们只有很少的硬盘空间用于大对数增长）。
有什么建议么？
我在过去看到类似的零星问题，即使是小批量的5000个记录，锁定仍然会发生。在我们的例子中，每个删除/更新都包含在它自己的Begin Tran ... Commit循环中。纠正问题，逻辑
等待延迟'00：00：00：01'
被放置在每个循环的顶部并且纠正了问题。
我在过去看到类似的零星问题，即使是小批量的5000个记录，锁定仍然会发生。在我们的例子中，每个删除/更新都包含在它自己的Begin Tran ... Commit循环中。纠正问题，逻辑
等待延迟'00：00：00：01'
被放置在每个循环的顶部并且纠正了问题。
首先 - 它看起来像您的DELETE执行群集索引扫描，我建议执行以下操作：
create index [IX.IndexName] ON t1(YearProcessed, PrimaryKey)
第二 - 有没有需要加入t2表？
然后使用以下查询删除行，假设您的PrimaryKey列是INT类型：
declare @ids TABLE(PrimaryKey INT)
WHILE 1=1
BEGIN
INSERT @ids
SELECT top 10000 DISTINCT t1.PrimaryKey
FROM table t1
INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
WHERE t1.YearProcessed <= 2007
IF @@ROWCOUNT = 0 BREAK
DELETE t1
WHERE PrimaryKey in (Select PrimaryKey from @ids)
delete from @ids
END
如果不需要，请不要忘记从连接中删除t2表
如果它仍然导致锁定 - 那么降低每轮中删除的行数
我认为你走在正确的轨道上。
看看这两篇文章：
和：
http://www.dbforums.com/microsoft-sql-server/985516-deleting-without-locking.html
在运行删除之前，请检查估计的查询计划以查看是否存在 正在做一个索引寻找删除，或仍在做一个完整的表 扫描/访问。
除了其他建议（旨在减少删除期间完成的工作）之外，您还可以将SQL Server配置为在对表执行删除时不阻止其他读取器。
这可以通过使用SQL Server 2005引入的“快照隔离”来完成：
http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx
如果您有任何带有级联删除的内容，请确保它们已编入索引。
突出显示DELETE查询并单击 Display estimated execution plan 将显示建议的索引 - 在我的情况下包括一些级联删除。
为那些添加索引使删除速度更快 - 但我仍然不会尝试一次删除所有行。
我找到的最好的方法是表单asp.net DeleteExpiredSessions。你执行READUNCOMMITTED选择并将记录放在临时表中，而不是使用CURSOR删除记录。
ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
DECLARE @now datetime
SET @now = GETUTCDATE()
CREATE TABLE #tblExpiredSessions
(
SessionID nvarchar(88) NOT NULL PRIMARY KEY
)
INSERT #tblExpiredSessions (SessionID)
SELECT SessionID
FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
WHERE Expires < @now
IF @@ROWCOUNT <> 0
BEGIN
DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT SessionID FROM #tblExpiredSessions
DECLARE @SessionID nvarchar(88)
OPEN ExpiredSessionCursor
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
END
CLOSE ExpiredSessionCursor
DEALLOCATE ExpiredSessionCursor
END
DROP TABLE #tblExpiredSessions
RETURN 0
尝试这个，
DECLARE @RowCount int
WHILE 1=1
BEGIN
BEGIN TRANSACTION
DELETE TOP (10000) t1
FROM table t1
INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
WHERE t1.YearProcessed <= 2007
END TRANSACTION
COMMIT TRANSACTION
SET @RowCount = @@ROWCOUNT
IF (@RowCount < 10000) BREAK
END