UMP custom dashboard SQL queries are timing out

Products

• DX Infrastructure Management
• NIMSOFT PROBES

Issue/Introduction

We have created custom dashboards with our own custom SQL queries in UMP - SLM.  At times, these will time out or not refresh.
Is there anything we can do other than increase the memory for wasp?

Cause

The database queries may not be optimized or they may be pulling back too much data.

Environment

UIM 8.5.1
MS SQL 

Resolution

Consider testing the SQL inside a MS SQL tuning tool like MS SQL Tuning Adviser.
Ensure your query is not performing a table scan and is using columns that are indexed.
Pay attention to where the greatest cost is for the query and if Tuning Adviser has any suggestions for the query or for a a new index.

Also, consider limiting the number of rows that the query can return.

In environments with extensive monitoring, database tables can contain a large number of rows. To maintain UMP response time, SLM SQL queries in UMP version 6.0 and later are now limited to 1000 rows and columns to 1024 bytes.

To override the limits, edit the <slm> section of wasp.cfg, add the following parameters, and restart the wasp probe:

• max_rows The maximum number of rows returned
• max_field The maximum length of any column

For example

   <slm>

      reloadable = true

      cross_context = true

      load_on_startup = true

      unpack_war = true

      path = /slm

      max_rows = 10      

      max_field = 2048

   </slm>

 

Additional Information

Send SQL Queries to the UIM Database

https://docops.ca.com/ca-unified-infrastructure-management/8-5-1/en/using-ump/the-slm-portlet/send-sql-queries-to-the-uim-database