Accepted Solutions (0)
Answers (5)
When we load data(Insert/Update or Delete) into a table a lock is set(Enqueue) and when the loading process is complete the lock is removed(dequeue).
This is common for all OLTP(SAP Source Systems) and OLAP(Datawarehouses) systems.
SAP Locking concept explains different types of locking & Relationship between SAP Locks and Database Locks
However let’s focus two types of locks - Shared(S) & Exclusive(E).
Shared Lock - Several users or processes can have access to locked data at the same time in Display Mode. Exclusive Locks - Just as it says.
When your BW ETL(Process Chains) jobs are running find out the types of Locks occurring when DTP Load, DSO Activation, SID Generation & OHD’s are running. If lots of time is spent on SID generation then Number Range Buffering is your fix.
Blocking is wait situation that are currently caused by locks which is explained in detail via the DBA Cockpit for MS SQL
Deadlock occurs when two or more processes or users prevent each other from proceeding with the SQL statement.
SAP uses Database parameters to manage MS SQL Server locks, Here is how we Analyze Deadlocks when running SAP on MSSQL. You can find the same for your DB.
Real World Example to explain Locks, Blocks, Deadlocks, Bad Code & Optimizations.
Let's use a daily commute in the Bay Area as an example - Start home at 6:25 and reach Starbucks by 6:33 and reach train station at 6:45 to ride the 6:48 ACE Train to get to work by 8 AM. Now let's say our 8 AM meeting is canceled - Leave home at 7:10 get to starbucks at 7:23 and will reach train station at 7:50 AM and take the 7:53 train.
Locks - Cash Registers
Blocks - All the traffic of parents dropping kids at school, and all the people in front of me in starbucks waiting to order
Deadlocks - People creating a spill while in a hurry while picking up their drinks, cash register hung up
Optimizations - Adding an extra cash register during peak times, your regular order should you be a regular customer, paying with your app
Slow Performing Code - People taking longer time to give exact change, spending long time to order or changing order multiple times, a new pot of coffee being brewed now
Metrics to track and avoid slowness / locking are
- Jobs running more than 1 hour
- Which jobs are not starting on time and not finishing on time
- Full loads with over say 1 Million rows
- Inconsistent Scheduling of process chains (same chain triggered twice)
Create a table like below for the Top 20 Process chains by runtime
In the above example if there are 14000 unique SID's generated for 0PCA_ITEMNO ( this detail is available in your process chain log) set the number range buffer to 2300 records. It would have accessed the NRIV table 5 times before and now with this config setting it will go there once there by reducing locks.
To avoid general slowness in your BW Production System
- ( it has links to all parts)
Finally if your RS* tables are growing there are multiple programs to reduce those.
| User | Count |
|---|---|
| 90 | |
| 10 | |
| 10 | |
| 10 | |
| 7 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.