Purpose
The purpose of this blog is to troubleshoot the BW performance issue which we sometime face due to obvious reason. To troubleshoot we should perform the below mandatory check one by one and note it down in separate in notepad/word document.- Basis Checks from Application
- Basis Checks from OS
- Basic Checks from DB
Action/Tcode
|
Expectation
|
|
Check
GUI
|
Should be able to login
|
If note use Basic check for OS/DB.
|
SM51
|
All
3 Application should be available
|
If
not check application server from OS
|
SM50
|
Check Background/Dialog WP on all
application server, both WP should be available
|
If Background and Dialog WP is not
available, then note it down and check which user/report causing this. Steps
|
SM66
|
Note
down the long running and high memory consuming jobs as well as any WP in
private mode.
|
Steps:
|
ST22
|
Check dumps and investigate
|
|
SM21
|
Check
log
|
|
SM12
|
Check lock entries
|
|
SM13
|
Check
update is active or not as well as failed updates.
|
If
Update is disable, then enable it from SM13 administrator or Sm14. If enable
and various lock entries are there then check step:
|
SM37
|
Check active jobs and long running
jobs as well as jobs which I failed today
|
If jobs are getting failed check
steps:
If Jobs are running from long time
check steps:
|
RSDANLCON
|
Check
NLS database is available
|
If
not report this to DB team
|
SMLG
|
Check logon group load
|
SMLG --> F5
|
SM58
|
Check
entries in error
|
Inform
Application Team
|
SMQ1
|
Check entries in error
|
Inform Application Team
|
SMQ2
|
Check
entries in error
|
Inform
Application Team
|
Basis Checks from Operating System (OS)
Action/Tcode
|
Expectation
|
|
Server
logon
|
Try to login to server
|
If Not Inform the Unix team
|
R3Trans
|
Run
R3trans –d and make sure output is 000
|
If
not check from database end most probably database is down
|
DPMON
|
Go to profile directory and run
dpmon pf=Instance profile
|
Check dispatcher queue and number
of WP
|
Log
|
Check
log
|
Check
Dispatcher and WP log for error
|
Timing
|
Check the timing of Application and
DB
|
If timing is not correct then
inform unix for ntp sync
|
Basis Checks from Database
Action/Tcode
|
Expectation
|
|
Server
logon
|
Try to login to server
|
If Not Inform the Unix team
|
Status
of DB
|
sapcontrol
-nr 00 -function GetSystemInstanceList
|
Check
all nodes are green
|
Status
of services
|
Hana studio : Landscape: Services
|
Check last start time of services
|
Memory
|
Check
memory status
|
If
it’s in red/yellow find out reason
|
MVCC
|
Check MVCC blocker Hana studio:
System information: MVCC query
|
Cancel after confirmation
|
Alert
|
Check
alerts for today/yesterday
|
Hana
studio: Alerts
|
Background WP Not Available
Reason
Check WP available on all application server from SM50 make sure it’s free on all apps
If work process not available mostly background, then follow below
Action
Find out the user who is using maximum number of session with AL08
Find out active jobs from user whose jobs are using most of background WP
Resolution
Inform user to cancel his/her jobs or better kill before it goes for P1
Cancel job or remove user from system
SAP Notes
2098461 - PRIV process management
2537149 - Work Process in PRIV Mode are not ended based on max_wp_runtime
MVCC Blocker
a)
Multi Version Concurrency Control (MVCC) is a
concept that ensures transactional data consistency by isolating transactions
that are accessing the same data at the same time.
b)
SAP HANA use multi version concurrency control
to make schedule serializable. Each user connected to the database sees a
snapshot of the database at a particular instant in time. Any changes made by a
writer will not be seen by other users of the database until the changes have
been completed (or, in database terms: until the transaction has been
committed.)
Action: To resolve this particular situation, we need to perform 3
below task
a) Find out the connection which is causing it
using MVCC Blocker Connection script.
b) Find
out statements that may be blocking garbage collection using MVCC Blocker
Script.
c) Find
out transactions that may be blocking garbage collection using MVCC Blocker Transaction
d) After finding we had to kill the session and
transaction
Resolution
a) MVCC alerts need to monitor carefully
as its already configured on hana database in daily monitoring.
b) Parallel job/processing should be
avoided which using same table by different process.
c) Cancel job/session/transaction from
hana database as per below.
You stop a session using the following
SQL syntax:
ALTER SYSTEM CANCEL SESSION '<
connection_id >'
Keep in mind, the CANCEL operation does
not happen on the spot. It could take some times before the session stops.
[DISCONNECT SESSION]
You stop a session by disconnecting it
using the following SQL syntax:
ALTER SYSTEM DISCONNECT SESSION '<
connection_id >'
This is a 2 internal steps procedures.
The current transaction is cancelled and rolled back first then it is
disconnected.
Important Note: you should execute a
DISCONNECT SESSION with care if the CANCEL SESSION didn't succeed.
How to get session details using sap
hana studio?
Performance tab --> session
killing transaction
hdbcons 'transaction c <
transaction_id > ‘(run from sidadm)
SAP Notes
2081856
- How to handle HANA Alert 59: 'Percentage of transactions blocked'
2169283
- FAQ: SAP HANA Garbage Collection
BW data
load stuck due to ECC job failure
Reason
BW load is stuck due to dump error " Internal session terminated
with a runtime error DBSQL_SQL_ERROR (see ST22)" in ECC system
Its happed because of dead lock on table, please avoid to run multiple
jobs at the same time which have same table.
A database deadlock occurs when two processes lock each other's
resources and are therefore unable to proceed.
This problem can only be solved by terminating one of the two
transactions. The database more or less
at random terminates one of the transactions.
Resolution
a) Check SM58 in ECC
and clear the failed RFCs after confirmation from application team.
b) Menu --> Edit
--> Here you find 2 execute options 1-Execute LUW (F6) - this will process
single LUW.
c) there is another
option "EXECUTE LUWs" --> select this option -->Enter date and
destination and select the statuses and execute in background.
SAP Notes
2063206 - SM58: Transaction RFC: Log manual
actions