OS Disk Defrag

  • dba92081

    SSC Eights!

    Points: 934

    In reviewing the posts on this forum for OS disk defragmentation, I'm confused.

    Is it truly necessary to defrag the OS disk on a SQL server?

    Our data is located on a SAN. The drive the data files is located on is fragmented to 51%.

    Would you recommend we run the Windows defrag tool on this drive?

    What is the pro/con to doing this? I'm assuming this is something we should do after hours due to performance.

    Any guidance is most appreciated.

  • The_SQL_DBA

    SSChampion

    Points: 10494

    There is no harm in Defragmenting the SAN. The advantage of defragmentation would be reducing file system overhead before any physical disk IO occurs. Also the file would be in the best possible place for physical access according to the disk controllers intelligence..DOING IT INTHE MAINTENANCE WINDOW IS PREFERABLE

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • dba92081

    SSC Eights!

    Points: 934

    I read on some of the forum posts that we need to take the databases offline to defrag the hard drive. Is that true? Or can we just run the Windows defrag tool as normal?

    Thank you for your advice!

  • The_SQL_DBA

    SSChampion

    Points: 10494

    No need to take DB offline...just do it on a maintenance window for the first time to note how long it takes to complete. From there you can schedule it during the night to run defragment process. Just that the performance would be slow while this task is running but would boost IO once it is complete..

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • dba92081

    SSC Eights!

    Points: 934

    Great - thank you!

  • TheSQLGuru

    SSC Guru

    Points: 134017

    1) Yes, you should defrag (and keep defrag'd) OS-level files.

    2) I would consider shutting down sql server if possible.

    3) Take a look at Disk Keeper. They have a very nice server defrag product.

    4) I had a client that had over 300000 disk file fragments for their database and log files. Upon defrag they got back 18% throughput. This was DAS, but clearly there is a benefit.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    I'm with TheSQLGuru. I'd shut down SQL Server. If the files are being written to, it's possible that you are slowing things down or there could be an issue. There shouldn't be, but I don't want to take the chance.

    After you do the OS, I'd consider cleaning up the internal fragmentation in SQL Server. Who knows where the extents have been moved to.

  • Anam Verma

    SSCertifiable

    Points: 5365

    i usually run sql server on virtual servers for my development purposes.

    will disk fragmentation on physical server help in increasing speed IO for virtual server?

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Good Question,

    I do the same here, running development on a virtual server.

    But my guess is you should shut down your virtual O.S, cause i have seen locks on VM-Ware server files while taking a nightly backup of a windows server.

    So even when the file would be fragmented the host Windows OS would not be able to touch it and thus nothing would change.

    Wkr,

    Eddy

  • Perry Whittle

    SSC Guru

    Points: 233794

    definitely shutdown SQL server instances.

    make sure you have a full backup of the data files before defrag.

    Rebuild all your indexes after the defrag

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Debbi Porter

    Old Hand

    Points: 334

    Question for the gurus - traditional thinking over the years - you never defrag at the o/s level as it can cause issues for SS and you defrag from within SS when you rebuild your indices. Who actually is currently doing it (o/s defragging on DB server), on what kind of schedule and what is best practice? Is this new with SS2K5? Is it a good process to follow on SS2K8? TIA.

  • SA-1

    SSCrazy Eights

    Points: 8113

    OS level fragmentation usually occurs if your data files aren't sized correctly and have to keep growing to accomodate data growth. This is definitely not a new issue for SQL 2005 or SQL 2008. You can use windows defragger or diskkeeper to see the level of fragmentation.

    To avoid this, you should be sizing the database keeping in mind future growth. This way you will minimize the performance impact due to disk fragmentation and allow for growth within the database.

    I agree with the other members that SQL Server should be shutdown (post a backup) before the data files be defragmented.

  • MichaelJasson

    SSCrazy

    Points: 2732

    Do we really require rebuilding of indexes after defrag. Yes we should shut the m\c down if possible.

    -MJ
    Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    I 2nd the advice from "sa" above.

  • SA-1

    SSCrazy Eights

    Points: 8113

    i have in the past done an index rebuild after a disk defrag especially since the disk defrag is done during a maintenance window and the index rebuilds are also scheduled during maintenance windows. You could look at the level index fragmentation and decide whether a index rebuild is necessary.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply