Dare I ask a dumb question, but… have you guys rebooted the box? (boxes SQL + APPServer)
My turn for a dump question. Where is that setting?
Not since Sunday when Aaron was working on it.
Here’s a great article to read: You experience performance issues in applications and services when the system file cache consumes most of the physical RAM - Microsoft Support
I think Jose covered the REQUESTS_QUEUED and ACTIVE_REQUESTS, correct?
Yup it’s cleared out. MrpQueue has 47rows, MrpProc 11, and MrpProcQueue 43
Another dumb question, do you have any other LOB (Line of Business) applications, other than Epicor, running on this server?
Thanks for the link to that thread, very interesting read. It doesn’t look like a Cache issue, at least according to the article:
Just Epicor, it’s a combined SQL / AppServer.
1 Like
With the app server running (wild) run SP_WHO and or SP_WHO2 in the SQL Server
Any locking?
Or you could run this:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
on Test, that query return, 45,126 rows. 
#1 - In the tracing turn OFF Triggers. Noise levels are huge because Triggers are hit a LOT (Why I try to minimize Data Triggers).
Next, I could not follow the thread. I could not tell is you stopped Task Agent and recycled App Server to get a fresh baseline with no TA activity - what happened. That would be a helpful trace to see what Operations were occurring when the server took off again.
2 Likes
You shouldn’t have any rows if you’re referring to my script, Randy.
Jose, the status on these two show no locks. Both return over 700 rows for TEST db, but the status results are:
1 Like
There should be NO rows on this LoL if you are returning 42K rows… then stop the presses and reboot that SQL box lol
Try:
use Your Epicor database here
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
Correct, but lets see how many of those locks are on the Epicor DB first.
1 Like
maybe try upgrading to server 2016?
https://www.microsoft.com/en-us/cloud-platform/windows-server-2008
Bart, Aaron was working on this primarily but had to step away so asked me to take over. I’ve turned off Triggers and restarted the TEST app server.
Ah, I knew that but forgot to put that in first. 0 returns on Test DB.
We’re working on it. 