Skip to Content
0

Select on BSAD significantly longer fetch time in Prod than Pre-Prod

Jun 22, 2017 at 11:31 AM

98

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Eduardo Rezende
Jun 22, 2017 at 03:34 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Jonel Rienton Jun 23, 2017 at 01:51 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Phil Martin Jun 23, 2017 at 09:37 AM
0

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?


ep1-bsad.jpg (166.7 kB)
ep2-bsad.jpg (202.6 kB)
ep2-bsad.jpg (202.6 kB)
sql-trace.png (29.4 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Phil,

You can explore Open/Fetch/Close Cursor methodology.

More info available on SCN if you search with the keyword "Open Cursor".

K.Kiran.

0