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
How can I enable DEBUG mode for UMP, USM, ListViewer or Performance Report displays?
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 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.
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: