Get-sqmFileLatencyReport
PerformancesqmSQLTool v1.9.169+ · New🔓 VIEW SERVER STATE required
sys.dm_io_virtual_file_stats together with sys.master_files and calculates the average read and write latency of every database file, plus MB read and written and the drive or mount point. A per-drive summary shows which volume is slow.Cumulative (default): values since the last SQL Server start. Good for the overall picture, but a short slow phase disappears in a long uptime.
Sample (
-SampleSeconds): two snapshots N seconds apart, the values are the difference. Shows what the storage delivers right now, for example during the nightly batch.Rating: data files OK up to 20 ms, Warning up to 50 ms, above Critical. Log writes OK up to 5 ms, Warning up to 15 ms. Files with fewer than
-MinIo I/Os are listed with n/a, because a handful of slow I/Os on an idle file says nothing. The latency SQL Server sees covers the whole path (driver, HBA, SAN, cache); compare with the Windows counters Avg. Disk sec/Read and sec/Write to locate the cause.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Target SQL Server instance. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. |
| -Database | string[] | Optional | all incl. tempdb | Only files of these databases (wildcards). |
| -SampleSeconds | int (0-3600) | Optional | 0 | 0 = cumulative since start, otherwise live sample over that many seconds. |
| -MinIo | long | Optional | 1000 / 10 | Minimum I/Os before a file is rated: 1000 cumulative, 10 in sample mode. |
| -OutputPath | string | Optional | <config>\FileLatencyReport | Directory for CSV and HTML. |
| -NoReport | switch | Switch | $false | Do not write CSV/HTML, only return objects. |
| -NoOpen | switch | Switch | $false | Do not open the HTML report after creation. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately instead of Write-Error. |
Execution Flow
Return Value
PSCustomObject per file, worst first: DatabaseName, LogicalName, FileType, Drive, Reads, AvgReadMs, ReadRating, MBRead, Writes, AvgWriteMs, WriteRating, MBWritten, Rating, SizeMB, PhysicalName, Mode.
Examples
Latency since the last start
Get-sqmFileLatencyReport -SqlInstance "SQL01"
What does the storage deliver during the batch right now (60 s sample)
Get-sqmFileLatencyReport -SqlInstance "SQL01" -SampleSeconds 60
Only critical files
Get-sqmFileLatencyReport -SqlInstance "SQL01" -NoReport | Where-Object Rating -eq 'Critical'