The table "sqlwatch_trend_perf_os_performance_counters" keeps growing

[sozezzo]

The table "sqlwatch_trend_perf_os_performance_counters" keeps growing, even when we set all retention day to 1. We can set all the retention except for the table "sqlwatch_trend_perf_os_performance_counters".

The retention is hardcoded at Stored Procedure : [dbo].[usp_sqlwatch_internal_exec_activated] 1 day, 90 days and 720 days.

Describe the solution you'd like No longer use the valid_until column to determine when it need to be deleted.

Add in table "sqlwatch_config_snapshot_type" 3 more retention options. (exemple: os_performance_counters_1min, os_performance_counters_5min, os_performance_counters_60min)

Describe alternatives you've considered We add a Job to delete after one day, because we use the Central Repository, BUT this is a patch, it is not a real solution.

Additional context nope.

[marcingminski]

Thanks. This was a "lift and shift" from the agent job where you could change the @valid_days easily but not so easy now in the procedure. I like the suggestion of making it into a snapshot. I will think of that, Thanks.

[sozezzo]

This is my suggestion

GO
if ((SELECT COUNT(*) FROM [dbo].[sqlwatch_config_snapshot_type] where [snapshot_type_id] in (32, 33, 34)) = 0 )
begin

    insert into [dbo].[sqlwatch_config_snapshot_type] ([snapshot_type_id], [snapshot_type_desc], [snapshot_retention_days], [collect])
    select 32 AS [snapshot_type_id], 'OS performance counters by 1 min' as [snapshot_type_desc],    1 as [snapshot_retention_days], 1 as [collect];

    insert into [dbo].[sqlwatch_config_snapshot_type] ([snapshot_type_id], [snapshot_type_desc], [snapshot_retention_days], [collect])
    select 33 AS [snapshot_type_id], 'OS performance counters by 5 min' as [snapshot_type_desc],    7 as [snapshot_retention_days], 1 as [collect];

    insert into [dbo].[sqlwatch_config_snapshot_type] ([snapshot_type_id], [snapshot_type_desc], [snapshot_retention_days], [collect])
    select 34 AS [snapshot_type_id], 'OS performance counters by 60 min' as [snapshot_type_desc], 720 as [snapshot_retention_days], 1 as [collect];

end
GO

GO
ALTER PROCEDURE [dbo].[usp_sqlwatch_internal_retention]
as

set nocount on;
set xact_abort on;

declare @snapshot_type_id tinyint,
        @batch_size smallint,
        @row_count int,
        @action_queue_retention_days_failed smallint,
        @action_queue_retention_days_success smallint,
        @application_log_retention_days smallint,
        @sql_instance varchar(32) = dbo.ufn_sqlwatch_get_servername();

select @batch_size = [dbo].[ufn_sqlwatch_get_config_value](6, null)
select @action_queue_retention_days_failed = [dbo].[ufn_sqlwatch_get_config_value](3, null)
select @action_queue_retention_days_success = [dbo].[ufn_sqlwatch_get_config_value](4, null)
select @application_log_retention_days = [dbo].[ufn_sqlwatch_get_config_value](1, null)
select @row_count = 1 -- initalitzaion, otherwise loop will not be entered

declare @cutoff_dates as table (
    snapshot_time datetime2(0),
    sql_instance varchar(32),
    snapshot_type_id tinyint,
    primary key ([sql_instance], [snapshot_type_id])
)

/*  To account for central repository, we need a list of all possible snapshot types cross joined with servers list
    and calculate retention times from the type. This cannot be done for retention -1 as for that scenario, 
    we need to know the latest current snapshot.    */
insert into @cutoff_dates
    select snapshot_time = case when st.snapshot_retention_days >0 then dateadd(day,-st.snapshot_retention_days,GETUTCDATE()) else null end
        , si.sql_instance
        , st.snapshot_type_id
    from [dbo].[sqlwatch_config_snapshot_type] st
    cross join [dbo].[sqlwatch_config_sql_instance] si

