SQL server to JD Edwards user profile

Michael L.

Well Known Member
Hello List. I’m more a ‘400’ competency but now work as an employee in a SQL 2014 environment. We are 9.2, 9.2.1.1, SQL 2014.

Last Friday we had two Production issues.

• Issue # 1 was the TempDB drives ran close to out of capacity very suddenly due to three queries running concurrently each > 5 hours.
• Issue # 2 was we had 30+ wait type LCK_M_U

Keeping in mind we have only ONE proxy user (JDE) and using the examples above, might you share how we can connect the issue(s) from SQL server back to a specific JD Edwards user profile?

Thank in advance for any assistance,

Michael
 
Hello List. I’m more a ‘400’ competency but now work as an employee in a SQL 2014 environment. We are 9.2, 9.2.1.1, SQL 2014.

Last Friday we had two Production issues.

• Issue # 1 was the TempDB drives ran close to out of capacity very suddenly due to three queries running concurrently each > 5 hours.
• Issue # 2 was we had 30+ wait type LCK_M_U

Keeping in mind we have only ONE proxy user (JDE) and using the examples above, might you share how we can connect the issue(s) from SQL server back to a specific JD Edwards user profile?

Thank in advance for any assistance,

Michael

How much tempdb space did you allocate?

How many concurrent E1 users do you have?

How big is your JDE_PRODUCTION database?


30 processes waiting with LCK_M_U means that 30 processes were waiting to update records on a resource that was locked, creating a blocking chain. Run the script below during the blocking to gather information about both the blocked process and the blocking process:


----------------

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO


Tracing the SQL process back to a user is going to be difficult. Chances are this is a UBE and the path back to the user is difficult.

I am betting that the tempdb issue is at the root of this; solve that problem and you may not have to worry about who is running what.
 
Do you run any adhoc queries like Data warehouse or Cognos ?. You might have to look at those too.

Chan
 
Back
Top