Another Filegroup question

I'm building a reporting server. I have a server with 2 processors, 2 drive
arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb of
memory (negotiating for more). I have about 130 gb of data and a little more
than half is indexes. One table makes up 50% of the data and indexes. Also, I
plan to use transactional replication.
I'm considering creating 2 file groups, one on each drive/array and
separating the indexes from the data. I'm assuming this will yield a
performance benefit and I'm wondering if I should also create 2 files in each
filegroup to futher enhance performance but am not sure if this will result
in further gains.
Thanks
MG
I don't know that two separate files for one filegroup (on the same physical
drive or RAID array) would improve performance notably. Putting your
transaction log on a separate drive would probably provide a much bigger
performance enhancement.
Michael C#, MCDBA
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
of
> memory (negotiating for more). I have about 130 gb of data and a little
more
> than half is indexes. One table makes up 50% of the data and indexes.
Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
each
> filegroup to futher enhance performance but am not sure if this will
result
> in further gains.
> Thanks
> MG
|||I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
add those other two disks to the other Raid 1+0 and place all the files
there. Yes you can get increased performance by placing data and indexes on
separate arrays but probably not as much overall gain from adding more disks
to the Raid 1+0 and separating the logs.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
> I'm building a reporting server. I have a server with 2 processors, 2
> drive
> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server with 2 gb
> of
> memory (negotiating for more). I have about 130 gb of data and a little
> more
> than half is indexes. One table makes up 50% of the data and indexes.
> Also, I
> plan to use transactional replication.
> I'm considering creating 2 file groups, one on each drive/array and
> separating the indexes from the data. I'm assuming this will yield a
> performance benefit and I'm wondering if I should also create 2 files in
> each
> filegroup to futher enhance performance but am not sure if this will
> result
> in further gains.
> Thanks
> MG
|||Thanks for the suggestions. I should have mentioned that the size of the db
will grow quickly to accomodate maintaining more history on the report
server. The size of the data will surpass the 146 gb of the one array very
quickly so I can't put the t-log on a separate array. I only have space for
4 physical drives on this box and I'll have to live with it for about 9
months. Consequently I see my options as 1. creating one filegroup across two
arrays and possibly creating multiple files to improve threading and
performance or 2. create two filegroups and look at distributing the load
somehow.
Now, considering that this is a reporting server I'm toying with the idea of
breaking the mirrors to free up 2 drives ??
MG
"Andrew J. Kelly" wrote:

> I agree with Michael. I would use 2 disks in a RAID 1 for the log files and
> add those other two disks to the other Raid 1+0 and place all the files
> there. Yes you can get increased performance by placing data and indexes on
> separate arrays but probably not as much overall gain from adding more disks
> to the Raid 1+0 and separating the logs.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:2B260F96-324A-40A2-9EFE-88B66E85C349@.microsoft.com...
>
>
|||Mardy,
I am confused now as to what you actually have. You originally stated:
[vbcol=seagreen]
A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
1+0's. If you only have 4 disks you can not have two separate Raid 1+0
drive arrays. You must have two Raid 1 drive arrays. That is a big
difference. If this is indeed a reporting server that usually means you will
have lots of table or index scans and will most likely have a lot of disk
access. This configuration will probably not meet your expectations due to
the drive configurations. If you are going to need more disk space than
146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
4 disks. That will give you twice the disk space but you will have to place
everything on one array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestions. I should have mentioned that the size of the
> db
> will grow quickly to accomodate maintaining more history on the report
> server. The size of the data will surpass the 146 gb of the one array very
> quickly so I can't put the t-log on a separate array. I only have space
> for
> 4 physical drives on this box and I'll have to live with it for about 9
> months. Consequently I see my options as 1. creating one filegroup across
> two
> arrays and possibly creating multiple files to improve threading and
> performance or 2. create two filegroups and look at distributing the load
> somehow.
> Now, considering that this is a reporting server I'm toying with the idea
> of
> breaking the mirrors to free up 2 drives ??
> MG
> "Andrew J. Kelly" wrote:
|||Andrew
Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know it's
less than ideal but it's what I have to work with and I need to make the most
of it until I get the funding to upgrade. So you suggest 1 array, 1 file
group. Correct? What about using multiple files... any benefit?
Now I do have a couple of other options. I can break the mirror. After all
it is a reporting server.. important but not mission critical. Also, one
other option. Due to an expense classification/policy oddity, I could secure
4 - 300 gb drives. My concern, however, is that the seek time on these drives
would be painful and the manufacturer does not recommend them for database
use. I believe that the rpms are a little lower than the 146gb drives as well.
Thanks again MG
"Andrew J. Kelly" wrote:

> Mardy,
> I am confused now as to what you actually have. You originally stated:
>
> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk Raid
> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0
> drive arrays. You must have two Raid 1 drive arrays. That is a big
> difference. If this is indeed a reporting server that usually means you will
> have lots of table or index scans and will most likely have a lot of disk
> access. This configuration will probably not meet your expectations due to
> the drive configurations. If you are going to need more disk space than
> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with all
> 4 disks. That will give you twice the disk space but you will have to place
> everything on one array.
> --
> Andrew J. Kelly SQL MVP
>
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@.microsoft.com...
>
>
|||When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...[vbcol=seagreen]
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
> it's
> less than ideal but it's what I have to work with and I need to make the
> most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
> secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
> drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
> well.
> Thanks again MG
> "Andrew J. Kelly" wrote:
|||Since it is for report server -Why not RAID5 - This will give you the best
Disk capacity and fault tolerance.
Even though you will not get best WRITE i/o performance, that shouldn't be
a problem for a report server.
-Sarav
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@.microsoft.com...
> Andrew
> Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know
it's
> less than ideal but it's what I have to work with and I need to make the
most
> of it until I get the funding to upgrade. So you suggest 1 array, 1 file
> group. Correct? What about using multiple files... any benefit?
> Now I do have a couple of other options. I can break the mirror. After all
> it is a reporting server.. important but not mission critical. Also, one
> other option. Due to an expense classification/policy oddity, I could
secure
> 4 - 300 gb drives. My concern, however, is that the seek time on these
drives
> would be painful and the manufacturer does not recommend them for database
> use. I believe that the rpms are a little lower than the 146gb drives as
well.[vbcol=seagreen]
> Thanks again MG
> "Andrew J. Kelly" wrote:
Raid[vbcol=seagreen]
will[vbcol=seagreen]
disk[vbcol=seagreen]
to[vbcol=seagreen]
all[vbcol=seagreen]
place[vbcol=seagreen]
the[vbcol=seagreen]
very[vbcol=seagreen]
space[vbcol=seagreen]
9[vbcol=seagreen]
across[vbcol=seagreen]
load[vbcol=seagreen]
idea[vbcol=seagreen]
files[vbcol=seagreen]
files[vbcol=seagreen]
indexes[vbcol=seagreen]
more[vbcol=seagreen]
2[vbcol=seagreen]
with 2[vbcol=seagreen]
little[vbcol=seagreen]
indexes.[vbcol=seagreen]
a[vbcol=seagreen]
files[vbcol=seagreen]
will[vbcol=seagreen]