By Edward Polley,
2012/03/06
This is accomplished with the T-SQL code below. You would customize the definition of obsolete rows (I call deadheads) to your shop. In this case a sale without money and no contact is obsolete. Historical cleanup is a common use of this routine. This version works with multiple tables in this case a parent table and its two child tables. The column SalesID relates the tables.
Two layers of temporary tables are used: the outer table to contain all the obsolete primary keys for this run; the inner table to contain a subset of the same. The number of keys placed in the temporary tables is best determined by altering the number of rows until the highest rows deleted per minute is reached. I started with 1,000 rows in the outer table and 100 in the inner table. I slowly scaled up to 500,000 rows in the outer table and 5,000 rows in the inner table. 25,316 deletions per minute was the peak value. Beyond 500,000 rows in the outer table the deletion per minute value declined on my server.
Since the application tables are defined with primary keys using the identity property the rows are inserted with ever increasing primary key values making it easy to pick up the oldest rows via SELECT top. Your case may be based upon a date column so alter the SELECT top 500000 accordingly.
The code loops through the 500,000 row outer table loading 5,000 keys values into the inner table. Those 5,000 rows are then deleted from the application tables and outer table. The last step is to truncating the inner table. This loop continues until all 500,000 outer table rows are deleted. I find keeping the inner table small (5000) minimizes lock contention but you will need to adjust this number to your shop.
If desired use the WAITFOR command to pause processing and allow other applications to process without contending with cleanup.
At any point the job can be cancelled resulting in a brief rollback only for the last inner table set of keys. Committed deletes are not rolled back.
The job can be restarted from the top without issue and can be run as many times a day as needed. I run this job two times a night on tables needing a quick cleanup then pull back to once a week to maintain optimal performance.
Problem
Your SQL Server production database must be available 7x24 but it contains millions of obsolete rows which hurt performance. How do you delete these rows without the lock contention and business impact of a standard delete? In this tip we look at how you can delete massive number of rows in small minimal lock batches.Solution
Quick OLTP is critical to a business’s success so DBAs need to mindful of data contention caused by application cleanup jobs. One approach is to select a huge number of keys in one pass then break the cleanup into small batches with commits and sleep windows to minimize contention. I recommend enabling the cleanup in a SQL job and run at low system usage time.This is accomplished with the T-SQL code below. You would customize the definition of obsolete rows (I call deadheads) to your shop. In this case a sale without money and no contact is obsolete. Historical cleanup is a common use of this routine. This version works with multiple tables in this case a parent table and its two child tables. The column SalesID relates the tables.
Two layers of temporary tables are used: the outer table to contain all the obsolete primary keys for this run; the inner table to contain a subset of the same. The number of keys placed in the temporary tables is best determined by altering the number of rows until the highest rows deleted per minute is reached. I started with 1,000 rows in the outer table and 100 in the inner table. I slowly scaled up to 500,000 rows in the outer table and 5,000 rows in the inner table. 25,316 deletions per minute was the peak value. Beyond 500,000 rows in the outer table the deletion per minute value declined on my server.
Since the application tables are defined with primary keys using the identity property the rows are inserted with ever increasing primary key values making it easy to pick up the oldest rows via SELECT top. Your case may be based upon a date column so alter the SELECT top 500000 accordingly.
The code loops through the 500,000 row outer table loading 5,000 keys values into the inner table. Those 5,000 rows are then deleted from the application tables and outer table. The last step is to truncating the inner table. This loop continues until all 500,000 outer table rows are deleted. I find keeping the inner table small (5000) minimizes lock contention but you will need to adjust this number to your shop.
If desired use the WAITFOR command to pause processing and allow other applications to process without contending with cleanup.
At any point the job can be cancelled resulting in a brief rollback only for the last inner table set of keys. Committed deletes are not rolled back.
The job can be restarted from the top without issue and can be run as many times a day as needed. I run this job two times a night on tables needing a quick cleanup then pull back to once a week to maintain optimal performance.
The Code
USE [msdb] GO /****** Object: StoredProcedure [dbo].[usp_ObsoleteRowCleanup] Script Date: 12/18/2011 14:00:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /******************************************************************** File Name: usp_ ObsoleteRowCleanup.sql Applies to: SQL Server 2005/8 Purpose: To cleanup obsolete rows. Prerequisite: None. Assumption: Removal of obsolete rows improves performance Parameters: None Returns: 0 = Succesful, 1 = Errors. Author: Edward A. Polley Spec -- Definition of deadhead. Select COUNT(*) from SalesLead L, Transaction T WHERE L.SalesID = T.SalesID AND L.ContactId IS NULL AND T.MoneyIn = 0 ********************************************************************/ CREATE PROCEDURE [dbo].[usp_ ObsoleteRowCleanup] --Will delete first 500000 deadheads --Delete is from child tables: SalesZip and Transaction then parent SalesLead -- AS SET NOCOUNT ON SET QUOTED_IDENTIFIER OFF DECLARE @DeadHeadCnt int, @Toprow int BEGIN PRINT 'Building Outer Temp Tbl ' PRINT getdate() CREATE TABLE #OuterTemp(SalesID bigint) -- hold all deadhead keys CREATE INDEX IX_OuterTemp_SalesId on #OuterTemp (SalesID) CREATE TABLE #InnerTemp(SalesID bigint) -- hold subset of deadhead keys CREATE INDEX IX_InnerTemp_SalesID on #InnerTemp (SalesID) INSERT INTO #OuterTemp -- select first 500,000 keys Select TOP 500000 L.SalesID from YourDB.dbo.SalesLead L (Nolock), YourDB.dbo.Transaction (Nolock) T WHERE L.SalesID = T.SalesID AND L.LeadContactId IS NULL -–substitute your condition AND T.MoneyIn = 0 OPTION (MAXDOP 8); -- allow parallel processing -- mainline Select @DeadHeadCnt = count(*)From #OuterTemp PRINT 'Number of Dead Heads' PRINT @DeadHeadCnt While @DeadHeadCnt > 0 BEGIN PRINT 'Begins deletes ' PRINT getdate() PRINT 'Top row ' SELECT @toprow = min(SalesID) From #OuterTemp -- ever incr key PRINT @toprow BEGIN TRANSACTION INSERT INTO #InnerTemp -- experiment with this value Select TOP 5000 SalesID from #OuterTemp -- Delete from application tables by processing small intertemp DELETE from YourDB.dbo.SalesZIP where SalesID in (Select SalesID from #InnerTemp (nolock) ); --innertemp DELETE from YourDB.dbo.Transaction where SalesID in (Select SalesID from #InnerTemp (nolock) ) ; DELETE from YourDB.dbo.SalesLead WHERE SalesID in (Select SalesID from #InnerTemp (nolock) ); --- Delete from outer table DELETE from #OuterTemp WHERE SalesID in (Select SalesID from #InnerTemp (nolock) ) ; Select @DeadHeadCnt = count(*)From #OuterTemp -- reset loop cnt TRUNCATE TABLE #InnerTemp -- cleanup working table COMMIT --WAITFOR DELAY '00:00:01' -- adjust as needed PRINT 'End Deletes' END -- Destroy all temporary tables. IF OBJECT_ID('tempdb..#OuterTemp') IS NOT NULL DROP TABLE #OuterTemp IF OBJECT_ID('tempdb..#INNERTEMP') IS NOT NULL DROP TABLE #InnerTemp END GO
