5/25/2016 3:18:27 PM

SQL Monitor will run in production... What is the performance overhead and average data volume?

The SQL Monitor data collection is highly optimized to ensure that it can run in parallel to production. From a technical perspective, the SQL Monitor relies on a memory based data collection process.

If e.g. transaction VA01 executes a SELECT statement then SQL Monitor kernel functionality stores the measured performance data in the local memory of this internal session. If the same SELECT statement is executed in this session again then there is no new data record but the data of the second execution is aggregated into the same data record created before.

If this session is rolled out or closed then the collected data is transferred and aggregated into shared memory of the server. Only if the assigned shared memory area is totally filled up a file is used to handle this memory overflow situation. Finally, the shared memory data is transferred to the database asynchronously by a batch job.

Since the data collection is dominated by fast memory accesses the overhead for the running processes is very low (in average < 3 %.)

The SQL Monitor is already active in several big productive systems without any noticeable performance impact for the running business processes.

Separated from the running business processes, SQL Monitor batch jobs collect periodically the data from shared memory to the dedicated SQL Monitor database tables.

The amount of SQL Monitor entries depends on the traffic on the system and the diversity of processes executing SQL statements. First experience shows that after one day (24h) you see between 30.000 and 400.000 SQL Monitor entries. The number SQL Monitor entries then quickly reach a kind of saturation so that after 2 weeks we reach between 100.000 and some million entries.

Important: The processes in each and every productive system are different and e.g. masses of requests with GUID like URLs would prevent aggregation/saturation and may create a lot of SQL Monitor entries. This may lead to a long runtime of the SQL Monitor data replication batch job. Therefore we recommend monitoring the number of created SQL Monitor entries and the runtime of SQL Monitor jobs (see page 7) during the first hours/first day. If more than 2 Million SQL Monitor entries (visible on the start screen of SQL Monitor) were created, we recommend switching off SQL Monitor.

In general we do not recommend measurement periods longer than 2 weeks since the quality of the SQL Monitor data decreases if the data aggregation covers many code changes, new business phases etc.

Therefore, after one or two weeks, the SQL Monitor shall be stopped, reset and then restarted.

What are the steps to switch on SQL Monitor in my productive system?
In the following table you find a step by step description on how to implement and switch on the SQL Monitor in your productive system:
Step SQLM Variant / Release Description
Software Deployment Implement the Kernel version, OSS notes, NW SP/ST-PI add on as described in OSS note 1885926
Attach Authorizations to SQL Monitor user NW Separate user who administrate the SQL Monitor and user who only display SQL Monitor data Standard authorization object for trace tools (ST05 etc...): authority object S_ADMI_FCD Dedicated new authorization values for SQL Monitor. SQMA (administrate SQL Monitor: switch on/off etc...) SQMD (display SQLM Data)
Authorization for transaction code SQLM (administrate SQL Monitor) Authorization for transaction code SQLMD (display SQL Monitor data)
Attach Authorizations to SQL Monitor users ST-PI Standard transaction for trace tools (ST05 etc...): authority object S_ADMI_FCD with values: ST0M: Authorization to change trace switches ST0R: Authorization to analyze traces
Authorization for transaction code /SDF/ZQLM (administrate SQL Monitor) Authorization for transaction code /SDF/ZQLMD (display SQL Monitor data)
SQL Monitor: Main switch profile parameter = on NW / ST-PI Display profile parameter abap/sqlm/main_switch in transaction code RZ11. The value of the profile parameter shall be on. (= default) You can use this profile parameter in an "emergency" situation to switch off the SQL Monitor.
Switch on SQL Monitor and define an expiry date NW /ST-PI Run transaction SQLM (ST-PI: /SDF/ZQLM) Standard is to switch on SQL Monitor on all servers to collect all running SQL statements in the system: Choose: SQL Monitor->Activate->all servers
If you want to switch on SQL Monitor only on dedicated servers then choose SQL Monitor->Activate->Selected servers and choose the servers in the provided server list. You can use the same functionality to change the server activation at any time.
In both scenarios you are prompted for the SQL Monitor activation expiry date - meaning when the SQL Monitor shall be switched off automatically. The default setting is to switch off SQL Monitor after 7 days. You may run SQL Monitor for one day, 1 week or 2 weeks. Longer periods are not recommended since the data is aggregated over the complete period and the value/significance of the data may decrease because of code changes, new processes etc. After activation you can always change the activation expiry date using SQL Monitor->Maintain Deactivation Schedule.
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 actual SQL Monitor activation state and whether the actual state matches the expected state. SAPPractical SQL Performance Monitoring
If there is an inconsistency then you can try to correct this using SQL Monitor -> Server State -> Ensure Server State. Or you deactivate and activate SQL Monitor again.
Verify/Schedule SQL Monitor jobs Only for NW 740 SP2 Verify in the Job overview (SM37) that the job RTM_PERIODIC_JOB is running periodically (once an hour). If this job is not scheduled, then run transaction SRTM and choose Runtime Monitor-> Administration -> Settings. Here you see the job administration for the job RTM_PERIODIC_JOB which should have been scheduled automatically when accessing Runtime monitor (SRTM) the first time. Verify that the SQL Monitor data replication is scheduled: Check in the job overview if the job RSQLM_UPDATE_DATA is scheduled periodically. If this job is not scheduled, then follow the instructions of OSS note 1859369.
Verify/Schedule SQL Monitor jobs NW 740 SP3, NW 702, NW 731 ST-PI No action necessary. The following jobs are scheduled/verified automatically when SQL Monitor is switched on:
  • RTM_PERIODIC_JOB Runs periodically once an hour. Basic job which is in most cases running anyhow (independent of SQL Monitor). It collects data from shared memory and stores it in DB tables.
  • SQLM_UPDATE_DATA/SCHEDULE (ST-PI: /SDF/ZQLM_UPDATE_DATA/SCHEDULE) Runs periodically once an hour.Replicates data to SQL Monitor tables.
  • SQLM_DEACTIVATE/SCHEDULE (ST-PI: /SDF/ZQLM_DEACTIVATE/SCHEDULE) Runs at the specified date and time to deactivate the SQL Monitor.
