# Prophet 21 is slow or freezing: how to find and fix SQL Server blocking > Source: https://netray.co/erp-help/p21-slow-performance-sql-server-blocking-fix > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer When Prophet 21 hangs for many users at once, the cause is usually one SQL Server session blocking others, or a long-running report holding locks on hot tables such as order and inventory location rows. Find the head blocker with sys.dm_exec_requests, identify the statement and the P21 client or report behind it, and fix that, rather than restarting the application server. ## Kind how-to ## System epicor ## Erp Epicor Prophet 21 ## Module Administration / SQL Server performance ## Query Prophet 21 running slow order entry freezing SQL Server blocking ## Alternate Queries - P21 order entry slow - Prophet 21 SQL Server blocking deadlock - P21 screens hang when many users are in - how to find blocking query in Prophet 21 database ## Applies To Prophet 21 on Microsoft SQL Server (on-prem or hosted). The DMV queries apply to any supported SQL Server version; screen names vary by P21 release. ## Steps ### Find the blocker in about ten minutes **Items:** Ask 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. On 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. Note 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. Check 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. If 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. Record 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. After the incident, review index and statistics maintenance and look at Query Store or Activity Monitor for the top resource consumers over the past day. ## Sections ### Why Prophet 21 slows down for everyone at once **Body:** Prophet 21 is a SQL Server application. Client windows, the application server, reports, and integrations all end up as SQL sessions against one database. Distribution workloads are hot spots by nature: the same item and location rows are read and updated by order entry, receiving, picking, and invoicing all day. When a long transaction or a large scan touches those rows, everything behind it queues. The pattern users describe is consistent: one window saves and spins, then several more freeze, then the whole branch complains. That is the signature of a lock chain, not a general capacity shortage. A true capacity problem looks different: everything is uniformly slow, CPU or disk is saturated, and no single session is at the head of a chain. Separate the two before changing anything. A blocking chain is fixed by removing the head blocker. A capacity problem is fixed by indexing, query changes, or hardware. Restarting services clears both temporarily and teaches you nothing. ### The blocking query **Body:** This query lists every request that is currently waiting on another session, with the statement it is trying to run. Run it in SSMS with permission to view server state. Look for the session IDs that appear in blocking_session_id but are not themselves waiting. That is the head blocker. Then query sys.dm_exec_sessions for that session to get login, host and program, and sys.dm_exec_input_buffer to see its last statement. If the head blocker shows as sleeping with an open transaction, a client or integration started a transaction and never finished it. **Code:** 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 ### Common culprits in a distribution database **Body:** Reports that scan the transaction tables are the most frequent cause. A Crystal or SSRS report with a wide date range against order line or invoice line, run at 10am by someone in finance, can hold shared locks or push the buffer cache out for everyone. Move heavy reporting to a read-only replica, a nightly snapshot, or at least to off-peak hours, and make sure report filters use indexed columns. Custom integrations are second. An EDI import, an eCommerce order feed, or a warehouse automation interface that opens a transaction, calls an external service, and then commits will hold locks for as long as that call takes. The fix is in the integration: keep transactions short and do external calls outside them. Third, stale statistics and missing maintenance. If index rebuilds and statistics updates are not running, plans degrade slowly over months and users feel it as 'the system is slower than last year'. Confirm a maintenance job exists and has been succeeding. ### Deadlocks versus blocking **Body:** Blocking is one session waiting for another to finish. A deadlock is two sessions each waiting for the other, and SQL Server resolves it by killing one as the victim. Users then see an error about being chosen as a deadlock victim and the transaction is rolled back. If you see deadlock errors rather than freezes, capture the deadlock graph from the system_health Extended Events session. It names both statements and the objects involved, which is what you need to decide whether to change an index, change access order in custom code, or retry the operation. Do not enable snapshot isolation options on the P21 database without checking with Epicor support first. It changes locking behavior and tempdb load for the whole application, and supported configuration matters here. ## 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. ## Ai Angle An assistant grounded in your Prophet 21 database can take 'why is order entry hanging' and run the read-only DMV checks for you, showing the blocking chain, the head blocker's host and program, and the statement involved, with the query visible so your DBA can verify it. It can also compare today's top resource consumers against last week. ERPray is read-only by default, so it diagnoses and explains but does not kill sessions or change indexes; those stay with your DBA. ## Faq ### Is it safe to kill a blocking session in Prophet 21? **Answer:** Only after you know what it is doing. Killing an idle report session is usually harmless. Killing a posting, receiving, or invoicing session rolls back its transaction and can leave a document needing to be redone. Identify the program and statement first, ask the user, and prefer asking them to finish or cancel. ### How do I tell blocking from a general capacity problem? **Answer:** Blocking shows sessions waiting on LCK_M_* waits with a blocking_session_id pointing at another session. Capacity problems show high CPU, disk latency, or memory pressure across the board with no single head blocker. Check the DMV query first, then server counters for CPU, memory, and disk latency. ### Why is it slow only at certain times of day? **Answer:** Usually a scheduled job or recurring report. Match the slow periods against SQL Agent jobs, integration schedules, and when finance or purchasing run their large reports. Query Store or a saved log of the blocking query will show which statement appears at those times. ### Should I move reporting off the production database? **Answer:** For heavy reporting, yes. A read-only copy refreshed nightly, or a replica where licensing allows, keeps large scans away from order entry. It also lets analysts query freely. The trade-off is that reports are as current as the last refresh. ## Search terms prophet 21 slow, p21 order entry slow, p21 sql server blocking, prophet 21 freezing, p21 deadlock victim, p21 performance tuning, sys.dm_exec_requests blocking, prophet 21 database performance, p21 head blocker, epicor p21 hangs --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json