Prophet 21 is slow or freezing: how to find and fix SQL Server blocking

Find the blocker in about ten minutes

1. 1Ask users one question first: is it everyone or one person? Everyone points at the database or server; one person points at the client, a printer, or a single locked record.
2. 2On the SQL Server, run the blocking query below. A session with a blocking_session_id of 0 that appears as the blocker of others is the head blocker.
3. 3Note the head blocker's login_name, host_name, program_name and the SQL text. The host and program tell you whether it is a P21 client, a Crystal report, an integration, or a person in SSMS.
4. 4Check how long it has been running and its wait type. LCK_M_* waits mean lock contention. PAGEIOLATCH_* means disk reads. CXPACKET or CXCONSUMER with high CPU means parallel scans.
5. 5If the head blocker is an open transaction that has been idle for a long time, find the user or integration and have them complete or cancel the window. Kill the session only as a last resort and only after confirming what it was doing.
6. 6Record the statement and the table involved. Repeat offenders are the real fix: a missing index, a report scanning a large table, or an integration that holds a transaction open while it waits on an external call.
7. 7After the incident, review index and statistics maintenance and look at Query Store or Activity Monitor for the top resource consumers over the past day.

The blocking query

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       s.login_name, s.host_name, s.program_name,
       DB_NAME(r.database_id) AS db,
       t.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- Who is the head blocker and what has it got open?
SELECT s.session_id, s.status, s.login_name, s.host_name, s.program_name,
       s.open_transaction_count, s.last_request_start_time
FROM sys.dm_exec_sessions s
WHERE s.session_id = 123;  -- replace with the head blocker

Deadlocks versus blocking

Common pitfalls

• !Killing the head blocker without knowing what it was doing. If it is a posting or invoicing process you can leave data half-processed; check the transaction scope first.
• !Restarting the application server as a routine fix. It hides the offending report or integration, so the same freeze returns tomorrow.
• !Adding indexes to the vendor database without telling Epicor support. Unsupported schema changes can complicate upgrades and support cases; coordinate and document them.
• !Running heavy ad hoc queries from SSMS against production at peak time and then wondering why order entry stalls. Use a reporting copy.
• !Assuming the network or the client PC is the problem when only one workstation is affected. Check for a stuck record lock or a printer hang before touching the database.