Performance Hit w/ Filegroups?

Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon