If you get the error "Invalid object name 'dbo.__SuppressionHolding__keys__" This is most likely a lack of resources on the machine processing the suppressions and is more likely to occur on virtual servers/PCs.
The most likely reason this is happening is because the physical resources are in high demand from other processes, or other virtual machines running on the host machine.
We highly recommend running your suppression processing on dedicated physical machines due to the nature of this type of processing which has very high processor and hard drive access demands, particularly if processing time is a key requirement.
Option 1: Increase or upgrade the resources on the machine running the suppression process
If you are using a virtual environment then unfortunately we are unable to advise you specifically how to do this because of the variety of virtualisation systems being used, but primarily the processor count and the hard drive access times are the biggest factors in suppression processing, so increasing the allocation of processor and the RAM allocated to the virtual machine will help. Please note however that the nature of virtualisation means that these resources are not always dedicated and simply increasing the allocation may not resolve the issue.
The following advice applies to physical machines and to the physical machine hosting the virtual environment.
Fast access SSD drives improve processing times but PCIe SSD's deliver significant performance improvements when the suppression database is located on them.
A high processor count will also improve processing time. We advise multiple cores and ideally hyper-threaded processors. Suppression processing will take advantage of all available cores and threads so the more it can use, the faster the processing will be.
RAM is also important and we recommend a minimum of 16GB, although more will provide performance gains.
Option 2: Specify the number of tasks the suppression package is allowed to run concurrently
This usually does not require changing but in virtualised environments, where there can be competition for resources we have found that this reduces the occurrence of this error. Please be aware though that this setting will reduce performance.
This setting needs to be changed in the background suppression package which is where the suppressions are actually running and not in the suppression configurator task where you directly edit the suppression options on a daily basis. Please ensure that you take the upmost care when editing this package and only make the changes described below
To change this you will need to first determine which version of SQL Server is being used on the machine exhibiting the error, and the number of processors the machine has or has allocated if virtual. Once determined, navigate to "C:\matchIT SQL\SSIS\suppression" and locate and open the folder with the year relevant to the SQL Server version you are using and run the "Suppression.sln" file within that folder which will open the background suppression package.
Once the package is open, you need to right click on the background of the package and not within a container to see the setting. A large amount of this package is contained within a container so you will need to zoom out significantly in order to do this. Once zoomed out, right click anywhere outside of the container (but not on any of the tasks) and go to properties. You will then need to identify the 'MaxConcurrentExecutable' setting in the properties of the package and change the setting from the default of -1 to the processor count of the machine.
The default -1 setting means that the package allows the maximum number of concurrently running executables to equal the number of processors plus two. So if the machine is virtual, and placed under high demand from other processes or virtual machines on the host, this can cause some of the running tasks to fail and produce the "Invalid object name 'dbo.__SuppressionHolding__keys__" error. By limiting this to the number of processors accessible, it should ensure that the processing doesn't exceed the resources the machine has.
Please note however, as already stated, in virtual environments these resources are not always dedicated so there is still potential for the resources to fall below the critical level and cause the processing to fail. We suggest starting out at the same number of processors to keep the performance as high as possible but if you continue to see this error you may need to reduce it further. We suggest reducing by 1 each time. It is for this, and other reasons, that we do not recommend virtual environments for this type of processing.