# SQL Server Wait Types Reference for ERP Databases

> Source: https://netray.co/tools/sql-server-wait-types-erp-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Searchable lookup of common SQL Server wait types with plain-language meaning, typical cause in an ERP workload and the first thing to check.

## Category

developer

## Erp

SQL Server based ERPs

## Audience

- ERP analyst
- DBA
- ERP administrator

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### Wait type

**Key:** wait

**Type:** text

#### Group

**Key:** group

**Type:** text

#### What it means

**Key:** meaning

**Type:** text

#### Typical cause in an ERP workload

**Key:** cause

**Type:** text

#### First thing to check

**Key:** check

**Type:** text

#### Usually benign?

**Key:** benign

**Type:** text

**Rows:**

#### Large scans from reports, missing indexes, too little buffer pool memory, slow data storage.

**Wait:** PAGEIOLATCH_SH

**Group:** Disk I/O

**Meaning:** Task waits for a data page to be read from disk into memory for reading.

**Check:** Read latency per file in sys.dm_io_virtual_file_stats; top queries by physical reads.

**Benign:** No

#### Updates touching pages not in memory, large batch postings, slow data storage.

**Wait:** PAGEIOLATCH_EX

**Group:** Disk I/O

**Meaning:** Task waits for a data page to be read from disk so it can be modified.

**Check:** Same file latency check; look at batch jobs running during the peak.

**Benign:** No

#### Allocation pages being read under heavy insert or tempdb activity.

**Wait:** PAGEIOLATCH_UP

**Group:** Disk I/O

**Meaning:** Waiting for a page being read from disk where an update latch is needed.

**Check:** Tempdb file count and latency; allocation contention.

**Benign:** Depends

#### Slow log volume, thousands of tiny commits from row-by-row integrations.

**Wait:** WRITELOG

**Group:** Transaction log

**Meaning:** Waiting for the transaction log record to be flushed to disk at commit.

**Check:** Log file write latency; batch many rows into one transaction.

**Benign:** No

#### Log flush cannot keep up with the rate of changes.

**Wait:** LOGBUFFER

**Group:** Transaction log

**Meaning:** Waiting for space in the in-memory log buffer to write log records.

**Check:** Log disk latency and throughput; large bulk loads.

**Benign:** No

#### Reports reading rows while a long posting transaction holds exclusive locks.

**Wait:** LCK_M_S

**Group:** Locking

**Meaning:** Waiting for a shared lock because another session holds an incompatible lock.

**Check:** Head blocker via sys.dm_exec_requests; consider read committed snapshot.

**Benign:** No

#### Two sessions updating the same rows or scans that must lock many rows first.

**Wait:** LCK_M_U

**Group:** Locking

**Meaning:** Waiting for an update lock.

**Check:** Blocking chain and the statement held by the head blocker.

**Benign:** No

#### Concurrent updates to the same inventory, order or ledger rows.

**Wait:** LCK_M_X

**Group:** Locking

**Meaning:** Waiting for an exclusive lock.

**Check:** Head blocker, open transaction age, missing index forcing wider locks.

**Benign:** No

#### A higher-level exclusive or schema lock is held, often by a table-level operation.

**Wait:** LCK_M_IS

**Group:** Locking

**Meaning:** Waiting for an intent shared lock at table or page level.

**Check:** Look for lock escalation or index maintenance on the same table.

**Benign:** No

#### A table-level lock from escalation or maintenance blocks new row updates.

**Wait:** LCK_M_IX

**Group:** Locking

**Meaning:** Waiting for an intent exclusive lock at table or page level.

**Check:** sys.dm_tran_locks for table-level locks; escalation settings.

**Benign:** No

#### DDL or an offline index rebuild queued behind running queries, or blocked by them.

**Wait:** LCK_M_SCH_M

**Group:** Locking

**Meaning:** Waiting for a schema modification lock.

**Check:** Reschedule DDL outside peak; check long running queries on the table.

**Benign:** No

#### A schema modification is in progress or queued on the object being queried.

**Wait:** LCK_M_SCH_S

**Group:** Locking

**Meaning:** Waiting for a schema stability lock.

**Check:** Find the session holding or waiting for SCH_M on the same table.

**Benign:** No

#### Parallel plans on large ERP reports, skewed row distribution, default MAXDOP and cost threshold.

**Wait:** CXPACKET

**Group:** Parallelism

**Meaning:** Threads of a parallel query waiting for other threads to finish their share.

**Check:** MAXDOP and cost threshold for parallelism; compare with SOS_SCHEDULER_YIELD.

**Benign:** Depends

#### Normal in parallel plans.

**Wait:** CXCONSUMER

**Group:** Parallelism

**Meaning:** Consumer side of a parallel exchange waiting for rows from producer threads.

**Check:** Only investigate if it dominates alongside slow reports.

**Benign:** Yes

#### CPU pressure from heavy queries, scalar functions or many concurrent reports.

**Wait:** SOS_SCHEDULER_YIELD

**Group:** CPU

**Meaning:** A task used its time slice and voluntarily yielded the CPU.

**Check:** CPU use by query; plan quality; parallelism settings.

**Benign:** Depends

#### Large sorts and hash joins, bad row estimates from stale statistics, too many big reports at once.

**Wait:** RESOURCE_SEMAPHORE

**Group:** Memory

**Meaning:** Query is waiting for a memory grant before it can start.

**Check:** sys.dm_exec_query_memory_grants; update statistics; trim report scope.

**Benign:** No

#### Long blocking chains that pile up sessions, or a connection storm from an integration.

**Wait:** THREADPOOL

**Group:** CPU

