FileGroup Structures

[Barb Dornbrook]

I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).

Any Info Will help Thanks
Barb

[Anthony B. Kenitzki]

Multiple database filegroups improve performance if the physical files reside on different disks or RAID arrays. It's the simple principle that adding disk spindles for a database to use will increase i/o performance.

Is this the case with your system, or do your file groups reside on a single hard drive? In the former case, your select queries will run better under a heavy load. In the latter case you shouldn't notice anything spectacular, if at all.

Anthony B. Kenitzki


------------
Barb Dornbrook at 4/14/99 3:38:25 PM

I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).

Any Info Will help Thanks
Barb

[Barb Dornbrook]

Anthony we do have level 5 raid devices. They are seen by SQL as one drive,
so there should be a performance increase if I am understanding you correctly?


------------
Anthony B. Kenitzki at 4/14/99 4:07:21 PM


Multiple database filegroups improve performance if the physical files reside on different disks or RAID arrays. It's the simple principle that adding disk spindles for a database to use will increase i/o performance.

Is this the case with your system, or do your file groups reside on a single hard drive? In the former case, your select queries will run better under a heavy load. In the latter case you shouldn't notice anything spectacular, if at all.

Anthony B. Kenitzki


------------
Barb Dornbrook at 4/14/99 3:38:25 PM

I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).

Any Info Will help Thanks
Barb

[Anthony B. Kenitzki]

Not necessarily,

If you have a single .MDF file on that one raid array, it is already striped across all of the disks on the RAID. Adding filegroups won't change the distribution of your data.

If you have two or more raid disk arrays, and created one database file on each group of disks, you will see a performace increase. Putting the transaction log file and database files on separate disk arrays is a big plus.

Microsoft has a great paper on SQL 7.0 performance tuning on their website
http://www.microsoft.com/sql/70/whpprs/perftun.htm
Download it and read the section on RAID arrays and file groups.

Mr. Wynkoop would be a clever kid if he collected all of the links I've been giving out and posted them somewhere all together for everyone to use.

Anthony B. Kenitzki









------------
Barb Dornbrook at 4/15/99 10:39:51 AM

Anthony we do have level 5 raid devices. They are seen by SQL as one drive,
so there should be a performance increase if I am understanding you correctly?


------------
Anthony B. Kenitzki at 4/14/99 4:07:21 PM


Multiple database filegroups improve performance if the physical files reside on different disks or RAID arrays. It's the simple principle that adding disk spindles for a database to use will increase i/o performance.

Is this the case with your system, or do your file groups reside on a single hard drive? In the former case, your select queries will run better under a heavy load. In the latter case you shouldn't notice anything spectacular, if at all.

Anthony B. Kenitzki


------------
Barb Dornbrook at 4/14/99 3:38:25 PM

I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).

Any Info Will help Thanks
Barb