Continuing my efforts on penning down some notes on SQL Server IO, here is the most critical (probably) weapon in your arsenal – PAGEIOLATCH wait type – identifying workloads that are waiting on page read IO completion. There are many IO operations (and of different sizes) in SQL Server. One of the most common is reading data pages (8 KB) from the disk (when they are not already in the memory). This is physical IO. And as the data page is read, a latch is acquired to protect the physical integrity of the page. If your SELECT query is waiting to complete this operation due to IO bottleneck, then most likely, your query is waiting on PAGEIOLATCH wait type. In other words, PAGEIOLATCH wait type indicates a delay in reading data pages from the disk. Capturing this wait type can give you a lot of insights related to IO slowness. But yes, you need to know which queries are causing this wait type. The cumulative wait stats that you get from sys.dm_os_wait_stats might not be very helpful if you want to deeply diagnose the problem. You got to capture query-level wait stats using Extended Events. Sounds interesting? Watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.
Happy Learning.
Leave A Comment