Take the 2-minute tour ×
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.

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
    
Has anything in the config of the machine or SQL itself changed recently? –  Max Vernon Mar 15 '13 at 12:27
    
nothing in the config has changed this recently. They were change to simple recovery model and had the compatability flag updated a few weeks ago. The stats are set to update every night –  Tim Alexander Mar 15 '13 at 12:39
    
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: technet.microsoft.com/en-us/library/bb677179.aspx –  Tim Alexander Sep 16 '13 at 9:03
    
can you tell me how you get this graph is it script or a tool look very clear please share this with me it be very helping thanks –  yehufa Oct 17 '13 at 8:11
add comment

1 Answer

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
1  
I was eyeballing those 2 greens and wondering which was which. That combined with the good spot on the system drive IO leads to +1. –  Mark Storey-Smith Mar 15 '13 at 15:20
    
As a team we have just reached your exact conclusions! We had been combating SAN to SAN replication causing performance hits and this would fit with increased disk I/O due to swapping. Thank you for the very concise desription and explanation. –  Tim Alexander Mar 15 '13 at 15:36
1  
@TimAlexander If Jon's answer was correct, would you consider marking it as such? –  Mark Storey-Smith Mar 16 '13 at 1:22
2  
@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
2  
@Tim: Here's the blog post link: voluntarydba.com/post/2013/03/19/… –  Jon Seigel Mar 19 '13 at 14:16
show 3 more comments

Your Answer

 
discard

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.