E9.2 Enable WITH (NOLOCK) for individual SQL statements.

[BOster]

As you know there is a global setting that causes JDE to re-issue queries with the WITH (NOLOCK) qualifier if the original query doesn't return after X number of seconds, the theory being if records are locked for what ever reason this will allow a dirty read and the query can execute. We just did an upgrade from 9.0 where we had this enabled to 9.2 where we had to disable it because we found a bug on the JAS server (maybe ES as well, just found it on the JAS server) that, in some cases, generates mal-formed SQL when it re-issued the query with the NOLOCK qualifier. I always hated that global setting so was glad to disable it.

Well, we are experiencing some performance issues in some code that was the same in 9.0 and 9.2 and it is really starting to look like there may be some TP type blocking going on preventing a SQL query from executing on the ES.

So:
1. Is it possible, via JDEBASE API or otherwise to execute an individual SQL query and explicitly specify that it execute with the NOLOCK qualifier?
2. Is it possible to enable the NOLOCK global setting on the ES but still have it disabled on the JAS?
3. Any other way to get more granular with the NOLOCK qualifier other than just a global setting outside of something like a SQL view, etc.?

 

[JEMILLER]

1. I am not aware of any JDEBASE API parameter that can cause NOLOCK to be added to the generated query.
2. The ES configuration for query timeout, retries and eventual NOLOCK is separate from the JAS and configured in the JDE.INI.
3. I don't believe there is a way to control NOLOCK other than turning it off completely or configuring the query timeouts and retries before NOLOCK.

I am personally against the use of NOLOCK in just about any situation. I understand the argument that a dirty read might be acceptable for some reports but with dirty reads being used as input for further processing you could run into situations where the dirty read bring data into a follow on transaction that updates the database. If that dirty data is then invalidated when a pending transaction is rolled back you have now updated the database with input data that no longer exists in the state you thought it did.

Are you using RCSI (Read Committed Snapshot Isolation)? From what you are describing it sounds like you are not using RCSI.

I began using RCSI with SQL Server when it came out in SQL 2005 and never looked back. With blocking SELECT statements under SQL Server without RCSI, JDE often experiences inconsistent performance for both batch and interactive activity. If you add any type of external reporting activity on top of the JDE database it only adds fuel to the fire. Coming from an Oracle DB background I was mind blown that SELECT statements could block other SELECT statements.

Initially RCSI was not officially recommended by JDE and if you read the Microsoft considerations around using it there was scary language around impacts on TEMPDB. I have never experienced ill effects in the database turning it on and it became a simple magic trick for my JDE clients who were complaining of inconsistent night batch performance. (Obviously it doesn't solve all performance issues but it removes a major source of contention that should never have been there in the first place.)

If you look at support document:

E1: DB: What are the JD Edwards Recommended Tuning Settings with an RCSI Implemented SQL Database Server? (Doc ID 2565588.1)​

you will see that the recommendation is to turn off NOLOCK completely when using RCSI. RCSI essentially makes SQL Server behave on par with Oracle DB's row level locking and gives non-blocking SELECT statements. Under Oracle if you have a blocking lock it is either there for a reason because the code need to lock a record or you have a hung or crashed JDE process that has left a lock open. I feel that you still need to monitor for blocking locks under RCSI to detect blocking locks held beyond a tolerable threshold. That is just good DB monitoring practice for any database platform.

 

[craig_welton]

Same here for RCSI. Witnessed it fix blocking and increase performance. The magic seting.

 

[BOster]

Yes, we have been using RCSI for years in both 9.0 and 9.2 and it did solve almost all of our blocking issues. And, I am not even sure that blocking is the problem we have right now and we have not detected any blocking in the DB (and we do monitor for it). Turning off NOLOCK was just one of the big things that we did change as part of our upgrade. Like you I always hated it and strongly advocated for turning it off. I think I have our *very* intermittent PD performance issue tracked down to a BSFN and was hoping I could selectively turn NOLOCK on for the queries that it executes as sort of an isolated test to rule that the NOLOCK change in/out as a suspect.

 

[bfcdn1]

Seeing a lot of SQL Server db performance issues - with JDE on SQL - solved by implementing AOAG. Having those other nodes to off-load the BI workloads obviously helps a ton for oltp & thus overall performance. AOAG comes with other benefits as well. A solid solution for our JDE Clients.

B. Farrell
www.techdevops.com
SQL Server Experts