Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Am trying to troubleshoot performance on an SQL server that we have for our document management server and today performance has hit an all time low. Looking at the 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 neccessity 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 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 2008 R2 as is SQL. It is a VM running two vCPUs and 8GB RAM with the disks hosted on a separate array on a SAN.

enter image description here

EDIT: I think I may have misread this graph on the server. I actually think it is Lock Wait that has increased dramatically not Buffer I/O. The colours are far too similar for me to pick out at a glance :(

share|improve this question
How did you create these performance reports/graphs? – Alex Marshall Sep 13 '13 at 16:07
Hi Alex, these were all generated using the Management Data Warehouse that you can deploy since 2008 I think. There is a whole wizard to kick you off but this link should help: – Tim Alexander Sep 16 '13 at 9:03
up vote 6 down vote accepted

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.

share|improve this answer
@Tim: Did you restart the box after making the max server memory setting change? If not, it can take time for pages to be swapped out of the page file and back into memory (they will never go back to disk if things are set appropriately). I have a blog post coming out tomorrow on this topic that goes into more detail; I'll comment-reply you with a link to it when it's live. – Jon Seigel Mar 18 '13 at 13:20
@Tim: Here's the blog post link:… – Jon Seigel Mar 19 '13 at 14:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.