December 14, 2007 at 8:58 am
Yah man. I tell them how many disk per luns I need and that my stuff gets isolated. If the dont like it I tell them that I need Direct attached Storage.
December 14, 2007 at 10:03 am
GSquared (12/12/2007)
I've actually used a SAN for SQL Server, and at first the admin (outsourced) didn't want me to have any say in how it was set up. He set the whole SAN up as a single RAID 5 array, and I had to have all my OLTP data files on the same array as my log files and my OLAP files, etc.After several months of arguing about it, management finally told him to work with me to configure it as needed in order to get best performance.
We ended up splitting it into six sets of disks. One RAID 5 for a file server and a few other things that had nothing to do with me, one small RAID 1 array of fast disks for the most critical OLTP database's data files, another for it's index files, and a third for it's log files, and 2 larger RAID 10 arrays for all the other databases (separating data files and log files).
All these disks were already in the SAN, it was just a matter of allocating them differently.
It took a weekend to get it set up and running the way I wanted, and the main OLTP database sped up by over 200% as a result, with the other databases getting between 50% and 100% increases in speed.
Management was very, very happy with that. They had, at the recommendation of the admin (all hardware administration and network administration was done by his company), been considering a $100k+ investment in server hardware, and now they didn't have to do that. (For a 25-person company, that's a lot of server.)
might not be the SAN admin's fault
we talked to a SAN vendor some months back and they do this as standard practice. the entire thing is configured as RAID5 and you make logical volumes that hosts can see. they say this is the same performance.
i had a similar experience
we've had problems on our production sql servers for some time. we bought some storage for the SAN and had it configured a different way and used it on a test SQL server. index rebuilds were more than twice as fast as production on a restored copy of a db.
it's also management. they see free space on a SAN and want to know why you can't use it. so you end up running multiple apps off the same spindles. SAN vendor will never say don't do it since it makes them look bad
December 14, 2007 at 10:27 am
ah well if you do the snia stuff you get to learn this stuff. Fibre channel disks and SAS are full duplex, everything else isn't. the main reason sata disks don't work is the spin speed.
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/01/26/how-fast-is-a-disk.aspx
the other missleading stat put forward on disk performance is the throughput or bandwidth - for sata disks you'll often find this involves making 1mb io read writes, sql server doesn't generally do them - random io is only 8k so you multiply by the io rate and you find all those nice big figures tumble away.
I find most people have very little idea of how disk io and throughput work and how sql server works, despite there being some good microsoft white papers.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 14, 2007 at 12:09 pm
It is truly amazing how I/O type (random/sequential) and I/O size (1K, 2K, 8K, ... 1024K, etc) can affect net I/O throughput on a storage subsystem!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 14, 2007 at 1:14 pm
too true, the difference when we moved from 2k to 8k pages ( and thus io size ) with sql 7.0 was absolutley stunning.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 10, 2008 at 8:35 am
I like this post very much. Good discussions from the gurus. As the answer is Yes, the new question is “How to”. I would like to invite you to take a look at this post. Your input is much appreciated.
http://www.sqlservercentral.com/Forums/Topic453651-146-1.aspx
Thank you,
Vivien
February 10, 2008 at 9:46 am
All good conversation and good information from all. Guess the answer to the OP's question should be, "Yes" or maybe even "Yes, most definitely". 😀 Some of the best drivers are also some of the best mechanics...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2008 at 11:17 am
I just wanted to jump into the queue to praise this precious thread!
It's hard to configure drives, filegroups and files for your SQL Instance if all you get is a "cloud" representing your storage.
Although a SAN cloud might be perfectly valid in small installations. The issue only starts as soon as you get performance issues. So you need to know where you want to go and also predict your future requirements.
In case you get only a "cloud" from your network / storage guys, the first thing for you to do is formulate an SLA with them.
I assume you have an existing system where storage will be replaced with a SAN. This means that you should already have a baseline, performance objectives (txns per second as an example) and so on and so forth. With this information of the existing system, you should be able to lean back until the new setup is in place and then run your tests against the new configuration.
If you have done your homework well, its now the network / storage guys' turn to prove that they have done their homework.
Btw, if they know your requirements well by having hard numbers, it might be easier for you to convince them in the first place (before anything is implemented).
Just my 2 €-cents
EDIT: Of course you also need to make sure that the performance issues are not mainly caused by bad SQL, otherwise this might bounce back harder than you like;-)
Best Regards,
Chris Büttner
February 11, 2008 at 7:02 am
Jeff Moden (2/10/2008)
..Some of the best drivers are also some of the best mechanics...
All race pilots must know the gobbledegook to direct their technicians to
figure out the solution to a problem. If they don't their team is obsolete !
(because they'll not get most out of it)
Regarding the SAN... preparation is crucial.
There is a huge gray zone if you concider performance responsibility !
Learn to work together with your SAN-admin(s).
Don't exceed thier level, but hand over the needed hints, so they can figure out for themselves your guidance makes sence !
And you have a nice "shortlist" regarding where to begin in this great thread.:cool:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2008 at 7:10 am
ALZDBA (2/11/2008)
Learn to work together with your SAN-admin(s).Don't exceed thier level, but hand over the needed hints, so they can figure out for themselves your guidance makes sence !
Those two points, right there, are probably the most important. Everyone in a company has to remember that it's a company and a team. Cooperation and understanding between groups is absolutely essential to the level of success enjoyed by the groups AND the company!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2008 at 4:16 pm
just configured a new server to be a super subscriber for a few hundred GB of data and i took care to try to do it right. so far it's going very fast. a 350 million row table took around 90 minutes to run the snapshot and this was on slow 100mbps
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply