Findings and Performance Analysis

The goal of this section is to focus on the entire fleet of databases within your environment over a larger period of time, compared to other diagnostics tools that focus on immediate events. This tool tracks historical data, assisting in creating a more proactive action plan through custom analysis.

There are five main use cases that help you identify problem areas within your database fleet, these are viewed in the main Performance Summary.

Figure 4-3 SQL Analytics Performance Summary Dashboard

• Degrading SQLs:

  SQL Statements whose performance has degraded considerably over time.

• Variant SQLs:

  SQL Statements that have a significant variance in its executions times

• Inefficient SQLs:

  SQL Statements that are not optimized and/or have high wait times of I/O usage and high rates CPU utilization.

• SQLs with Plan Changes

  SQL Statements that are utilized by multiple plans with different results.

• Tops SQLs by CPU and by I/O

  A ranking of all existing SQL Statements by CPU usage or by I/O utilization, ranking from highest to smallest usage percentage.

To begin your custom performance analysis, select the use case of interest by clicking on the impacted SQL statement number below each use case (Degrading, Variant, Inefficient, Plan Changes). This will take you to the heat map for each use case. Here you can analyze the performance of individual SQL Statements. The heat map is arranged in a friendly manner where you can easily view the SQL statements by database, size (represented by different sized squares) and rate of change (represented by a color key). When viewing the heat map click on a database of your choice to review; next, click on the matching SQL ID displayed below the heat map. This will load the data for that particular database; based on the use case selected.

Figure 4-4 SQL Analytics Heat Map

Once the SQL Statement has been selected you can review it's SQL Text and database location as well as it's key performance metrics under: Performance Summary and it's Execution Plan Insights

• Performance Summary: This chart helps you view the trend in the SQL Statement performance for the current time period, based on the following measurement criteria:
  • The Average Response time in seconds.
  • The change percentage in response time. A negative value indicates an increase in the response time.
  • The number of SQL Statements that were executed per hour.
  • The variability value of the SQL Statement, which indicates the extent of variance in the SQL Statement's performance in the current time period.
  • The time range within which the maximum executions of the SQL Statement occurred.
  • The inefficiency percentage which is based on the idle wait time or non-productive time of the SQL Statement.
• Execution Plan Insights: This section of the page is available only if the SQL Statement uses more than one execution plan. It provides the following information:
  • Plans Used
  • The best and worst performing plans
  • Plans with the most CPU and I/O usage
  • The most executed SQL Plan

Note:

The following screen descriptions are regardless of use case scenario. All use cases use the same second and third level screens.

In the Home tab of the page, you can view the representation of the performance data in the form of different charts.

• Activity:
  The Activity chart displays the session activity of the SQL Statement, for the current time period. It also shows how the SQL Statement activity is categorized into different wait classes indicated by the color, as described in the legend on the chart.

  Figure 4-5 Session Activity Chart for a SQL Statement

  
  
  Description of "Figure 4-5 Session Activity Chart for a SQL Statement"
  

  For example, this chart indicates that the SQL Statement spent a significant portion of its idle time in the ‘Other Wait’ category and some portion in the ‘CPU’ and ‘I/O’ categories between 23 and 28 March 2018.

• Response Time Distribution: This bar chart plots the number of executions of the SQL Statement for a given response time range.

  Figure 4-6 Response Time Distribution Chart

  
  
  Description of "Figure 4-6 Response Time Distribution Chart"
  

  For example, this chart indicates that the maximum number of SQL executions occurred within three seconds. With this information, you can check on the conditions that favored a faster response time and analyze the reasons why other SQL executions had a relatively slow response time.

• Response Time Breakdown: The chart displays information by plan or by database and helps you identify where the SQL Statement spent most of its time by breaking down the average response time into different wait classes such as CPU Time, I/O time, and so on.

  Figure 4-7 Response Time Breakdown Chart

  
  
  Description of "Figure 4-7 Response Time Breakdown Chart"
  

  For example, this chart indicates that the SQL Statement used a single execution plan and a significant number of its executions spent a high time in the ‘Other Wait Time’ category and hence, has resulted in a slow response time.

In the Compare tab of the page, you can view the performance trend by comparing two or more plans or databases based on the following parameters:

• Average Response Time
• Executions Per Hour
• I/O Time
• CPU Time

Figure 4-8 Compare Performance Trends

In the Plan tab of the page, you can view a drill down version of any selected SQL execution plan performance.

Note:

To enable the Plan tab, first you must enable SQL Text and SQL Plan data collection, for more information please see: Disable and Re-Enable SQL Execution Plan Collections.

The Execution Plan is presented in a top-down view by operation, you can further see the Operation Cost, Estimated Rows and Estimated bytes of each operation; allowing you to locate problematic operations. The higher the operation cost, the more problematic the operation can be.

Figure 4-9 Execution Plan Breakdown

In this example we have highlighted in red a MERGE JOIN PARTITION OUTER command high in Cost, Rows and Bytes that is causing issues in SQL Plan 8345741210.