If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

The file fragmentation on the disk is invisible to SQL Server... It sees a file as a contiguous stream of 8 Kb pages, but it is quite ignorant of how those pages are actually laid out on the disk(s).

rdjabarov brings up a good point though, in that even a table or database that is contiguous from SQL Server's perspective might be on a Windoze file that has grown many times, so the head still has to hop all over creation to retrieve data. It is a good idea to periodically detach the database and defrag the Windoze disk after the database file(s) grow.

So what defragmentation tool is available that is able to defrag a database file considering it's internal structure?

I've seen several implementations of defragtools; one would only move about the occupied sectors, making it easier for the OS the find free space. Others would do a file approach, selecting the sectors involved and move those around. I've had a defrag tool that did a file approach and moved the first sectors of a file to the beginning, working to the end. It took ages, _and_ the tool somehow mixed up sectors, rendering some files useless.

With database files it's tricky: indexes, tables, procedures, views.. where to store them? Would a tool consider? Does it speed up performance or does it go down the drain?

I have to admit it's been a long time since I last looked at defragmentation tools; so I don't know how advanced they are these days and how well they work. SQLServer already claims a contiguos block of data when a db is created, I think adding new parts works the same. If a growth of a db is the only change to the disk, and sqlserver claims the new block right after the db-block, what are the odds a defrag tool doesn't leave it alone and make it worse?

I'm not an expert on defragmentation, I'm also not an expert on sqlserver, I know from experience a defrag helps when a lot of small files are scattered around the disk, but I wonder the effect on db-files.

I'm sorry, I didn't catch Kuzco's reply earlier. I was thinking in terms of the TRUNCATEONLY option when I wrote that the DBCC SHRINKDATABASE didn't move pages. The NOTRUNCATE option can move pages, but it doesn't always do what you'd expect it to... See BOL on DBCC SHRINKDATABASE or better yet Inside SQL Server 2000 for more detailed descriptions.