Defrag Hard Drive Before or After Reindexing Tables?

[JohnBates]

hi everyone,

This is a SS 2000 db on WIN 2000 SERVER

I have scheduled a job to perform DBCC REINDEX on the tables in my database, followed by a step that will update usage.

I've discovered that both drives on this server (Win 2000 Server) are very fragmented, but I don't know whether I should schedule the Diskeeper Defrag to run Before or After the table reindex job....

Any thoughts?
Thanks, John

 

[mrdenny]

Defragging the hard drive of a SQL Server can be tricky. While the SQL data file will be made contigous, the data pages on the disk will now be in an unexpected order, and could potentially cause further slowdown.

You will end up with better results if you copy the data file to another drive, then defrag the drive, then copy the data file back.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnBates]

Thanks mrdenny.

I'm using Executive Software's Diskeeper, which *claims* to do a good job of defragging SQL Server data files.

I've set a schedule when there is (almost) no use of this db.

John

 

[JohnBates]

mrdenny makes an interesting comment: "You will end up with better results if you copy the data file to another drive, then defrag the drive, then copy the data file back."

** Does anyone else have an opinion on using Diskeeper to defraf SQL Server data files? **

(I assume the built-in disk defrag tool that's included with Windows Server 2003 is pretty much worthless for SQL Server files, right?)

Thanks, John

 

[ptheriault]

First yes the Windows defrag tool is worthless.
I also agree with Denny and that I have used that method in the past. It works great and the defrag is much fast because the datafiles are not on the disk.


- Paul
- If at first you don't succeed, find out if the loser gets anything.