dear sir,

when the database start under sqlanywhere 8, i get the message database file "c:\test\test.db" consists of 17497 disk fragments, and some queries is so slow it's related to this error????

asked 26 Sep '12, 01:59

thaerlb's gravatar image

thaerlb
12791014
accept rate: 0%

edited 27 Sep '12, 10:32

Volker%20Barth's gravatar image

Volker Barth
31.5k320462677

If de-fragmenting the file does not improve your slow queries (which is quite possible, even very fragmented databases can work surprisingly fast), I suggest reposting an example of a problem query with it's execution plan (see How to capture a Graphical Plan) in a new question and someone my be able to help.

(26 Sep '12, 04:28) Justin Willey

File fragments can influence the performance. So try defragmenting the DB file and check again. You can use the tool from microsoft contig.

permanent link

answered 26 Sep '12, 02:51

Martin's gravatar image

Martin
8.6k119151237
accept rate: 14%

You are to fast for me ;-)

(26 Sep '12, 02:55) Thomas Dueme...

As Thomas rightly pointed out, make sure the database is offline when you defrag the file.

(27 Sep '12, 08:42) Reg Domaratzki

I'm not sure this is really the case. It may be a piece of computer "lore" that used to be true, but no longer means anything at all. (Except that you get to watch pretty lights dance around the screen).

I found this article, which is quite old, discussing this exact problem on Windows NT using NTFS. Two things stand out. Random Access, such as reads from a DB, are inherently fragmented anyway. And the advent of journaled FS with extent trees means that finding a file fragment is relatively cheap.

Just a few thoughts.

http://superspeed.stores.yahoo.net/entertech01p0.html

(03 Oct '12, 22:14) Jonathan Baker
Replies hidden

IMO the statement that extreme file fragmentation adversely affects performance is most certainly not an urban myth... not according to my experience, and not according to Microsoft http://windows.microsoft.com/en-US/windows-vista/Improve-performance-by-defragmenting-your-hard-disk

The article you quote is an extended advertisement for a cache software product: "Finally, with all of our tests pointing to cache improvements as having by far the most dramatic effects on performance, we turned to an add on caching product, SuperSpeed's SuperCache, to boost performance."

A huge RAM cache is one of the easiest ways to improve performance. However, disk I/O is very important in the real world, and extreme file fragmentation can have a huge effect on performance. The good news is that Windows 7's builtin defragmenter seems to keep the situation under control... without the pretty lights :)

(04 Oct '12, 10:29) Breck Carter

One might also add that table/index scans and the like are usually not "Random Accesses" at all.

(04 Oct '12, 10:33) Volker Barth

You should defragment the drive when the Database is offline.

Or use CONTIG from Sysinternals to defragment only the database file.

The database file will get fragmented when the database needs more space and the file is grown. You can reduce the fragmentation if you add larger chunks to the database at a time with the ALTER DBSPACE command.

permanent link

answered 26 Sep '12, 02:53

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k263661
accept rate: 17%

dear sir,

i use windows server 2000 ,the contig command not exist.what can i do?

(27 Sep '12, 08:17) thaerlb
Replies hidden
1

Follow the link in my answer and download it.

(27 Sep '12, 08:20) Thomas Dueme...
Comment Text Removed
1

re: "You should defragment the drive when the Database is offline."

From experience, both the Windows defrag utility and Diskeeper can be used while a SQL Anywhere database is active. FWIW in Windows 7 the builtin utility makes Diskeeper unnecessary, and both are far more convenient than contig (which is an old-school utility from the days when everyone had to walk to school barefoot in the snow uphill both ways :)

(27 Sep '12, 09:25) Breck Carter
Replies hidden
1

You don't walk barefoot anymore? - Man, now I really feel old-school.

(Yes, I'm still gladly using contig.exe against running databases.)

(27 Sep '12, 10:07) Volker Barth
1

... and for people who dislike command line tools there's WinContig

(04 Oct '12, 04:24) Reimer Pods
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×248
×7

question asked: 26 Sep '12, 01:59

question was seen: 3,910 times

last updated: 04 Oct '12, 10:36