Jiwa Sales order Performance issues

[SBarnes]

We have a customer that is getting performance issues with Jiwa related to sales orders, the symptoms are as follows:

Unless the database is reindexed and restatted daily quite oftern user can't open the sales order screen.
A scheduled plugin that runs quite often reports read errors from the sales order business object, errors in the event log about timeouts.

Things that have been tried to alleviate the problem are

Changing all custom SQL reads against these tables to use no lock, this includes the stored procs for sales orders and invoices.
Making sure the scheduled plugins don't run overnight for when the reindexing will run
Having Jiwa auto log off any users so there are also not users logged in when the reindexing runs
Seperating scheduled plugins into their own service each

We are wating for the customer to approve archiving sales order data as this is a fairly old database and has over 4 million records in SO_Lines, which hopefully will occur in the next day or so.

The question is are there any other ideas which could be tried?

[pricerc]

You can try changing the clustered index on SO_Main, SO_History, and SO_Lines.

For SO_Main, make it clustered on InvoiceNo. For SO_History, on InvoiceID + HistoryNo, for SO_Lines, InvoiceHistoryID + LineNo.

For the custom tables, make it clustered on the 'Parent' key + SettingID.

It will take a while to change.

[Mike.Sheen]

A scheduled plugin that runs quite often reports read errors from the sales order business object, errors in the event log about timeouts.

The question is are there any other ideas which could be tried?

That smells like a possible network issue. I've seen a few times where a flaky piece of equipment in the network drops packets and it results in poor performance.

[SBarnes]

That smells like a possible network issue. I've seen a few times where a flaky piece of equipment in the network drops packets and it results in poor performance.

My fault as I should have mentioned two things its probably not going to be network or hardware related becuase the scheduler is on the same virtual machine as the database server and this customer has just done a lift and shift from on-premise servers into Azure so the vrtual machines are now copies of the original virtual machines but now sitting in Azure.

[pricerc]

That smells like a possible network issue. I've seen a few times where a flaky piece of equipment in the network drops packets and it results in poor performance.

My fault as I should have mentioned two things its probably not going to be network or hardware related becuase the scheduler is on the same virtual machine as the database server and this customer has just done a lift and shift from on-premise servers into Azure so the vrtual machines are now copies of the original virtual machines but now sitting in Azure.

Is the Azure VM adequately spec'ed ?

(ducks for cover)

[SBarnes]

It is now they started it with 8GB of RAM it now has 128GB, that had to be changed on day 1 when they went to Azure, don't laugh I didn't do the initial set up their IT support company did, who assured me they knew what they were doing, the customer is licensed for 35 licenses for Jiwa, it ran like a dog until that got fixed why they never set the same amount of RAM that the on-premise system had amazes me.

[pricerc]

It is now they started it with 8GB of RAM it now has 128GB, that had to be changed on day 1 when they went to Azure, don't laugh I didn't do the initial set up their IT support company did, who assured me they knew what they were doing, the customer is licensed for 35 licenses for Jiwa, it ran like a dog until that got fixed why they never set the same amount of RAM that the on-premise system had amazes me.

When it comes to SQL Server, IT companies are the bane of my life.

As a rule, I find that they don't actually *get* SQL Server, and treat it like they would any old program they'd install on their own computer, such as their favourite game, and they cannot comprehend that it works "differently".

Of course, I come from a more mainframe background, so have a different view of database servers to your average Microsoft networking "expert".

As an example, we have a standard 'white paper' on SQL configurations that we provide to customers, recommending physically discrete devices for data and logs.

I just had the IT people for a new customer tell the customer "That's not how SANs work". The customers "SAN" being a single storage rack with 20 (I think) SSDs.

[Mike.Sheen]

The question is are there any other ideas which could be tried?

If you haven't already turn on Query Store and check that daily.

SSMS_QueryStore_1.png (12.92 KiB) Viewed 2794 times

You'll want to select "Top Resource Consuming Queries" - that'll default to just the last hour, so you want to hit the configure button at the top right

And then select last day.

SSMS_QueryStore_Configure.png (35.62 KiB) Viewed 2794 times

Then the bar graph will show the most expensive queries on the left, click the bar to reveal the SQL Query behind the metric

Often there will be a suggested index change (none in my screenshot but it is obvious when there is one) - if you opt to action the recommended index change make sure you document this so you know what you changed and can return to a known position if needed.

Just do that once a day for 2 weeks and that should be enough to catch all the low hanging fruit in terms of index tuning. It will also reveal other inefficiencies which may not benefit from an index tune, but will point you in the right direction for closer examination.

[Mike.Sheen]

It is now they started it with 8GB of RAM it now has 128GB, that had to be changed on day 1 when they went to Azure

Am I right in reading this as they have provisioned a VM in Azure and installed SQL Server on it?

If so, then they are doing cloud wrong. We recently saw an IT Services provider who was managing a shared SQL Server in an Azure VM have to provision a second VM to host a second instance of SQL Server because when they onboarded a new Jiwa customer it severely degraded the performance experience of the existing Jiwa customers.

Just use Azure SQL, this is the tool most suited to the job. Multiple redundant SQL Servers hosting the same database, transparently with automatic failover and provisioning all in separate fault zones in the data center - it has what everyone should have when running a database server but are not capable of configuring and maintaining it.

[SBarnes]

Am I right in reading this as they have provisioned a VM in Azure and installed SQL Server on it?

If so, then they are doing cloud wrong. We recently saw an IT Services provider who was managing a shared SQL Server in an Azure VM have to provision a second VM to host a second instance of SQL Server because when they onboarded a new Jiwa customer it severely degraded the performance experience of the existing Jiwa customers.

Just use Azure SQL, this is the tool most suited to the job. Multiple redundant SQL Servers hosting the same database, transparently with automatic failover and provisioning all in separate fault zones in the data center - it has what everyone should have when running a database server but are not capable of configuring and maintaining it.

Preaching to the choir my friend , they did a complete lift and shift using Azure migrate, I even had to correct the perception that Jiwa wouldn't run under Azure SQL but no amount of noise from me changed how this went down.

The other IT service provider you mentioned, I hope we are thinking of the same provider otherwise I am telling you there are two occurrences of the same thing recently with a VM running SQL locally that wasn't adequate to the task.

Its looking like the customer has agreed to archiving their 15-year-old database but I will pass the other information up the chain to see if that will help.

Interestingly it now appears that not only is the Jiwa sales order object having read errors but stored procedures in customisations are running into deadlocks, I fixed this by changing them to use with(NOLOCk) and also a third-party software that extracts sales data is having a similar problem.

All roads are leading to Rome as the saying goes and the issue clearly lies at the database level with the sales order tables, archiving will be the first step and then following your suggestions posted here will also be followed, I hope.

Thanks for the suggestions.