This is an overview of the procedure we used to identify where non-clustered indexes could be added to improve USM performance (slwo graph rendering due to fetch of data against 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.
- 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 (refer to the Article if necessary at https://support.ca.com/us/knowledge-base-articles.TEC000003890.html )
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.
...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])
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 nonclustered 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.
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 nonclustered 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.