cancel
Showing results for 
Search instead for 
Did you mean: 

Database getting smashed by particular query - OILM/OBTL/ILM1

RaMstah
Explorer
0 Kudos

Hi All,

Having difficulty tracking down a runaway SQL query - I believe it is originating from SAP itself via either the UI or Service Layer. Hoping someone can point me in the direction of what is generating this so I can look at limiting usage or rewriting outside of SAP.

The SQL query in question is as follows:

SELECT T0.[DocLineNum], T2.[SysNumber], T0.[ActionType], T1.[BinAbs], T1.[Quantity] 
FROM [dbo].[OILM] T0 
INNER JOIN [dbo].[OBTL] T1 ON T0.[MessageID] = T1.[MessageID] 
LEFT OUTER JOIN [dbo].[ILM1] T2 ON T1.[MessageID] = T2.[MessageID] AND T1.[SnBMDAbs] = T2.[MdAbsEntry] 
WHERE T0.[TransType] = (@P1) AND T0.[DocEntry] = (@P2) 
ORDER BY T0.[DocLineNum],T1.[SnBMDAbs],T1.[BinAbs]

This is far and away our biggest resource user on the SQL server, and we do suffer from performance issues constantly with 100's of users over many systems (SAP UI, POS systems, WMS, Service Layer usage etc).

We've been unable to track this query down as an internally generated statement and believe its originating from SAP itself as its using the common T0, T1 etc format.

I have tried adding the suggested "missing" index which pops up from the query store in SQL 2019 and also from Brent Ozar's index scanner, but as evidenced from the plan information average time the yellow square and circle average response/query time went up with this index, on top of slower writes of course, so this didn't help.

Anyone have any ideas or explain its usage? Is it a some right click transaction search or something from the UI? Thanks in advance!

PLATFORM: SAP BUSINESS ONE 2402 SQL edition
Windows Server Data Center 2022
SQL 2019 (15.0.4345.5)

View Entire Topic
Johan_H
Active Contributor

Hi,

Apparently you use bins, and you create a lot of stock transactions. This system query is run each time a stock transaction is created. I am afraid your options are limited:

  • You can log a support ticket with your SAP Partner about the system being slow, providing your findings about this query.
  • Another option is to analyse your business scenario and use of the system. You mention 100's of users and a POS system. My guess is a number of stores, and their POS systems hooked directly into B1. You may want to look into a consolidating layer. I.e. do not hook the POS systems up to B1 directly, but instead to their own database, which you then syncronize with B1 separately.
  • Have a separate B1 database for each location, and consolidate their data into one database for the central office.
  • Look into switching to a larger scale SAP product, for example SAP All-In-One.

In short, do not use a single B1 database to handle everything. It just wasn't designed for such large scenarios.

Regards,

Johan

P.S. Adding indexes without approval from SAP support is not recommended. This is because indexes can slow down the system in unexpected ways, as you have now experienced.

RaMstah
Explorer
0 Kudos

I have solved this bottle neck. Actually it is related to the query and SQL optimizer setting a bad / parallel plan depending on the servers MAXDOP and parallelism settings. I created a plan guide and confirmed via the query store metrics that indeed we have a winner with massively reduced min, avg and max durations.

EXEC sp_create_plan_guide
@name = N'Slow SAP Parallel Fix', 
@stmt = N'SELECT T0.[DocLineNum], T2.[SysNumber], T0.[ActionType], T1.[BinAbs], T1.[Quantity] FROM  [dbo].[OILM] T0  INNER  JOIN [dbo].[OBTL] T1  ON  T0.[MessageID] = T1.[MessageID]    LEFT OUTER  JOIN [dbo].[ILM1] T2  ON  T1.[MessageID] = T2.[MessageID]  AND  T1.[SnBMDAbs] = T2.[MdAbsEntry]   WHERE T0.[TransType] = (@P1)  AND  T0.[DocEntry] = (@P2)   ORDER BY T0.[DocLineNum],T1.[SnBMDAbs],T1.[BinAbs]',
@type = N'SQL',
@module_or_batch = N'SELECT T0.[DocLineNum], T2.[SysNumber], T0.[ActionType], T1.[BinAbs], T1.[Quantity] FROM  [dbo].[OILM] T0  INNER  JOIN [dbo].[OBTL] T1  ON  T0.[MessageID] = T1.[MessageID]    LEFT OUTER  JOIN [dbo].[ILM1] T2  ON  T1.[MessageID] = T2.[MessageID]  AND  T1.[SnBMDAbs] = T2.[MdAbsEntry]   WHERE T0.[TransType] = (@P1)  AND  T0.[DocEntry] = (@P2)   ORDER BY T0.[DocLineNum],T1.[SnBMDAbs],T1.[BinAbs]',
@params = '@P1 int,@P2 int',
@hints = N'OPTION (MAXDOP 1)'