I would caution that the Maximo logs for SQL time can be wrong. What I mean by that is Maximo is the timer, so when the server has high CPU utilization for example that prevent threads from being able to execute the query the timer is still active. So Maximo thinks the query took 61 seconds for example, but may have spent 60 seconds waiting to execute the query. We see this a lot pre-heap dump or core dump on JVMs where you'll see a bunch of queries that look to be taking insane amounts of time.
Tim's suggestion of trying to retrieve using the REST API (or creating a custom app based on SR) is a good idea to see if there is some UI customization impacting performance. While importing the out of the box XML eliminates some configuration settings, there are others (such as Conditional UI) that are stored in tables that are not impacted by the import.
On the SQL platform, we recommend disabling mxe.db.optionuse (setting to 0) in every system. This is where that OPTION (FAST 1000) comes from. It tells the database platform to use an execution plan that will return 1000 records the fastest. It sounds OK on paper, but the Maximo framework depends on the query to complete so you really want the database platform to execute the fastest execution plan (which is the default). We've seen queries go from <1 second to taking >8 seconds just by having this flag. We have never seen a query execute faster with it. We've been discussing this (and other SQL Server issues) with the IBM team.
Another SQL Server specific option that improves query performance, but can cause serious issues, is mxe.db.sqlserverPrefetchRows. IBM's performance recommendations suggest setting this to 200 but the application will always run faster if it's set to 0 (disabling the setting). BUT there's a reason that the 200 recommendation exists (though I'd suggest a higher number than 200). If you have a larger database, Maximo can heap dump when someone does something such as "All Records" inside of the Work Order application because Maximo will load too much of the data from the database in memory at a single time. This helps avoid that, but makes it much slower because of it. I don't think this is the cause of your issue but would be something I'd recommend evaluating.
------------------------------
Steven Shull
Projetech Inc.
------------------------------
Original Message:
Sent: 03-04-2021 16:35
From: Diane Nohner
Subject: The SR Module takes between 1-2 minutes to open a SR Record #
the webclient.enabledoclinkonload is set to "false".
When I looked at the log file for long running queries
This is from the log file -
[WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (SCCONFIG) : select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc OPTION (FAST 1000) (execution took 82502 milliseconds)
[WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (MAXGROUP) : select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN' ) OPTION (FAST 1000) (execution took 60579 milliseconds)
I ran each select query from the above log file in the database on SQL Server and it queried no issues with in seconds.
Select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc
select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN'
in admin mode it did not change the results of the SR it still took 1-2 minutes to load.
Any other suggestions?
------------------------------
Diane Nohner
City of Minneapolis
Original Message:
Sent: 02-16-2021 09:01
From: Steven Shull
Subject: The SR Module takes between 1-2 minutes to open a SR Record #
I think Chris might be spot on. I would check to see if you have the system property webclient.enabledoclinkonload enabled (IE true or 1). This is one of the only settings I can think of that can negative impact performance opening a record that wouldn't occur on the list tab. If enabled, this reverts back to how it functioned in the Maximo 7.1 days where the attached document indicator changes whether or not there are attachments on the record. Because the image changes, it needs to complete the query before it will open the record.
Beyond that, looking for slow running queries in the logs. Maximo by default will log anything that takes longer than 1 second or returns more than 1000 records so you should hopefully be able to track it that way.
If it's not related to SQL query performance, I'd lean towards some sort of customization (such as automation script) that fires on initialize (either of the object or attribute) that doesn't do anything on the list tab. If you can recreate this in a non-production environment, try to put Maximo into Admin Mode and try it there. This will disable most of the automation scripts, which makes triaging easier. If it's quick in Admin Mode, it's probably an automation script.
------------------------------
Steven Shull
Projetech Inc.
Original Message:
Sent: 02-12-2021 11:14
From: Diane Nohner
Subject: The SR Module takes between 1-2 minutes to open a SR Record #
SR Module takes between 1-2 minutes to open a SR Record from both the find service request and list tab.
It loads the SR list in a timely manner when you select a SR # is when it takes between 1-2 minutes to load.
Steps taken to resolve
updated statistics and refreshed index tables
imported out of the box xml file.
Have a case open with IBM support and are stumped.
This is the only module with the issue all others function as intended.
This started after upgrading to Maximo 7.6.1.1
System Information
App Server IBM WebSphere Application Server 8.5.5.14
Version
Tivoli's process automation engine 7.6.1.1-IFIX20200522-1523 Build 20190514-1348 DB Build V7611-365 HFDB Build HF7611-13
IBM Maximo Asset Management Work Centers 7.6.0.4 Build $build$ DB Build V7604-119
IBM Maximo Asset Management Scheduler 7.6.7.3 Build 20190514-1348 DB Build V7673-78 HFDB Build HF7673-12
IBM Tpae Integration Framework 7.6.1.1 Build 20190419-2330 DB Build V7611-01
IBM Maximo Spatial Asset Management 7.6.0.5-20200127-1155 Build 20190522-1410 DB Build V7605-09 HFDB Build HF7605-06
IBM Maximo Asset Management 7.6.1.1 Build 20190514-1348 DB Build V7611-01
IoT Connection Utility 7.6.0.2 Build 20190426-2206 DB Build V7602-07
Server OS Windows Server 2012 R2 6.3
Server DB Microsoft SQL Server 13.0 (13.00.4435)
#EverythingMaximo
------------------------------
Diane Nohner
City of Minneapolis
------------------------------