SQL Server Performance

Full Disk Performance

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by asc11, Jan 22, 2008.

  1. asc11 New Member

    I was just having a discussion about our storage situation when someone mentioned that disk performance goes down as the disk fills up. This sounds familiar, but I can't find any documentation on it (I guess I just don't the right words/phrases to search on). Does anyone have any links supporting or refuting this idea?
  2. Luis Martin Moderator

    Well, I don't know about documentation. But, common sense I agree with that.
    Suppose you have a filegroup with 10% automatic growing. If SQL try to get 10% more and you don't have space you will have some message and performance will decrease.
    Just a thought.
  3. asc11 New Member

    Thanks for the reply.
    I guess I should have chosen a better title for this thread. I realize that a full disk will cause all kinds of problems... including but not limited to performance. My question is about database or just disk performance as the disk fills up, but still has enough room for normal operations. For example, what is the performance difference between a disk that is 50% full and 80% full?
    Thanks again.
  4. Luis Martin Moderator

    If the application don't need a 20% free to, just an example, sort something (tempdb must increase), in my opinion there is no performance difference.
  5. satya Moderator

    if you have write-intensive operation on that disk with 80% full will have issues in writing or keeping up the performance as compared to the 50%. In any case look at the TEMP directory on the server where it normally keeps up the temporary files and same in the case of SQL Server usage of TEMPDB.
  6. asc11 New Member

    Thank you both Luis and Satya for your responses (although they present opposite opinion... interesting).
    Assuming satya's response is correct and performance will, in fact, decrease as the free space dwindles, the bigger question is: why? There are so many pieces involved in SQL Server IO operations: which one is resposible for the decreased performance in a "low disk space" scenario?
  7. satya Moderator

  8. satya Moderator

  9. ScottPletcher New Member

    I think the general consensus is that performance really starts to be hurt at 80%, and goes down almost exponentially from there.
    Main reason I think is that when a file expands, more disk allocation pages must be searched to find that amount of space. Also, often multiple/split allocations have to be made instead of a single, contiguous allocation. For example, allocating 100M on a drive with lots of free space typically results in a single 100M allocation. Getting 100M on a more-full drive may require allocating two 50M areas or even more, say, 40M, 40M and 20M areas.
  10. asc11 New Member

    So if I understand you correctly, you're talking about file fragmentation at the OS level. That would certainly be understandable.
    If that is the case, let's say I have a 90GB disk dedicated to the data files for three user databases, and each of these dbs only use one data file. If file fragmentation is the issue, I should be able to set the initial file size for these three data files at 30GB each and, even though the disk is now full, there will be no (or negligible) IO performance impact since there is no file fragmentation. Correct? This is also assuming that the files will not need to grow (yes I realize it would be a problem if they needed to).

Share This Page