1

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.

Results from dm_io_virtual_file_stats

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.

enter image description here

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?
  • Can you run Crystal Disk Mark and share the results? brentozar.com/archive/2012/03/how-fast-your-san-or-how-slow – Erik Darling Nov 28 '18 at 11:13
  • Also, search your error log for "15 seconds" and see if you have any warnings. – Erik Darling Nov 28 '18 at 11:14
  • A SQL Server error log string search of "15 seconds" just came up some database backup and CHECKDB entries. Looking into using Crystal Disk Mark, but this is our production server and I see there is an overhead. Would you recommend we run this test overnight/off-peak? – James D Nov 28 '18 at 11:23
  • If you have to ask... – Erik Darling Nov 28 '18 at 11:59
  • What PerfMon counters we could use in the meantime, whilst we look at scheduling downtime? Are these unlikely to highlight the issue? – James D Nov 28 '18 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.