Understanding SQL Server PAGEIOLATCH Wait Types

Problem

For ensuring ACID properties, SQL Server must write the data to disk and read when required. Disk IO is comparatively slower than memory and often leads to bottlenecks with concurrent large table scans. Insufficient memory or suboptimal queries can also make the scenario worse and make it harder to identify the root cause. In this tip, we will simulate PAGEIOLATCH stress and check some mitigation techniques and best practices. Let’s start.

Solution

For this article, we will use the free tool SQLQueryStress for PAGEIOLATCH wait type simulation and different DMVs for troubleshooting purposes.

Types of PAGEIOLATCH

PAGEIOLATCH is lightweight and non-configurable locks used by internal processes within SQL Server to manage access to the page buffer in memory. It happens, when SQL Server needs to read data from disk because the data is not already in memory. It puts a latch on the page while fetching it from disk.

PAGEIOLATCH can be one of these:

  • PAGEIOLATCH_KP (Keep latch) – It signals to keep the page in the buffer pool alive thus protects it from destroying it from memory.
  • PAGEIOLATCH_SH (Shared latch) – When SH latch is in place, multiple threads can simultaneously read the page.
  • PAGEIOLATCH_UP (Update latch) – It remains in place until pages are successfully written to disk.
  • PAGEIOLATCH_EX (Exclusive latch) – It blocks other threads from writing to or reading the page.
  • PAGEIOLATCH_DT (Destroy latch) – It must be in place before destroying the page from the buffer pool.

Simulation of PAGEIOLATCH Wait Types

Run the below query using SQLQueryStress to generate a disk IO load on SQL Server (Figure-1).

-- !!! First, clear the DMV. Be cautious about clearing it in production environment.
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
-- Paste below query in SQLQueryStress
CHECKPOINT; -- write all dirty pages to disk and cleans the buffers
DBCC DROPCLEANBUFFERS; -- remove all from buffer pool
DBCC FREEPROCCACHE;
 
SELECT *
FROM Sales.SalesOrderDetail sod
CROSS JOIN Production.TransactionHistory th
WHERE th.TransactionType <> 'W'
OPTION (MAXDOP 1);
sql query stress tool

Figure – 1: Putting stress on SQL Server using SQLQueryStress

Identifying PAGEIOLATCH Wait Types

Use this query to identify all wait types.

To just check PAGEIOLATCH_XX, you can also execute the following query (Figure-2).

SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_time_ms DESC;
pageiolatch wait types

Figure – 2: PAGEIOLATCH_XX wait types

Top Reasons Behind PAGEIOLATCH_* Wait Types

PAGEIOLATCH_* wait types can be prevalent in the following scenarios:

  1. Suboptimal queries with large table scans due to:
    • Missing indexes
    • Outdated statistics
  2. System is starving from memory
  3. System has a really slow disk system

PAGEIOLATCH_* waits due to sub-optimal queries

Most of the performance issues stem from inefficient queries or missing indexes. When queries do large table scans instead of targeted seeks, SQL Server ends up reading too many pages. It causes high physical and logical I/O. This can lead to PAGEIOLATCH_XX.

Look for problems like:

  • Missing indexes
  • Implicit conversions (which block index use)
  • Outdated statistics

The following query will help you find queries with missing indexes and statistics.

-- It will find out queries with missing indexes
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  
    COALESCE(DB_NAME(st.dbid),DB_NAME(CONVERT(INT, qp.dbid))) AS [DatabaseName],
    qs.creation_time AS [PlanCreationTime],
    qs.last_execution_time AS [LastExecutedTime],
    qs.execution_count AS [ExecutionCount],
    qs.total_worker_time / 1000 AS [Total_CPU_Time_ms],
    (qs.total_worker_time / qs.execution_count) / 1000 AS [Avg_CPU_Time_ms],
    (qs.total_worker_time / 1000) AS [Cumulative_CPU_Time_All_Executions_ms],
    (qs.total_logical_reads + qs.total_logical_writes) AS [TotalLogicalIO],
    (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [AvgLogicalIO],
    SUBSTRING(st.text,
              (qs.statement_start_offset / 2) + 1,
              ((CASE qs.statement_end_offset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE qs.statement_end_offset
                 END - qs.statement_start_offset) / 2) + 1) AS [QueryText],
    qp.query_plan AS [ExecutionPlan]
FROM    (
    SELECT  TOP 10 *
    FROM    sys.dm_exec_query_stats 
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.value('count(//p:MissingIndexGroup)', 'int') > 0
GO
 
-- Below query will display statistics info.
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,
dsp.modification_counter,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id
FROM sys.stats s
JOIN sys.stats_columns sc
ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
ORDER BY DaysOld;
GO

PAGEIOLATCH waits due to memory pressure

Sometimes, PAGEIOLATCH_XX wait types are by products of memory pressure in SQL Server.

This happens when:

  • The buffer pool does not have enough memory, or
  • Other processes are overusing memory

In that case, you need to allocate sufficient memory for SQL Server by upgrading physical memory on the server.

The below querywill show system memory, available memory, acquired memory and SQL Server’s target memory.

SELECT
    osm.total_physical_memory_kb / 1024 AS [Total_Physical_Memory_MB],
    osm.available_physical_memory_kb / 1024 AS [Available_Physical_Memory_MB],
    osi.committed_kb / 1024 AS [SQL_Server_Committed_Memory_MB], -- Committed
    osi.committed_target_kb / 1024 AS [SQL_Server_Target_Committed_Memory_MB], -- Target    
osm.system_memory_state_desc
FROM sys.dm_os_sys_memory AS osm
CROSS JOIN sys.dm_os_sys_info AS osi  
GO
ScenarioMeaning
Committed ≈ TargetMemory usage is stable. SQL Server acquired what it needs.
Committed < TargetSQL Server can still grow. Workload or data volume might increase.
Committed > TargetOS level memory pressure. SQL Server may start releasing memory.

Table-1:- Relationship between Committed and Target memory

PAGEIOLATCH waits due to disk subsystem issues

Sometimes, the real reason for PAGEIOLATCH_XX waits is simply slow storage. For example, during database backup time, you may observe slower I/O. However, if you see consistently slow IO, then there could be disk IO related issue.

You can use PerfMon to monitor disk speed using these counters (Figure-3):

  • Avg. Disk sec/Read
  • Avg. Disk sec/Write

If the latency is more than 10 milliseconds, it usually means something is wrong or too slow — though the acceptable value can vary depending on the disk subsystem.

perfmon counters

Figure-3: Perfmon Counters – Avg. Disk sec/Read & Avg. Disk sec/Write

Best Practices Regarding Disk System

  • Use SSD for your SQL Server.
  • Use RAID-10 for your write-heavy workloads and performance-critical systems.
  • Place data files and log files in separate drives (if possible keep them in separate physical disk).
  • Put tempdb in separate high performing SSD drives.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *