Multiple Filegroups

Hi experts,

I'm new to SQLServer 2000 and would like some advice on filegroups.

Is there any advantage to seperate the filegroups for different type of data.

For example:

Data_1 for tables
Index_1 for Indexes
Audit_1 for Audit tables

The files for these filegroups would be placed on RAID Disk.

Thanks for any suggestions.Any comments?

I need to know whether there are any performance gain in seperating types of data in different filegroups or is it just good enough to set it to a default secondary filegroup for user data objects.

Please comment.|||Hi,
you can save a lot of system time if you are able to store smart your tables, indexes, ....
This is part from SQL books:

Placing Tables on Filegroups

A table can be created on a specific filegroup rather than the default filegroup. If the filegroup comprises multiple files spread across various physical disks, each with its own disk controller, then queries for data from the table will be spread across the disks, thereby improving performance. The same effect can be accomplished by creating a single file on a RAID (redundant array of independent disks) level 0, 1, or 5 device.

If the computer has multiple processors, Microsoft? SQL Server? 2000 can perform parallel scans of the data. Multiple parallel scans can be executed for a single table regardless of the number of files that are in its filegroup. Additionally, any text, ntext, or image columns within a table can be created on a filegroup other than the one that contains the base table.

Eventually, there is a saturation point when there are too many outstanding I/O's causing bottlenecks in the disk I/O subsystem. These bottlenecks can be identified by using Windows NT? Performance Monitor to monitor the PhysicalDisk object and Disk Queue Length counter. If the Disk Queue Length counter is greater than three, consider spreading the file across more disk drives. For more information, see Monitoring Disk Activity.

It is advantageous to get as much data spread across as many physical drives as possible in order to improve throughput through parallel data access. To spread data evenly across all disks, you can place a single file across striped disks or maintain each disk separately and place a file on each disk.

I hope it will help you. It's very good text.
Bye

|||No, there is no appreciable gain. Filegroups are mainly for ease of administration across multiple volumes, not performance.sql

Multiple Apps on Single SQL Server?

Question for the SQL experts.
We are installing new 2 - BL20p 3.6Xeon 1 - BL40p 3.0Xeon servers and
I wanted to know others opinions as to how to configure the SQL
databases.
Here are the applications that we will be loading. Sharepoint Portal
(125 users), Project Server (50 users) and SQL 2000.
The usage on these apps will not be heavy so can I consolidate all
databases to 1 server without any negative performance? Or should I
install SQL on BL40p and Sharepoint Portal and Project on another
server?
I am fairly new at SQL and I wanted to try and consolidate if possible.
Will a single instance be fine for all 3 apps?
Your thoughts?? I got confused by your question.
Yes, you can consolidate all databases (at least those you mentioned)
to one MSSQL backend (whether you like to set it as one instance or
multiple instances).
No, it is better to have a dedicated server as a sorely Database Server
(MSSQL in this case), which separate from the application server(s).
It will be better to have Portal and Project install on its own server
as a application server but the databases stores in a separate
dedicated MSSQL Server.
Hope it helps.
Mel