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/secand
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