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.
|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.
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:
|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. 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. 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.|
|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:|
|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).|
|Display the usage of a DB table||Limit the display to one database table to evaluate which customer ABAP code is accessing the table:|
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.