Further optimizing the ServiceManager database and workflows
These questions might belong on the Microsoft forums, but I am interested to find out what all the bright minds here can come up with, first.
- Has anyone successfully migrated ServiceManager tables (particularly the ECL, RECL, and DiscoverySource tables...) to SQL 2016/2017 in-memory tables, without breaking any functionality? Indexes and constraints can be rebuilt, but since clustered indexes are a no-go, I am curious if re-creating them as hash/noncluster/etc. breaks SCSM.
- Generally speaking, of course, what is a good DOP (Degrees of Parallelism) Threshold for Service Manager? The default for SQL Server is 5, I've seen recommendations (not specific to SCSM) to bump it to between 25 and 50, but it seems to perform best around 10. This seems to be a function of the execution plan for queries wanting to execute with parallelism while maintaining enough "free" cores to process other, simpler queries at the same time, as I understand it. With the number of blocks and long waits I'm seeing on Insert operations, this seems like it would be a factor.
- There are some horrendously poor performing stored procs in the ServiceManager DB, which no amount of CPU, RAM, or I/O investments (~3ms SSD here...) seems to help. e.g.: p_EntityChangeLogSnapshot which gets called on all the save/Commit calls to the SDK. Are there any supported ways to optimize these?
- Are there any ways to limit the performance impact of using the SCOM Alert Connector with a high volume of alert IR's?
- Does anyone have a good formula to use for picking the right batch size and delay with which to override OOB workflows that are struggling to keep up? (i.e.: more instances keep getting created before the previous one finishes due to too many items) I have been experimenting and getting mixed results.