Different Filegroups SQL Server 2K SP3 on SAN (EO8.10)

[acm]

EnterpriseOne 8.10, SQL Server 2000 SP3, SAN configured on RAID5 across all disks.

Is there any performance benefit to have different tables in different filegroups and hence each filegroup in different LUNS if in essence each LUN is going to be spread across all disks in the SAN anyway?

Thank you very much for taking the time to read this question.

 

[gregglarkin]

Acm,

I cannot think of any performance benefit to that. I CAN however think of lots of disadvantages to that, the major one being unneccesary complexity. We beefed up performance by making SQL active/active. My production business data and the system tables are on the same cluster node as JDE. The PY and DV databases and Cognos databases are on the other node of my cluster. This way testing and reporting do not degrade the speed of committing transactional data.

Gregg Larkin
JDE System Administrator (CNC) / North America
Praxair, Inc.

 

[cmanderson]

I think the key part of your question is "if in essence each LUN is going to be spread across all disks in the SAN anyway?"

Is 100% RAID 5 an absolute requirement? Is it possible for you to add more storage as RAID 10 (1+0)? If so - then it would be beneficial for tables which receive lots of write activity but relatively small record sizes. If not, what Gregg said. It could be beneficial during a staggered Unicode conversion (if you aren't already there yet, that is...)

 

[jmccullough]

Hi,

I have seen a performance benefit on doing this. We where getting IO queues in SQL running on our SAN, I split the top 4 tables off into another file group and put them on another LUN (RAID 1+0) and we immediatelty saw improvements. Our SAN is virtualised which means that in theory all IOs are spread across all the disks, and there should be no difference in spliting the IOs across different Luns. When we spoke to our storage vendor they indicated that the bottleneck was on the server, Windows has IO queues per disk and it seemed that these queues where saturated. It should be said that this will only be a beneift where IOs are high. Hope this sheds some light.

 

[tomjr93]

We tested that scenario on our SAN here and found that it actually decreased performance. The SAN could not handle the activity.

Tom