Incurs_seek_penalty column of sys.dm_os_volume_stats

A new column, incurs_seek_penalty, was introduced in the SQL Server 2019 sys.dm_os_volume_stats DMF. This tinyint column is not currently documented.

Does anyone have information about the enumerated values returned in this column? I've seen values of 0 (bare metal server) and 2 (VM). I'm curious as to what these values mean and what other values might be returned. My internet search hasn't turned up anything.

This is now officially documented, with the following values:

Indicates the type of storage supporting this volume. Possible values are:

0: No seek penalty on this volume, typically when the storage device
is PMM or SSD

1: Seek penalty on this volume, typically when the storage device is
HDD

2: The storage type can't be determined when the volume is on a UNC
path or mounted shares

NULL: The storage type can't be determined on Linux operating system

Applies to: SQL Server (starting with SQL Server 2019 (15.x))

It looks like this is related to the low level DEVICE_SEEK_PENALTY_DESCRIPTOR structure (thanks, LowlyDBA!) - possibly in an attempt to check if a drive is an SSD or not (that approach is also discussed on Stack Overflow).

It would still be interesting to know if this information about the storage is (or will be) used by SQL Server (e.g., for updated I/O cost estimates, or performance optimizations inside the storage engine).

StackExchange Database Administrators Q#271727, answer score: 6