Am trying to troubleshoot performance on a SQL Server that we have for our document management server and today performance has hit an all time low.
Looking at the Management Data Warehouse (MDW) I setup recently, I can see a huge spike in the Buffer I/O. The combined SQL wait time is in the region of 1500-2000 ms which is considerably higher than normal (around 500ms). The large proportion of this increase is Buffer I/O.
Not being a DBA and doing this out of necessity, I am massively out of my depth.
Is there any way to tell why there has been a large increase in Buffer I/O? Is this likely due to a query pulling large amounts of data or incorrect config of SQL Server memory limits? Is there anything specific (or general for that matter) I should be looking at or DMVs I can query to help troubleshoot?
The server is SQL Server 2008 R2. It is a VM running two vCPUs and 8GB RAM with the disks hosted on a separate array on a SAN.
The graphs above were all generated using SQL Server Management Data Warehouse.