Thank you to Stephen Leuthold of BlogMyNog.com and HASMUG.com for sharing this excellent information!
Situation:
Massive I/O waits seen on the OperationsManagerDW resulting in major performance problems. It is unknown what the root cause of the Situation was.
Reference:
The Microsoft baseline I/O ranges for OLTP systems are as follows:
1-5ms for Log files
4-20ms for Data on OLTP Systems (ideally 10ms or less)
30ms or less on DSS (Decision Support System) Systems
Solution:
- Backed up the OperationsManagerDB database
- Backed up the OperationsManagerDW database
- Restarted the Server hosting the DW
- Stopped all SCOM services connecting to the OperationsManagerDW database
- Renamed table Alert.AlertStage to Alert.AlertStage_Backup
- Recreated table Alert.AlertStage
- Restarted all SCOM services connecting to the OperationsManagerDW database
- Allowed normal SCOM processes to run as scheduled without manually triggering
Performance Graphs:
During 70.88 ms/IO
After the Solution was put into place 15.38 ms/IO
