Performance issues loading data into the Operator Console Metric Viewer

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

When loading the Metric Viewer in Operator Console with many data points, there are performance issues.  The graphs load very slowly, or not at all.

Environment

• UIM 23.4 (up to CU6)
• Operator Console 23.4 (up to CU6)
• Backend database: MS SQL Server (any supported version)

Cause

Queries in the stored procedure used to prepare the data points in the graphs need to be optimized.

 

NOTE:  The Metric Viewer is not expected be able to load if there are more than 1 million data points in the view.  The number of data points in the view can be determined by taking into account the number of devices, metrics, collection interval, and time frame. 

Resolution

The stored procedure used to create the metric viewer data has had the legacy row-by-row processing updated with high-performance set-based operations (Recursive CTEs and Window Functions) to significantly reduce execution time on large datasets.

Also, new granular logging has been integrated into the procedure. This allows us to track the duration of each internal logic block (interpolation, aggregation, etc.) in real-time.

To update the stored procedure, do the following:

1. Make sure that you have a recent backup of your MSSQL database
2. Run the attached query in your MSSQL Server.  Look for the successful completion of the command
   
3. No restarts are needed.  The Metric Viewer reports should be executed with higher performance once the stored procedure is updated in step 2.

 

Additional Information

• These optimizations are slated for inclusion in future releases (post CU6).
• To monitor the performance of the various execution flows, you can run the following query:

  SQL
  SELECT * FROM tbnlogging ORDER BY starttime DESC;