SQL Server Timeout Settings

JMast

Reputable Poster
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
 
Jeremy,

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

Craig
 
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.
 
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
 
Hello Jeff,

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

Jeremy
 
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
 
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?
 
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:
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.com/2008/09/determine-index-fragmentation-in-all.html

Are your data files/transaction log files also on SSD?
 
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.
 
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.

Just out of curiosity, could you post the results of this query?

Code:
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'QDS_SHUTDOWN_QUEUE',
	N'SP_SERVER_DIAGNOSTICS_SLEEP') 
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO
 
Here are the results.
 

Attachments

  • Wait Stats 180706.zip
    59.7 KB · Views: 4
Here are the results.

Ahhh, should have given you the query that ignored the QDS stuff. Try this:

Code:
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
       100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
 
        -- Maybe uncomment these four if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
 
        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
 
        -- Maybe uncomment these six if you have AG issues
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
 
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
        N'ONDEMAND_TASK_QUEUE',
        N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_RECOVERY',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO
 
Ok, here are the results.
 

Attachments

  • Wait Stats 180709.zip
    6.7 KB · Views: 2
Ok, I can't believe I am posting this - I can't find the Advanced Tuning for JDEdwards Enterprise One Implementations" guide on support or docs.oracle.com. Can someone provide a document number or other tip on where this document resides? Thank you.
 
Well, now that would explain it. It didn't occur to me that it would be a book. Craig phrased it as "Oracle's recommendation is" and I was thinking Oracle document.

Thanks for the tip.
 
Back
Top