disk I/O problem with Redgate

this is a DBA Team ‘episode‘ – very clever but way too long for the useful content.  After we get “don’t make Grant, the Scary DBA, angry, we learn:

The RDBMS is slowing down, which seems to follow an application install

  • Physical Disk\Avg. Disk Reads/sec and Physical Disk\Avg. Disk Writes/sec are slow, over 50ms
  • sys.dm_os_wait_stats shows lots of PAGEIOLATCH_SH waits:  the buffer pages are not being latches rapidly becuase the disk is slow to provide the data
  • fact tables, entity-Attribute-Value (EAV) and ORMs are evidence of lack of minding the DBA
  • sys.dm_os_waiting_tasks will show which tasks are waiting, being blocked or blocking, The resource_description field will show the ID of the blocking page and therefore the table to which that data belongs.
  • Virtual files statistics shows how reads and writes are occuring across files and databases.
  • even if the problem is I/O, a bad application can overwhelm a good filesystem and make I/O become the bottleneck
  • Application developers hate RDBMS and love noSQL, which does not need a DBA