DX Operational Intelligence

Expand all | Collapse all

SOI database purge

Jump to Best Answer
  • 1.  SOI database purge

    Posted 02-22-2019 11:01 AM

    SOI v4.0.2

    Hi, I'm interested in other people's experiences with regularly purging excess data in the SAMStore.

     

    We currently keep 90 days worth of data and each day look to purge one day's worth - we do this with the following soitoolbox commands, run locally on the database server with SOI still running:

     

    soitoolbox.exe -x -v -b 300 -t 86400 --purgeClearedAlerts 90
    soitoolbox.exe -x -v -b 300 -t 86400 --cleanHistoryData 90
    soitoolbox.exe -x -v -b 300 -t 1200 --rebuildIndexes

     

    However it is taking approximately 6 hours to purge 20,000 alerts - these are timings taken from my development environment and in Production we get roughly 10,000 alerts per day, so I am assuming it would take around 3 hours.

     

    The server isn't CPU or Memory bound during execution and I was wondering whether other people were experiencing these sorts of times.

     

    Many thanks
    Chris



  • 2.  Re: SOI database purge

    Posted 02-25-2019 06:06 AM

    We have a similar set of maintenance scripts running, but with slightly different figures:

     

    purgeClearedAlerts 45

    cleanHistoryData 180

     

    Besides the above we also have

     

    rebuildIndexes

    purgeDBInconsistencies

     

    In our situation purgeClearedAlerts takes around 220 secs to clear approx. 50,000 alerts every two days

    cleanHistoryData takes 5 to 15 secs every week

    rebuildIndexes takes max. 130 secs every 

    purgeDBInconsistencies takes 500 to 1500 secs every week

     

    We seem to have the same version of SOI running. Planning to upgrade on very short notice.

     

    Hope this helps.

    Vincent



  • 3.  Re: SOI database purge

    Posted 02-26-2019 06:56 AM

    Thanks very much for your response Vincent and these figures show your purges to be considerably quicker than ours! - I have one further question, if you wouldn't mind; Are your purges performed whilst SOI is still running? As I know they will be a lot quicker if SOI is stopped. I am running my soitoolbox purges locally on the database server, so that it doesn't have to go over the network.


    I have reached out to our DBAs for advice and they have suggested the following changes:

     

    1. Decrease the max degree of parallelism from 3 to 2, that means every DB process will use 2 CPUs instead of 3 (current configuration), to try to get a better performance on CPU utilization.

    2. Create 3 indexes on the below tables with recommended indexes, as these have been identified as impacting the performance:

    [SAMStore].[dbo].[ca_ssa_binaryrelationship]
    [SAMStore].[dbo].[AuditRecords]
    [SAMStore].[dbo].[ca_ssa_binaryrelationship]

     

    I will supply updates on my findings.

     

    Regards
    Chris



  • 4.  Re: SOI database purge

    Posted 03-06-2019 02:47 AM

    The above changes made no difference to performance whatsoever :-(

    I've opened a Support case with CA Broadcom to see if they can help.



  • 5.  Re: SOI database purge

    Posted 03-11-2019 05:39 AM

    Hey Chris,

     

    All of the above soitoolbox commands run whilst SOI is still running, without any issues.

     

    Vincent



  • 6.  Re: SOI database purge
    Best Answer

    Posted 03-27-2019 07:13 AM

    CA Support have been working with us and have produced a revised soitoolbox command that targets specific database tables and also allows a "batchsize" to be defined which will speed up the online processing - this has dramatically improved our timings and I'm told will be included in future releases.