Quick note: Speeding up OpsMgr dashboards based on the SQL visualization library

  • Article

Summary for the impatient:
With SQL MP version 6.6.7.6 we provide an OpsMgr rule “DW data early aggregation” that can be enabled to pre-load data thus speeding up dashboards based on the SQL visualization library.

As most of you guys know, we introduced new dashboard capabilities with the SQL visualization library in SQL MP Version 6.6.6.0 (see e.g. https://blogs.technet.microsoft.com/dirkbri/2015/07/06/new-scom-dashboard-gems-available-with-sql-mp-6-6-0-0/).
The beauty of this library is, that you can use it for creating your own, powerful dashboards that do not need to be SQL related. It could be an AD Domain Dashboard or an Exchange Dashboard, based on any discovered OpsMgr object you need.

Dashboards based on this library pull their data out of the OpsMgr DWH and not from the OpsMgr DB.
When you create such dashboards and include a lot of data (hundreds or even thousands of objects) and haven’t accessed the dashboard for a while, it can happen, that the dashboard take a long time (up to several minutes) to load or in a worst case the dashboard even times out:

A well-known workaround to speed up the dashboard loading is to execute two stored procedures in the OpsMgr DWH that will "warm-up" or "pre-load" data needed by the dashboards:

 USE [OperationsManagerDW]
EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateLastValues]
EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateHierarchy]

And several bloggers recommend scheduling a SQL agent job that executes these SPROCs on a regular basis.

BUT:
There is a better (and almost unknown) way!
Introduced in version 6.6.7.6 (released in March 2016), we provide an OpsMgr rule “DW data early aggregation” that executes these SPROCs. The rule is disabled by default and can be enabled and scheduled on your requirements:

I suggest to start with a 4h interval, but you can try tuning it to 2h -> 1h if the waiting time will still be more than 1 min.
When you use intervals < 4h please have a close look at your OpsMgr DWH resource usage!