What are some options to delete QOS data?
search cancel

What are some options to delete QOS data?

book

Article ID: 277942

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Customer guidance on options to delete QOS data, e.g., to cleanup Test/DEV environment QOS, or prune the data for example, the S_QOS_DATA table based on date.

These options are best used in consort with your DBA.

Environment

  • Any DX UIM version
  • data_engine
  • MS SQL Server
  • TEST/DEV/Lab environments
  • Production
  • orphaned data

Cause

  • Guidance

  • S_QOS_DATA table may have been identified as need indexing or slowing down other queries

  • In some environments, if the S_QOS_DATA table has > 1 million rows

    • This can occur and there may be a need to purse old-unused QOS

Resolution

WARNING!!!

USE AT YOUR OWN RISK!!! TEST IT OUT FIRST IN A TEST/DEV ENVIRONMENT ON A SMALL SET/AMOUNT OF DATA!

Best practice is to take a backup/snapshot of the Database first!


Option 1

Use the nas to run this script to delete QOS data but ONLY after adjusting the number of days.

Run the script SHOWN BELOW via the nas. But as always, we recommend working with a DBA to assist and testing it out in a Test/DEV environment first and on a small scale, e.g., e.g., set the days to 1 and check the 'before and after' results.

iDays = 30 -- delete qos-sources which did not deliver values in the past X days. Only specify FULL days here.
sDatabaseType = "sqlserver" -- specify the database type you're using. Valid values: sqlserver | mysql
-- if you have Oracle, send support an e-mail and/or work with your Oracle DBA.
bHot = 0 -- set this to 0 if you do not want the script to perform any real modifications, set it to 1 just to run it.

-- do not modify after this line unless you know what you're doing --
database.open("provider=nis;database=nis;driver=none") -- open the database

-- first, find all used RN_TABLES
sSql = "select distinct r_table from s_qos_data"
tRtables = database.query(sSql )

