December 7, 2011 at 3:05 pm
Hi folks,
it's time for a (partial) hardware replacement and our company decided to provide a SAN as a replacemenet of the dedicated drives of let's say 10 server with two drives each, originally.
To be specific: there'll be only one rather small drive per server just to hold the OS and the pagefile, eventually. (I say "eventually" since the the folks responsible
for the sizing decided to use a standard config of less than 50GB, regardless of the servers memory. My question how they would provide a full memory dump for a server with 256GB memory in case of a support request to MS is "still under investigation"... but that's a different story).
The SAN will have 15 spindles in total and RAID0, RAID1, and RAID5 groups.
There will be about 50 SQL Server DBs (ranging from just a few MB up to 80GB) as well as several applications.
If I'm not completely wrong there'll be only max. 7 spindles net to hold all the LUNs (based on the RAID level and the physical number of spindles).
Even though the total size of storage capacity is enough to meet our needs, the number of physical spindles seems to be too low. From my perspective the better disc performance (e.g. 15krpm vs. currently 10krpm) will not compensate the parallel access from several server to the same spindle(s).
My question "Why not reduce the disc size in favor of more spindles?" has been answered with "The discs we've chosen are based on the lowest cost per GB and the best performance." I suggested to at least increase the local disc size to have TempDb stored locally (on a separate logical drive), but so far without any luck...
Am I worried for no reason?
Edit: I'm planning to "attack" the SAN using SQLIO concurrently from several servers prior to going into production to verify the performance metrics they've told us.
December 7, 2011 at 3:30 pm
There are lots of strategies that can be used. But I am a fan of RAID10 or Raid 0 + 1 or Raid 1 + 0, basically mirroring and striping, and unless someone can't point out some stats otherwise it is the fastest configuration in most cases..
A Quick look at http://en.wikipedia.org/wiki/RAID
It sounds like you are gonna be sharing these 15 spindles accross 10 servers. In most cases where I have control and available spindles I do RAID 10 for data log and tempdb. I am a little concerned about the number of servers sharing the storage but was thinking 6 drives RAID 10 for data, 4 drives RAID 10 for Logs, and 4 drives RAID 10 for Tempdb with 1 hot spare..
I will say I am by FAR not an expert on these things but I am usually looking for resiliency and performance.
When I got my new cluster I worked over all the drives with SQLIO, one drive at a time, then a couple at the same time, then ALL of them. It helped me understand what I could expect in terms of performance overall.
CEWII
December 7, 2011 at 3:53 pm
Thank you for the prompt response, Elliot. Much appreciated.
I'm concerned, too. Especially, since all of our DBs now would have to share the same physical drives for logs and tempdb. The (low) physical number of spindles together with the RAID concept you suggested (which makes sense) doesn't leave that much room for alternatives.
I guess I'll invest some more time in planning the SQLIO test. I wasn't sure if I should expand the test to include all server at the same time. But it seems like this will at least provide me some more detailed figures what to expect. "Worst case" could be to be able to demonstrate the concept failed (in terms of not providing the promised performance).
Did you run the same config file on each server at the same time or did you use different cofigurations?
December 7, 2011 at 4:00 pm
One of the things that I strongly recommend is formatting the SQL drives as 64K clusters. Which as I understand it allows SQL to grab 1 extent of data in a single read, where an extent is 64K, where the default which is 4K would require 16 reads..
I'm sure someone might be able to school me but thats my understanding..
Coincidently the way I laid it out Dat, Log, and Tempdb, their spindles are not shared within a server but ARE shared among the servers.. Server A uses the same spindles as Server B for Data, but Server A uses different spindles for Data/Log/TempDb, Server B uses the same spindles as Server A.
You might even consider not changing a little server in favor of less sharing.. Not sure of your hardware age..
CEWII
December 7, 2011 at 4:11 pm
The SAN is provided by a 3rd party and a different group of our company is responsible for it.
But I'll try to raise the 64k formatting you mentioned. Let's see how they react...
The server we currently replace are three years old. Some of those would easily run for a few more years but due to some policies there'll be a replacement every three years without any further discussion. Is it the rule? Yes. Does it makes sense? No.
If I would have a vote I wouldn't replace half of the server at this time. But then the SAN would be over-sized leading to an even lower number of spindles (what would be your preferred spindle-setup for 8 spindles instead of 15 😉 ). Unfortunately, the folks responsible for the SAN are only calculating by storage space required.
December 7, 2011 at 4:24 pm
Don't give them a choice.. Once the disk is presented to the machine it can be formatted any way you like.. I wouldn't bend on this topic.. Reformat it when you get it. There are some topics that are not open for discussion and this is one of them.
3y.. Hm, Actually I consider that old.. And I would argue it does make sense. Especially in light of things like hardware maintenance agreements that for after a certain period the vendor won't do OR jacks the price up. I think HP/Compaq does this.. This radically increases the risks associated with a hardware failure and that hardware might not be availabe to replace and you are then forced into a disorderly upgrade. So yeah, I think it makes sense. And in a lot of ways is good since we have a predictable maintenance schedule but the machines should be spaced so not ALL or even a substantial number get replaced at a time.
I like more spindles, so 15 isn't a problem, but of the 15 I would keep one out as a hot spare, but thats me.. I would break the remainder down the way I discussed earlier.. I was thinking same number of spindles less machines connecting to it..
Also, how will the connection to the SAN be made, Fiber? iSCSI?, other?
CEWII
December 7, 2011 at 4:33 pm
Yes on the 64K, get them to format as 64K clusters. If filestreaming with larger files, you may want larger cluster sizes.
The spindles seems off.
The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).
More spindles would be a far better idea.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2011 at 4:51 pm
SQLRNNR (12/7/2011)
Yes on the 64K, get them to format as 64K clusters. If filestreaming with larger files, you may want larger cluster sizes.The spindles seems off.
The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).
More spindles would be a far better idea.
I hear both of ya!! :crying:
I'll keep trying to talk those folks into more spindles and much larger local drives (the fact they still didn't answer my memory dump related question counts for our group I guess...).
I'll also insist on the 64K setting. Thanks to both of you for bringing that up!
Right now nothing is ordered yet. Fortunately. I might need to get management involved if we can't reach an agreement. Seems like it's becoming a "battle zone" requiring a little more attention... 😎
If I can't "win the battle" before the metal & fibre get installed, I'll do a little stress test (well, I'll do it anway...). If they can prove me wrong, I'd be happy... (sort of, at least)
December 7, 2011 at 6:11 pm
LutzM (12/7/2011)
I hear both of ya!! :crying:I'll keep trying to talk those folks into more spindles and much larger local drives (the fact they still didn't answer my memory dump related question counts for our group I guess...).
I'll also insist on the 64K setting. Thanks to both of you for bringing that up!
Right now nothing is ordered yet. Fortunately. I might need to get management involved if we can't reach an agreement. Seems like it's becoming a "battle zone" requiring a little more attention... 😎
If I can't "win the battle" before the metal & fibre get installed, I'll do a little stress test (well, I'll do it anway...). If they can prove me wrong, I'd be happy... (sort of, at least)
A third vote for 64k. With more than white papers, but biased provincial evidence. Insane returns on the run-times of warehouse queries, to the tune of ~15 seconds for each minute it used to run... on the exact same iron with just different formatting and settings.
One of the huge things you're going to need to deal with Lutz is how they're going to handle the SAN's cache. Raid5 vs. RAID 10 these days isn't enough of a big deal, mostly because of the cache and improvements to the RAID5 controller programming. There's still a difference but it's not the "What's wrong with you?!?!?!" question it used to be.
That cache and its usage is going to be critical to if you have enough spindles or not.
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
December 7, 2011 at 6:27 pm
SQLRNNR (12/7/2011)
The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).
Not sure I agree with that.. I would expect 50GB to be about right, provided that NO system databases are allowed on the OS drives, which is my policy. I have quite a few installs of SQL 2008R2 on Win 2008R2 and I expect the base install with required components AND the components of SQL that must be installed on C: to use up right about 20GB + the page file.. I have seen this over and over again.. And I still have sufficient space for SPs of the OS..
CEWII
December 8, 2011 at 2:57 am
Whoever designed that is making the standard mistake of specing for space, not performance. Grab a copy of 'Troubleshooting SQL Server' by Jonathan Kehayias and check out chapter 2 where he discussed this mistake and the consequences thereof (free download from RedGate or buy paper from Amazon)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2011 at 8:45 am
Elliott Whitlow (12/7/2011)
SQLRNNR (12/7/2011)
The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).Not sure I agree with that.. I would expect 50GB to be about right, provided that NO system databases are allowed on the OS drives, which is my policy. I have quite a few installs of SQL 2008R2 on Win 2008R2 and I expect the base install with required components AND the components of SQL that must be installed on C: to use up right about 20GB + the page file.. I have seen this over and over again.. And I still have sufficient space for SPs of the OS..
CEWII
Hmmm. Odd, our installs were consistently 35-38GB. Granted still enough space on a 50GB drive.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2011 at 8:45 am
GilaMonster (12/8/2011)
Whoever designed that is making the standard mistake of specing for space, not performance. Grab a copy of 'Troubleshooting SQL Server' by Jonathan Kehayias and check out chapter 2 where he discussed this mistake and the consequences thereof (free download from RedGate or buy paper from Amazon)
Great resource (the book too).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2011 at 8:59 am
SQLRNNR (12/8/2011)
Elliott Whitlow (12/7/2011)
SQLRNNR (12/7/2011)
The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).Not sure I agree with that.. I would expect 50GB to be about right, provided that NO system databases are allowed on the OS drives, which is my policy. I have quite a few installs of SQL 2008R2 on Win 2008R2 and I expect the base install with required components AND the components of SQL that must be installed on C: to use up right about 20GB + the page file.. I have seen this over and over again.. And I still have sufficient space for SPs of the OS..
Hmmm. Odd, our installs were consistently 35-38GB. Granted still enough space on a 50GB drive.
Hmm, not sure what to tell you do you guys install a lot of extra software? Use really big page files? Have extra install binaries left on C? Kind of running short on ideas here..
As an aside I have an R2 running Hyper-V fitting fully onto a 32GB SSD for a test machine.
CEWII
December 8, 2011 at 1:06 pm
GilaMonster (12/8/2011)
Whoever designed that is making the standard mistake of specing for space, not performance. Grab a copy of 'Troubleshooting SQL Server' by Jonathan Kehayias and check out chapter 2 where he discussed this mistake and the consequences thereof (free download from RedGate or buy paper from Amazon)
Excellent book (just finished a quick scan of chapter 2, will reread it in detail)! Thank you for the link!
And I agree, it seems like our vendor will focus on space mainly. Unfortunately, I'm "just a production guy" who's not supposed to have any voice regarding hardware spec. They think.
So I might need to focus on "hard facts" by testing the he** out of the SAN (after a lot more reading, of course)...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply