SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Pageiolatch_ex and how to reduce it

08 December,2012 by Jack Vamvas

SQL Server Books online defines the SQL wait type pageiolatch_ex as “Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.“

The underlying causes typically relate to disk to memory , memory pressure and disk IO subsystem issues such as caching problems.

 The following scenario illustrates how this wait type arises. A user process will request some data which is not currently in the buffer cache. At that  point – SQL Server will attempt to allocate a buffer page – and while the data is moved from disk to buffer cache an exclusive PageIOLatch_ex is created on the buffer. At the same time a PageIOLatch_sh is created on the buffer from the user process perspective.

When data is written to cache the PageIOLatch_ex is released. As the user still has the PageIOLatch_sh – the user can read the data.

How To Troubleshoot : Pageiolatch_ex

1)  PageIOLatch_ex and PageIOLatch_sh  are often reported as high – if so, disk io subsystem will be your first target

2) The disk spin rate  should not be the focus – but rather the workload. Therefore , for testing – run the workload and monitor disks

3) It’s not just slow IO subsystems relative to workload but also overworked IO subsystems that can be the cause.

4) General database tuning can help – but a single transaction is defined by : amount\speed of disks , memory amount availability , processor speed.

5) Index management

6) Differentiate between disk issues and memory issues – afterall ,if memory is experiencing pressure – than dealing with incoming data will be slower

7) Logical drive profiles – separating random file access with sequential files

Read More

SQL Server - Disk IO performance and SQLIO - SQL Server DBA

Calculate disk IO throughput and MB per second - SQL Server DBA

SQL Server – Logical Disk Read Bytes/sec and disk io - SQL

IO_completion on disk solved with Wait Stats and Perfmon


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer