{"id":107854,"date":"2025-11-19T11:00:00","date_gmt":"2025-11-19T11:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107854"},"modified":"2025-12-17T11:21:31","modified_gmt":"2025-12-17T11:21:31","slug":"monitoring-and-tuning-batched-deletion-processes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/monitoring-and-tuning-batched-deletion-processes\/","title":{"rendered":"How to Monitor and Optimize Batched Deletion Processes in SQL Server"},"content":{"rendered":"\n

Batched deletions are a common strategy in SQL Server<\/a> to manage large datasets without overloading the system, but poorly tuned deletes can cause blocking, long-running transactions, and heavy log usage. Learn how to monitor and optimize these processes for smooth, efficient database performance.<\/strong><\/p>\n\n\n\n

In previous <\/a>articles<\/a> I showed patterns for working with large amounts of data on big tables while keeping locking<\/a> at a minimum. These processes can allow migrations and maintenance<\/a> without requiring downtime but, in environments with unpredictable database workloads, there is a risk of heavy traffic starting at any time and disrupting a once smooth operation. In this article, I’ll demonstrate how to augment these processes to allow dynamic adjustment of the configuration.<\/p>\n\n\n\n

For most systems, the main limitation these techniques run into is the speed and throughput of I\/O (input\/output)<\/a>. During periods of low traffic, a large batch size may perform great with no impact to production, but as traffic increases, the storage subsystem may not be able to keep up. <\/p>\n\n\n\n

I’ll show two workarounds to deal with this issue: lowering the batch size, and introducing a small delay in between batches. Both will allow the process to continue running with less I\/O demand but, if this still isn’t enough, we can easily stop the process and restart at a different time.<\/p>\n\n\n\n

Logging Tables in SQL Server<\/h2>\n\n\n\n

Before we get into the configuration options, let\u2019s consider how we can get feedback on our process while it\u2019s running, so we’re better informed about the adjustments we want to make. Let\u2019s review the code for purging in batches explored in this article<\/a>:<\/p>\n\n\n\n

SET NOCOUNT ON;\n--control the number of rows deleted per iteration\nDECLARE @BatchSize INT = 5000;\n \n--variable used to tell the process to stop\nDECLARE @Stop INT = 0;\n \nIF (OBJECT_ID ('tempdb..#ToProcess') IS NULL)\n  CREATE TABLE #ToProcess (Id INT NOT NULL PRIMARY KEY CLUSTERED);\nIF (OBJECT_ID ('tempdb..#Batch') IS NULL)\n  CREATE TABLE #Batch (Id INT NOT NULL PRIMARY KEY CLUSTERED);\n \n-----------------Gather Ids------------------------------------\nINSERT INTO #ToProcess (Id)\nSELECT Id\nFROM dbo.Posts\nWHERE CreationDate < '2011';\n \n-----------------Main Loop------------------------------------\nWHILE (@Stop = 0)\nBEGIN\n  --Load up our batch table while deleting from the main set\n  DELETE TOP (@BatchSize) #ToProcess\n  OUTPUT DELETED.Id INTO #Batch (Id);\n \n  --Once the rowcount is less than the batchsize,\n  -- we can stop (after this loop iteration)\n  IF @@ROWCOUNT < @BatchSize\n    SELECT @Stop = 1;\n \n--Perform the DELETE\n  DELETE FROM p\n  FROM dbo.Posts p\n    JOIN #Batch b ON p.Id = b.Id;\n \n  --Clear out Batch table\n  TRUNCATE TABLE #Batch\n \nEND;<\/pre><\/div>\n\n\n\n
Now, we’ll add a new table to hold our logging:<\/p>\n\n\n\n
IF (OBJECT_ID('dbo.PurgeLogging') IS NULL)\n\tCREATE TABLE dbo.PurgeLogging (\n\t LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED\n\t,StartTime DATETIME2(7) NOT NULL DEFAULT GETDATE()\n\t,EndTime DATETIME2(7) NULL\n\t,Duration_ms AS \nDATEDIFF(MILLISECOND\n, StartTime\n, ISNULL(EndTime, GETDATE()))\t\n\t,RowsAffected INT NULL\n\t,[BatchSize] INT NOT NULL\n\t,DelayTime varchar(8) NOT NULL\n\t,ErrorCode INT NULL\n\t,ErrorMessage NVARCHAR(255) NULL\n\t)<\/pre><\/div>\n\n\n\n
This table will contain a row for every iteration of the loop, tracking the time it takes for the DELETE<\/code> statement<\/a> to run (because that’s what will alert us to any blocking). Depending on the latency<\/a> demands of your system, even a 1-2 second iteration may be too slow, but other systems may function without issue as long as this is below the default timeout of 30 seconds. <\/p>\n\n\n\n
Besides the time tracking, we’ll also record the values of our configuration<\/a> parameters and any errors that come up during the operation. We do this by inserting a record at the beginning of our loop:<\/p>\n\n\n\n
INSERT INTO dbo.PurgeLogging (StartTime,[BatchSize], DelayTime)\nSELECT GETDATE(), @BatchSize, @DelayTime\nSELECT @LogId = SCOPE_IDENTITY()<\/pre><\/div>\n\n\n\n
And by updating that record at the end of our loop:<\/p>\n\n\n\n
UPDATE dbo.PurgeLogging \n\tSET EndTime = GETDATE()\n\t, RowsAffected = @RowsAffected\n\t, ErrorMsg = @ErrorMsg\n\t, ErrorNum = @ErrorNum\nWHERE LogId = @LogId<\/pre><\/div>\n\n\n\n
Configuration Tables in SQL Server<\/h2>\n\n\n\n
In our previous script we used a static variable to hold the BatchSize, but we’ll now store this in a table along with other parameters to adjust the process. <\/p>\n\n\n\n
This allows us to make changes on the fly without stopping the script and rolling back a midflight transaction<\/a>. It also opens up the possibility of using an automated process to tweak these parameters based on system load.<\/p>\n\n\n\n
Let\u2019s look at our configuration table:<\/p>\n\n\n\n
IF (OBJECT_ID ('dbo.PurgeConfig') IS NULL)\n\tCREATE TABLE dbo.PurgeConfig (\n\t\tConfigId INT NOT NULL DEFAULT (1) PRIMARY KEY CLUSTERED \n\t\t, DelayTime varchar(8) NOT NULL\n\t\t, [BatchSize] INT NOT NULL\n\t\t, [Stop] bit NOT NULL DEFAULT (0)\n\t\t, CONSTRAINT CH_PurgeConfig_ConfigId CHECK (ConfigId=1)\n\t\t);<\/pre><\/div>\n\n\n\n
This table contains our parameters as well as a check constraint<\/a> to ensure we only have one row stored at any time. We’ll update the running variables at the end of each loop (as well as at the beginning of the script):<\/p>\n\n\n\n
SELECT @BatchSize = [BatchSize]\n\t\t, @DelayTime = [DelayTime]\n\t\t--ONLY UPDATE @Stop when it is 0\n\t\t, @Stop = CASE WHEN @Stop = 1 THEN 1 ELSE [Stop] END\nFROM dbo.PurgeConfig<\/pre><\/div>\n\n\n\n
Error Handling in SQL Server<\/h2>\n\n\n\n
While we are making improvements to the script, let\u2019s add some polish by introducing error handling<\/a> inside the main loop. This can be as simple as adding a TRY\/CATCH<\/em> block and some variables to store the error message and number. <\/p>\n\n\n\n
You might also opt to set @Stop = 1<\/em> in this section if you want the script to stop any time it hits an error. In this example, I’m letting it continue because I can address the error and rerun the script later; any records that failed to delete will be scooped up by the insert to the #ToProcess<\/em> table on the next run.<\/p>\n\n\n\n
Putting It All Together<\/h2>\n\n\n\n
Now let\u2019s look at what our script looks like with these new tables:<\/p>\n\n\n\n
SET NOCOUNT ON;\n--control the number of rows deleted per iteration\nDECLARE @BatchSize INT;\n \n--variable used to tell the process to stop\nDECLARE @Stop INT = 0;\n\n--add a delay between iterations\nDECLARE @DelayTime VARCHAR(8);\n\n--logging variable for rowcount\nDECLARE @RowsAffected INT;\n\n--logging variable for the log record\nDECLARE @LogId INT;\n\n--logging variables for errors\nDECLARE @ErrorMsg nvarchar(4000)\n\t, @ErrorNum INT;\n\nIF (OBJECT_ID ('tempdb..#ToProcess') IS NULL)\n  CREATE TABLE #ToProcess (Id INT NOT NULL PRIMARY KEY CLUSTERED);\nIF (OBJECT_ID ('tempdb..#Batch') IS NULL)\n  CREATE TABLE #Batch (Id INT NOT NULL PRIMARY KEY CLUSTERED);\n\n--------------Logging Table----------------------------------- \nIF (OBJECT_ID('dbo.PurgeLogging') IS NULL)\n\tCREATE TABLE dbo.PurgeLogging (\n\t LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED\n\t,StartTime DATETIME2(7) NOT NULL DEFAULT GETDATE()\n\t,EndTime DATETIME2(7) NULL\n\t,Duration_ms AS \n    DATEDIFF(MILLISECOND\n    , StartTime\n    , ISNULL(EndTime, GETDATE()))\t\n\t,RowsAffected INT NULL\n\t,[BatchSize] INT NOT NULL\n\t,DelayTime varchar(8) NOT NULL\n\t,ErrorCode INT NULL\n\t,ErrorMessage NVARCHAR(255) NULL\n\t);\n\n-----------------Configuration Table---------------------------\nIF (OBJECT_ID ('dbo.PurgeConfig') IS NULL)\nBEGIN\n\tCREATE TABLE dbo.PurgeConfig (\n\t\tConfigId INT NOT NULL DEFAULT (1) PRIMARY KEY CLUSTERED \n\t\t, DelayTime varchar(8) NOT NULL\n\t\t, [BatchSize] INT NOT NULL\n\t\t, [Stop] bit NOT NULL DEFAULT (0)\n\t\t, CONSTRAINT CH_PurgeConfig_ConfigId CHECK (ConfigId=1)\n\t\t);\n\n-----------------Add Initial Configuration---------------------\n\tINSERT INTO dbo.PurgeConfig (DelayTime, [BatchSize], [Stop])\n\tVALUES ('00:00:00', 5000, 0);\n\tSELECT @BatchSize = [BatchSize]\n\t\t\t, @DelayTime = [DelayTime]\n\t\t\t--ONLY UPDATE @Stop when it is 0\n\t\t\t, @Stop = CASE WHEN @Stop = 1 THEN 1 ELSE [Stop] END\n\tFROM dbo.PurgeConfig;\nEND\n\n-----------------Gather Ids------------------------------------\nINSERT INTO #ToProcess (Id)\nSELECT Id\nFROM dbo.Posts\nWHERE CreationDate < '2011';\n\n-----------------Main Loop------------------------------------\nWHILE (@Stop = 0)\nBEGIN\n  --Create a logging record\n  INSERT INTO dbo.PurgeLogging (StartTime,[BatchSize], DelayTime)\n  SELECT GETDATE(), @BatchSize, @DelayTime;\n  SELECT @LogId = SCOPE_IDENTITY();\n\n  --Load up our batch table while deleting from the main set\n  DELETE TOP (@BatchSize) #ToProcess\n  OUTPUT DELETED.Id INTO #Batch (Id);\n  \n  --Once the rowcount is less than the batchsize,\n  -- we can stop (after this loop iteration)\n  IF @@ROWCOUNT < @BatchSize\n    SELECT @Stop = 1;\n  \n  BEGIN TRY\n    --Perform the DELETE\n    DELETE FROM p\n    FROM dbo.Posts p\n      JOIN #Batch b ON p.Id = b.Id;\n    --Store the rowcount\n    SELECT @RowsAffected = @@ROWCOUNT;\n  END TRY\n  BEGIN CATCH\n    --if we have an error, store the message and log it\n    -- optionally you can stop the process here by setting @stop =1\n\t\tSELECT @ErrorMsg = ERROR_MESSAGE()\n\t\t, @ErrorNum = ERROR_NUMBER()\n\tEND CATCH\n\n  --Update the logging table\n  UPDATE dbo.PurgeLogging \n\tSET EndTime = GETDATE()\n    , RowsAffected = @RowsAffected\n    , ErrorMessage = @ErrorMsg\n    , ErrorCode = @ErrorNum\n  WHERE LogId = @LogId;\n\n  --Refresh running configuration from config table\n  SELECT @BatchSize = [BatchSize]\n\t\t, @DelayTime = [DelayTime]\n\t\t--ONLY UPDATE @Stop when it is 0\n\t\t, @Stop = CASE WHEN @Stop = 1 THEN 1 ELSE [Stop] END\n  FROM dbo.PurgeConfig;\n\n  --Clear out Batch table\n  TRUNCATE TABLE #Batch;\n\n  \u2013-Wait before running the next batch\n  WAITFOR DELAY @DelayTime\n \nEND;<\/pre><\/div>\n\n\n\n
After we kick off this script we can monitor the progress by querying the PurgeLogging<\/em> table:<\/p>\n\n\n\n
SELECT TOP 100 * FROM dbo.PurgeLogging\nORDER BY LogId DESC<\/pre><\/div>\n\n\n\n
This results in each batch taking less than 200 milliseconds to complete:<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
Then, if we want to change the batch size or add a delay in between batches, we can update the PurgeConfig <\/em>table like so:<\/p>\n\n\n\n
UPDATE PurgeConfig\n\tSET DelayTime = '00:00:02'\n\t, [BatchSize] = 100000<\/pre><\/div>\n\n\n\n
We can see in our logging table that the change takes effect seamlessly:<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
Notice that there’s now a second gap between the StartTime and EndTime of the previous row, which could help slower disks keep up.<\/p>\n\n\n\n
We can also see that each batch is now taking more than a second – if we feel this is too long, we can update our PurgeConfig table once again to lower the batch size:<\/p>\n\n\n\n
\"Image<\/figure>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Adding logging and dynamic configuration to this technique allows us to tune the process to the unique capabilities and requirements of any environment. By looking at the time each loop takes to execute, we can adjust the batch size to keep our impact to an acceptable amount. If our I\/O is saturated, we can add a delay in between batches to allow other processes to complete. <\/p>\n\n\n\n
This technique can be used to purge old data as I have shown here, but it can also be used for more advanced processes like changing the datatype on a column while a table is being used<\/a>, or deleting from multiple tables with foreign key<\/a> relationships.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
10 tools for every stage of SQL Server development<\/h3>\n                
\n                                            SQL Toolbelt Essentials includes 10 ingeniously simple tools that cover your entire database development lifecycle.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: Batched Deletions in SQL Server<\/h2>\n\n                        
1. What is a batched delete in SQL Server?<\/h3>\n            
\n                
A batched delete in SQL Server removes rows in small chunks instead of one large transaction to reduce blocking, log growth, and performance impact.<\/div>\n            <\/div>\n                    
2. Why use batched deletes in SQL Server?<\/h3>\n            
\n                
Batched deletes in SQL Server improve stability, avoid long locks, and prevent transaction log issues during large data purges.<\/span><\/p>\n            <\/div>\n                    
3. How do I pick a batch size in SQL Server?<\/h3>\n            
\n                
Start small (e.g., 1,000 rows), monitor performance, and adjust until you balance speed with minimal blocking.<\/span><\/p>\n            <\/div>\n                    
4. How do batched deletes affect the transaction log in SQL Server?<\/h3>\n            
\n                
Deletes are fully logged. Smaller batches help control log growth and make backups faster.<\/span><\/p>\n            <\/div>\n                    
5. Do batched deletes in SQL Server cause fragmentation?<\/h3>\n            
\n                
Yes. Plan index maintenance and update statistics after large deletes.<\/span><\/p>\n            <\/div>\n                    
6. How can I prevent blocking?<\/h3>\n            
\n                
Prevent blocking in SQL Server by using indexed predicates, short transactions, smaller batches, and consider snapshot isolation.<\/span><\/p>\n            <\/div>\n                    
7. What should I monitor when using batched delete in SQL Server?<\/h3>\n            
\n                
Track rows per batch, duration, waits, blocking, CPU\/IO, and log usage.<\/span><\/p>\n            <\/div>\n                    
8. Can partitioning speed up deletes in SQL Server?<\/h3>\n            
\n                
Yes! Partition switching is much faster and less disruptive than row-by-row deletes.<\/span><\/p>\n            <\/div>\n                    
9. How do I resume after a failure?<\/h3>\n            
\n                
Save the last processed key and restart from there to avoid duplicates.<\/span><\/p>\n            <\/div>\n                    
10. How do I confirm efficiency?<\/h3>\n            
\n                
Check execution plans for index seeks, avoid scans, and tune predicates.<\/span><\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Batched deletions are a common strategy in SQL Server to manage large datasets without overloading the system, but poorly tuned deletes can cause blocking, long-running transactions, and heavy log usage. Learn how to monitor and optimize these processes for smooth, efficient database performance. In previous articles I showed patterns for working with large amounts of……<\/p>\n","protected":false},"author":344195,"featured_media":105376,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143524],"tags":[4168,4170,4150,4151],"coauthors":[159137],"class_list":["post-107854","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-sql-server","tag-database","tag-database-administration","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107854","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/344195"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=107854"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107854\/revisions"}],"predecessor-version":[{"id":108131,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107854\/revisions\/108131"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105376"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=107854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=107854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=107854"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=107854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}