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

[RaMstah]

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)

[RaMstah · Accepted solution]

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)'

[Johan_Hakkesteegt]

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]

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)'

[JimSpath]

When posing a code question, you can improve the legibility and likelihood of good answers if you use the code insert/edit control feature here. 

I apologize for posting this suggestion as an "answer" since the comment reply does not allow formatting to show an example.

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]

So you did an "explain plan" which suggested an index which doesn't prevent the code from running?

Do full database traces show source calls?

Multiple simultaneous runs? Disk I/O (shudder)? When you say "writes" are they temporary caches for the SELECT statement?

[RaMstah]

Thanks Jim, I updated it as per your suggestion - much better 😉

Multiple simultaneous run and disk I/O - its running a lot I am sure there is overlap, the server itself is on a big fast RAID 10 SAN giving 500,000 IOPS/sec. The report of this query eating resources comes from the SQL query store.

I will look into traces and see if it'll name the exe/process that is calling. Cheers.