UMP custom dashboard SQL queries are timing out

Document ID : KB000076482
Last Modified Date : 04/04/2018
Show Technical Document Details
Issue:
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?
Environment:
UIM 8.5.1
MS SQL 
Cause:
The database queries may not be optimized or they may be pulling back too much data.
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