Developer ToolsFree referencePDF, Excel, Word exportSQL Server based ERPs

SQL Server Wait Types Reference for ERP Databases

When an ERP screen hangs or a posting job crawls, the fastest clue on SQL Server is what the session is waiting for. This reference turns the cryptic wait type name into a meaning, a likely ERP cause and a first check.

Download:

Wait type	Group	What it means	Typical cause in an ERP workload	First thing to check	Usually benign?
PAGEIOLATCH_SH	Disk I/O	Task waits for a data page to be read from disk into memory for reading.	Large scans from reports, missing indexes, too little buffer pool memory, slow data storage.	Read latency per file in sys.dm_io_virtual_file_stats; top queries by physical reads.	No
PAGEIOLATCH_EX	Disk I/O	Task waits for a data page to be read from disk so it can be modified.	Updates touching pages not in memory, large batch postings, slow data storage.	Same file latency check; look at batch jobs running during the peak.	No
PAGEIOLATCH_UP	Disk I/O	Waiting for a page being read from disk where an update latch is needed.	Allocation pages being read under heavy insert or tempdb activity.	Tempdb file count and latency; allocation contention.	Depends
WRITELOG	Transaction log	Waiting for the transaction log record to be flushed to disk at commit.	Slow log volume, thousands of tiny commits from row-by-row integrations.	Log file write latency; batch many rows into one transaction.	No
LOGBUFFER	Transaction log	Waiting for space in the in-memory log buffer to write log records.	Log flush cannot keep up with the rate of changes.	Log disk latency and throughput; large bulk loads.	No
LCK_M_S	Locking	Waiting for a shared lock because another session holds an incompatible lock.	Reports reading rows while a long posting transaction holds exclusive locks.	Head blocker via sys.dm_exec_requests; consider read committed snapshot.	No
LCK_M_U	Locking	Waiting for an update lock.	Two sessions updating the same rows or scans that must lock many rows first.	Blocking chain and the statement held by the head blocker.	No
LCK_M_X	Locking	Waiting for an exclusive lock.	Concurrent updates to the same inventory, order or ledger rows.	Head blocker, open transaction age, missing index forcing wider locks.	No
LCK_M_IS	Locking	Waiting for an intent shared lock at table or page level.	A higher-level exclusive or schema lock is held, often by a table-level operation.	Look for lock escalation or index maintenance on the same table.	No
LCK_M_IX	Locking	Waiting for an intent exclusive lock at table or page level.	A table-level lock from escalation or maintenance blocks new row updates.	sys.dm_tran_locks for table-level locks; escalation settings.	No
LCK_M_SCH_M	Locking	Waiting for a schema modification lock.	DDL or an offline index rebuild queued behind running queries, or blocked by them.	Reschedule DDL outside peak; check long running queries on the table.	No
LCK_M_SCH_S	Locking	Waiting for a schema stability lock.	A schema modification is in progress or queued on the object being queried.	Find the session holding or waiting for SCH_M on the same table.	No
CXPACKET	Parallelism	Threads of a parallel query waiting for other threads to finish their share.	Parallel plans on large ERP reports, skewed row distribution, default MAXDOP and cost threshold.	MAXDOP and cost threshold for parallelism; compare with SOS_SCHEDULER_YIELD.	Depends
CXCONSUMER	Parallelism	Consumer side of a parallel exchange waiting for rows from producer threads.	Normal in parallel plans.	Only investigate if it dominates alongside slow reports.	Yes
SOS_SCHEDULER_YIELD	CPU	A task used its time slice and voluntarily yielded the CPU.	CPU pressure from heavy queries, scalar functions or many concurrent reports.	CPU use by query; plan quality; parallelism settings.	Depends
RESOURCE_SEMAPHORE	Memory	Query is waiting for a memory grant before it can start.	Large sorts and hash joins, bad row estimates from stale statistics, too many big reports at once.	sys.dm_exec_query_memory_grants; update statistics; trim report scope.	No
THREADPOOL	CPU	No free worker thread is available to run a new request.	Long blocking chains that pile up sessions, or a connection storm from an integration.	Find and clear the head blocker; review max worker threads and connection pooling.	No
PAGELATCH_EX	Latch	Waiting for an exclusive latch on a page already in memory.	Last-page insert hotspot on an ever-increasing key, or tempdb allocation contention.	Resource description in sys.dm_os_waiting_tasks; tempdb data file count.	Depends
PAGELATCH_UP	Latch	Waiting for an update latch on an in-memory page.	Allocation bitmap pages (PFS, GAM) contended in tempdb.	Tempdb files of equal size, count suited to CPU cores.	Depends
LATCH_EX	Latch	Waiting for an exclusive latch on a non-buffer internal structure.	Internal contention such as B-tree root or metadata structures under heavy concurrency.	sys.dm_os_latch_stats to see which latch class dominates.	Depends
ASYNC_NETWORK_IO	Network	SQL Server has results ready but the client is not consuming them fast enough.	ERP client fetching a huge result set row by row, slow reporting tool, WAN link.	Which application and host; reduce rows returned; fetch in larger blocks.	Depends
IO_COMPLETION	Disk I/O	Waiting for a non-data-page I/O such as sort spills or log reading.	Spills to tempdb from underestimated sorts, slow tempdb or log volume.	Tempdb latency; spill warnings in execution plans.	Depends
ASYNC_IO_COMPLETION	Disk I/O	Waiting for background asynchronous I/O such as backup, restore or file operations.	Backup or restore running during production hours.	Backup schedule in msdb; overlap with business peak.	Depends
BACKUPIO	Backup	Backup is waiting on the backup device to accept data.	Slow backup target, network share or tape.	Backup throughput against the target; consider striping or compression.	Depends
BACKUPBUFFER	Backup	Backup is waiting for a buffer to hold data to write.	Backup target slower than the data volume reads.	Backup target speed and buffer settings.	Depends
HADR_SYNC_COMMIT	Availability groups	Commit waits for the synchronous secondary replica to harden the log.	Network latency to the secondary or a slow secondary log disk.	Secondary log write latency and network round trip time.	Depends
OLEDB	External	Waiting on an OLE DB call, such as a linked server query. Also used by some DBCC commands.	Queries joining local ERP tables to remote servers, or running CHECKDB.	Linked server queries in the plan; move data local or filter remotely.	Depends
CMEMTHREAD	Memory	Contention on a thread-safe memory object.	Heavy ad hoc query compilation filling the plan cache.	Plan cache size and single-use plans; parameterize generated SQL.	Depends
PREEMPTIVE_OS_WRITEFILEGATHER	File growth	Waiting on the operating system while a data or log file grows and is zero-initialised.	Small autogrowth increments, no instant file initialisation, log growth.	Autogrowth settings; pre-size files.	Depends
TRACEWRITE	Tracing	Waiting for a trace rowset consumer, typically Profiler, to take events.	A Profiler trace left running against production.	sys.traces; replace with Extended Events.	No
WAITFOR	Idle	Session is executing a WAITFOR statement.	Job or script deliberately delaying.	None unless a job should have finished.	Yes
SLEEP_TASK	Idle	Background task sleeping between work.	Internal housekeeping.	Exclude from wait analysis.	Yes
LAZYWRITER_SLEEP	Idle	Lazy writer is sleeping between buffer pool scans.	Internal housekeeping.	Exclude from wait analysis.	Yes
CHECKPOINT_QUEUE	Idle	Checkpoint thread waiting for work.	Internal housekeeping.	Exclude from wait analysis.	Yes
XE_TIMER_EVENT	Idle	Extended Events timer waiting.	Internal timer.	Exclude from wait analysis.	Yes
REQUEST_FOR_DEADLOCK_SEARCH	Idle	Deadlock monitor sleeping between searches.	Internal timer, runs about every 5 seconds.	Exclude from wait analysis.	Yes

Names and meanings follow Microsoft documentation for sys.dm_os_wait_stats. Behaviour varies by version, and a high wait only matters when it dominates during the time users complain. Filter the idle waits out before ranking.

Optional wait analysis review

Send us your top wait output and we will point out what to check first for your ERP.

Optional. The tool and every export work without this. No spam.

How to use this reference

Run a query against sys.dm_os_wait_stats, or sys.dm_exec_requests for live sessions, and note the top wait types by total wait time. Search the table here for each name and read the cause and first check columns. Always compare waits taken over the period when users complained, not since server start, because cumulative numbers include months of quiet time and maintenance windows. Take two snapshots a few minutes apart and subtract them to see what the server waited on right now.

Search by wait name or by group such as Locking or Disk I/O
Ignore waits marked benign when ranking
Use the group to choose between storage, query and blocking work

Reading waits in an ERP workload

ERP databases mix short transactional writes with long reporting reads. That mix shows up as lock waits when a report reads rows that a posting job is changing, and as PAGEIOLATCH waits when reports scan tables bigger than memory. WRITELOG points to commit volume, usually from integrations that commit each row. ASYNC_NETWORK_IO often blames the database for a slow client. Match the dominant group to the symptom: slow screens suggest locking, slow reports suggest I/O or memory grants, slow integrations suggest log writes.

Benign waits and parallelism

Many waits are SQL Server resting. LAZYWRITER_SLEEP, SLEEP_TASK, XE_TIMER_EVENT and similar entries pile up time constantly and say nothing about performance. CXPACKET and CXCONSUMER belong to parallel plans: some is normal, and CXCONSUMER on its own is generally harmless. CXPACKET only deserves attention when parallel queries are slow and CPU is saturated, in which case review MAXDOP and the cost threshold for parallelism. The default cost threshold of 5 is low for modern hardware and lets small queries go parallel.

Common mistakes

The most frequent mistake is tuning the biggest number on a fresh server. Others are clearing wait stats with DBCC SQLPERF and losing history, adding indexes against a lock wait that is really a long open transaction, and blaming storage for ASYNC_NETWORK_IO. A wait is a symptom, so confirm with a second signal such as file latency, blocking chains or CPU before changing anything. Test changes on a copy of the ERP database first, since vendors sometimes support only specific settings.

Frequently Asked Questions

Which wait type matters most for ERP performance?

There is no single one. Lock waits (LCK_M_*) explain frozen screens, PAGEIOLATCH explains slow reports, and WRITELOG explains slow posting and integrations. Rank by total wait time over the period users complained, after removing idle waits.

Is CXPACKET always a problem?

No. It appears in any parallel plan and some is normal. It matters when parallel queries are slow and CPU is saturated. CXCONSUMER on its own is generally harmless, so do not tune it without another symptom.

How do I reset wait statistics?

DBCC SQLPERF with the clear option resets sys.dm_os_wait_stats, and a service restart does too. It is better to snapshot twice and subtract, so you keep the history.

What does a high ASYNC_NETWORK_IO mean?

SQL Server has rows ready but the client is slow to take them. Look at the application or reporting tool, the volume of rows returned and the network path before changing anything on the server.

If you want ERP-aware wait and blocking analysis that names the screen or job behind the wait, we can set that up against your read-only replica.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools