5/26/2016 11:22:26 AM
Which data is displayed in SQL Monitor and how can I read it?
Example screenshot (aggregation = none, order by executions, no filter). All not self explaining columns have a F1 help to provide the necessary information.
Here is a short summary of the most interesting and more complex fields:
|Mean Time[ms]||0,322 ms||Average (aggregated) execution time of the SQL statement in milliseconds|
|Mean Records||0,419||Average number of rows selected or changed by the SQL statement|
|Table Names||V_PTRV_APPR,PTRV_SHDR||DB tables used during execution of the SQL statement. For joins, sub queries or dynamic access you may see several DB tables|
|SQL Operation||Update (Open SQL)||SQL Operation kind - Here you can separate Read, Write access and Open, Native SQL|
|Int. Sessions||64.510||Number of different internal sessions (roll areas) where this SQL statement was executed. For a simple report (request type â€œSubmit reportâ€) the number of internal sessions equals the number of report executions.|
|Executions/Session||587,917||Average execution counter of the SQL statement per session. In this example the SQL was executed in average 588 times per internal session. High execution/session values are often an indication for a nested select.|
Hint: All time values are displayed in milliseconds. All numbers are displayed with 3 decimal places. (Don't overlook the comma before the last 3 digits - e.g. 2.220,570 ms are ~ 2.201 milliseconds)
What is the best strategy to analyze SQL Monitor data?
In principal, we recommend to focus on total time of the SQL statements to optimize the performance of the business processes.
Whereas you would concentrate additionally on the top SQL statements ranked by executions before the SAP HANA migration in order to find the often executed SQL statements.
|Step 1: Process view Get an overview of your main SQL driven requests||In the selection screen of transaction SQLMD: Choose aggregation by request - no filter (and no max nr of records limitation!). Now you have the complete list of requests, which did run SQL statements in your system! Rank this list by â€œExecutionsâ€ to get the requests which run most of the SQL statements. Rank this list by â€œTotal timeâ€ to get the processes with the highest total SQL execution time. Rank this list by â€œTotal Recordsâ€ to get the processes with the highest total fetch/change row count. Now check the top 10 of these rankings (you may additionally filter by business criticality of the process) and memorize the aggregated value of the measurement value you are interested in (e.g. Total time). Get the SQL profile of the process by drilling down (link in "Records" column) and rank again by the column of interest (e.g. Total Time). Analyze the top entries which contribute most to the total value you found on process level. Experience shows that the first top 5 SQL statements in the SQL profile contribute up to 80% to the measurement value of interest.|
|Step 2: System view Get the most often and most expensive SQL statements (limited to customer code)||In the selection screen of transaction SQLMD: Choose aggregation by source position. No filter (and no max nr of records limitation!). Now you have the complete list of ABAP SQL statements which did run in your system! Sort by â€œTotal timeâ€ or â€œExecutionsâ€. Check the min/max/standard deviation values of these top entries to figure out if e.g. only one SQL execution was extremely slow because it was hanging at a lock or if almost all executions are slow... You can drill down using the "Records" column to see which processes are driving these top SQL statements. For top execution you can use the drill down to focus on the top entries which have high numbers in column "Executions/session" since these are most often SQL statements in loops which have optimization potential.|
|Step 3: System view Get the SQL statements reading/writing most of the data||In the selection screen of transaction SQLMD: Choose aggregation by source position. No filter (and no max nr of records limitation!). Sort by â€œTotal Recordsâ€. Check the min/max values for records. If the maximum and minimum is always the same (and equals the total number of lines stored in the DB table) then this might be a SQL statement without a where clause. If you see a big difference between max and min value then this might be a SQL statement which encounters an empty FOR ALL ENTRIES table or empty RANGE tables from time to time...|
|Step 4: Process view Analyze dedicated processes which you want to optimize||In the selection screen of transaction SQLMD: Choose aggregation by request. Filter by Request Type and Request entry point (e.g. transaction VA01). Memorize the total values of the fields you are interested in (e.g. Total time) - Drill down and rank by the fields of interest -> see step 1.|
This approach will lead to a work list of tuning proposals. After importing all the SQL/ABAP corrections we recommend to start SQL Monitoring from scratch to check if the corrections were beneficial (How did the SQL profile of your process change?).
But remember: Create a SQL Monitor snapshot before deleting SQL Monitor data!
What are the steps to switch off SQL Monitor and to get rid of all the data collected?
The following table describes how to switch off the SQL Monitor on all servers and delete the SQL Monitor data.
|Steps||SQLM Variant / Release||Description|
|Switch off SQL Monitor||NW/ST-PI||In SQL Monitor (transaction SQLM or /SDF/ZQLM for ST-PI version) use menu SQL Monitor -> Deactivate|
|Verify server activation status||NW /ST-PI||Run transaction SQLM (ST-PI: /SDF/ZQLM) and choose SQL Monitor -> Server State -> Check Server State. As a result you will see a server list with information about the current SQL Monitor activation state and if the actual state matches the expected state. If there is an inconsistency then you can try to correct this using SQL Monitor -> Server State -> Ensure Server State. Or you directly logon to the inconsistent server and deactivate SQL Monitor for this server again.|
|Delete SQL Monitor data||NW /ST-PI||In SQL Monitor (transaction SQLM or /SDF/ZQLM for ST-PI version) use menu SQL Monitor -> Data-> Delete|
SQL Performance Monitoring-SAP SQLM Part-2
If you like this blog, please share (Facebook/LinkedIn/Google+) to click below links so it will reach to others.