Important: If the runtime of the data replication job SQLM_UPDATE_DATA/SCHEDULE is getting too high (reaching 1h) then you may change the period to e.g. 4 hours. As a consequence it will take 4 hours until you see new data in SQL Monitor but the load of data replication goes down. If the job scheduling fails then this status information is visible in the SQL Monitor start screen. SAPPractical SQL Performance Monitoring In order to solve this just deactivate and then activate SQL Monitor. If this does not help please verify that you are allowed to schedule batch jobs.

How do I use the data of SQL Monitor?
After activating the SQL Monitor it takes up to one hour until the first data can be displayed. This delay comes up because the periodic SQL Monitor batch jobs must run to collect the data.
The SQL Monitor data can be accessed via transaction SQLMD (/SDF/ZQLMD for ST-PI version) or by pressing the button "Display data" in SQL Monitor start screen.
The following table contains usage scenarios and how these can be realized using SQL Monitor:
Step Description
Analyze SQL Monitor Log to know when the SQL Monitor was activated Run transaction SQLM or /SDF/ZQLM Choose SQL Monitor -> Display Log/History Specify a start date/time for the log display As a result you will get a chronological list of SQL Monitor events like activation/deactivation of SQL Monitor, deletion of SQL Monitor records, replication of data done by the periodic jobs etc.. In order to analyze the SQLM data you can use this information to find out when the measurement started or if someone deleted the data in between. SAPPractical SQL Performance Monitoring Additionally, this information can be used to monitor the SQL Monitor activity and to check for errors or performance issues e.g. in the periodic SQL Monitor batch jobs. (E.g. you may change the frequency of the SQLM_UPDATE_DATA/SCHEDULE from one to e.g. four hours if the job needs too much time)
Download SQL Monitor data You normally download SQL Monitor data when you want to analyze it in another system or if you intend to switch off SQL Monitor and delete all the data to start from scratch (e.g. after you corrected already many SQL errors). To download SQL Monitor data press button "Download Data" in the SQL Monitor start screen. NW 740 SP2 only: Run report RSQLM_DOWNLOAD_DATA to download the current SQL Monitor data to a local file. SAPPractical SQL Performance Monitoring In the selection screen you can choose what you want to download - default is everything. But you may limit the download to e.g. only customer code packages (Z*, Y*). Additionally you specify the local file for the download. As a result you get a zip file containing the SQL Monitor data in XML format. In transaction SWLT (SQL Performance Tuning Work List) you can upload the file in a so called SQL Monitor snapshot: Create a SQL Monitor snapshot: Button “Manage snapshot”, on the next screen choose “Create snapshot by file-import”. Display SQL Monitor snapshot: Button “Select snapshot”, choose NONE in frame “Static Check settings” -> Run (F8)
Access SQL Monitor data Run transaction SQLMD or /SDF/ZQLMD or press the button "Display data" in the SQLM or /SDF/ZQLM monitor start screen.
Display the top n custom code SQL statement s of the system sorted by Total Time / Executions In the SQL Monitor data selection screen limit the packages to your custom code (e.g. Z*, Y*) and aggregate by source position. Order by total time or executions. SAPPractical SQL Performance Monitoring
Display the top processes with the highest SQL load / highest SQL traffic In the SQL Monitor data selection screen choose aggregation by request and order by Total Time/ Executions: SAPPractical SQL Performance Monitoring
Display the SQL profile of a process You can drill down from the top n process list (see scenario "Display the top processes with the highest SQL load") or directly limit the display to one process (e.g. transaction VA01). SAPPractical SQL Performance Monitoring
Display the usage of a DB table Limit the display to one database table to evaluate which customer ABAP code is accessing the table: SAPPractical SQL Performance Monitoring
SQL Performance Monitoring-SAP SQLM Part-1
SQL Performance Monitoring-SAP SQLM Part-3

If you like this blog, please share (Facebook/LinkedIn/Google+) to click below links so it will reach to others.