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
JimSpath
Active Contributor

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. 

insert-code-sample.png

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
Explorer
0 Kudos

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.