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 6 5630
"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
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
"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.
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.
"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. :-)
:)
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. :-)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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,...
|
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...
|
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...
|
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...
| |
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
|
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...
|
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...
|
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?
|
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...
|
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,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
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...
|
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
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |