How to resolve PAGEIOLATCH_ and PAGELATCH_ in SQL Server

Leave a reply

Resolving PAGEIOLATCH_ and PAGELATCH_ waits in SQL Server means fixing issues with I/O and page conflicts. PAGEIOLATCH_ waits happen when SQL Server is waiting for a page to be read from or written to disk.

Check the queries causing I/O wait:

SELECT TOP 10
    qs.sql_handle,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_physical_reads,
    qs.total_elapsed_time,
    qs.total_worker_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_logical_writes / qs.execution_count AS avg_logical_writes,
    qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qs.total_worker_time / qs.execution_count AS avg_worker_time,
    SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS query_text
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
    qs.total_physical_reads DESC;

Solution:

  1. Rebuild or reorganize fragmented indexes to improve query performance.
ALTER INDEX ALL ON YourTableName REBUILD;

2. Create the mixing index on the tables by checking estimated execution plan.

SELECT
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) AS Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS TableName,
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '')
+ '] ON ' + dm_mid.statement
+ ' (' + ISNULL(dm_mid.equality_columns, '')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL(dm_mid.inequality_columns, '')
+ ')'
+ ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM
sys.dm_db_missing_index_groups dm_mig
INNER JOIN
sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
WHERE
dm_mid.database_ID = DB_ID()
ORDER BY
Avg_Estimated_Impact DESC;

3. Increase the amount of memory available to SQL Server to reduce the need for
disk I/O.

To increase the memory allocated to SQL Server, follow these steps:

  1. Open SQL Server Management Studio (SSMS).
  2. Connect to your SQL Server instance.
  3. Right-click on the server name in Object Explorer and select Properties.
  4. Navigate to the “Memory” page.

Here, you can adjust the following settings:

  • Minimum server memory (in MB): Set the minimum amount of memory that SQL Server will not release.
  • Maximum server memory (in MB): Set the maximum amount of memory that SQL Server can use.

Example:

If you want to set the maximum server memory to 16 GB, you can do it through SSMS as described above or by using the following T-SQL command:

Copy the codeEXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 16384; -- 16 GB
RECONFIGURE;

Using commands:

Check the SQL Server memory usage:

SELECT 
    physical_memory_in_use_kb / 1024 AS SQLServerMemoryInMB,
    large_page_allocations_kb / 1024 AS LargePageAllocationsInMB,
    locked_page_allocations_kb / 1024 AS LockedPageAllocationsInMB,
    total_virtual_address_space_kb / 1024 AS TotalVASInMB,
    virtual_address_space_reserved_kb / 1024 AS VASReservedInMB,
    virtual_address_space_committed_kb / 1024 AS VASCommittedInMB,
    virtual_address_space_available_kb / 1024 AS VASAvailableInMB,
    page_fault_count AS PageFaultCount,
    memory_utilization_percentage AS MemoryUtilizationPercentage,
    available_commit_limit_kb / 1024 AS AvailableCommitLimitInMB,
    process_physical_memory_low AS IsPhysicalMemoryLow,
    process_virtual_memory_low AS IsVirtualMemoryLow
FROM sys.dm_os_process_memory;

If you want to set SQL Server memory area to 16GB, then use:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 16384; -- 16 GB
RECONFIGURE;

4. Distribute data files across multiple disks to balance the I/O load.

Leave a Reply