if bHot == 0 then
print( "-- table count: "..#tRtables.." RN-Tables" )
print( "-- the following output is valid SQL, you can copy this and execute it in your database shell manually" )
else
print( "housekeeping will delete data from "..#tRtables.." RN-Tables" )
end

-- now iterate over all rn_tables and determine orphaned qos-data
for iIx, tRow in pairs(tRtables) do
sDelQuery = ""
if sDatabaseType == "sqlserver" then
sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having datediff(day, max(sampletime), getdate() ) > "..iDays.." );"
end
if sDatabaseType == "mysql" then
sDelQuery = "delete from s_qos_data where table_id in (select table_id from "..tRow["r_table"].." group by table_id having max(sampletime) < date_add( now(), INTERVAL -"..iDays.." DAY);"
end

if bHot ~= 1 then
print( sDelQuery )
else
print( "deleting orphaned qos-data for table "..tRow["r_table"] )
database.query(sDelQuery)
end

sDelUndeliveredQuery = "delete from s_qos_data where r_table='"..tRow["r_table"].."' and table_id not in (select distinct table_id from "..tRow["r_table"]..")"
if bHot ~= 1 then
print( sDelUndeliveredQuery )
else
print( "deleting never delivered qos-data for table "..tRow["r_table"] )
database.query( sDelUndeliveredQuery )
end
end

print( "-- housekeeping done." )

database.close()

Option 2

Another option in this case is to delete QOS data using the snapshot table QOS data sampletime as a reference:

Best practice is to take a backup/snapshot of the Database first!

You can also test this on a small scale first/delete only 1 days' worth of data and then check to make sure it worked as expected. You can adjust the date.

But if this is a TEST/DEV environment, and you don't care if you lose the QOS data in the database, you can run through the following steps:

1. Deactivate the data_engine

2. Run select * from S_QOS_SNAPSHOT DESC and examine the rows/sampletimes and dates since the delete will be based on these entries/table id's and sampletimes.

3. Change/adjust the date value shown below to meet your historical data purge needs - this is ONLY an example.

4. Run the query

delete sqd
from s_qos_data sqd
inner join s_qos_snapshot sqs on sqd.table_id = sqs.table_id
where sqs.sampletime < '2024-12-31';

The raw data in the RN_QOS_DATA_* tables should age out over time automatically via data_engine nightly maintenance based on the data_engine retention settings. Make sure Partitioning was already enabled. If not, open  open a support case and request guidance.

Your DBA MUST make sure there is enough memory configured to handle any long running delete jobs.


Option 3 (wiping out all QOS data in a LAB environment)

If you want to take a more aggressive approach and delete ALL of the QOS data, not just raw/historical, please refer to the steps listed below

  1. Deactivate data_engine

  2. DELETE FROM s_qos_snapshot;

  3. DELETE FROM s_qos_data;

  4. DELETE FROM s_qos_snapshot;

  5. DELETE FROM s_qos_definition;

  6. Activate data_engine

  7. Restart every robot once to resend all QOS definitions

This will just wipe out everything if needed.

Option 4

-- orphans_query.sql

-- Here is the edited and renumbered steps to run orphans_query.sql to help reduce the number of rows in the S_QOS_DATA table which can become bloated over time with QOS that is no longer used. In some cases where resources on the database server may be limited, when the row count is 1M or higher it may slow down DB query performance.

-- IMPORTANT!!! READ every comment below and if you have any questions, discuss this with Support first!

-- Instructions (Warning: TEST in a TEST environment first!) - Review this approach to purge unused 'orphaned' QOS entries from the S_QOS_DATA table when the row count is greater than 1M and/or queries against S_QOS_DATA are taking longer than 60 secs to complete - but USE AT YOUR OWN RISK!

-- Run select count(*) from S_QOS_DATA beforehand to see and record the row count

 -- Instead of setting null for all ci_metric_id values in s_qos_data, we can only set NULL for those values where we are not getting data for since the last 14 days.

-- In this way the load can be reduced on the data engine in a large environment but note that the number of days can be adjusted. UPDATE S_QOS_DATA  SET CI_METRIC_ID = NULL  where table_id in(select table_id from s_qos_snapshot where sampletime < DATEADD(DAY, -14, GETDATE())) 

-- We HIGHLY recommend testing this in a test environment before executing in production 

-- 1. BACKUP the S_QOS_DATA table to be safe!!! for example run:

SELECT * INTO S_QOS_DATA_tmp1 from S_QOS_DATA 

--  Note that after you run the statements shown at the bottom (which starts with declare @table_id int)  once, if it fails on the #orphans table,

-- 2. DEACTIVATE the data_engine

-- Run
SELECT * FROM S_QOS_SNAPSHOT table to list the count of rows and to make sure its populated and document the count

-- 3. Execute the query:
UPDATE S_QOS_DATA  SET CI_METRIC_ID = NULL  where table_id in(select table_id from s_qos_snapshot where sampletime < DATEADD(DAY, -14, GETDATE()))

--  Note again that the number of days in the statement above can be adjusted/increased.

-- 4. ACTIVATE the data_engine

-- 5. WAIT AT LEAST 24 hours or longer, e.g., 7 days, or whatever makes sense to be safe/sure that all live QOS data is being populated/updated and no QOS collection has been disabled/temporarily turned off because if it has, you may end up deleting QOS data that you or someone else/another admin only 'temporarily' disabled for some valid reason.***

 --- After 24 hours or longer, there will be several entries in S_QOS_DATA THAT REPRESENT METRICS THAT ARE NO LONGER BEING COLLECTED.

-- you can then run this script and it will go through all of these entries with a NULL ci_metric_id and check to see if there is any data in the RN_QOS_DATA associated with the entry.

-- If there is no data in RN_QOS_DATA_XXXX for the entry, an entry/entries will be created in a table called #orphans

-- this entry contains the rn_table and table_id

6. --Run the following

=============================================================================

declare @table_id int,

@rn_table varchar(255),

@fstatus int,

@sql varchar(500); 

IF NOT EXISTS (select * from information_schema.tables where table_name = '#orphans')

BEGIN

create table #orphans (

rn_table varchar(50),

table_id int

);

END 

declare a cursor for

select table_id,r_table from s_qos_data where ci_metric_id is null;

open a;

fetch next from a into @table_id, @rn_table;

set @fstatus = (select top 1 fetch_status from sys.dm_exec_cursors(0) where name = 'a' order by statement_start_offset desc);

while (@fstatus = 0)

BEGIN 

set @sql = 'IF NOT EXISTS (SELECT * FROM ' + @rn_table + ' WHERE TABLE_ID = ' + cast(@table_id as varchar) + ') INSERT INTO #orphans(rn_table,table_id) VALUES(' + CHAR(39) + @rn_table + CHAR(39) + ',' + cast(@table_id as varchar) +')';

--print(@sql)

exec(@sql) 

fetch next from a into @table_id, @rn_table;

set @fstatus = (select top 1 fetch_status from sys.dm_exec_cursors(0) where name = 'a' order by statement_start_offset desc);

END 

close a;

deallocate a;

--------------------------------------------
--  you can then run this query to DELETE the 'orphaned' entries:

   7. DELETE from S_QOS_DATA WHERE table_id in (select table_id from #orphans)

   8. --  Run select count(*) from S_QOS_DATA beforehand to see the row count, e.g., 2.65M, then AFTER that SELECT query is run, run it again to see the count after the query completes.

-- If necessary, run 'DROP table orphans' and then you can run the orphans query again successfully.

=============================================================================

 

Powered by Wolken Software