SQL Monitor Unleashed
Earlier this year our team at SAP has released a next-generation performance analysis tool – the SQL Monitor. This utility can be used – not only but especially – for the purpose of optimizing custom code for SAP HANA and is available starting with Netweaver 7.0.
“So what?”, you might think. “We already have enough trace and monitor tools which provide us with all the info needed to optimize our code.” You’re really sure about that? What if you wanted to answer questions like:
- Which of the business processes (transactions, reports, …) in my productive system consume the most database time?
- What is the SQL profile of my main business processes?
- What are the most expensive SQL statements in my system and by which business processes are they triggered?
None of the standard performance analysis tools you might know is capable of answering any of these questions in a satisfying way. Let’s take a moment to figure out why.
Trace tools like ST05 (SQL Trace) or SAT (ABAP Runtime Analysis), on the one hand, are designed to trace a single process but not your entire system. Hence, even if activated only for a short period of time, the trace files would become way too big and the performance overhead would be unacceptable in a productive system.
Monitoring tools such as STAD (Business Transaction Analysis) and ST03 (Workload Monitor), on the other hand, run system-wide and provide aggregated performance data on the process level. However, they don’t allow you to drill down in the data so there is no way to get the SQL profile of a process. Other monitoring utilities like ST04 (DB Performance Monitor) supply you with detailed information about every executed SQL statement but cannot provide a link to the driving business processes.
So how can you answer the questions stated above? This is where the new SQL Monitor kicks in by providing you with system-wide aggregated runtime data for each and every database access. You may think of it as an aggregated SQL trace that runs permanently and without user restriction. On top of that, the SQL Monitor also establishes a connection between the SQL statement and the driving business process. To be more precise, this tool not only provides you with the source code position of every executed SQL statement but also with the request’s entry point that is, for instance, the transaction code.
|Background Knowledge: Request Entry Point|
In the context of the SQL Monitor the term “request entry point” is used to describe one of two things:
Moreover, the SQL Monitor offers a number of ways to aggregate the recorded data and also allows you to drill down into the details. For instance, you may group the monitoring records by the request entry point which yields an overview of the – in terms of database performance – most expensive business processes in your system (question 1). For each entry point you can then drill down into the associated SQL profile that means the list of all SQL statements that were triggered by this entry point (question 2). So if, for example, you wanted to optimize the database performance of a specific transaction or report, you could start by inspecting the top SQL statements in the corresponding SQL profile.
Similarly, you can also go the other way around by choosing to aggregate the data by the source code position which gives you an overview of the most expensive SQL statements in your system. For each of these statements you may then drill down into the list of request entry points that caused the statement to be executed (question 3). This list embraces transactions, reports and other entry points that would directly benefit from a local optimization of the SQL statement (e.g. elimination of an empty FOR ALL ENTRIES table). Looking at it from another angle, the list can also show you which entry points should be considered for a non-local optimization of the statement (e.g. SELECT statement in an external loop).
No matter what aggregation strategy you choose, the SQL Monitor provides you with an extensive set of runtime measures such as the number of executions, the total and mean execution time or the minimum and maximum number of accessed database records. Despite the comprehensive data range the SQL Monitor’s own performance footprint is extremely small and so the tool can easily be activated in a productive system.
The very best part, however, is: The new SQL Monitor comes with your Netweaver installation – just like that. Details about the availability on different support package levels can be found in SCN Document 47444 which also includes a very valuable set of generic best practices on how to analyze the monitoring data.
I hope by now you’re all fired up to see the new SQL Monitor in action. If so, I’m pleased to announce that this blog post marks the beginning of a series of posts in which I will walk you through a multitude of specific examples which will illustrate how to control the SQL Monitor and how to make the most of the recorded data. So if you’re up for a deep dive into next-generation performance monitoring, make sure to watch out for my next blog post on ABAP for SAP HANA.
Looking for the next post in the series? There you go: Hands-On with the SQL Monitor Part 1: Top Requests, SQL Profile and Nested Loops