473,695 Members | 2,841 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Balancing IO. File Groups vs Raid

I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?

Thanks,
-Bernie
Jul 20 '05 #1
6 5623

"Bernie" <ve*****@ix.net com.com> wrote in message
news:e7******** *************** **@posting.goog le.com...
I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?
Can't answer all of those (partly I'm not fully awake. :)

But you absolutely want your logs on RAID 1 or RAID 10, NOT RAID 0.
Remember, RAID 0 has no redundancy. If you lose your transaction log, you
lose data. Or more specifically, you lose your ability to recover up to the
most recent point in time.

Ideally, I'd probably dedicate some LUN to the Data (this lun can comprise
of a RAID 5, RAID 10, etc, depending on needs, disk space, etc. (I prefer
if I have the space to use RAID 10), have another for TEMPDB (though I've
always cheated on this and put it on my DATA disk, and then a separate RAID
10 for logs.

The best reason I can see for separate filegroups is to separate out indices
to their own drive (we do this) or to break out very large tables so that
you can use some more advanced backup schemes.

That's my 2 cents at 1:00 AM.


Thanks,
-Bernie

Jul 20 '05 #2
The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.

My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.

On 11 Dec 2003 10:18:37 -0800, ve*****@ix.netc om.com (Bernie) wrote:
I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?

Thanks,
-Bernie


Jul 20 '05 #3

"Ellen K." <72************ ************@co mpuserve.com> wrote in message
news:nq******** *************** *********@4ax.c om...
The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.
Personally I would not do this (single disk for non-clustered indices).
Mostly because in my environment (others of course will vary) loss of the
indices will effectively make the DB useless from a DB point of view.
My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.

Jul 20 '05 #4
If it happens and inconveniences the users, we're that much closer to
a SAN. :)

On Mon, 15 Dec 2003 03:57:12 GMT, "Greg D. Moore \(Strider\)"
<mo*****@greenm s.com> wrote:

"Ellen K." <72************ ************@co mpuserve.com> wrote in message
news:nq******* *************** **********@4ax. com...
The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.


Personally I would not do this (single disk for non-clustered indices).
Mostly because in my environment (others of course will vary) loss of the
indices will effectively make the DB useless from a DB point of view.

My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.


Jul 20 '05 #5

"Ellen K." <72************ ************@co mpuserve.com> wrote in message
news:bc******** *************** *********@4ax.c om...
If it happens and inconveniences the users, we're that much closer to
a SAN. :)

Now see, thre I like how you think. :-)

Jul 20 '05 #6
:)

Actually your previous post made me realize that I should organize
something the tech guys can use to make the database quickly reusable
in the event the singleton fails and I'm not around. So thank you!
:)

I think maybe I will create an empty filegroup "EMERGENCY" on the RAID
5 array, and make a script to drop all the nonclustered indexes from
the filegroup that's on the singleton and then recreate them in
EMERGENCY. Whaddya think?

On Tue, 16 Dec 2003 15:01:43 GMT, "Greg D. Moore \(Strider\)"
<mo*****@greenm s.com> wrote:

"Ellen K." <72************ ************@co mpuserve.com> wrote in message
news:bc******* *************** **********@4ax. com...
If it happens and inconveniences the users, we're that much closer to
a SAN. :)

Now see, thre I like how you think. :-)


Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1467
by: Mike Hibbert | last post by:
Hello All, I am looking at the performance of our production database. It is 40gb, and growing reasonably fast. It is placed in one file group on a RAID-5 array. The array is made up of 20 (or so) 9gb disks. The data, the indexes and the transaction log are all on the "one logical disk". My question then, is, would it be better to move the transaction log onto a separate device (with Raid-1), and then separate out the indexes and the...
1
4350
by: Jeff Roughgarden | last post by:
We are hosting a 140 GB database on SQL Server Version 7 and Windows 2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5 SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to the OS) and a 4 GB swap file. (The PeopleSoft CIS application will not permit us to upgrade to SQL 2K.) We recently upgraded the server from 4 to 8 cpus and the SAN disks from 10K to 15K drives. But we still have heavy SAN disk usage,...
4
4696
by: esmith2112 | last post by:
We replaced an aging system with a newer (faster 4-way) and presumably better system. Perfomance for most queries has improved as one would expect. However, we're getting slaughtered on performance for summary-style queries. What took 2 minutes now takes 2-3 hours to complete. We backed up DB and restored it on a borrowed (somewhat inferior class) box to see if we could do some diagnostics to determine why we had such horrid...
6
4059
by: Andrew Robinson | last post by:
I am running two servers with a hardware network load balancing device. I know that to share session information between the two servers I need to implement some type of SQL based session information, but I would simply like to share the SessionID. Mainly for some logging applications. The SessionID does currently seem to be shared between the two servers and I haven't changed anything with my configuration or implemented anything...
8
1293
by: Mike Read | last post by:
Hi On our SQl Server (2000) we can have queries that take at least 10-20mins (require full table scan fo billion row table). When one of these queries is running it substantailly slows down very quick queries (sub second queries take several seconds). I believe there is no way to set priorities but was wondering if there are other configuration settings that could help. The server is dual processor so maybe setting maxdop to 1 will...
13
15402
by: Dave | last post by:
RAID 5 beats RAID 10 Can I get some feedback on these results? We were having some serious IO issues according to PerfMon so I really pushed for RAID 10. The results are not what I expected. I have 2 identical servers. Hardware: PowerEdge 2850
0
1016
by: dave1678 | last post by:
I need to separate files into to specific file groups to spread across several raid 1 devices. Since database admin is a new concept to me I would like to know what is the best way to decide on how to separate the file groups should I recode perfmon and what should I look for. Most transactions per second, Stored procedures and queries should I pay close attention to joins just a few guidelines would be of great help. Thanx in advance...
2
2357
by: rcamarda | last post by:
With help of others on this group, I've been learning and researching about indexes; an area I neglected. I see I can specify which filegroup I wish to create an index, which the default is Primary. I have more than one drive in my SQL server where I put data and logs on their own logical raid groups. My databases are SIMPLE, so they dont use much, if any logs (none as I understand). I was thinking of adding an additional file to my...
1
2265
by: m.a | last post by:
Hello, I am looking for a hosting solution for my asp.net application. I found that some ISP stated that session states are not preserved in a load balancing system. As I know, if the asp.net is configured to save the session states in SQL server or file system, it would be preserved in load balancing systems. What is the whole story from the hosting point of view?
0
8619
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8555
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8824
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6484
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4336
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4571
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2258
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.