|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Table Partitioning vs. RAID Technology
Hi,
I'm wondered that which strategy (only in terms of performance) is better: Suppose that you have 5 disks on your SQL Server. 1 goes for the Log File, but for the other 4 disks, you can choose RAID 0 (Striping) that has great IO performance, or you can partition your big and critical "Orders" and "Order Details" tables based on each season. I'm only considering performance. How should I make decision? Any help would be greatly appreciated. Leila On 04.05.2007 13:59, Leila wrote:
> I'm wondered that which strategy (only in terms of performance) is better: My gut feeling tells me to go with RAID because increasing IO bandwidth > Suppose that you have 5 disks on your SQL Server. 1 goes for the Log File, > but for the other 4 disks, you can choose RAID 0 (Striping) that has great > IO performance, or you can partition your big and critical "Orders" and > "Order Details" tables based on each season. > I'm only considering performance. How should I make decision? > Any help would be greatly appreciated. is what RAID is good at and you will always benefit, even if you pull data that would sit in a single partition according to your model. Which RAID level etc. is a different question. Regards robert Raid 0 is the way to go.
-- Show quoteHide quoteI drank alot of beer and ended up in the police department database. Drank more beer and learned SQL in the dark hours. DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006; I love SQL :) "Leila" wrote: > Hi, > I'm wondered that which strategy (only in terms of performance) is better: > Suppose that you have 5 disks on your SQL Server. 1 goes for the Log File, > but for the other 4 disks, you can choose RAID 0 (Striping) that has great > IO performance, or you can partition your big and critical "Orders" and > "Order Details" tables based on each season. > I'm only considering performance. How should I make decision? > Any help would be greatly appreciated. > Leila > > > "Leila" <Lei***@hotpop.com> wrote in message Well I would NEVER consider RAID 0 for anything that I consider "critical". news:engI5OkjHHA.3264@TK2MSFTNGP04.phx.gbl... > Hi, > I'm wondered that which strategy (only in terms of performance) is better: > Suppose that you have 5 disks on your SQL Server. 1 goes for the Log File, > but for the other 4 disks, you can choose RAID 0 (Striping) that has great > IO performance, or you can partition your big and critical "Orders" and > "Order Details" tables based on each season. > I'm only considering performance. How should I make decision? It's bad enough not having your LOG file on a RAID disk, but having data on a 4 disk RAID 0 setup simply increases your chances of failure by a factor of 4. So RAID 0 isn't really an option here. So I'd reconsider my decision to only consider performance. That said, w/o knowing how large your Orders and [Order Details] tables are and what sort of work you expect to do, partitioning may or may not really be required. > Any help would be greatly appreciated. > Leila > -- Greg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html Hi Leila,
The argument could be made that as long as you have a database backup safely somewhere and an unbroken log chain and the log on RAID 1 and you could accept an increase liklihood of downtime then RAID 0 would work and offer great performance. That said, you'd be mad; fault tolerance should always be there.... Whether you create a single RAID 10 array or 2 RAID 1 arrays is another question; RAID 10 would give better throughput because of the striping and more spindles; that said - the 2 RAID 1 arrays can be used with partitioning (seperate file groups) if you know your IO patterns well enough - its all a disk head seek thing, if the Orders and OrderDetail is on the same RAID 10 array then you'll have disk head movement, whereas on 2 RAID 1's, you could have better throughput because of disk head placement (less seeking). The only real way to understand what's best for you is to do some realistic trials with your kit. -- Show quoteHide quoteTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] "Leila" <Lei***@hotpop.com> wrote in message news:engI5OkjHHA.3264@TK2MSFTNGP04.phx.gbl... > Hi, > I'm wondered that which strategy (only in terms of performance) is better: > Suppose that you have 5 disks on your SQL Server. 1 goes for the Log File, > but for the other 4 disks, you can choose RAID 0 (Striping) that has great > IO performance, or you can partition your big and critical "Orders" and > "Order Details" tables based on each season. > I'm only considering performance. How should I make decision? > Any help would be greatly appreciated. > Leila > Thanks Tony,
I'm always in doubt about real functionality of partitioning for performance. Could you please give an example where partitioning is recommended (when RAID also can be available) Thanks again. Show quoteHide quote "Tony Rogerson" <tonyroger***@torver.net> wrote in message news:uPgaDTmjHHA.460@TK2MSFTNGP05.phx.gbl... > Hi Leila, > > The argument could be made that as long as you have a database backup > safely somewhere and an unbroken log chain and the log on RAID 1 and you > could accept an increase liklihood of downtime then RAID 0 would work and > offer great performance. > > That said, you'd be mad; fault tolerance should always be there.... > > Whether you create a single RAID 10 array or 2 RAID 1 arrays is another > question; RAID 10 would give better throughput because of the striping and > more spindles; that said - the 2 RAID 1 arrays can be used with > partitioning (seperate file groups) if you know your IO patterns well > enough - its all a disk head seek thing, if the Orders and OrderDetail is > on the same RAID 10 array then you'll have disk head movement, whereas on > 2 RAID 1's, you could have better throughput because of disk head > placement (less seeking). > > The only real way to understand what's best for you is to do some > realistic trials with your kit. > > -- > Tony Rogerson, SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson > [Ramblings from the field from a SQL consultant] > http://sqlserverfaq.com > [UK SQL User Community] > > > "Leila" <Lei***@hotpop.com> wrote in message > news:engI5OkjHHA.3264@TK2MSFTNGP04.phx.gbl... >> Hi, >> I'm wondered that which strategy (only in terms of performance) is >> better: >> Suppose that you have 5 disks on your SQL Server. 1 goes for the Log >> File, but for the other 4 disks, you can choose RAID 0 (Striping) that >> has great IO performance, or you can partition your big and critical >> "Orders" and "Order Details" tables based on each season. >> I'm only considering performance. How should I make decision? >> Any help would be greatly appreciated. >> Leila >> >
Show quote
Hide quote
"Leila" <Lei***@hotpop.com> wrote in message
news:%23mmUOtmjHHA.5012@TK2MSFTNGP06.phx.gbl... > Thanks Tony, > I'm always in doubt about real functionality of partitioning for > performance. > Could you please give an example where partitioning is recommended (when > RAID also can be available) > Thanks again. > > > "Tony Rogerson" <tonyroger***@torver.net> wrote in message > news:uPgaDTmjHHA.460@TK2MSFTNGP05.phx.gbl... >> Hi Leila, >> >> The argument could be made that as long as you have a database backup >> safely somewhere and an unbroken log chain and the log on RAID 1 and you >> could accept an increase liklihood of downtime then RAID 0 would work and >> offer great performance. >> >> That said, you'd be mad; fault tolerance should always be there.... >> >> Whether you create a single RAID 10 array or 2 RAID 1 arrays is another >> question; RAID 10 would give better throughput because of the striping >> and more spindles; that said - the 2 RAID 1 arrays can be used with >> partitioning (seperate file groups) if you know your IO patterns well >> enough - its all a disk head seek thing, if the Orders and OrderDetail is >> on the same RAID 10 array then you'll have disk head movement, whereas on >> 2 RAID 1's, you could have better throughput because of disk head >> placement (less seeking). >> >> The only real way to understand what's best for you is to do some >> realistic trials with your kit. >> >> -- >> Tony Rogerson, SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson >> [Ramblings from the field from a SQL consultant] >> http://sqlserverfaq.com >> [UK SQL User Community] >> >> >> "Leila" <Lei***@hotpop.com> wrote in message >> news:engI5OkjHHA.3264@TK2MSFTNGP04.phx.gbl... >>> Hi, >>> I'm wondered that which strategy (only in terms of performance) is >>> better: >>> Suppose that you have 5 disks on your SQL Server. 1 goes for the Log >>> File, but for the other 4 disks, you can choose RAID 0 (Striping) that >>> has great IO performance, or you can partition your big and critical >>> "Orders" and "Order Details" tables based on each season. >>> I'm only considering performance. How should I make decision? >>> Any help would be greatly appreciated. >>> Leila >>> >> > > "Leila" <Lei***@hotpop.com> wrote in message They're solving two different issues really.news:%23mmUOtmjHHA.5012@TK2MSFTNGP06.phx.gbl... > Thanks Tony, > I'm always in doubt about real functionality of partitioning for > performance. > Could you please give an example where partitioning is recommended (when > RAID also can be available) Yes, both can help with performance, but in different ways. With Partitioning, the example you use is not necessarily a bad use. Especially if you routinely need to "drop" or move a particular partition. Say you only keep 4 quarters worth of complete data. Rather than having to do a "delete from FOO where dates between X and y" you can drop the oldest partition which will be far faster and have less impact on I/O. Or, you can have partitions setup so you can back them up separately which can enhance your data recovery model. You can with Enterprise restore only specific partitions and keep working while you restore the others. So in the case of a major data failure, you might restore the partition containing the most current data so you can restart your OLTP functionality while restoring the other partitions as time permits and gradually restore your data warehouse funtionality. RAID also have the advantage of protecting against disk loss if you use something other than RAID 0. (Which technically isn't REDUNDANT). Typically for a DB you'll see RAID 1, 5, 10, 50. Chosing the wrong RAID method can actually HURT performance, but may buy you other benefits such as more redundancy and/or space (like RAID 5 for logs. generally a bad idea from a performance POV, but a plus if disk space is an issue.) Show quoteHide quote > Thanks again. > > > "Tony Rogerson" <tonyroger***@torver.net> wrote in message > news:uPgaDTmjHHA.460@TK2MSFTNGP05.phx.gbl... >> Hi Leila, >> >> The argument could be made that as long as you have a database backup >> safely somewhere and an unbroken log chain and the log on RAID 1 and you >> could accept an increase liklihood of downtime then RAID 0 would work and >> offer great performance. >> >> That said, you'd be mad; fault tolerance should always be there.... >> >> Whether you create a single RAID 10 array or 2 RAID 1 arrays is another >> question; RAID 10 would give better throughput because of the striping >> and more spindles; that said - the 2 RAID 1 arrays can be used with >> partitioning (seperate file groups) if you know your IO patterns well >> enough - its all a disk head seek thing, if the Orders and OrderDetail is >> on the same RAID 10 array then you'll have disk head movement, whereas on >> 2 RAID 1's, you could have better throughput because of disk head >> placement (less seeking). >> >> The only real way to understand what's best for you is to do some >> realistic trials with your kit. >> >> -- >> Tony Rogerson, SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson >> [Ramblings from the field from a SQL consultant] >> http://sqlserverfaq.com >> [UK SQL User Community] >> >> >> "Leila" <Lei***@hotpop.com> wrote in message >> news:engI5OkjHHA.3264@TK2MSFTNGP04.phx.gbl... >>> Hi, >>> I'm wondered that which strategy (only in terms of performance) is >>> better: >>> Suppose that you have 5 disks on your SQL Server. 1 goes for the Log >>> File, but for the other 4 disks, you can choose RAID 0 (Striping) that >>> has great IO performance, or you can partition your big and critical >>> "Orders" and "Order Details" tables based on each season. >>> I'm only considering performance. How should I make decision? >>> Any help would be greatly appreciated. >>> Leila >>> >> > > -- Greg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html Where are you putting the OS and paging file?
The thing about partitioning is that it should allow table scans to look at just one partition, it's more of a logical thing, and also good way to do rolling quarters and such. On the RAID, you might even want to have two for the log, that's RAID 1 (mirrored). I usually just figure RAID5 for the rest, but really, tempdb does a lot of writing, so if you can get that on a RAID 1 or 10, it can be a good thing. If you have an app that rebuilds big tables frequently for some ETL purposes, as I have, that might also be profitably put onto a RAID 1 or 10 instead of 5, in fact, you might want to put it on a separate database and put that on a RAID 1 or 10 or even 0 or none, and run it in simple backup mode, if you can freely do reruns. Lots of stuff you can do physically, for one degree or another of performance boost. Sadly, in my world, I seldom get around to it. I come in, study the situation, restore the PK, put a covering index on the critical query, everything is then running 10x faster so I then get the golden handshake. Ah well. :) And of course, always have at least 4gb of RAM on your 32 bit machines, and as much as you can get on the 64 bit machines! Again, that can be such a boost, disk performance problems are forgotten. J. Show quoteHide quote On Fri, 4 May 2007 20:13:02 +0330, "Leila" <Lei***@hotpop.com> wrote: >Thanks Tony, >I'm always in doubt about real functionality of partitioning for >performance. >Could you please give an example where partitioning is recommended (when >RAID also can be available) >Thanks again. > > >"Tony Rogerson" <tonyroger***@torver.net> wrote in message >news:uPgaDTmjHHA.460@TK2MSFTNGP05.phx.gbl... >> Hi Leila, >> >> The argument could be made that as long as you have a database backup >> safely somewhere and an unbroken log chain and the log on RAID 1 and you >> could accept an increase liklihood of downtime then RAID 0 would work and >> offer great performance. >> >> That said, you'd be mad; fault tolerance should always be there.... >> >> Whether you create a single RAID 10 array or 2 RAID 1 arrays is another >> question; RAID 10 would give better throughput because of the striping and >> more spindles; that said - the 2 RAID 1 arrays can be used with >> partitioning (seperate file groups) if you know your IO patterns well >> enough - its all a disk head seek thing, if the Orders and OrderDetail is >> on the same RAID 10 array then you'll have disk head movement, whereas on >> 2 RAID 1's, you could have better throughput because of disk head >> placement (less seeking). >> >> The only real way to understand what's best for you is to do some >> realistic trials with your kit. >> >> -- >> Tony Rogerson, SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson >> [Ramblings from the field from a SQL consultant] >> http://sqlserverfaq.com >> [UK SQL User Community] >> >> >> "Leila" <Lei***@hotpop.com> wrote in message >> news:engI5OkjHHA.3264@TK2MSFTNGP04.phx.gbl... >>> Hi, >>> I'm wondered that which strategy (only in terms of performance) is >>> better: >>> Suppose that you have 5 disks on your SQL Server. 1 goes for the Log >>> File, but for the other 4 disks, you can choose RAID 0 (Striping) that >>> has great IO performance, or you can partition your big and critical >>> "Orders" and "Order Details" tables based on each season. >>> I'm only considering performance. How should I make decision? >>> Any help would be greatly appreciated. >>> Leila >>> >> >  
Other interesting topics
finding a string somewhere in the MS SQL user tables
SQL - Linking Tables Error installing SQL 2005 SP2 KB 921896 SQL Server 2005 - newbie question Case Statement Woes RECEIVE in transactions Difference between Index & Statistics AMD vs Intel for SQL Servers SQL Server 2005 Sgent will not start - Service Time out error CR & LF Problems |
|||||||||||||||||||||||
