November 11, 2008 at 4:10 pm
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.
November 11, 2008 at 6:51 pm
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."
November 11, 2008 at 7:05 pm
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!
November 11, 2008 at 7:27 pm
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."
November 11, 2008 at 9:23 pm
Great - thank you!
November 13, 2008 at 12:51 pm
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
November 13, 2008 at 1:10 pm
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.
November 13, 2008 at 6:04 pm
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?
November 14, 2008 at 12:06 am
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
November 14, 2008 at 3:28 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2009 at 8:20 am
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.
July 8, 2009 at 8:45 am
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.
July 8, 2009 at 8:52 am
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.
July 8, 2009 at 8:54 am
I 2nd the advice from "sa" above.
July 8, 2009 at 9:04 am
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 23 total)
You must be logged in to reply to this topic. Login to reply