DUMP and fragmentation

Bret Halford

2009-12-11 16:54:24 UTC

Post by dalepa
We do DUMPs of a ASE 15 database (solaris 10) daily that takes 40
minutes to disk ~50GB.... If we LOAD the dump (DB1) to a NEW
database (DB2), DUMPS of DB2 take 20 minutes.
We are thinking that we may have have a disk fragmentation issue, and
the DUMP/LOAD process in effect defrags the phyiscal disk...
Is it possible that the DUMP/LOAD process to defrag the database....
Or maybe the DUMP/LOAD process somehow defrags the table space or some
other internal data?

No, dumps do not affect fragmentation. Dumps copy all the allocated
pages in a database (and sometimes larger chunks if sp_dumpoptimize
has been used), on load the pages are restored to the same page number
location - so any data that was on page 33456 will be on 33456
after the load, no data moves around.

Some possible factors might be
a) perhaps DB2 was created on faster disk drives than DB1?
b) perhaps DB1 has concurrent user activity during the dump and DB2 does
not? Dumps have three phases - the first efficiently copies all the
allocated pages in the database. The later phases transfer over pages
involved in new activity since the dump started. Those later phases
might account for the difference.