Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 24

Thread: SQL Server Timeout Settings

  1. #1

    SQL Server Timeout Settings

    Hello All,

    We are experiencing a lot of locking on our 9.2 system. In looking for answers, I found a post on the List discussing locking. One reply referenced time out settings. It isn't clear if they are referring to the settings on the HTML server or the enterprise server.

    I don't seem to be able to find Oracle documentation on the JDE.INI settings for the database section like there is for kernels or BI Publisher. Server Manager refers to it as the Bootstrap Datasource section. There are two timeout settings:

    SQL Server Query Timeout - 1
    SQL Server Query Timeout Retries - 17

    Do I change these settings? Currently, on the HTML servers, we have it set for queries to timeout after 30 seconds and retry 3 times. What would be your recommendation for these settings to work well together?

    Thank you for any advice you have,

    Jeremy
    Current: E1 9.2, TR 9.2.2.5, Win2012 R2, SQL Server 2014 R2, Weblogic, OneView, Embedded BIP
    Upgrade: E1 9.1, TR 9.1.4.4, Win2008 R2, SQL Server 2008 R2, WebLogic, OneView, Embedded BIP
    Original: E1 8.0, SP 24.1.2, Win2003, SQL Server 2005 SP3, all fat client

  2. #2
    Member craig_welton's Avatar
    Join Date
    Oct 2000
    Location
    Litchfield, CT
    Posts
    923
    Jeremy,

    Do you have RCSI turned on? When enabled, we have seen that fix SQL Server blocking in E1.

    Craig
    Craig Welton
    PatWel Group Inc.
    http://www.patwel.com
    Home of the FREE JDE Object Browser, JDETrace and NERDup Tools

    E1 9.0 8.98.4.2 Wintel SQL 2008
    E1 9.2 9.2.1.4 iSeries

  3. #3
    Quote Originally Posted by JMast View Post
    Hello All,

    We are experiencing a lot of locking on our 9.2 system. In looking for answers, I found a post on the List discussing locking. One reply referenced time out settings. It isn't clear if they are referring to the settings on the HTML server or the enterprise server.

    I don't seem to be able to find Oracle documentation on the JDE.INI settings for the database section like there is for kernels or BI Publisher. Server Manager refers to it as the Bootstrap Datasource section. There are two timeout settings:

    SQL Server Query Timeout - 1
    SQL Server Query Timeout Retries - 17

    Do I change these settings? Currently, on the HTML servers, we have it set for queries to timeout after 30 seconds and retry 3 times. What would be your recommendation for these settings to work well together?

    Thank you for any advice you have,

    Jeremy
    RCSI is your answer.

  4. #4
    Craig,

    Thanks for the suggestion.

    No we don't have RCSI turned on. I have looked at it in the past, but it seems quite an undertaking to implement correctly. Our system was never really performance tuned, so I am looking for low hanging fruit at this point.

    Jeremy
    Current: E1 9.2, TR 9.2.2.5, Win2012 R2, SQL Server 2014 R2, Weblogic, OneView, Embedded BIP
    Upgrade: E1 9.1, TR 9.1.4.4, Win2008 R2, SQL Server 2008 R2, WebLogic, OneView, Embedded BIP
    Original: E1 8.0, SP 24.1.2, Win2003, SQL Server 2005 SP3, all fat client

  5. #5
    Hello Jeff,

    Thanks for the post. I was hoping for something more immediate as our CSRs are hit hard as F4211 gets hammered.

    Jeremy
    Current: E1 9.2, TR 9.2.2.5, Win2012 R2, SQL Server 2014 R2, Weblogic, OneView, Embedded BIP
    Upgrade: E1 9.1, TR 9.1.4.4, Win2008 R2, SQL Server 2008 R2, WebLogic, OneView, Embedded BIP
    Original: E1 8.0, SP 24.1.2, Win2003, SQL Server 2005 SP3, all fat client

  6. #6
    Member craig_welton's Avatar
    Join Date
    Oct 2000
    Location
    Litchfield, CT
    Posts
    923
    Jeremy,

    I understand it seems like a big change, but it really isn't. Yes, you need to make sure tempdb is big enough. Then it's enabled with one command in single user mode. Those INI settings for retries and timeouts are bandaids around the blocking issue. A client was chasing these issues for a couple of years before taking the RCSI plunge. Once implemented one weekend during server maintenance, the blocking stopped and performance improved overall.

    As a side note, there are some commercial applications that support SQL Server as their repo. I have seen that they require RCSI to be turned on and won't install if it's disabled. Also, Oracle's recommendation in "Advanced Tuning for JD Edwards Enterprise One Implementations" is to turn it on.

    Have faith. It's a proven fix.

    Craig
    Craig Welton
    PatWel Group Inc.
    http://www.patwel.com
    Home of the FREE JDE Object Browser, JDETrace and NERDup Tools

    E1 9.0 8.98.4.2 Wintel SQL 2008
    E1 9.2 9.2.1.4 iSeries

  7. #7
    Quote Originally Posted by JMast View Post
    Hello Jeff,

    Thanks for the post. I was hoping for something more immediate as our CSRs are hit hard as F4211 gets hammered.

    Jeremy
    Are you doing index maintenance?

    Tempdb configuration?

    Statistics maintenance?

  8. #8
    Jeff,

    We do index reorg once a month. Our tempdb configuration - we have 4 core, so we have 4 tempdb files on SSD with plenty of space. Statistics are set to auto for update.

    The locking issues only show up when batch requests/sec are high (4,000+). Unfortunately, I haven't been able to track down all the processes that generate that level of activity, but one that does is R42750. Often, the locking starts as the same user on themselves which I believe to be the CSR submitting the order and then hitting find. It can overflow to other users on common tables like F41002/3. My hope with the settings was to make the read query wait in the neighborhood of 10-20 seconds to see if the R42750 can get done. If the find is bailing after 1 second and retrying 17 times, it could just be adding to the issue.

    Thanks for the advice,

    Jeremy
    Last edited by JMast; 07-05-2018 at 06:18 AM.
    Current: E1 9.2, TR 9.2.2.5, Win2012 R2, SQL Server 2014 R2, Weblogic, OneView, Embedded BIP
    Upgrade: E1 9.1, TR 9.1.4.4, Win2008 R2, SQL Server 2008 R2, WebLogic, OneView, Embedded BIP
    Original: E1 8.0, SP 24.1.2, Win2003, SQL Server 2005 SP3, all fat client

  9. #9
    Quote Originally Posted by JMast View Post
    Jeff,

    We do index reorg once a month. Our tempdb configuration - we have 4 core, so we have 4 tempdb files on SSD with plenty of space. Statistics are set to auto for update.

    The locking issues only show up when batch requests/sec are high (4,000+). Unfortunately, I haven't been able to track down all the processes that generate that level of activity, but one that does is R42750. Often, the locking starts as the same user on themselves which I believe to be the CSR submitting the order and then hitting find. It can overflow to other users on common tables like F41002/3. My hope with the settings was to make the read query wait in the neighborhood of 10-20 seconds to see if the R42750 can get done. If the find is bailing after 1 second and retrying 17 times, it could just be adding to the issue.

    Thanks for the advice,

    Jeremy
    Alright, 4k batches/s is pretty high. If you have a system where batches/s consistently is above 1k/s and peaks at 3-4k/s you really should look into RCSI. If you need someone to implement, let me know.

    Index reorg or defrag? Once a month seems pretty long to wait, I generally defrag weekly. Depending on the size of your database you may need to use a selective defrag method.

    Stats should be manually updated as a part of maintenance on an E1 system. Auto update just doesn't cut it.


    Check your fragmentation levels with this: http://jeffstevenson.karamazovgroup....on-in-all.html

    Are your data files/transaction log files also on SSD?

  10. #10
    4k is actually low. Most of today, for example, has been around 6k. Thanks for the offer, I would curious to know what you think would be involved - send me a private message, if you want. We are planning a downtime to add RAM in the next couple weeks, so I hope to have RCSI sorted out for that window.

    Index Rebuild. I had not heard of updating stats manually, I will have to look at the benefits of that.

    All of our drives are SSD.

    Thanks for the tips.
    Current: E1 9.2, TR 9.2.2.5, Win2012 R2, SQL Server 2014 R2, Weblogic, OneView, Embedded BIP
    Upgrade: E1 9.1, TR 9.1.4.4, Win2008 R2, SQL Server 2008 R2, WebLogic, OneView, Embedded BIP
    Original: E1 8.0, SP 24.1.2, Win2003, SQL Server 2005 SP3, all fat client

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.