October 25, 2010 at 8:27 am
i have here a database that has grown exceptionally since it was set up: the previous database (used with an earlier version of the software) has remained at about 16 GB for a long time - the newer software version had the database grow to about 140G within a year or so...
now i fear that the datafile is heavily fragmented (physical fragmentation, not index fragmentation).
and that this impacts performance.
now, i will have to move the database on a new server (and new sql server version, 2005). will a copy of the entire datafiles onto an all-new disk already be enough to 'defragment' the file?
or doesn't it matter anyway...?
ah, i also have to mention that the database is on a san.
so i might not need to copy the file at all, as the san just could be attached to the new server and a lot of time is saved - if it won't help with the fragmentation or if it does not matter?
hm, what are your experiences / opinions about this?
Thank you
October 25, 2010 at 9:25 am
The copy will no degragment the file. It might move the various segments on disk around, but it will not move the internal allocations.
You need to rebuild clustered indexes to fix this. DBCC SHOWCONTIG will help you determine if the tables are fragmented.
October 25, 2010 at 11:17 am
but i'm NOT talking about 'internal' fragmentation - i'm talking about 'external' fragmentation of the datafile itself. this might also be corrected using OS defrag tools; but they usually take a long time to defrag large disks - and i usually can't get such long downtimes. but the upcoming migration / server move might give the opportunity for a longer downtime if needed and i'm trying to find out if i can do something for performance too while i'm about it.
October 25, 2010 at 11:26 am
If you have a freshly cleaned/formatted disk, and you copy new files to it, they will be 100% contiguous after the copy.
October 25, 2010 at 12:52 pm
physical fragmentation can hurt performance. diskkeeper is one tool that can defragment disks whilst SQL is online.
---------------------------------------------------------------------
October 26, 2010 at 11:26 am
Yes, that should do the defrag in real-time without having to go offline.
ZDnet had a whitepaper on this that may be of some interest.
http://whitepapers.zdnet.com/abstract.aspx?docid=329168
Another, older thread on a similar topic that may be worth a read...
http://www.sqlservercentral.com/Forums/Topic509609-65-1.aspx
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply