Performance issues can be caused by multiple reasons, some not directly related to Produmex WMS software. It is important to obtain information to quickly diagnose the main causes of performance degradation. Areas that can affect Produmex WMS performance include:

External tools and queries being run by 3^rd party software on the database server.
Extensive SAP B1 reports for sales, stocks, or marketing.
Not enough memory for SQL server.
Incorrect configurations.

To be the most effective in finding the reasons and solution to performance problems, we suggest a few steps before calling support.

Review the scenarios presented in this article: Important scenarios related to WMS performance
Verify your hardware infrastructure is capable of supporting your workload.
Consult with the server sizing requirements
1. Further requirements in case of MSSQL:
  The available memory to MSSQL is close to the size of the MDF files
  (i.e. a 40 GB MDF file should have an MSSQL with a 40 GB reserved memory, the size of the log file is not relevant).
  Note: Remember to check is the MSSQL server has memory restrictions.
Upgrade to the latest product version.
We provide performance improvements with every new release, therefore we cannot analyse older versions for performance, as we cannot differentiate problems that might still exist from problems already solved.
Run the Archiving function (see documentation here).
Some PDMX tables can grow very large in time, while the information provided in those tables is important for historical reasons, is not relevant for most of the day-to-day operations.
Archiving part of those tables can have a significant impact on performance.

In many scenarios, this will improve your performance, however, we understand that some environments will require further exploration, and it needs to be handled on a case-by-case base.

What can I do right away?

There are two critical settings in SQL Server to can provide a major difference in running operations. Changing these settings can have an imidate impact on your operations.

Cost Threshold for Parallelism
Maximum Degree of Parallelism (MAXDOP)

Quick tip/setting for Cost Threshold for Parallelism

Run this query on the production database:

CREATE TABLE #SubtreeCost(StatementSubtreeCost DECIMAL(18,2));

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

INSERT INTO #SubtreeCost
SELECT
    CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS DECIMAL(18,2))
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

SELECT StatementSubtreeCost
FROM #SubtreeCost
ORDER BY 1;

SELECT AVG(StatementSubtreeCost) AS AverageSubtreeCost
FROM #SubtreeCost;

SELECT
    ((SELECT TOP 1 StatementSubtreeCost
    FROM
        (
        SELECT TOP 50 PERCENT StatementSubtreeCost
        FROM #SubtreeCost
        ORDER BY StatementSubtreeCost ASC
        ) AS A
    ORDER BY StatementSubtreeCost DESC
    )
    +
    (SELECT TOP 1 StatementSubtreeCost
    FROM
        (
        SELECT TOP 50 PERCENT StatementSubtreeCost
        FROM #SubtreeCost
        ORDER BY StatementSubtreeCost DESC
        ) AS A
    ORDER BY StatementSubtreeCost ASC))
    /2 AS MEDIAN;

SELECT TOP 1 StatementSubtreeCost AS MODE
FROM   #SubtreeCost
GROUP  BY StatementSubtreeCost
ORDER  BY COUNT(1) DESC;

DROP TABLE #SubtreeCost;

You obtain a result similar to this:

On your SQL Server settings

Set your Cost Threshold for Parallelism to the lowest value between Average and Median.
Appraise any improvements after a few hours, and if not impact increase that value towards the highest value between Average and Median.

If you use any performance monitoring software, you are trying to optimize (lower) the value of CXPACKET wait states.

This setting can be changed on-the-fly and does not require a restart of the server.

Quick tip/setting for Maximum Degree of Parallelism

Run this query on the production database:

SELECT * 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255;

If that returns more than 8, please set MAX DOP at 8.
Another approach is to set MAX DOP to be 1 processor less than the max available on the server, this will ensure a single high-parallelism query will not gobble up all the processors, preventing other queries to be run.

More information about those settings can be found here:

Cost Threshold for Parallelism

Maximum Degree of Parallelism (MAXDOP)

Next steps?

In many scenarios, this will improve your performance, however, we understand that some environments will require further exploration.

Identify if the performance cause is at the core of WMS or other 3rd party software
- Remove/disable any 3rd party customizations, add-ons, etc.
- Ensure customizations/changes done on these store procedures are also removed:
  - SBO_SP_TransactionNotification
  - SBO_SP_PostTransactionNotice
- Once only SAP B1 and WMS add-on are running, verify that the performance is still impacted.

Add one-by-one the extra add-on, customizations, and 3rd party code.
- Track how the performance is affected as you add each new component.

If the performance degradation is felt with only SAP B1 and WMS running, then the next step is to create a Zendesk ticket. Our support team needs a minimal set of information to get started:

Is the performance issue related to the overall behaviours of Produmex WMS, or is it focused on certain flows/steps/operations?
1. In the case of flows/steps/operations, please describe in detail (videos are highly appreciated) the steps/operations that are being done and their conditions.
Document the exact layout of your infrastructure:

How many servers are there?
Are they separate servers for the MSSQL server, the SAP server and the Terminal Server?
What is running/installed on each server beside Produmex WMS?

Are the servers physical or VMs?
How are the servers/VMs configured?
CPU make and model (CPUs assigned in case of VM)
Memory
Network connections (single or multiple network adapters grouped together)
The number of concurrent users / user-type (B1 Client, B1 Client with Produmex WMS add-on, Produmex Fat Clients)

What happens next?

The Boyum Support Team reviews the submitted Zendesk ticket. Our support team will have more requests, but those are decided on a case-by-case basis.

Addendum.

WMS future versions will have a few new indexes, but while the new version is not released, you can add these new indexes to your current Produmex WMS database.

Table PMX_PIOP

This is a high traffic table. It is constantly being accessed and updated.

Index creation was done based on the above. It will increase the disk space considerably as we are leaving 50% of leaf space to allow new entries to be created without forcing a re-index all the time. This is done as the table gets written many times.

Create Index on PickListDocEntry & PickListLineNum

/****** Object: Index [PFI_PickListDoc&Line] ******/

DROP INDEX [PFI_PickListDoc&Line] ON [dbo].[PMX_PIOP]

/****** Object: Index [PFI_PickListDoc&Line] ******/

CREATE UNIQUE NONCLUSTERED INDEX [PFI_PickListDoc&Line] ON [dbo].[PMX_PIOP]

(

[PickListDocEntry] ASC,

[PickListLineNum] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

Create Index on MoveDocEntry & MoveLineNum

/****** Object: Index [PFI_MoveDoc&Line] ******/

DROP INDEX [PFI_MoveDoc&Line] ON [dbo].[PMX_PIOP]

/****** Object: Index [PFI_MoveDoc&Line] ******/

CREATE NONCLUSTERED INDEX [PFI_MoveDoc&Line] ON [dbo].[PMX_PIOP]

(

[MoveDocEntry] ASC,

[MoveLineNum] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

These indexes are minor, but will also improve data access, again this table is read from many different places in a multitude of times. Leaf space was reduced for these indexes.

Create Index on PickLocationCode

/****** Object: Index [PFI_PickLocationCode] ******/

DROP INDEX [PFI_PickLocationCode] ON [dbo].[PMX_PIOP]

SET ANSI_PADDING ON

/****** Object: Index [PFI_PickLocationCode] ******/

CREATE NONCLUSTERED INDEX [PFI_PickLocationCode] ON [dbo].[PMX_PIOP]

(

[PickLocationCode] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Create Index on ReasonCode

/****** Object: Index [PFI_ReasonCode] ******/

DROP INDEX [PFI_ReasonCode] ON [dbo].[PMX_PIOP]

SET ANSI_PADDING ON

/****** Object: Index [PFI_ReasonCode] ******/

CREATE NONCLUSTERED INDEX [PFI_ReasonCode] ON [dbo].[PMX_PIOP]

(

[ReasonCode] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Create Index on Itri

/****** Object: Index [PFI_Itri] ******/

DROP INDEX [PFI_Itri] ON [dbo].[PMX_PIOP]

/****** Object: Index [PFI_Itri] ******/

CREATE NONCLUSTERED INDEX [PFI_Itri] ON [dbo].[PMX_PIOP]

(

[Itri] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

Table PMX_OSEL

High traffic table for the mobile client.

Create Index on ZoneCode

/****** Object: Index [PFI_ZoneCode] ******/

DROP INDEX [PFI_ZoneCode] ON [dbo].[PMX_OSEL]

SET ANSI_PADDING ON

/****** Object: Index [PFI_ZoneCode] ******/

CREATE NONCLUSTERED INDEX [PFI_ZoneCode] ON [dbo].[PMX_OSEL]

(

[PmxZoneCode] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Table PMX_INLD

High traffic table

Create Index on BaseType & BaseEntry

/****** Object: Index [PFI_BaseType_BaseEntry] ******/

DROP INDEX [PFI_BaseType_BaseEntry] ON [dbo].[PMX_INLD]

SET ANSI_PADDING ON

/****** Object: Index [PFI_BaseType_BaseEntry] ******/

CREATE NONCLUSTERED INDEX [PFI_BaseType_BaseEntry] ON [dbo].[PMX_INLD]

(

[BaseType] ASC,

[BaseEntry] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Multiple tables, with support for multiple queries

Theses indexes will affect various queries.

CREATE NONCLUSTERED INDEX [PFI_PickListProposalEntry] ON [dbo].[PMX_PLHE] ([PickListProposalEntry]) INCLUDE ([DocEntry])

CREATE NONCLUSTERED INDEX [PFI_BoxType_ItemCode] ON [dbo].[@PMX_BTIT] ([U_BoxType],[U_ItemCode])

Performance Issues and Produmex WMS

What can I do right away?

Quick tip/setting for Cost Threshold for Parallelism

Quick tip/setting for Maximum Degree of Parallelism

More information about those settings can be found here:

Next steps?

What happens next?

Addendum.

Table PMX_PIOP

Create Index on PickListDocEntry & PickListLineNum

Create Index on PickLocationCode

Create Index on ReasonCode

Create Index on Itri

Table PMX_OSEL

Create Index on ZoneCode

Table PMX_INLD

Create Index on BaseType & BaseEntry

Multiple tables, with support for multiple queries

Comments

What can I do right away?

Quick tip/setting for Cost Threshold for Parallelism

Quick tip/setting for Maximum Degree of Parallelism

More information about those settings can be found here:

Next steps?

What happens next?

Addendum.

Table PMX_PIOP

Create Index on PickListDocEntry & PickListLineNum

Create Index on PickLocationCode

Create Index on ReasonCode

Create Index on Itri

Table PMX_OSEL

Create Index on ZoneCode

Table PMX_INLD

Create Index on BaseType & BaseEntry

Multiple tables, with support for multiple queries

Related articles