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.
Best Answer
If you look carefully at the waits graph, the coloured bars actually indicate lock waits, not I/O waits.
Also, the I/O graph shows increased system disk usage, not SQL Server disk usage.
Given that the memory graph is approaching your system memory size as the system utilization increases over time, I believe your instance is overcommitted for the current max server memory setting (or the max server memory setting isn't set at all), and one or more of the SQL Server memory pools are swapping to disk.
The solution is to lower the max server memory setting (or set it to an appropriate value if it isn't set). Please see my answer here for a more in-depth answer from a similar scenario, and my blog post:
Is my SQL Server's memory over-committed?