We migrated our platform to new SQL Server 2016 instances a couple of nights ago. Immediately following the migration, we ran sp_Blitz again, but noticed the following entry, which relates to the dedicated volume holding tempdb (all eight data files and the single log file reside on the same dedicated volume). All of the database volumes are using SSDs so this is somewhat surprising:
Slow Storage Writes on Drive T Writes are averaging longer than 100ms for at least one database on this drive. For specific database file speeds, run the query from the information link.
No link was provided, but delving into the code, we found that sp_Blitz uses dm_io_virtual_file_stats to assess the following logic:
o_stall_write_ms / ( 1.0 + num_of_writes ) ) > 100 AND num_of_writes > 100000
Using this logic, the function points to all eight of our tempdb data files, with write speeds of between 164-198ms, which suggests we have a serious issue.
To prove the issue to management, we have since run Performance Monitor traces to review the "Avg. Disk sec/Write" counter for all volumes attached to the server, but the T volume did not appear to be any slower than the other volumes during the working-hours time frame we sampled.
We have since run CrystalDiskMark against the OS, Data, Log and tempdb volumes on the secondary (log shipped) server, which uses the same SAN storage as the server which raised the sp_Blitz warning. All storage volumes show very similar figures to the below.
QUESTIONS:
- Are the CrystalDiskMark speeds expected figures for SSDs, and are they consistent with the warnings in sp_Blitz?
- Are we using the wrong PerfMon counter to prove this? If so, what counter should we use?
- Is the above function showing us live speeds, or just the worst speed it ever encountered (i.e. should we ignore this warning, unless it is still there after the next server restart)?
- Could anyone please provide further insight/advice?
