December 28, 2008 at 12:25 pm
kendal.vandyke (12/28/2008)
Perry Whittle (12/28/2008)
jlp3630 (12/27/2008)
Assuming that the system was running, how would you go about systematically getting baselines for reads/writes?as you say, depends if the system is already live. If your scoping new disks for a current server then you should already have baseline data for disk I\O anyway. If your scoping for a complete new system then the database vendor would be a good start (if a 3rd party app) or try it in a test lab if you have it.
Perry,
Telling him that he should already have baseline metrics didn't really answer his question.
jlp3630, you said that your system was already running...here are some ways you can gather baseline metrics:
1) Set up a performance monitor counter log that writes to a SQL database. You can write ad-hoc queries to look at the raw data.
2) Use a tool like SQLH2 to gather perfmon metrics on a schedule, then use the reports that come with the tool to look at the numbers the tool collected. You can also use your own queries directly against the SQLH2 repository if you don't like the pre-canned reports.
3) Use a 3rd party tool like SQLSentry Performance Advisor or Idera SQL Diagnostic Manager[/url]
#1 & #2 are free but don't have the "polish" of the 3rd party tools.
thats rich, all you've told him to do is run perfom. What about a list of the counters to use. A simple google search or search on this site would give reference to pages that detail how to monitor sql counters.
if the poster wants detailed info on how to monitor sql server they should create their own topic and not hijack someone elses thread
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 28, 2008 at 1:05 pm
RAID 10 will generally offer a performance boost over RAID1 due to the striping across mirrored sets. As SQL server writes to the data files are a fairly random affair this suites sql server just fine. As RbarryYoung pointed out the log file is more important as it is suffers sustained serial writes and so performance requirements are different here. It all depends on how many disks and controllers are used in the config too. Also do you have any baseline for the expected reads\writes, if not it may be worth obtaining some?
The question wasn't about where to put log files; it was dealing strictly with performance of data files using four drives configured as either two RAID 1 drives vs. a single RAID 10 drive.
On what are you basing "SQL server writes to the data files are a fairly random affair"? SQL round robins writes to files in the same filegroup. If I have two RAID 1 drives, each holding a file belonging to the same filegroup and writes are done round robin between those two files that's striping across mirrored sets.
December 28, 2008 at 1:08 pm
thats rich, all you've told him to do is run perfom. What about a list of the counters to use. A simple google search or search on this site would give reference to pages that detail how to monitor sql counters.
if the poster wants detailed info on how to monitor sql server they should create their own topic and not hijack someone elses thread
No more rich than telling the guy that he should already have those numbers, which didn't even answer his question. At least I gave him a place to start by pointing him in the right direction. If you had a problem with him asking a question related to one of your replies why give him that kind of answer in the first place? Just tell him to start a new thread.
December 28, 2008 at 1:27 pm
I support kandal.vandyke's purpose of starting this post as I am curious to know the detailed technical difference between the two RAID's..but would like to throw some stuff from my side
RAID 1 Disadvantages:
Highest disk overhead of all RAID types (100%) - inefficient
Typically the RAID function is done by system software, loading the CPU/Server and possibly degrading throughput at high activity levels. Hardware implementation is strongly recommended
May not support hot swap of failed disk when implemented in "software"
Not to mention RAID 10 array can sustain multiple simultaneous drive failures..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
December 28, 2008 at 2:48 pm
kendal.vandyke (12/23/2008)
Lots of great articles on the intertubez about disk alignment and RAID configurations but I haven't found an answer (or a good way to test, and yes I know about SQLIO) for a simple scenario:Suppose I have 4 local physical disks available to me and I'm going to create an OLTP DB (for the sake of arguing let's say we're 50\50 on reads and writes, or otherwise average usage). Here are two scenarios that I would consider:
1) Two RAID 1 drives. Create the DB with two data files, i.e. one data file on each drive
2) One RAID 10 drive. Create the DB with one single data file
In the RAID 1 scenario (as I understand it) SQL will round robin writes between the two data files, thus creating a software equivalent of striping. RAID 10, on the other hand, handles the striping at a hardware level. Those differences aside, I haven't found a good technical explanation - or numbers to back it - for what's happening under the covers that would make me believe one option is better than the other.
So which of these two scenarios is more ideal and why?
Here is my understanding as a hardware amateur:
I don't think the RAID 1 scenario is like a software raid.
Although SQL Server does allocate space homogeneously across the two data files, this does not happen immediately.
Either the lazy writer or a checkpoint will write dirty pages to disk, but the order by which these are written to disk we do not know.
Striping, as I understand it, makes more sense with an OLAP db where huge amounts of data are read frequently from disk (and not from the buffer)
If only small amounts of data (like in OLTP) are accessed, striping might not provide the expected benefit (like flying with a private plane to congress;-)
The comment regarding multiple threads was also important. You do not want to have only one file for a heavily used DB that does not reside mostly in memory for most operations.
Also, an OLTP db is more or less random access. This complicates things even more.
As said before, I am not really an expert in this topic, so please use these comments with care. And please correct me if any of these assumptions are wrong or incomplete.
Best Regards,
Chris Büttner
December 28, 2008 at 3:03 pm
The_SQL_DBA (12/28/2008)
RAID 1 Disadvantages:Highest disk overhead of all RAID types (100%) - inefficient
Typically the RAID function is done by system software, loading the CPU/Server and possibly degrading throughput at high activity levels. Hardware implementation is strongly recommended
May not support hot swap of failed disk when implemented in "software"
Not to mention RAID 10 array can sustain multiple simultaneous drive failures..
Thanks!!
I'm not sure the above is necessarily true (other than the hardware vs software point - and I'd never want to implement software raid in a meaningful environment). I'm certainly not an IO expert, but isn't raid 10 also 100% overhead? It still has to mirror the data on a 1:1 basis.
As to raid 10 allowing more simultaneous failures - that is true, but only because it has more drives. All is well unless both drives in a pair fail. I would presume this to be true regardless of whether it's 1+0 or 0+1.
December 28, 2008 at 9:12 pm
First off, thanks for the reply...
RAID 1 Disadvantages:
Highest disk overhead of all RAID types (100%) - inefficient
RAID 10 suffers the same 100% inefficiency as RAID 1 with regards to mirroring of disks.
Not to mention RAID 10 array can sustain multiple simultaneous drive failures..
Only if the drives that fail are not in the same pair. Lose the two drives in a mirrored set and you are SOL, no different than RAID 1.
December 28, 2008 at 10:22 pm
matt stockham (12/23/2008)
If I remember correctly, fragmentation statistics aren't accurate for multiple files either.
Great idea, and for clarification I looked this one up. The value for Extent Scan Fragmentation in DBCC SHOWCONTIG is not accurate for an index spread across multiple files (See BOL here).
In 2005\2008 the value shown for avg_fragmentation_in_percent in the sys.dm_db_index_physical_stats DMV does take into account multiple files (see BOL here).
December 29, 2008 at 2:57 pm
It appears to me that the real question here is whether to choose between RAID10 (a hardware based RAID solution) and letting SQL stripe across multiple data files on separate RAID1 arrays (a software based stripping solution using RAID1).
My experience tells me to choose the hardware raid10 over a any software raid alternative. Less system overhead this way and higher IO throughput since the controller does most of the work.
Using RAID1 has no performance increase over using a single disk so essentially the only way of gaining any performance is to allow SQL to do the stripping which is not what I consider a hardware raid.
December 29, 2008 at 3:38 pm
Someone mentioned that you might gain some performance because the OS is accessing two files in parallel, instead of one. However, there is nothing that prevents you from having two (or more) data files on a single RAID 10 array, instead of having one (or more) data file on each of two RAID 1 sets.
December 29, 2008 at 3:43 pm
Hardware is the only RAID I'd recommend for any server. Maybe software on a desktop, but not a server.
I'm not sure you gain from having two files on one device v two devices. I think this is highly dependent on the workload and it might even vary on the same instance at different times.
I still vote for R10
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 29, 2008 at 4:31 pm
while its true that with RAID10 a failure of a mirrored pair is terminal. Because of the amount of disks required for RAID10 its a sort of safety in numbers type scenario, the likelyhood of a failed mirrored pair is reduced and of course you have always got backups 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 29, 2008 at 4:43 pm
borismoyse (12/29/2008)
Using RAID1 has no performance increase over using a single disk so essentially the only way of gaining any performance is to allow SQL to do the stripping which is not what I consider a hardware raid.
No, RAID 1 has twice the Read IO throughput of a single disk.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 29, 2008 at 7:07 pm
Perry Whittle (12/28/2008)
jlp3630 (12/27/2008)
Assuming that the system was running, how would you go about systematically getting baselines for reads/writes?as you say, depends if the system is already live. If your scoping new disks for a current server then you should already have baseline data for disk I\O anyway. If your scoping for a complete new system then the database vendor would be a good start (if a 3rd party app) or try it in a test lab if you have it.
Perry,
What data would you collect for your "baseline data"? For example, if you were using perfmon, what counters would you monitor and with what sampling frequency?
December 30, 2008 at 4:05 am
Steve's topic at the following link should get you started
http://qa.sqlservercentral.com/articles/Administering/performancemonitoringbasiccounters/1348/
also check out Brad McGehee's topics on this subject. A google search should provide the link for that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply