SQL Anywhere database - DB file fragmentation

Author

DB file fragmentation

handheldmaster

2005-08-18, 9:40 am

Using asa 8.0.2(4372)

As per docs
<<<<<
Performance Tip
Running a disk defragmentation utility after pre-allocating
disk space helps ensure that the database file is not
fragmented over many disjoint areas of the disk drive.
Performance can suffer if there is excessive fragmentation
of database files.[color=darkred]

Plan to use external reload with dbunload (doing this as
also plan to increase the page size to the recommended 4096)
otherwise get the message database page size too small.

Question is when do we run the Windows Uitlity for File
Defragmentation ... is the sequence below good ? Which is
better from performance point of view ??

(1) Create new db file with Page Size 4096
(2) Pre-allocate space 1 GB
(3) Run dbunload to do an external reload into new db file
(4) Run Windows File Defragmentation

Should I be following the other way round ?
(1) Run Windows File Defragmentation
(2) Create new db file with Page Size 4096
(3) Pre-allocate space 1 GB
(4) Run dbunload to do an external reload into new db file

Breck Carter [TeamSybase]

2005-08-18, 11:23 am

Use the first scenario; AFAIK Windows defrag doesn't do a very good
job on free space, only allocated files.

IMO Diskeeper is much better; I have it running all the time on all my
computers. I think Windows defrag is a stripped version of Diskeeper
but that might be an urban myth :)

FWIW the topic of "Database Reorganization with Unload/Reload" is
covered in my book.

Breck

On 18 Aug 2005 07:24:26 -0700, handheldmaster wrote:

>Using asa 8.0.2(4372)
>
>As per docs
><<<<<
>Performance Tip
>Running a disk defragmentation utility after pre-allocating
>disk space helps ensure that the database file is not
>fragmented over many disjoint areas of the disk drive.
>Performance can suffer if there is excessive fragmentation
>of database files.
>
>
>Plan to use external reload with dbunload (doing this as
>also plan to increase the page size to the recommended 4096)
>otherwise get the message database page size too small.
>
>Question is when do we run the Windows Uitlity for File
>Defragmentation ... is the sequence below good ? Which is
>better from performance point of view ??
>
>(1) Create new db file with Page Size 4096
>(2) Pre-allocate space 1 GB
>(3) Run dbunload to do an external reload into new db file
>(4) Run Windows File Defragmentation
>
>Should I be following the other way round ?
>(1) Run Windows File Defragmentation
>(2) Create new db file with Page Size 4096
>(3) Pre-allocate space 1 GB
>(4) Run dbunload to do an external reload into new db file

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

handheldmaster

2005-08-18, 11:23 am

Will it make sense to do a windows os file defrag
before-and-after as will be able to query the database to
check # of fragments

btw -- do not have that book you mention ... does sybase
education sell books on database maintenance.

> Use the first scenario; AFAIK Windows defrag doesn't do a
> very good job on free space, only allocated files.
>
> IMO Diskeeper is much better; I have it running all the
> time on all my computers. I think Windows defrag is a
> stripped version of Diskeeper but that might be an urban
> myth :)
>
> FWIW the topic of "Database Reorganization with
> Unload/Reload" is covered in my book.
>
> Breck
>
> On 18 Aug 2005 07:24:26 -0700, handheldmaster wrote:
>
> pre-allocating >disk space helps ensure that the database
> file is not >fragmented over many disjoint areas of the
> disk drive. >Performance can suffer if there is excessive
> fragmentation >of database files.
> 4096) >otherwise get the message database page size too
> small. >
> is >better from performance point of view ??
> file >(4) Run Windows File Defragmentation
> file
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Greg Fenton

2005-08-19, 3:23 am

handheldmaster wrote:
>
> btw -- do not have that book you mention ... does sybase
> education sell books on database maintenance.
>

Breck does not work for Sybase. He is an independent contractor who
happens to know A LOT about SQLAnywhere, having worked with it for many
years....so he wrote a book about it called "SQL Anywhere Studio 9
Developer's Guide".

From the footer of his newsgroup postings:

SQL Anywhere Studio 9 Developer's Guide
Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

David Kerber

2005-08-22, 9:23 am

In article < 2u89g1p0llj8grv5f16u
m7ni531clp88o0@4ax.com>,
NOSPAM__bcarter@risi
ngroad.com says...
> Use the first scenario; AFAIK Windows defrag doesn't do a very good
> job on free space, only allocated files.

Neither does DiskKeeper, and I won't buy it until it does. Norton
SpeeDisk will defrag free space, though.

> IMO Diskeeper is much better; I have it running all the time on all my
> computers. I think Windows defrag is a stripped version of Diskeeper
> but that might be an urban myth :)

No, it's true.

.....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Sponsored Links