/*  Once we have a list of snapshots and dates, 
    we can get max snapshot for the rest - to avoid excesive scanning
    and try force a seek, we are limiting this to only those have not got date yet i.e. snapshot types = -1 */
update c
    set snapshot_time = t.snapshot_time
from @cutoff_dates c
inner join (
    select snapshot_time=max(sh.snapshot_time), sh.sql_instance, sh.snapshot_type_id
    from dbo.sqlwatch_logger_snapshot_header sh
    inner join @cutoff_dates cd
        on cd.sql_instance = sh.sql_instance collate database_default
        and cd.snapshot_type_id = sh.snapshot_type_id
    where cd.snapshot_time is null
    group by sh.sql_instance, sh.snapshot_type_id
    ) t
on t.sql_instance = c.sql_instance collate database_default
and t.snapshot_type_id = c.snapshot_type_id

while @row_count > 0
    begin
        begin tran
            delete top (@batch_size) h
            from dbo.[sqlwatch_logger_snapshot_header] h (readpast)
            inner join @cutoff_dates c 
                on h.snapshot_time < c.snapshot_time
                and h.sql_instance = c.sql_instance
                and h.snapshot_type_id = c.snapshot_type_id

            -- do not remove baseline snapshots:
            where h.snapshot_time not in (
                select snapshot_time
                from [dbo].[sqlwatch_meta_snapshot_header_baseline]
                where sql_instance = h.sql_instance
                )

            set @row_count = @@ROWCOUNT
            print 'Deleted ' + convert(varchar(max),@row_count) + ' records from [dbo].[sqlwatch_logger_snapshot_header]'
        commit tran
    end

    /*  delete old records from the action queue */
    delete 
    from [dbo].[sqlwatch_meta_action_queue] 
    where [time_queued] < case when exec_status <> 'FAILED' then dateadd(day,-@action_queue_retention_days_success,sysdatetime()) else dateadd(day,-@action_queue_retention_days_failed,sysdatetime()) end
    Print 'Deleted ' + convert(varchar(max),@@ROWCOUNT) + ' records from [dbo].[sqlwatch_meta_action_queue]'

    /* Application log retention */
set @row_count = 1
while @row_count > 0
    begin
        delete top (@batch_size)
        from dbo.sqlwatch_app_log
        where event_time < dateadd(day,-@application_log_retention_days, SYSDATETIME())

        set @row_count = @@ROWCOUNT
        Print 'Deleted ' + convert(varchar(max),@@ROWCOUNT) + ' records from [dbo].[sqlwatch_app_log]'
    end

    /*  Trend tables retention.
        These are detached from the header so we can keep more history and in a slightly different format to utilise less storage.
        We are going to have remove data from these tables manually */

    set @snapshot_type_id = 1 --Performance Counters
    delete top (10000) d from [dbo].[sqlwatch_trend_perf_os_performance_counters] d inner join @cutoff_dates c -- limit delete operation
    on d.snapshot_time < c.snapshot_time
    where (d.interval_minutes = 1 and c.[snapshot_type_id] = 32)
    OR   (d.interval_minutes = 5  and c.[snapshot_type_id] = 33)
    OR   (d.interval_minutes = 60 and c.[snapshot_type_id] = 34)
    --and sql_instance = @sql_instance and [sqlwatch_trend_perf_os_performance_counters].sql_instance = c.sql_instance  -- we do not import configuration
    --and getutcdate() > valid_until -- it does not longer needed
    Print 'Deleted ' + convert(varchar(max),@@ROWCOUNT) + ' records from [dbo].[sqlwatch_trend_perf_os_performance_counters]'
GO
/*
exec [dbo].[usp_sqlwatch_internal_retention]
go
select top 5 * from [sqlwatch_trend_perf_os_performance_counters] where interval_minutes = 60 order by snapshot_time asc
select top 5 * from [sqlwatch_trend_perf_os_performance_counters] where interval_minutes = 5 order by snapshot_time asc
select top 5 * from [sqlwatch_trend_perf_os_performance_counters] where interval_minutes = 1 order by snapshot_time asc
GO
*/

by the way, the "valid_until" column is no longer required.