Hi Walter,

I'm just re-using this message to add some information. . .

It appears that, while NT itself does NOT permit deliberately controlled fragmentation, SQL Server can (if I'm reading correctly). It seems to be a new feature of SQL Server 7.0 ("Inside Microsoft SQL Server 7.0" by Soukup/Delaney).

As I read it one can create a DATABASE with "File Groups", apparently with as many as one wants and with whatever sizes one desires and wherever one chooses to put them. A "File Group" is actually a separate 'segment' (my terminology) of the database in question.
Now this can be done with the CREATE DATABASE command or later, with ALTER DATABASE commands. File Groups can be added/removed with the ALTER command.

It appears to be customary to make all of the File Groups for a Database in the initial CREATE DATABASE command. But, unless one is creating File Groups on different drives, this would appear to result in continguous space allocation for the databases anyway. For instance, if I create 3 databases, each with 5 file groups on the same volume, then the first CREATE will put the 5 separate files beside each other (I'm inferring this), the second CREATE would put its 5 beside the first database's last (group) file, etc.

On the other hand, it looks like one could run the 3 CREATEs for the 3 databases, first using only a smallish size. Then one could ALTER each of the 3, in order, to ADD a file group of a specific size. Repeating this 3 (or should it be 4?) more times in the same way should result in an allocation layout on the drive of:
DB1 first
DB2 first
DB3 first
DB1 FileGroup1
DB2 FileGroup1
DB3 FileGroup1
DB1 FileGroup2
DB2 FileGroup2
DB3 FileGroup2
DB1 FileGroup3
DB2 FileGroup3
...etc

I think that this is correct and I also think that this could have significant positive impact on a system's performance.

Allow that each database is to be sized (in full) to 100meg each. Further, allow that all 3 are required for the most heavily used transaction(s) in the system.
The "standard" (non-fragmented) way puts the DB2 a minimum of 100megs away from the DB1 and DB3 100megs away from DB2 and 200megs away from DB1. This will be lots and lots of head movement over the course of a day.

Using 5 groups as above allows for 'segments' of 20megs each - 5 groups for each DB at 20megs per group still gives 100megs per DB.
However, in optimum circumstances DB2 is only 20megs from DB1 and DB3 only 20megs from DB2 and 40megs from DB1. This significantly reduces the head movement which can only improve response and give far less wear and tear to the drive. Though probably statistically possible, I can't see where this fragmented situation can ever be worse than the standard contiguous method.

Just FYI (and the others too)

Jim

>Jim,
>
>>But there is a distinct absence of being able to CONTROL the fragmentation that makes this "dicey" at best. We cannot in Win9x, but I wonder about in NT - do you know???
>
>As far as my limited knowledge reaches there are no (de)fragmentation tools which can control this kind of behaviour. But on the other hand if you don't defrag your disc you might have any benefit of the described behaviour.
>
>I think not many people see the use of such a tool which mainly applies to databases and it's tables (and maybe a limited number of other sort of files), so I don't think that any sotware manufacturer would see any $$ in the product.
>
>And of course, the circumstances of *good* fragmentation are limited:
>- Systems mainly used for adding data and querying and analyzing only the most recent data.
>- Heavy loaded system
>- A significant number of concurrent users using the fragmented data.
>- If the psysical disk is also has to serve processes which access other parts of the disk, the benefit might be NULL.
>- and last but not least: This is all theory we don't know how disc caches jump in here.
>
>
>>good to see you back
>
>Thanks, I've been in Turkey and Greece, but did not experience any earthquakes. After a week, I really began to miss my friends here on the UT. I'm glad to be back (as a married man now).
>
>
>Walter,