SCOM: SQL Dashboards workaround for slow performance

Microsoft has finally officially recommended a workaround that some of us have been using for some time to keep the SQL dashboards in a usable state.

Dashboards may work slowly if used rarely

Issue: When used rarely or after a long break, the dashboards may work rather slowly due to large amounts of the collected data to be processed; especially, it is related to large environments (2000+ objects).

Resolution: Below is a “warming up” script, which may be used to create an SQL job to run on some schedule. Before scheduling it as an SQL job, please test how long these queries will be executing (if you will schedule it to run too often or execution time is too long, that may kill the performance). If you have dashboards with thousands of objects to load, then time to load the content will be 10+ seconds anyway. It was tested with 600 000 objects, and the dashboard loading time was 1-2 minutes.

USE [OperationsManagerDW]

EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateLastValues]

EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateHierarchy]

It is also worth noting that the following versions of SQL Server Management Pack are considered as deprecated and suspended:

  • 1.314.35
  • 1.400.0
  • 3.173.0
  • 3.173.1
  • 4.0.0
  • 4.1.0
  • 5.1.0
  • 5.4.0
  • 6.0.0
  • 6.2.0
  • 6.3.0

2 thoughts on “SCOM: SQL Dashboards workaround for slow performance

  1. Dmitry Kalinin

    Hi!

    I have to add that we’ve incorporated this script into the Dashboards management pack.
    It is available since version 6.6.7.6 (released in March, 2016):

    If you run into long loading times every morning (Rule of the thumb: >1 minute to open dashboard) you should enable and set overrides for the special rule in Dashboard (visualization library) MP.
    It is named “DW data early aggregation”.

    It runs this SQL script to update and aggregate data in advance:
    USE [OperationsManagerDW]
    EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateLastValues]
    EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateHierarchy]

    I’d suggest to start with 4h interval, but you can try tuning it to 2h -> 1h if the waiting time will still be more than 1 min.

    Thank you,
    Dmitry Kalinin.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.