Concurrency issues during massive updates / archiving

Mass updates / mass deltes often cause contention issue with locking / blocking. One technique I suggest to my students is to update / delete parts of the table at a time, where feasible, so as to avoid embarassing issues like table locks.

The following code could be improved, but it has the right general idea: For each involved table, delete 1000 rows at a time until we're done.

CREATE PROCEDURE [dbo].[DeleteSnapshotData]
@I_lNumDays int
AS

SET NOCOUNT ON
-- get cutoff date string
declare @tablename varchar(50)
declare @dtCutoff smalldatetime
declare @strCutoff varchar(50)
declare @command nvarchar(2000)
set @dtCutoff = getdate() - @I_lNumDays
set @strCutoff = cast(year(@dtCutoff) as varchar) + '-' + right('00' + cast(month(@dtCutoff) as varchar), 2) + '-' + right('00' + cast(day(@dtCutoff) as varchar), 2)

-- build temp table
set rowcount 0
select sysobjects.name into #snapshots from sysobjects join syscolumns on sysobjects.id = syscolumns.id where sysobjects.name like '%snapshot%' and (OBJECTPROPERTY(sysobjects.id, N'IsTable') = 1) and (syscolumns.name = 'tdate')

set rowcount 1
select @tablename = name from #snapshots

while @@rowcount > 0
begin

delete from #snapshots where name = @tablename
if ((@tablename != 'Closing_Charges_Snapshot') and (@tablename != 'Closing_Credits_Snapshot'))
BEGIN
print 'deleting expired data from ' + @tablename + '...'

SET ROWCOUNT 1000
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
SET NOCOUNT OFF
select @command = N'delete from ' + @tablename + ' where tdate < ''' + @strCutoff + ''''
exec (@command)

IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END

COMMIT TRANSACTION
END
END
SET NOCOUNT ON
set rowcount 1
select @tablename = name from #snapshots
end
drop table #snapshots

Undocumented system procedure

Critical path for most folks is preventive maintenance, whether they know it or not.

If you're ever looking for a quick way of doing something on all (user) tables in a database, there's a system stored procedure for it.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

This command will run the "dbcc DBREINDEX" command on each table in the database.

Your table-level preventive maintenance regiman, CRUCIAL to performance success, include:

• keeping all table statistics up to date
• Keeping index structures balanced; this may be an indexdefrag (which, while intrusive, can be performed online) or preferably by rebuilding indexes (dbcc DBREINDEX )
• Making sure you have the indexes you need, and remove indexes you don't use (see new managment views for that!)