doc.md

So your system is on fire

First, check the BDTriggerAudit tables record counts:

select @sql
EXEC (@SQL)
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--CREATE PROCEDURE [dbo].[bd_purge_audit]
--AS   
DECLARE @SQL varchar(max)=''

select @SQL = @SQL + '
select ''' + table_name + ''', count(*) from
    dbo.' + table_name + ' (nolock)
    WHERE SyncFlag = ''true'';'
from INFORMATION_SCHEMA.TABLES
where table_name like 'bdtriggeraudit%'

select @sql
EXEC (@SQL)

Ideally, there are zero records in these tables. If they arent, enable the purge tables job, and for temporary relief run:

DECLARE @SQL varchar(max)=''

select @SQL = @SQL + '
SET NOCOUNT ON;
 
DECLARE @r_' + table_name +' INT;
 
SET @r_' + table_name +' = 1;
 
WHILE @r_' + table_name +' > 0
BEGIN
  BEGIN TRANSACTION;
 
  print ''' + table_name + '''
  delete top (1000)
    dbo.' + table_name + '
    WHERE SyncFlag = ''true'';
 
  SET @r_' + table_name +' = @@ROWCOUNT;
 
  COMMIT TRANSACTION;
END;'
from INFORMATION_SCHEMA.TABLES
where table_name like 'bdtriggeraudit%'

Now, check your index fragmentation level:

SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC

The avg_fragmentation_in_percent should be as low to 0 as possible, anything above 10 needs to be resolved. This should be regularlly maintained by your dba. We suggest using https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html