Max degree of parallelism and cost threshold for parallelism
Long queries benefit from parallel execution. However, initialisation, synchronisation and termination of parallelity cause an overhead. If you experience LATCH_EX and CXPACKET wait types with 1IM queries, lowering max degree of parallelism (default 0 = up to 64 CPUs depending on available CPUs), and increasing the cost threshold for parallelism (default 5) may improve performance.
A starting point would be 2 for the former and 60 for the latter, with careful consideration, monitoring of query cost and wait types and testing.
------------------------- check and set max degree of parallelism
EXEC sp_configure 'show advanced options', 1GO
Optimize for ad hoc workloads
If you have high amounts of Ad Hoc type execution plans as per sys.dm_exec_cached_plans, their execution plans consume memory for each one execution. In this case, setting optimize for ad hoc workloads to 1 (from default 0) with careful consideration and testing will delay execution plan storage until the second execution of a query.-----------------------
Enabling parameterization forced per database (from default false) forces parameterization of statements, therefore reusing existing execution plans. The downside is that also bad execution plans could now be reused, therefore this needs careful consideration and testing. In some cases (e.g. order history as per a customer report) this may need customisations with optimised queries.------------------
The information in the script(s) provided is known to work successfully; however, they have not been officially tested by One Identity Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by One Identity Support and Development. One Identity Support and Development recommend always making a backup of the current database prior to execution of any script(s) that may modify it. For customization of One Identity Manager, please contact our Professional Services Organization.