1

I'm trying to calculate the disk io latency (time required to fetch the page from disk into memory) for a SQL database.

Following are 2 approaches I am exploring:

Approach 1 - Using sys.dm_os_buffer_descriptors :

WITH AggregateBufferPoolUsage AS (
    SELECT
      DB_NAME(database_id) AS [Database Name], 
      CAST(COUNT_BIG(*) * 8/1024.0 AS DECIMAL (15,2)) AS [CachedSize], 
      COUNT(page_id) AS [Page Count], 
      AVG(read_microsec) AS [Avg Read Time (microseconds)] 
    FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) 
    GROUP BY
      DB_NAME(database_id)
)
SELECT
  ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank],
  [Database Name],  
  CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2))
      AS [Buffer Pool Percent], 
  [Page Count],
  CachedSize AS [Cached Size (MB)],
  [Avg Read Time (microseconds)] 
FROM AggregateBufferPoolUsage 
ORDER BY
  [Buffer Pool Rank]
OPTION (RECOMPILE); 

Approach 2 - Using sys.dm_io_virtual_file_stats:

SELECT 
    [ReadLatency] = 
        CASE WHEN [num_of_reads] = 0 
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, 
    [WriteLatency] = 
        CASE WHEN [num_of_writes] = 0 
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END, 
    [Latency] = 
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) 
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END, 
    [AvgBPerRead] = 
        CASE WHEN [num_of_reads] = 0 
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END, 
    [AvgBPerWrite] = 
        CASE WHEN [num_of_writes] = 0 
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END, 
    [AvgBPerTransfer] = 
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) 
            THEN 0 ELSE 
                (([num_of_bytes_read] + [num_of_bytes_written]) / 
                ([num_of_reads] + [num_of_writes])) END, 
    LEFT ([mf].[physical_name], 2) AS [Drive], 
    DB_NAME ([vfs].[database_id]) AS [DB], 
    [mf].[physical_name] 
FROM 
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs] 
JOIN sys.master_files AS [mf] 
    ON [vfs].[database_id] = [mf].[database_id] 
    AND [vfs].[file_id] = [mf].[file_id] 
ORDER BY [WriteLatency] DESC; 

For the database that I am analyzing, using approach 1 - the avg read_micro value shows as 2004 = 0.2ms

Where as using approach 2 - the Average read latency value (for the data file - .mdf) is showing as 12.3ms

Why is there such large gap/difference between the latency calculation using the above approaches?

2 Answers 2

4

Why is there such large gap/difference between the latency calculation using the above approaches?

sys.dm_os_buffer_descriptors includes new BUFs which will have a read time of 0, because they are new from memory and did not have to be read from disk. Conversely, sys.dm_io_virtual_files_stats includes read activity on the file which may be used for more than just BUFs.

If you create a new BUF because you're formatting a new page, it does not get read from disk, it's in memory only (aside from log entries) and won't be flushed to disk until checkpoint or lazywriter. There will be no read time since it was never read from disk. Now you query this DMV and it shows 0 for this BUF and average it, which brings down the overall average.

0
2

sys.dm_os_buffer_descriptors shows information for each page in the buffer pool, when it was last read. You are taking the average per page for pages that are still in the buffer pool, pages which are not in the buffer pool will not show up.

sys.dm_io_virtual_file_stats shows, on a per-file basis, what the total read time is for that file since the server started. This may include reading the same pages multiple times, and it may include long stalls for data which is not in the buffer pool anymore (or has been read again since).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.