**Meaning:** No free worker thread is available to run a new request.

**Check:** Find and clear the head blocker; review max worker threads and connection pooling.

**Benign:** No

#### Last-page insert hotspot on an ever-increasing key, or tempdb allocation contention.

**Wait:** PAGELATCH_EX

**Group:** Latch

**Meaning:** Waiting for an exclusive latch on a page already in memory.

**Check:** Resource description in sys.dm_os_waiting_tasks; tempdb data file count.

**Benign:** Depends

#### Allocation bitmap pages (PFS, GAM) contended in tempdb.

**Wait:** PAGELATCH_UP

**Group:** Latch

**Meaning:** Waiting for an update latch on an in-memory page.

**Check:** Tempdb files of equal size, count suited to CPU cores.

**Benign:** Depends

#### Internal contention such as B-tree root or metadata structures under heavy concurrency.

**Wait:** LATCH_EX

**Group:** Latch

**Meaning:** Waiting for an exclusive latch on a non-buffer internal structure.

**Check:** sys.dm_os_latch_stats to see which latch class dominates.

**Benign:** Depends

#### ERP client fetching a huge result set row by row, slow reporting tool, WAN link.

**Wait:** ASYNC_NETWORK_IO

**Group:** Network

**Meaning:** SQL Server has results ready but the client is not consuming them fast enough.

**Check:** Which application and host; reduce rows returned; fetch in larger blocks.

**Benign:** Depends

#### Spills to tempdb from underestimated sorts, slow tempdb or log volume.

**Wait:** IO_COMPLETION

**Group:** Disk I/O

**Meaning:** Waiting for a non-data-page I/O such as sort spills or log reading.

**Check:** Tempdb latency; spill warnings in execution plans.

**Benign:** Depends

#### Backup or restore running during production hours.

**Wait:** ASYNC_IO_COMPLETION

**Group:** Disk I/O

**Meaning:** Waiting for background asynchronous I/O such as backup, restore or file operations.

**Check:** Backup schedule in msdb; overlap with business peak.

**Benign:** Depends

#### Slow backup target, network share or tape.

**Wait:** BACKUPIO

**Group:** Backup

**Meaning:** Backup is waiting on the backup device to accept data.

**Check:** Backup throughput against the target; consider striping or compression.

**Benign:** Depends

#### Backup target slower than the data volume reads.

**Wait:** BACKUPBUFFER

**Group:** Backup

**Meaning:** Backup is waiting for a buffer to hold data to write.

**Check:** Backup target speed and buffer settings.

**Benign:** Depends

#### Network latency to the secondary or a slow secondary log disk.

**Wait:** HADR_SYNC_COMMIT

**Group:** Availability groups

**Meaning:** Commit waits for the synchronous secondary replica to harden the log.

**Check:** Secondary log write latency and network round trip time.

**Benign:** Depends

#### Queries joining local ERP tables to remote servers, or running CHECKDB.

**Wait:** OLEDB

**Group:** External

**Meaning:** Waiting on an OLE DB call, such as a linked server query. Also used by some DBCC commands.

**Check:** Linked server queries in the plan; move data local or filter remotely.

**Benign:** Depends

#### Heavy ad hoc query compilation filling the plan cache.

**Wait:** CMEMTHREAD

**Group:** Memory

**Meaning:** Contention on a thread-safe memory object.

**Check:** Plan cache size and single-use plans; parameterize generated SQL.

**Benign:** Depends

#### Small autogrowth increments, no instant file initialisation, log growth.

**Wait:** PREEMPTIVE_OS_WRITEFILEGATHER

**Group:** File growth

**Meaning:** Waiting on the operating system while a data or log file grows and is zero-initialised.

**Check:** Autogrowth settings; pre-size files.

**Benign:** Depends

#### A Profiler trace left running against production.

**Wait:** TRACEWRITE

**Group:** Tracing

**Meaning:** Waiting for a trace rowset consumer, typically Profiler, to take events.

**Check:** sys.traces; replace with Extended Events.

**Benign:** No

#### Job or script deliberately delaying.

**Wait:** WAITFOR

**Group:** Idle

**Meaning:** Session is executing a WAITFOR statement.

**Check:** None unless a job should have finished.

**Benign:** Yes

#### Internal housekeeping.

**Wait:** SLEEP_TASK

**Group:** Idle

**Meaning:** Background task sleeping between work.

**Check:** Exclude from wait analysis.

**Benign:** Yes

#### Internal housekeeping.

**Wait:** LAZYWRITER_SLEEP

**Group:** Idle

**Meaning:** Lazy writer is sleeping between buffer pool scans.

**Check:** Exclude from wait analysis.

**Benign:** Yes

#### Internal housekeeping.

**Wait:** CHECKPOINT_QUEUE

**Group:** Idle

**Meaning:** Checkpoint thread waiting for work.

**Check:** Exclude from wait analysis.

**Benign:** Yes

#### Internal timer.

**Wait:** XE_TIMER_EVENT

**Group:** Idle

**Meaning:** Extended Events timer waiting.

**Check:** Exclude from wait analysis.

**Benign:** Yes

#### Internal timer, runs about every 5 seconds.

**Wait:** REQUEST_FOR_DEADLOCK_SEARCH

**Group:** Idle

**Meaning:** Deadlock monitor sleeping between searches.

**Check:** Exclude from wait analysis.

**Benign:** Yes

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use this reference

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Which wait type matters most for ERP performance?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

sql server wait types, pageiolatch_sh meaning, lck_m_x wait type, writelog wait, cxpacket vs cxconsumer, async_network_io erp, sql server wait stats explained, benign wait types sql server

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json