We have a SQL Job set to run daily at 10PM for archiving data, but users are experiencing timeout errors on the site from 10PM to midnight while this job runs.
Here’s an overview of the job logic:
while @isArchiving = 1 and @itemsProcessed < @maxItems
exec ArchiveData @itemsProcessed output
if error_code <> 0
set @isArchiving = 0
waitfor delay '00:10:00'
The ArchiveData procedure takes 100 records created over 30 days ago, archives them in a different database, and deletes them from the main table, including related records from other tables. It also creates temporary tables during this process and removes them upon completion, updating @itemsProcessed with the count of processed records.
Could you suggest what might be slowing down our application during this period, and what I should look into to improve performance without disrupting users?
your archiving job might be blocking the main tables. deleting 100 records at once creates huge transactions that lock up other queries. try smaller batches - maybe 10-20 records - or throw in some “with (nolock)” hints where it’s safe to do so.
that 10-minute delay sounds kinda off. could your archiving be holding locks too long? what isolation level are you using? also, have you looked at dm_exec_requests during those slowdowns to see what’s getting blocked?
Your archival process is competing with normal user operations for resources. Those delete operations are probably creating heavy table locks, especially when you’re removing related records across multiple tables in one transaction. Plus the temp table creation is hammering your tempdb during peak hours. Try breaking up those big transactions with periodic commits - massive deletes can bloat your log files and slow down backups. Run DBCC SHOWCONTIG before and after to see how much fragmentation you’re dealing with. Best fix? Move this to off-peak hours or set up a read-only replica for archival work. Check sys.dm_os_waiting_tasks while it’s running to see exactly what’s causing the bottleneck.