NSDA is a modern, browser-based tool used to collect, analyze, and manage workloads from NonStop SQL. The graphical user interface displays SQL workload information and system resource usage in real-time and retains key performance metrics and query plans in its repository for later analysis. NSDA is an invaluable tool for customers using non-embedded SQL applications with complex query workloads.
The release of NSDA 1.4 brings a number of new features, including query auto-cancel, user interface enhancements to aid workload analysis, simplified installation using containers, and NSJ11 support.
Included in this release is a new tutorial (release as a manual[1]) which explains how to include graphs to spice up the look of customer-developed NSDA reports.
In addition to providing real-time data and valuable insights about SQL workloads, NSDA includes a Query Whiteboard page for generating reports from data contained in the NSDA repository. Customers can execute ad-hoc queries of their choosing or select from a library of pre-existing reports. Most of the built-in NSDA reports produce a graph of the results in addition to the report details. These graphs greatly enhance reports by concisely depicting trends, relationships, and quantities using colorful graphics, proving – just as that common adage states, “A picture is worth a thousand words”.
This article is a short introduction explaining how to add graphs to your own NSDA reports. You will find a comprehensive explanation in the manual indicated above, which guides you step-by-step through the process and includes examples of the different types of graphs and options. Once you understand the basics, you will appreciate how simple and easy it is to add graphs to reports.
Conveniently, not only does the graphing tutorial contain numerous examples, those same examples are available through the NSDA Query Whiteboard. To locate the examples, go to the Query Whiteboard page, and select “Open” from the “Actions” button on the top right of the page. The window below will appear, with a list of over 30 example reports that use graphs:
Hovering over the name of a report produces a description and shows where to find more information in the tutorial:
Open the example report to view the SQL query and graph definition (which is highlighted in gray):
The example shows the basic format of the graph definition. There is a section to define the variables to plot (in the ajaxParam section), the name of the label and the type of graph to display (a “points” graph, in the data section), and various formatting options (in the options section). That’s it.
Running the report produces the graph as well as the report results, as shown below. This is a very simple “points” graph, but it illustrates the basic concepts.
To change the report type to a line graph with points, simply change the graph definition to add the highlighted line, as follows:
“data” : [ { “label” : “Query Count”
“lines” : { “show”: true }
, “points”: { “show”: true }
Re-run the report to view the new results:
Similarly, you can change your graph to any of the supported types, including points, lines, bars, stacked bars, as well as pie and circle graphs. These give you plenty of options to produce the graphical effects that you need to bring better clarity to your reports.
Below is an example of a multi-graph report, which includes three graphs within the multi-graph definition to depict various dimensions of SQL workload performance:
The top bar graph shows SQL performance results for three separate datasources (as indicated by the x-axis labels), with the latency on the left vertical axis and counts on the right. There are five plotted metrics with labels to the right of the graph. The latency-oriented metrics include average and maximum SQL elapsed time while the counts-oriented metrics include average rows accessed and used, as well as the average disk I/Os, per SQL statement. The specific metrics used in your reports depend entirely on what you wish to analyze.
The second report (bottom left) breaks down the relative CPU consumption by SQL user, while the third report (bottom right) depicts the relative CPU consumption by the queries executed within each datasource.
The multi-graph is composed of three separate graphs, which makes it convenient for incrementally constructing the final chart. You simply build one graph at a time, and when satisfied, combine them together within the multi-graph definition. You can easily appreciate the powerful impact of multi-graphs – with one glance at the results, you can glean a great deal of information more quickly and simply than by parsing through multiple text-based reports.
The graph definition also provides a convenient option to include supplemental information using “tool tips”, which is where information you provide will appear automatically when you hover the mouse pointer over a data point (or bar, or pie-slice, etc.).
When enabled, the data label and value from the underlying graph element will appear when hovering, as depicted below. The example shows how the tooltip metric includes the raw value of CPU time for the given user:
You can supplement the data in the tooltip by providing additional text and data values. In the example below, the query count metric is included in the tooltip display, in addition to the other two metrics provided by default. This capability gives you yet another option for easily customizing reports:
The graph definition provides many options to control the size, shape, color, and other characteristics of the final graph. You can specify and change any option by simply including the corresponding attribute and value in the definition of the object.
Once you have completed the construction of your graph, simply save your NSDA report in the repository. The graph instructions become part of the report. Whenever you execute the report, your graph will automatically display the results, and will dynamically change as the report results change.
Additionally, if there are graph displays you favor from any of the built-in NSDA reports, you can apply their style or form to your own reports. Simply open the NSDA report and view or copy the graph instructions into your report, and modify them as necessary to suit your own needs.
With little effort, you can easily add professional-looking graphics to any data report generated from the NSDA repository. This capability is part of NSDA and is there to use freely, without the hassle of exporting results into other (and sometimes costly) graphing packages.
For a comprehensive tutorial on all the ways you can graph NSDA reports, be sure to check out the new NSDA 1.4 manual, noted above.
Be the first to comment