February 4, 2011 at 3:19 pm
Rich Yarger (2/4/2011)
Now - by this do you mean that the RAID 5 structure for the Filegroups should be replicated if for some reason this system acquires more db's over time? Which - believe it or not - has happened 4 times now prior to roleout! I think I understand what you mean, and yes - this is how I was taught when I went through formal training - spread out the disk IO.
Not exactly, but I'm apparently not being clear in what I'm trying to express. I'll be a little more detailed.
I'd personally take your RAID5 4spindle array and split it to two logical drives. First would be for TempDB, in case of mishap and/or bad code. I'd grow it to around 85% of the assigned space.
The lion's share of the physical, which is now one logical drive, I'd load up all my filegroups on. Now, you've made your choices as to what you're looking to do, but I'll make this a bit simpler.
Let's say I've got one database with 3 FileGroups. The first holds the ClusteredIndex to a massive logging table that everything else hangs off of. The second holds the nonclustered indexes for said table. The third holds my lookup table information.
The idea here is that I've got a physical storage, a nonclustered storage, and everything else that hooks off this primary storage (if it helps, think Star Schema... 😉 ).
Now, my main table finally gets Huuuuuge. Too big for here. Instead of having to move the entire database to this pretty new external array my network guys just hung on the rack for me, I can simply move my Clustered Index Filegroup. Suddenly, huge performance boost. My lookups are on one set of spindles, and my Clustered index is on another. I've got multiple read I/O.
It won't make a difference, for now. If this suddenly grows out of proportion, the multiple filegroups introduced now give you options that are painful to introduce later.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 3:37 pm
That is perfect...I think I should be able to reconfigure the RAID5 Array without too much difficulty at this point before rollout.
Craig, thank you again for all of your wisdom here, and I'll let you know how it all turns out!
Rich Yarger
SQL Server DBA
February 18, 2011 at 10:44 am
Hi Craig -
Wanted to pick your brain on one other idea for this RAID configuration I have been working on. We previously discussed the idea of putting tempdb in his own filegroup, and placing that on one of the local RAID 1 arrays. I'm glad you pointed out the error in that thinking, but I like your idea of Filegroups for the indexes, and was curious to hear your thoughts on putting that file group on my RAID 1 C:\DRIVE. Will that has the same type of IO issues that tempdb would? Or do you think that placing them there would give me some additional performace benefits, as opposed to having them in their own Filegroup out in the RAID 5 array?
Regardless - thank you again for all of your pointers in this discussion. I am looking forward to verifying the configuration that the colo-host did, and get things moving on this project.
🙂
February 18, 2011 at 12:06 pm
Rich Yarger (2/18/2011)
Hi Craig -Wanted to pick your brain on one other idea for this RAID configuration I have been working on. We previously discussed the idea of putting tempdb in his own filegroup, and placing that on one of the local RAID 1 arrays. I'm glad you pointed out the error in that thinking, but I like your idea of Filegroups for the indexes, and was curious to hear your thoughts on putting that file group on my RAID 1 C:\DRIVE.
There's two important parts to understanding why you typically don't want to much around with your OS install drive. First is the swapfile, the second is OS reinstalls. Putting things on C:\ risks interfering with these items.
Will that has the same type of IO issues that tempdb would? Or do you think that placing them there would give me some additional performace benefits, as opposed to having them in their own Filegroup out in the RAID 5 array?
Regardless - thank you again for all of your pointers in this discussion. I am looking forward to verifying the configuration that the colo-host did, and get things moving on this project.
It would have less, but it's still not a good idea. It's really going to depend on how much you need the swapfile, which will depend a lot on your RAM consumption. If you're going to put things on C:\ though, I'd recommend it be the TempDB, not real database files. If your OS craps the bed, you don't want anything of import on its drives, you want to be able to reinstall your ghost ASAP.
However, all of that said... Yes, you would definately get better performance if you aren't abusing your swapfile.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 18, 2011 at 1:35 pm
Craig, thank you again. I think the only other thing that will go on C:\DRIVE is going to be the backups, and that is it. That is the only thing that will not go into the RAID 5 Array (because frankly, I'm going to have plenty of room there on C:\DRIVE for the next 5 years), and I believe the risk here is semi-minimal (with it being RAID 1, I'm not as worried as I was iniitally with the RAID 0 design I had initially planned for C: and D:).
Thank you again for this guidance. I want the best performance possible, but I need to hold back on some of the compromises to get there.
February 18, 2011 at 1:53 pm
Rich Yarger (2/18/2011)
Craig, thank you again. I think the only other thing that will go on C:\DRIVE is going to be the backups, and that is it. That is the only thing that will not go into the RAID 5 Array (because frankly, I'm going to have plenty of room there on C:\DRIVE for the next 5 years), and I believe the risk here is semi-minimal (with it being RAID 1, I'm not as worried as I was iniitally with the RAID 0 design I had initially planned for C: and D:).Thank you again for this guidance. I want the best performance possible, but I need to hold back on some of the compromises to get there.
As usual, it's a balance of tradeoffs, you're right. I would mention again to make sure you've got separate server copies of your backups, even if you do leave a local copy on C:\, make sure it's not the only storage for your backups. Get a copy completely off the local arrays.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2011 at 12:10 pm
Craig if I may - just one last question on this subject, because you have mentioned it a couple of times. As I have previously stated, the server will be running 2003 Server 64-bit. Should I just allow Windows to manage the virtual memory? Or would you recommend some other scheme for this system? It's going to be an OLTP version of 2005, and will probably not exceed 100gb over a 5 year period.
Many thanks again!
February 20, 2011 at 1:07 am
Rich Yarger (2/19/2011)
Craig if I may - just one last question on this subject, because you have mentioned it a couple of times. As I have previously stated, the server will be running 2003 Server 64-bit. Should I just allow Windows to manage the virtual memory? Or would you recommend some other scheme for this system? It's going to be an OLTP version of 2005, and will probably not exceed 100gb over a 5 year period.Many thanks again!
To clarify, do you mean virtual machine memory, or dedicated server RAM? If dedicated server, I'd allocate RAM to SQL at around max - 4 GB (for the OS). If VM, errrr... that'd depend on a few things.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 11:04 am
Sorry Craig - that was kind of vague. I was actually trying to ask about the Virtual Memory that the 2003 Server would be using from my C:\DRIVE. I was wondering if I should leave it up to Windows to decide on what it wants, when it wants it, or if I should make a static partition.
But you have me curious now - what is the other half of this that you weren't certain if I was asking about?
February 22, 2011 at 11:16 am
Let me clarify further - here is what I am thinking from your suggestion:
- min server memory / max server memory = 4gb
- Virtual Memory Partition for Windows 2003 Server = Static = 4gb
This is an 8gb system, so obivously this may not be what I am wanting, but then again - I think it is.
I've decided to keep the bottom and top ends the same as a first practice, because according to Microsoft's Server Memory Options, on-line, they recommend this first, if other apps are not running on the server for SQL to compete with. There will be a web server/services, and an email services called NEXUS e2e to start (e.g. for the BETA), but nothing else other than that.
February 22, 2011 at 11:44 am
Rich Yarger (2/22/2011)
Let me clarify further - here is what I am thinking from your suggestion:- min server memory / max server memory = 4gb
- Virtual Memory Partition for Windows 2003 Server = Static = 4gb
This is an 8gb system, so obivously this may not be what I am wanting, but then again - I think it is.
I've decided to keep the bottom and top ends the same as a first practice, because according to Microsoft's Server Memory Options, on-line, they recommend this first, if other apps are not running on the server for SQL to compete with. There will be a web server/services, and an email services called NEXUS e2e to start (e.g. for the BETA), but nothing else other than that.
Under most circumstances I let Windows manage the swapfile. VM has settings that you can control each virtual machine's instance and throttle them back, so that's why it would depend on some things.
You had me doublechecking that I hadn't misread things along the way here. Virtual Memory Partition is just the size of the swapfile on the drive. I wouldn't set that to static, but I would keep up some perfmon counters on hard page faults and the like to track its usage.
What you're looking at doing with those settings is setting SQL Server take over 4GB of RAM, leave 4GB free for whatever else needs to work on the box (OS and email, for example), and limit the swap file to 4 GB on the drive. That's a reasonable start, but I personally prefer to monitor swapfile usage instead of set a hard limit (part of why I leave the C:\ free. Things go full circle! 😉 )
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply