E9.2 SQL Server 2019 CU23 on JDE

[dturkeltaub]

We patched our SQL Server 2019 instance from CU13 to CU23 a few weeks ago and ever since then we've had performance issues with JDE. Our MSP hasn't been able to find answers and has now engaged Oracle, but I don't expect anything to come of it. Is anyone else running CU23 with JDE and how is your DB performing?

 

[MFreitag]

Just went from CU23 to CU24 for a customer's test environment without any problems. A PROD-like test system was on CU23 as well and endured an integration test with positive results.

Any details you can elaborate on?

 

[dturkeltaub]

Just went from CU23 to CU24 for a customer's test environment without any problems. A PROD-like test system was on CU23 as well and endured an integration test with positive results.

Any details you can elaborate on?

Longer running UBE's are performing slower: MRP, Integrities, some custom stuff that performed consistently before are all now taking hours longer.

 

[MFreitag]

Interesting! Do you have a test machine where you could do the CU24 to do a side-by-side comparisson?

 

[TFZ]

Any chance the compatibility level of the DB is old? Just kind of shooting in the dark. Does the Activity monitor show anything? How does CPU look? If it's everything, I would assume something is putting a choke on it.

 

[MFreitag]

Good point, also - what does Performance Dashboard report in the graphs and statistics? Something you might've looked into already?

 

[dturkeltaub]

TFZ, the Compatibility level is set for 150. I believe it's been set that way since March when we moved to the SQL2019 server.

Matthias (we met in Dallas last year), I will have to get a DBA to show me the Performance Dashboard. I don't have permissions to run it.

 

[MFreitag]

TFZ, the Compatibility level is set for 150. I believe it's been set that way since March when we moved to the SQL2019 server.

Matthias (we met in Dallas last year), I will have to get a DBA to show me the Performance Dashboard. I don't have permissions to run it.

Will we meet again this year? I don't remember you just by this username, sorry!

Performance Dashboard is always a good idea as it shows you on one simple page where the SQL Server spends time waiting and how much, if there are missing indexes (there are, always - but not all of them make sense) and what the utilization of the SQL Server compared to the server's total ressources is. You can drill into all of these charts and bars which makes it quite easy to navigate. All of that info is stored in tables, obviously, but this makes it a lot easier to look at.

 

[TFZ]

SQL Activity Monitor is where you'd see the performance of the CPUs, and the queries that will affect it. In SQL Management Studio, it's one of the tabs. https://learn.microsoft.com/en-us/s...erver-management-studio?view=sql-server-ver16

And what I've seen happen with more recent compat levels is the optimizer starts ignoring indexes. There's a thread somewhere here about it that had a lot of back and forth. I have seen, in particular, where queries against the F0902 and the system tables around BIP jobs start bottle-necking the CPU. You would see those in Activity Monitor.

I've also seen where this has helped,

Cardinality Estimation (SQL Server) - SQL Server

The SQL Server Query Optimizer selects query plans that have the lowest estimated processing cost, which it determines based on rows processed and a cost model.

learn.microsoft.com

The reality is, you're going to need a SQL DBA's help (or a CNC that stayed in a Holiday Inn last night and is an "expert"). This is one area where Oracle will not really bend over backwards to assist.

 

[DSauve]

We patched our SQL Server 2019 instance from CU13 to CU23 a few weeks ago and ever since then we've had performance issues with JDE. Our MSP hasn't been able to find answers and has now engaged Oracle, but I don't expect anything to come of it. Is anyone else running CU23 with JDE and how is your DB performing?

It may be a stretch, but check to see if any antivirus or backup software is running on your server(s). Several years ago at a previous job, we had issues with a backup agent bringing our database server to its knees.

 

[MFreitag]

(or a CNC that stayed in a Holiday Inn last night and is an "expert")

Hold on a second...that's me!

 

[brother_of_karamazov]

Longer running UBE's are performing slower: MRP, Integrities, some custom stuff that performed consistently before are all now taking hours longer.

I would look into the docs on all the cumulatives from 13 to 23. My guess is that something with parallelism possibly got changed between the updates.

I would also update statistics on all indexes. The easiest way to do this is to execute a full index rebuild. A caution that, unless you are already doing full index rebuilds, doing so will increase the size of the transaction logs (and their associated backups) dramatically. Another caution: "Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile." This means that the initial run of a query will be slower due to the recompile.

 

[MFreitag]

Interesting! Do you have a test machine where you could do the CU24 to do a side-by-side comparisson?

Coming back to this now - said customer is now suffering under so far unidentified performance issues!
@dturkeltaub did you get any further on those issues?