Queries are taking ages and users get more and more frustrated? Leveraging on operations intelligence, we provide insights into query performance analysis as well as methods for identifying and troubleshooting long running queries in the BW system.
SAP BW system can be analysed from various perspectives, but its central KPI is fast reporting capability. Therefore, regular analysis of the query performance should be a part of regular BW maintenance. For the purpose of acquiring operations intelligence we used Datavard tool HeatMapTM developed for real-time usage analysis.
Let’s focus on the most important indicator of query performance: query runtime:
Fig. 1 List of executed queries sorted by average runtime, in a descending order
Average query runtime on an average BW system varies quite significantly form query to query. Fig.1 shows us an example of HeatMapTM output of the longest running queries on the customer’s production system. As we can see, we have here plenty of very long running queries and their negative effect is multiplied by the number of executions.
The adverse consequences of ′bad queries′ are most visible when we look at their overall contribution to the total runtime users spend on reporting. Fig.2 shows us a comparison between the contribution of long running queries to the overall number of query executions and runtime. As we can see, although the long running queries are responsible for “only” 17% of executions, their impact on the overall runtime is 77%.
Fig. 2 Overall impact of long running queries on system performance
Let’s dive a little deeper and have a closer look at the longest running query on the system, the ′Query GRP_MM_Q037_PM1′:
1. Filters: 0CALMONTH; 0MATERIAL__0MATL_TYPE; 0MOVETYPE; 0PLANT; 0PLANT__0BUS_AREA; 0RECORDTP; 0VAL_CLASS
2. Filters: 0CALMONTH; 0MATERIAL; 0MATERIAL__0MATL_TYPE; 0MOVETYPE; 0PLANT; 0PLANT__0BUS_AREA; 0RECORDTP; 0VAL_CLASS
3. Filters: 0CALMONTH; 0MATERIAL; 0MATERIAL__0MATL_TYPE; 0MOVETYPE; 0PLANT; 0PLANT__0BUS_AREA; 0RECORDTP; 0STOCKTYPE; 0VAL_CLASS
With the help of HeatMapTM drilldown functionality, we are able to analyze each query broken down into a set of filters (see footnote no.2). As you can see from fig. 3, we identified three sets of filters being used, where two of them have quite reasonable runtime (in sec.), however there is one long running query variant which is dragging down the average runtime. Looking at the used filters, we identified attribute 0MATERIAL as the one that should be included in the longest running variant to significantly improve its runtime. This way, the customers can focus their attention on the most problematic queries/variants directly and make the biggest benefit, instead of applying more general (indirect) and perhaps otherwise unnecessary measures of performance tuning, such as secondary indexes, cube compression etc.
From our experience, every query performance analysis should focus mainly on frequently executed queries, because they contribute the greatest deal to the overall performance as well as to the ′user experience′.