on 06-22-2017 12:31 PM
Hi there,
We've had a specific issue with a custom report that intermittently runs slower in production than pre-prod. The issue is a specific select statement on table BSAD. There is around x1.8 more data in prod than pre-prod, but the select statement shows x141 slower in prod than pre-prod.
The SQL trace appears to show the same indexes being used in both system and this is an intermittent issue. We don't see any link to wider system load, in fact one occasion was during a public holiday with virtually no users in the system.
My question is primarily whether the answer is there in the SQL trace results and we don't have the knowledge to spot it, or there is something else we can check - any additional logging at the basis layer for example that may give further clues?
The SQL trace shows:
Prod
Operation = open, duration = 805,379
Pre-prod
Operation = open, duration = 5,202
Both are using index BSAD~5 (index seek)
BSAD~0 (clustered index seek)
DB = MSSQL 11.00.6579.00.
Hi, what's the size of both boxes in terms of CPU and memory? Are both SQL configured the same way? What does the wait stats show? Are there any disk latency?
Your basis should be able to get this from DB02.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Eduardo, thanks for responding. BSAD in pre-prod has 1.1 million rows, prod has 2 millions rows.
The select statement where the issue occurs is below.
The Z table that feeds the internal table used in the select statement has the same number of entries in both systems and populates the itab with the same number of entries.
SELECT bukrs augdt augbl zuonr gjahr belnr buzei blart dmbtr vbeln INTO TABLE gt_bsad FROM bsad FOR ALL ENTRIES IN gt_whatif WHERE bukrs EQ gt_whatif-pbukr AND gjahr EQ gt_whatif-gjahr AND ( belnr EQ gt_whatif-refbn OR belnr EQ gt_whatif-belnr ).
Ive attached some SQL screenshots which I hope are of use.
DB corruption was checked and ruled out by our Basis team. DB statistics were confirmed to run daily and had run successful that morning.
Sorry I'm not sure what the DB execution plan is?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Phil,
Are there any differences between pre-prd and prd (number of rows on BSAD, DB size, ...)?
Is there any differences in the DB execution plan?
Regards,
Eduardo Rezende
SAP Support
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.