Will heavy disk fragmentation slow down my queries?

[former_SQLA_member1694915]

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????

[justin_willey]

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.

[MCMartin]

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

[thomas_duemesnil]

You are to fast for me 😉

[regdomaratzki]

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

[Former Member]

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

[Breck_Carter]

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 🙂

[VolkerBarth]

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

[thomas_duemesnil]

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.

[former_SQLA_member1694915]

dear sir,

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

[thomas_duemesnil]

Follow the link in my answer and download it.

[Breck_Carter]

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 🙂

[VolkerBarth]

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

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

[reimer_pods]

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