Menu
I have a query that is taking a long time in the middle of a transaction. When I get the wait_type of the process it is PAGEIOLATCH_SH.
What does this wait type mean and how can this be resolved?
253
There are two main categories for the SQL Server Wait Statistics; the Single Wait type, where the query is waiting for CPU resource availability and the Resource Wait type, where the query is waiting for a specific resource availability, such as I/O or Memory resources.
The SLEEP_TASK wait means that a thread is waiting on a resource or waiting for some event to occur, and could indicate background task scheduling, a query plan exchange operator that isn't tracked by CXPACKET, or it could be a hashing operation that spills to tempdb.
The SQL Server CXPACKET wait type is a result of parallel query execution and indicates that a session is waiting for the synchronization of threads involved in the parallel process to complete.
CXPACKET and CXCONSUMER are wait types that indicate that work isn't equally balanced. When you see these wait stats on your server, you'll know that SQL Server is running queries in parallel, but not doing a great job of distributing them across available processors.
From Microsoft documentation:
PAGEIOLATCH_SHOccurs when a task is waiting on a latch for a buffer that is in an
I/Orequest. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
In practice, this almost always happens due to large scans over big tables. It almost never happens in queries that use indexes efficiently.
If your query is like this:
Select * from <table> where <col1> = <value> order by <PrimaryKey> , check that you have a composite index on (col1, col_primary_key).
If you don't have one, then you'll need either a full INDEX SCAN if the PRIMARY KEY is chosen, or a SORT if an index on col1 is chosen.
Both of them are very disk I/O consuming operations on large tables.
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
