Installing SQL Server on a badly fragmented drive

Question

Installing SQL Server on a badly fragmented drive

MothInTheMachine

Hall of Fame

Points: 3251
More actions
June 23, 2010 at 8:13 am

#387205

I told our IT department that I wanted to defrag a highly fragmented hard drive before installing SQL Server 2008 on it. it's a virtual drive if that matters. They said it didn't matter and I said i thought it did. Am I barking up the wrong tree on this one?
Thanks 🙂
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

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

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

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 1

Do you mean that the virtual drive is fragmented on the physical one or that inside the virtual drive it appears to be fragmented?

We usually don't defragment drives often when SQL is on them, but before that I would think you would. There have been reports on this site of people gaining 10% in their IO performance after defragmenting a physical drive.

The head movements matter in SQL Server, and I can't believe it introduces that much of a delay to your system if you defragment first. I'd get it done just to be safe.

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 2

Steve Jones - Editor (6/23/2010)
Do you mean that the virtual drive is fragmented on the physical one or that inside the virtual drive it appears to be fragmented?

Thanks Steve - you ask a good question. I saw that defragging a virtual drive involved more than just degragging the c drive. I'm not sure what is fragged as ll I have access to is remote terminal to get onto the box and not the vmware part of it.

I should mention that sql server 2000 is currently installed on this drive. Will defragging cause me a headache or will I risk breaking something if I defrag? This is our test server and not a live production machine but I'd like to avoid any more heart ache for the moment.

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 3

You'll need to stop SQL 2000 or you can see some slowdowns. You'll also want to internally defrag the tables once the disk is done as things will have moved around on the disk and the idea is that you get your tables in physical order.

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 4

Thanks Steve. Is there a stored procedure to use for defragmenting tables?

Thanks!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 5

How does this sound for a defrag procedure?

1. Boot any users who are on the system during its lowest usage window.

2. Turn off SQL Server.

3. Run the Windows Disk Defragmenter (Other products maybe?).

4. Turn on SQL Server when fdefrag is complete.

5. Run DBCC SHOWCONTIG (or just run dbcc reindex since no one is on the system anyway and this will also defrag the indexes (not sure about that but I think it does)

6. Run DBCC INDEXDEFRAG on any objects requiring it

7. Let the users back on.

Thanks for your help!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 6

If you can rebuild the clustered indexes, just do that. Indexdefrag should work as well. Here's a whitepaper that might help you decide which is better for you: http://technet.microsoft.com/en-us/library/cc966523.aspx

David Sorauer SSCrazy Points: 2874 More actions · Answer 7

You can get tool (such as diskkeeper) that can defragment SQL files when they are in use.

I'm not sure why windows admins don't see the need to defrag drives anymore-especially local disks...

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 8

Do you think they aren't defragging them anymore? It could be cause the drives are so much bigger now that they can get away with not defragging them. It's kind of a pain in the butt. I did the process last night on a test machine and it's pretty involved. Although it could have seemed more involved just because it was new to me.

Can Diskkeeper defrag continuously? I can't imagine how that would be good for performance (with a software app running on the database server).

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 9

I know some people post that Diskkeeper (and similar tools) work fine with SQL Server, but I've always been nervous about anything accessing the SQL files while they're in use. I wouldn't recommend it, but I'm just not willing to take the risk. I have no evidence of issues.

David Sorauer SSCrazy Points: 2874 More actions · Answer 10

I'm not sure- windows admins don't seem to defragment drives on a regular basis (like they did in the past). The attitude I have heard from some was that they would rather do a full backup and restore of the disk if fragmentation is really an issue...

Diskkeeper has many options (like it will defrag when CPU is less than 5%). They claim that it's tested and suitable for SQL Server.

I think it's like an open file backup- it takes a copy of the file, defrags the copy and then switches the files (layman's understanding!).

I tested it around a year ago on a development server (there were no issues) and the fragmantation was reduced considerably...

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 11

I didn't even know that would do the trick. That sounds alot easier and probably saves a lot of time. But proabbly still a good idea to defrag a drive before doing a fresh install of a db onto a disk.

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

EdVassie SSC Guru Points: 60479 More actions · Answer 12

Doing a windows defrag will have zero impact on SQL Server index fragmentation.

The index defrag makes sure the extents used for a table are in ascending order within the database file. SQL neither knows or cares if the database file is fragmented and the file fragment containing extent 1000 is physically before or after the file fragment containing extent 999.

If your data is stored on some types of SAN or network drive, then physical defragmentation can be meaningless. Some types of SAN maintain a pool of unused tracks, and whenever you want to do a write they take a track from the pool and use it. The old track then goes back into the pool. (No disk-level updates are ever done in this scenario, only reads and writes.) With this type of SAN physical fragmentation is always 100%. Having very bad fragmentation at the NTFS level with this type of SAN can give a slight performance penalty, because Windows has to search very long file allocation chains to build the command to get the SAN to access the disk, but the impact of this is far lower then when disk space is allocated in contiguous chunks of disk tracks.

Other types of SAN reserve contiguous chunks of disk space for each LUN, and on these physical defragmentation gives greater advantages. You should discuss these issues with your storage administrator.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 13

Thanks for the info. The server I was referring to is sitting on a virtual drive, no SAN. it's a test server so it's not on a RAID either. My concern with fragmentation aren't index related on this machine but are head related. If the disk head has to move alot there's going to be a performance hit. Isn't that true of any system regardless of what the storage mechanism is? It will always be faster to walk one block than 3.

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Randolph West SSCommitted Points: 1588 More actions · Answer 14

You will probably see some sort of improvement, but the degree of improvement depends on many factors specific to your environment.

That said, I've had performance improve quite dramatically after defragging a virtual disk, and then defragging the physical disk as well.

SysInternals' Contig, and Defraggler from Piriform, both defrag SQL data files online, and they're both free. Contig in particular will stop running if the file is changed in any way, so you should be able to get by without stopping SQL. It will slow things down though.