UMP (USM) graphs are taking a long time to display
search cancel

UMP (USM) graphs are taking a long time to display

book

Article ID: 187797

calendar_today

Updated On:

Products

CA Unified Infrastructure Management SaaS (Nimsoft / UIM) DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

This article covers what steps to take when the rendering of graphs/charts in USM is very slow. Need to know how to optimize USM performance (for speeding up display of graphs/charts/data).

Environment

- Large Scale environment with 11,000 servers
- Many large tables with hundreds of millions of rows
- High fragmentation for many table indexes (avg. of 99.98%)
- Over 4TB Database
- MS SQL Server 2012 Enterprise
- 35 days worth of raw data, 180 of historic data
- DB Partitioning enabled
- UMP 8.5.1
- USM 8.5.2_HF8

Cause

- slowness with Reports/Graphs in UMP/USM

Resolution

This is an overview of the procedure we used to identify where nonclustered indexes could be added to improve USM performance (slow graph rendering due to fetch of data against very large tables). This procedure may differ slightly depending on the root cause of any slow performance in UMP/USM.
 

Note that USM graph display was primarily (adversely) affected by months of raw data that was previously stored, as well as very large, highly fragmented RN tables that were still present, and some missing indexes that were needed.

Symptoms:

  • USM Graph rendering would take an unreasonable amount of time and the window would remain empty, displaying “Retrieving data…” for long periods of time or graphs would gradually appear and the overall time to produce the graphs would take several minutes.
  • PRD responsiveness and charts would take a very long time to render, especially when changing the time range of the data displayed, e.g., from 1 or 7 days to 30 days.
  • Graph popup views (standalone.jsp) would also take a long time, e.g., ~20 seconds or more
  • Custom Reports containing a lot of data would take up to 15 minutes or more to run

 

Steps Taken to analyze query performance and the potential need for additional indexes

1. Enable USM debug mode

How can I enable DEBUG mode for UMP, USM, ListViewer or Performance Report displays?
https://knowledge.broadcom.com/external/article?articleId=33962

2. Run through the steps you/the end user took to display/render USM graphs/views

3. Hit the portal log url to expose the underlying queries and calls, or examine them in the debug window but take note of the timing and save the slowest queries via copy and paste into Notepad.

     http://<waspserver>/jsp/diag.jsp?file=portal.log

...then examine any/all queries that take more than 1+ seconds, especially those that took several seconds. Keep track of the steps taken and timings in a spreadsheet if slowness issues occur across multiple areas of UMP/USM.

 4. Run each of those queries manually within MS SQL Server Studio but first click on the icon to "Include the Actual Execution Plan" and "Client Statistics" and take note of how long the query took to run (to return the first row of data), in Studio.

5. Examine the “Actual Execution plan” under its Tab and see if SQL Server suggests adding a NONCLUSTERED index, (which will be displayed in green font).

6. Alter the original suggestion and give the nonclustered index a name for the missing index, remove “sysname,” and also remove any caret <> symbols. Be careful not to create the INDEX with any bogus characters otherwise you will have to delete it later.

-- Note that you can choose to examine the execution plan, then rt-click on the index being suggested by SQL Server (green font) and View Missing Index Details to copy it out and save it.

Here is an example of a properly formatted index creation statement:

CREATE NONCLUSTERED INDEX [Cust_S_QOS_DATA_02]

ON [dbo].[S_QOS_DATA] ([qos],[target])

INCLUDE ([table_id],[qos_def_id],[source],[r_table])

GO

 

Other examples…

Note that its very important to understand the advantages and disadvantages of different types of indexes and INCLUDES and you can find plenty of information on the web about CLUSTERED VERSUS NONCLUSTERED INDEXES. For instance creating too many non-clustered indexes for a table can slow other operations such as UPDATE, INSERT, DELETE, etc. Its best to review the potential impacts first and consult with a DBA.

https://www.mssqltips.com/sqlservertip/3041/when-sql-server-nonclustered-indexes-are-faster-than-clustered-indexes/

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

7. After creating the new custom NONCLUSTERED index, rerun the original query that was slow to determine if the graph rendering/query performance was improved. Check the time it took to return the first row of data in Studio and record it. Test using the same exact steps you took in USM as well to see how fast the graph/data displays.

In one customer scenario, we considered any hosts graph rendering over 10 seconds to be too long. Some of the graphs/reports were taking several minutes to display. Afterwards they only take a few seconds. We also saw great improvement when using PRD and changing graphing intervals from 1 or 7 days to 30 days. It used to take several minutes. Larger (extensive) end-user reports that took up to 15 minutes to run were reduced to seconds or only a few minutes. 

Graphs took ~2 seconds for any host after completing this procedure:

Short note on NONCLUSTERED INDEXES:

A nonclustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple onclustered indexes on a table or indexed view. Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index. Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. For example, to query the Person.Person table for people that have a certain last name, the query optimizer might use the nonclustered index IX_Person_LastName_FirstName_MiddleName; this has LastName as one of its key columns. The query optimizer can quickly find all entries in the index that match the specified LastName. Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

Note also that very high, e.g., 99.98% fragmentation of RN tables was NOT a contributing factor that affected query performance/graph rendering as long as the UIM DB is partitioned.

I recommend using a spreadsheet to track/measure UMP (USM) performance over time so you can more accurately compare the before/after results.

The following tables should be defragmented on a daily basis. Consult with your DBA on setting up a job at the end of the business day/off-hours:
 
Here is a list of tables that you should run a daily index defrag job upon:
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD;
ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD;
ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD;
ALTER INDEX ALL ON S_QOS_DATA REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;
ALTER INDEX ALL ON NAS_ALARMS REBUILD;
 
SQL Server Memory
Ask your DBA to check/keep an eye on MS SQL Server memory pressure over time, e.g., 30 days, to see if you need to add some more memory for ‘breathing room.’

--Here are 5 create nonclustered index statements we used for a customer (note these are just provided as examples and what indexes you add for a given Customer's UIM DB/tables may differ based on your query analysis/testing).
USE[CA_UIM]
CREATENONCLUSTERED INDEX [Cust_S_QOS_DATA_01]
ON[dbo].[S_QOS_DATA]([ci_metric_id])
INCLUDE ([target])
GO
 
USE[CA_UIM]
CREATENONCLUSTERED INDEX [Cust_CM_CONFIGURATION_ITEM_METRIC_01]
ON[dbo].[CM_CONFIGURATION_ITEM_METRIC]([ci_metric_type])
INCLUDE ([ci_metric_id])
GO
 
USE[CA_UIM]
CREATENONCLUSTERED INDEX [Cust_S_QOS_DATA_02]
ON[dbo].[S_QOS_DATA]([qos],[target])
INCLUDE ([table_id],[qos_def_id],[source],[r_table])
GO
 
USE[CA_UIM]
CREATENONCLUSTERED INDEX [Cust_BN_QOS_DATA_0061_01]
ON[dbo].[BN_QOS_DATA_0061]([table_id],[starttime],[stoptime])
INCLUDE ([samplevalue])
GO
 
USE[CA_UIM]
CREATENONCLUSTERED INDEX [Cust_RN_QOS_DATA_0061_01]
ON[dbo].[RN_QOS_DATA_0061]([table_id],[sampletime],[samplevalue])
GO

Additional Information

Legacy Techdoc: TEC1733974
Powered by Wolken Software