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