Running SQL database off of SAN -- is it feasible ?

Hi,
I'm trying to create a home for a database. The database will ultimately
reach approximately 200Gb in size. Usage patterns will consist of
approximately 80% read, 20% write. The vast majority of the reads will
require random access to small chunks of data (less than 64k).
Two SQL Server machines need to be connected to the same database. The
primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
Server will be a machine of lesser capabilities and will only come online if
the primary data server fails. This concept, as it has been explained to me,
is known as "failover."
My job is to investigate various solutions for housing the data. I am
considering the following storage device:
http://www-1.ibm.com/servers/storage/disk/ds4000/ds4100/index.html
This device holds 14 drives and transmits data via a mechanism called
Fibre Channel, which as far as I can tell, supports a throughput of 2Gbps.
Here are my questions and concerns:
1) Is this device suitable for hosting a database meeting the
characteristics and requirements that I've described?
2) Assuming that I placed 14 drives in this storage device, how would I
partition it? I've read that the transaction log should be on a separate
RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
and 3-14 as RAID 1+0 ? What do you recommend?
3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
relevant concern given that most I/O will be random, not sequential, and
therefore I may not even be hitting the 256MBps cap anyway?
I won't actually be the one installing the storage system. I'll
obviously need a professional for that. I just want to do enough research to
determine whether or not the slick salesman who sells us the solution is
giving me accurate information regarding the suitability of various storage
solutions.
Thanks for the help,
David> Two SQL Server machines need to be connected to the same database. The
> primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
> Server will be a machine of lesser capabilities and will only come online if
> the primary data server fails. This concept, as it has been explained to me,
> is known as "failover."
> My job is to investigate various solutions for housing the data. I am
> considering the following storage device:
> http://www-1.ibm.com/servers/storage/disk/ds4000/ds4100/index.html
We use EMC SAN devices. Pricy but well worth it.
> 1) Is this device suitable for hosting a database meeting the
> characteristics and requirements that I've described?
You probably shouldn't have any problems in that configuration.
> 2) Assuming that I placed 14 drives in this storage device, how would I
> partition it? I've read that the transaction log should be on a separate
> RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
> and 3-14 as RAID 1+0 ? What do you recommend?
I recommend you go over this with the people who configure your SAN. They
know its specific implementations, where it is fast, where it can lag, etc.
Hopefully they have SQL Server experts on board (likely mostly DB2 people),
if not, see above... Have EMC come in and talk to you before you decide on a
specific device (and even if you don't go with them, they'll likely give you
relevant information that at worst you can use as confirmation to what the
other guys tell you).
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?
You probably won't hit it much, and if you do, you will probably be more
constrained at that point by the network bandwidth sending it over the wire
than by reading it from disk.
> I won't actually be the one installing the storage system. I'll
> obviously need a professional for that. I just want to do enough research to
> determine whether or not the slick salesman who sells us the solution is
> giving me accurate information regarding the suitability of various storage
> solutions.
I can't speak for other vendors, because I was involved too late in the
process, but EMC was very up front with us, stands by their product, and
aren't out to screw you. I would bet I speak for most SAN vendors when I
say that they want to make money but they want to do so by providing you
with the best solution for your scenario, as you present it to them.
NAS, on the other hand, is a completely different ballgame. :-)|||Those Fibre Channel maximums are also per HBA/Channel, of which you can have
several. Moreover, EMC makes a product called Power Path that allows you to
use muliple HBAs and/or channels in a teaming configuration.
Check out this site for some configuration white papers if you're
interested.
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
This site is full of information. Make sure you scroll through it and read
all of the subtitles.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5B9DAF.3235%ten.xoc@.dnartreb.noraa...
> Two SQL Server machines need to be connected to the same database. The
> primary SQL Server will be a very powerful machine (4 CPUs). The second
SQL
> Server will be a machine of lesser capabilities and will only come online
if
> the primary data server fails. This concept, as it has been explained to
me,
> is known as "failover."
> My job is to investigate various solutions for housing the data. I am
> considering the following storage device:
> http://www-1.ibm.com/servers/storage/disk/ds4000/ds4100/index.html
We use EMC SAN devices. Pricy but well worth it.
> 1) Is this device suitable for hosting a database meeting the
> characteristics and requirements that I've described?
You probably shouldn't have any problems in that configuration.
> 2) Assuming that I placed 14 drives in this storage device, how would
I
> partition it? I've read that the transaction log should be on a separate
> RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
> and 3-14 as RAID 1+0 ? What do you recommend?
I recommend you go over this with the people who configure your SAN. They
know its specific implementations, where it is fast, where it can lag, etc.
Hopefully they have SQL Server experts on board (likely mostly DB2 people),
if not, see above... Have EMC come in and talk to you before you decide on a
specific device (and even if you don't go with them, they'll likely give you
relevant information that at worst you can use as confirmation to what the
other guys tell you).
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even
a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?
You probably won't hit it much, and if you do, you will probably be more
constrained at that point by the network bandwidth sending it over the wire
than by reading it from disk.
> I won't actually be the one installing the storage system. I'll
> obviously need a professional for that. I just want to do enough research
to
> determine whether or not the slick salesman who sells us the solution is
> giving me accurate information regarding the suitability of various
storage
> solutions.
I can't speak for other vendors, because I was involved too late in the
process, but EMC was very up front with us, stands by their product, and
aren't out to screw you. I would bet I speak for most SAN vendors when I
say that they want to make money but they want to do so by providing you
with the best solution for your scenario, as you present it to them.
NAS, on the other hand, is a completely different ballgame. :-)|||> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> This site is full of information. Make sure you scroll through it and
read
> all of the subtitles.
Anthony,
That's a great link. I appreciate the tip. I'll check out those
articles.
Aaron,
I'll check out EMC. I was thinking that since we are buying our servers
from IBM, it would be a natural decision to buy the storage from them as
well... but after reading your post, I now see that EMC has an excellent
reputation. Perhaps they have a solution that's more suitable. Are there any
other vendors that you think I should investigate? Any thoughts on Hitachi
Data Systems (hds.com)?
David|||:) We have our main production server with four paths on two 2gb switches
to the EMC SAN. The pathing is active/active so the throughput is quite
impressive. We are also an 80/20 shop, and we have had no issues at all
with disk speed. The main production environment is:
Logs: 4x73gb15k RAID 10
Data: 6x73gb15k RAID 5
I don't know that I would use the DS4100 solution for a SQL Server
environment, especially one that's high-end, high-availability. The
multiple channels (unless it's changed since the fastT days are for failover
only per single host. I could be wrong on this though, so you need to talk
to the vendor.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:#tsMaeQKFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Those Fibre Channel maximums are also per HBA/Channel, of which you can
have
> several. Moreover, EMC makes a product called Power Path that allows you
to
> use muliple HBAs and/or channels in a teaming configuration.
> Check out this site for some configuration white papers if you're
> interested.
> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> This site is full of information. Make sure you scroll through it and
read
> all of the subtitles.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE5B9DAF.3235%ten.xoc@.dnartreb.noraa...
> > Two SQL Server machines need to be connected to the same database.
The
> > primary SQL Server will be a very powerful machine (4 CPUs). The second
> SQL
> > Server will be a machine of lesser capabilities and will only come
online
> if
> > the primary data server fails. This concept, as it has been explained to
> me,
> > is known as "failover."
> >
> > My job is to investigate various solutions for housing the data. I
am
> > considering the following storage device:
> >
> > http://www-1.ibm.com/servers/storage/disk/ds4000/ds4100/index.html
> We use EMC SAN devices. Pricy but well worth it.
> > 1) Is this device suitable for hosting a database meeting the
> > characteristics and requirements that I've described?
> You probably shouldn't have any problems in that configuration.
> > 2) Assuming that I placed 14 drives in this storage device, how
would
> I
> > partition it? I've read that the transaction log should be on a separate
> > RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1
volume
> > and 3-14 as RAID 1+0 ? What do you recommend?
> I recommend you go over this with the people who configure your SAN. They
> know its specific implementations, where it is fast, where it can lag,
etc.
> Hopefully they have SQL Server experts on board (likely mostly DB2
people),
> if not, see above... Have EMC come in and talk to you before you decide on
a
> specific device (and even if you don't go with them, they'll likely give
you
> relevant information that at worst you can use as confirmation to what the
> other guys tell you).
> > 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> > equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this
even
> a
> > relevant concern given that most I/O will be random, not sequential, and
> > therefore I may not even be hitting the 256MBps cap anyway?
> You probably won't hit it much, and if you do, you will probably be more
> constrained at that point by the network bandwidth sending it over the
wire
> than by reading it from disk.
> > I won't actually be the one installing the storage system. I'll
> > obviously need a professional for that. I just want to do enough
research
> to
> > determine whether or not the slick salesman who sells us the solution is
> > giving me accurate information regarding the suitability of various
> storage
> > solutions.
> I can't speak for other vendors, because I was involved too late in the
> process, but EMC was very up front with us, stands by their product, and
> aren't out to screw you. I would bet I speak for most SAN vendors when I
> say that they want to make money but they want to do so by providing you
> with the best solution for your scenario, as you present it to them.
> NAS, on the other hand, is a completely different ballgame. :-)
>|||I like EMC, Hitachi, and IBM. I would stay away from HP with all the issues
they're having right now. Make sure whatever storage you choose has a
service location and parts depot close to you. My company is in Kansas
City. The closest Hitachi service center and parts depot is 3 hours away in
St. Louis, so it makes more sense for us to go with EMC which is local.
IBM costs more, but they have great service. Hitachi gives you a lot of the
software that EMC charges you for; however, EMC has more to offer in overall
features. Hitachi tends to cost less than EMC. You really don't have to
worry about buying your storage solution from IBM because the servers are
IBM. All the major storage/server vendors have relationships with each
other to alleviate this. Again, stay away from HP. Their service and
turnaround times for the enterprise platforms have been horrible lately.
Dell is EMC rebranded in case you decide to check them out. The hardware is
the same, but they have their own service staff, support etc. Depending on
your location, they sometimes do a better job than the actual EMC people.
So, that's something to think about also.
"Larry David" <invalid@.bogus.bum> wrote in message
news:M4qdnaIn9c-gzKvfRVn-2w@.giganews.com...
> >
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> >
> > This site is full of information. Make sure you scroll through it and
> read
> > all of the subtitles.
> Anthony,
> That's a great link. I appreciate the tip. I'll check out those
> articles.
> Aaron,
> I'll check out EMC. I was thinking that since we are buying our
servers
> from IBM, it would be a natural decision to buy the storage from them as
> well... but after reading your post, I now see that EMC has an excellent
> reputation. Perhaps they have a solution that's more suitable. Are there
any
> other vendors that you think I should investigate? Any thoughts on Hitachi
> Data Systems (hds.com)?
> David
>|||I haven't heard many good things about Hitachi; quite the opposite,
actually.
As for your servers, if you haven't already committed to IBM, I strongly
recommend giving Dell a shot at your RFP. We filled a couple of racks with
Dell servers, have had absolutely no problems with them, and not only did we
get four more servers for the same price as the IBM quote, they were more
powerful AND they lumped in some other goodies.
Our transactions with IBM (and the support thereafter) have been far from
stellar. Don't tie yourself to one hardware vendor!
On 3/14/05 10:06 PM, in article M4qdnaIn9c-gzKvfRVn-2w@.giganews.com, "Larry
David" <invalid@.bogus.bum> wrote:
>> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
>> This site is full of information. Make sure you scroll through it and
> read
>> all of the subtitles.
> Anthony,
> That's a great link. I appreciate the tip. I'll check out those
> articles.
> Aaron,
> I'll check out EMC. I was thinking that since we are buying our servers
> from IBM, it would be a natural decision to buy the storage from them as
> well... but after reading your post, I now see that EMC has an excellent
> reputation. Perhaps they have a solution that's more suitable. Are there any
> other vendors that you think I should investigate? Any thoughts on Hitachi
> Data Systems (hds.com)?
> David
>|||> We use EMC SAN devices. Pricy but well worth it.
>
Can I ask you a few questions about EMC SAN configuration? Our EMC SAN was
recently set up and configured, but we're not getting nearly the throughput
we expected.|||> :) We have our main production server with four paths on two 2gb switches
> to the EMC SAN. The pathing is active/active so the throughput is quite
> impressive. We are also an 80/20 shop, and we have had no issues at all
> with disk speed. The main production environment is:
> Logs: 4x73gb15k RAID 10
> Data: 6x73gb15k RAID 5
>
Hi Derrick,
Thanks for your comments. Since you've already been down this road, let
me ask you something unrelated. What type of machine are you using to run
SQL Server? I'm considering an IBM BladeCenter HS20 with 4 gigs of RAM, 2
3.6GHz Xeon Processors with 800MHz FSB, and 2MB of Level 2 cache. Not bad
for $2,800, but others have implied that blades don't make very good
database servers. What do you think?
David|||I've had good luck with IBM. I've also had good luck with Hitachi in the
past though. A lot seems to depend on the local sales/support teams
unfortunately. I agree with not tying yourself to one hardware vendor
though. We fought switching off of HP forever. The fact is they earned our
loss of business. They fought hard to lose it. Their service is horrible.
Their turnaround time is horrendous. They don't give a damn about the SLA
agreements on the warranties you buy. We had production servers down, and
sometimes they wouldn't even call back until the next day. This was on 24x7
4 hour response time warranties. If they don't get their act together, they
won't have to worry about not being able to produce servers. :) /rant
I've heard a lot of good things about Dell lately at both the server and
storage levels. Sounds like they got it together.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5BBF6B.33BC%ten.xoc@.dnartreb.noraa...
> I haven't heard many good things about Hitachi; quite the opposite,
> actually.
> As for your servers, if you haven't already committed to IBM, I strongly
> recommend giving Dell a shot at your RFP. We filled a couple of racks
with
> Dell servers, have had absolutely no problems with them, and not only did
we
> get four more servers for the same price as the IBM quote, they were more
> powerful AND they lumped in some other goodies.
> Our transactions with IBM (and the support thereafter) have been far from
> stellar. Don't tie yourself to one hardware vendor!
>
> On 3/14/05 10:06 PM, in article M4qdnaIn9c-gzKvfRVn-2w@.giganews.com,
"Larry
> David" <invalid@.bogus.bum> wrote:
> >>
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> >>
> >> This site is full of information. Make sure you scroll through it and
> > read
> >> all of the subtitles.
> >
> > Anthony,
> >
> > That's a great link. I appreciate the tip. I'll check out those
> > articles.
> >
> > Aaron,
> >
> > I'll check out EMC. I was thinking that since we are buying our
servers
> > from IBM, it would be a natural decision to buy the storage from them as
> > well... but after reading your post, I now see that EMC has an excellent
> > reputation. Perhaps they have a solution that's more suitable. Are there
any
> > other vendors that you think I should investigate? Any thoughts on
Hitachi
> > Data Systems (hds.com)?
> >
> > David
> >
> >
>|||How is it setup? What's the pathing, array, and LUN layout? What kind of a
SAN?
"Michael C#" <xyz@.abcdef.com> wrote in message
news:tXsZd.22624$Rc7.21810@.fe09.lga...
> > We use EMC SAN devices. Pricy but well worth it.
> >
> Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
was
> recently set up and configured, but we're not getting nearly the
throughput
> we expected.
>|||Can you show the topology? What kind of switches are you running through?
Is it all gig fiber or is some of it bottlenecked by 100 or (gasp) 10/100?
We have observed exemplary performance at the read/write level on the SAN
since the day they were configured. The SAN itself should never be your
bottleneck in my experience, but if your app is hitting a network throttling
issue on the way to or from, it could look like slow performance from the
SAN. The only servers we noticed issues with early on were the ones that
hasn't been upgraded to fiber... they were still running 100 and my gut
feeling is that the SAN was sitting there waiting for the data.
So, long story short, if you're not on fiber through and through, go
shopping. :-)
And of course, if you have more servers in the works that are going to make
use of the SAN, make sure they come equipped with gb cards.
On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga, "Michael C#"
<xyz@.abcdef.com> wrote:
>> We use EMC SAN devices. Pricy but well worth it.
> Can I ask you a few questions about EMC SAN configuration? Our EMC SAN was
> recently set up and configured, but we're not getting nearly the throughput
> we expected.
>|||I think they're crazy. :) We are running 6 IBM x365 4 processor servers
with 2.8ghz processors and 8gb or RAM for back office, accounting, HR,
third-party, development and testing. We are running 2 HP DL 760 G2 (don't
buy them...they break allllll the time) 8 processor servers with 2.8ghz
processors and 10gb redundant RAM (8gb usable) for production, reporting,
analytics, and backup. Basically, we have 4 pairs. The main servers are
dual/dual active/active pathed. The other servers are single/dual
active/active pathed to an EMC CX400. What kind of a systems are you
wanting to put on this? Do you know IOPS, type of load, etc?
The blades can make good database servers. A lot depends on your overall
needs. Saying they don't make "very good database servers" is like saying
Craftsman doesn't make good screwdrivers compared to Snap-On. Who cares if
all you need to do is screw the cover back on the old ladies mouth again.
You going to pay $1000 per screwdriver for that? (Well, maybe that was a
bad example.........)
"Larry David" <invalid@.bogus.bum> wrote in message
news:-P-dnSn-cqf8x6vfRVn-1A@.giganews.com...
> > :) We have our main production server with four paths on two 2gb
switches
> > to the EMC SAN. The pathing is active/active so the throughput is quite
> > impressive. We are also an 80/20 shop, and we have had no issues at all
> > with disk speed. The main production environment is:
> >
> > Logs: 4x73gb15k RAID 10
> > Data: 6x73gb15k RAID 5
> >
> Hi Derrick,
> Thanks for your comments. Since you've already been down this road,
let
> me ask you something unrelated. What type of machine are you using to run
> SQL Server? I'm considering an IBM BladeCenter HS20 with 4 gigs of RAM, 2
> 3.6GHz Xeon Processors with 800MHz FSB, and 2MB of Level 2 cache. Not bad
> for $2,800, but others have implied that blades don't make very good
> database servers. What do you think?
> David
>|||That's a fairly easy one to troubleshoot. If you have no queue lengths on
either end, but the outbound queue length somewhere in the connection is up
there, you are probably constrained by the network, as opposed to the disks.
People still run 100 or 10/100' WHY''''''
:)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5BC6AC.33E6%ten.xoc@.dnartreb.noraa...
> Can you show the topology? What kind of switches are you running through?
> Is it all gig fiber or is some of it bottlenecked by 100 or (gasp) 10/100?
> We have observed exemplary performance at the read/write level on the SAN
> since the day they were configured. The SAN itself should never be your
> bottleneck in my experience, but if your app is hitting a network
throttling
> issue on the way to or from, it could look like slow performance from the
> SAN. The only servers we noticed issues with early on were the ones that
> hasn't been upgraded to fiber... they were still running 100 and my gut
> feeling is that the SAN was sitting there waiting for the data.
> So, long story short, if you're not on fiber through and through, go
> shopping. :-)
> And of course, if you have more servers in the works that are going to
make
> use of the SAN, make sure they come equipped with gb cards.
>
>
> On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga, "Michael
C#"
> <xyz@.abcdef.com> wrote:
> >
> >> We use EMC SAN devices. Pricy but well worth it.
> >>
> >
> > Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
was
> > recently set up and configured, but we're not getting nearly the
throughput
> > we expected.
> >
> >
>|||It's Gig fiber. Hmm as for the topology, I'll have to get all that info
from the IT guy. I'll be the first to admit I don't know a darn thing about
the SAN (so please bear with me...), other than it's EMC and right now it
seems slow as heck :(
Right now all I've gleaned is that we're running like 40 Q-Logic LUNs (?),
although the EMC automatically makes it RAID 1+0, over 12 controllers.
We've configured the drives as several 160 GB RAID 1 drives per EMC's
recommendation.
I've run tons of tests, including SQLIO and the results are uggglllyyy. I
think I've eliminated SQL Server as the problem, and the vendor says that
the SAN is not the problem... Which leads me to believe that the problem is
somewhere between Windows 2003 and the SAN.
I'll get all my info. together and get back to you. In the meantime, do you
happen to know any hints or tips, or things I should look for, in the
Windows Server 2003 settings that might help?
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5BC6AC.33E6%ten.xoc@.dnartreb.noraa...
> Can you show the topology? What kind of switches are you running through?
> Is it all gig fiber or is some of it bottlenecked by 100 or (gasp) 10/100?
> We have observed exemplary performance at the read/write level on the SAN
> since the day they were configured. The SAN itself should never be your
> bottleneck in my experience, but if your app is hitting a network
> throttling
> issue on the way to or from, it could look like slow performance from the
> SAN. The only servers we noticed issues with early on were the ones that
> hasn't been upgraded to fiber... they were still running 100 and my gut
> feeling is that the SAN was sitting there waiting for the data.
> So, long story short, if you're not on fiber through and through, go
> shopping. :-)
> And of course, if you have more servers in the works that are going to
> make
> use of the SAN, make sure they come equipped with gb cards.
>
>
> On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga, "Michael
> C#"
> <xyz@.abcdef.com> wrote:
>> We use EMC SAN devices. Pricy but well worth it.
>>
>> Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
>> was
>> recently set up and configured, but we're not getting nearly the
>> throughput
>> we expected.
>>
>|||You ever post something, then after you hit the Send button,
think.....WOW....THAT WAS REALLY STUPID!!!!
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:O#PGUMRKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> I think they're crazy. :) We are running 6 IBM x365 4 processor servers
> with 2.8ghz processors and 8gb or RAM for back office, accounting, HR,
> third-party, development and testing. We are running 2 HP DL 760 G2
(don't
> buy them...they break allllll the time) 8 processor servers with 2.8ghz
> processors and 10gb redundant RAM (8gb usable) for production, reporting,
> analytics, and backup. Basically, we have 4 pairs. The main servers are
> dual/dual active/active pathed. The other servers are single/dual
> active/active pathed to an EMC CX400. What kind of a systems are you
> wanting to put on this? Do you know IOPS, type of load, etc?
> The blades can make good database servers. A lot depends on your overall
> needs. Saying they don't make "very good database servers" is like saying
> Craftsman doesn't make good screwdrivers compared to Snap-On. Who cares
if
> all you need to do is screw the cover back on the old ladies mouth again.
> You going to pay $1000 per screwdriver for that? (Well, maybe that was a
> bad example.........)
> "Larry David" <invalid@.bogus.bum> wrote in message
> news:-P-dnSn-cqf8x6vfRVn-1A@.giganews.com...
> > > :) We have our main production server with four paths on two 2gb
> switches
> > > to the EMC SAN. The pathing is active/active so the throughput is
quite
> > > impressive. We are also an 80/20 shop, and we have had no issues at
all
> > > with disk speed. The main production environment is:
> > >
> > > Logs: 4x73gb15k RAID 10
> > > Data: 6x73gb15k RAID 5
> > >
> >
> > Hi Derrick,
> >
> > Thanks for your comments. Since you've already been down this road,
> let
> > me ask you something unrelated. What type of machine are you using to
run
> > SQL Server? I'm considering an IBM BladeCenter HS20 with 4 gigs of RAM,
2
> > 3.6GHz Xeon Processors with 800MHz FSB, and 2MB of Level 2 cache. Not
bad
> > for $2,800, but others have implied that blades don't make very good
> > database servers. What do you think?
> >
> > David
> >
> >
>|||"Screw the cover back on the old ladies mouth again"?
Sounds like somebody needs a hug.
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:O%23PGUMRKFHA.2716@.TK2MSFTNGP15.phx.gbl...
>I think they're crazy. :) We are running 6 IBM x365 4 processor servers
> with 2.8ghz processors and 8gb or RAM for back office, accounting, HR,
> third-party, development and testing. We are running 2 HP DL 760 G2
> (don't
> buy them...they break allllll the time) 8 processor servers with 2.8ghz
> processors and 10gb redundant RAM (8gb usable) for production, reporting,
> analytics, and backup. Basically, we have 4 pairs. The main servers are
> dual/dual active/active pathed. The other servers are single/dual
> active/active pathed to an EMC CX400. What kind of a systems are you
> wanting to put on this? Do you know IOPS, type of load, etc?
> The blades can make good database servers. A lot depends on your overall
> needs. Saying they don't make "very good database servers" is like saying
> Craftsman doesn't make good screwdrivers compared to Snap-On. Who cares
> if
> all you need to do is screw the cover back on the old ladies mouth again.
> You going to pay $1000 per screwdriver for that? (Well, maybe that was a
> bad example.........)
> "Larry David" <invalid@.bogus.bum> wrote in message
> news:-P-dnSn-cqf8x6vfRVn-1A@.giganews.com...
>> > :) We have our main production server with four paths on two 2gb
> switches
>> > to the EMC SAN. The pathing is active/active so the throughput is
>> > quite
>> > impressive. We are also an 80/20 shop, and we have had no issues at
>> > all
>> > with disk speed. The main production environment is:
>> >
>> > Logs: 4x73gb15k RAID 10
>> > Data: 6x73gb15k RAID 5
>> >
>> Hi Derrick,
>> Thanks for your comments. Since you've already been down this road,
> let
>> me ask you something unrelated. What type of machine are you using to run
>> SQL Server? I'm considering an IBM BladeCenter HS20 with 4 gigs of RAM, 2
>> 3.6GHz Xeon Processors with 800MHz FSB, and 2MB of Level 2 cache. Not bad
>> for $2,800, but others have implied that blades don't make very good
>> database servers. What do you think?
>> David
>>
>|||(Maybe not so) Surprisingly, our disk queue is staying up there, but I know
we have GB fiber, and I *think* they're using GB all around. I'll
definitely double-check that though. BTW, I ran SQLIO against it and got
back the results, but I have no idea what to do with them now... It says
that I get best performance at 64KB I/O's with all buffering turned on. Any
idea what in the world I can do with those results? Is there a way to
ensure Windows is using these settings in the normal course?
Just off the top of your head, if we do have GB all around, any other ideas
what could be causing a bottleneck?
Thanks Guys
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OXhgfORKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> That's a fairly easy one to troubleshoot. If you have no queue lengths on
> either end, but the outbound queue length somewhere in the connection is
> up
> there, you are probably constrained by the network, as opposed to the
> disks.
> People still run 100 or 10/100' WHY''''''
> :)
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE5BC6AC.33E6%ten.xoc@.dnartreb.noraa...
>> Can you show the topology? What kind of switches are you running
>> through?
>> Is it all gig fiber or is some of it bottlenecked by 100 or (gasp)
>> 10/100?
>> We have observed exemplary performance at the read/write level on the SAN
>> since the day they were configured. The SAN itself should never be your
>> bottleneck in my experience, but if your app is hitting a network
> throttling
>> issue on the way to or from, it could look like slow performance from the
>> SAN. The only servers we noticed issues with early on were the ones that
>> hasn't been upgraded to fiber... they were still running 100 and my gut
>> feeling is that the SAN was sitting there waiting for the data.
>> So, long story short, if you're not on fiber through and through, go
>> shopping. :-)
>> And of course, if you have more servers in the works that are going to
> make
>> use of the SAN, make sure they come equipped with gb cards.
>>
>>
>> On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga, "Michael
> C#"
>> <xyz@.abcdef.com> wrote:
>> >
>> >> We use EMC SAN devices. Pricy but well worth it.
>> >>
>> >
>> > Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
> was
>> > recently set up and configured, but we're not getting nearly the
> throughput
>> > we expected.
>> >
>> >
>|||At least 6 times a day :)
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uvmLLSRKFHA.3336@.TK2MSFTNGP09.phx.gbl...
> You ever post something, then after you hit the Send button,
> think.....WOW....THAT WAS REALLY STUPID!!!!|||On Mon, 14 Mar 2005 16:22:56 -0800, "Larry David" <invalid@.bogus.bum>
wrote:
[ Snip ]
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
>equates to only 256MBps,
No, it doesn't. It equates to 212MB/sec.
> as opposed to SCSI which is 320MBps. Is this even a
>relevant concern given that most I/O will be random, not sequential, and
>therefore I may not even be hitting the 256MBps cap anyway?
PARALLEL SCSI (emphasis added, since FC is SCSI, too, in this
instance) peaks at 320MB/sec. You won't get 320MB/sec out of it.
And no, you won't care about the difference, and if you did, it's
easier to add another FC link than another U320 one, and 4Gbps Fibre
is on its way.
>David
Malc.|||Read those white papers in the link that I sent you. They go into a great
bit of detail on how to layout the LUN configuration in the SAN.
This, more than anything else, will effect your performance. You MUST
dedicate areas of the SAN to specific tasks, just as you would with DAS:
data files striped over as many spindles as possible, RAID 5, 50, or 10.
Log files on dedicated spindles in RAID 1 or 10. TempDB on a dedicated set
of disks.
If you are using the SAN to host multiple server, this layout configuration
can get rather complicated. We have found that if the end-user really knows
their stuff and know exactly what they are going to do with the system, the
EMC technicians that set up the internal SAN configuration do a wounderful
job. However, on the flip side, if you don't know what you want, they will
tend to configure a generic, homogeous solution...this eliminates hot spots,
but it is terrible for a DBMS.
Once you get your LUNs attached to the server, DO NOT USE THE OS STRIPING.
You can do one of two things, however. Either use FILEGROUPs to seperate
the tables from the indexes and you've already seperated the log files, all
on seperate disks. The other option is to create multiple files per
filegroup, and place each one on seperate sets of disks, this is called
plaiding. It is sort of a secondary striping mechanism, but uses SQL
Server's internal proportional fill algorythm to lay down the file usage.
Finally, check out EMC's Power Path. This will allow you to control
multiple channels to the SAN, increase your overall throughput, put also
give you better parallelism.
You should also contact Microsoft's Advisory Services. They work with most
of the Major vendors to tailor solution configurations to specifically match
your usage.
Good Luck.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.abcdef.com> wrote in message
news:VltZd.30802$re2.9284@.fe11.lga...
It's Gig fiber. Hmm as for the topology, I'll have to get all that info
from the IT guy. I'll be the first to admit I don't know a darn thing about
the SAN (so please bear with me...), other than it's EMC and right now it
seems slow as heck :(
Right now all I've gleaned is that we're running like 40 Q-Logic LUNs (?),
although the EMC automatically makes it RAID 1+0, over 12 controllers.
We've configured the drives as several 160 GB RAID 1 drives per EMC's
recommendation.
I've run tons of tests, including SQLIO and the results are uggglllyyy. I
think I've eliminated SQL Server as the problem, and the vendor says that
the SAN is not the problem... Which leads me to believe that the problem is
somewhere between Windows 2003 and the SAN.
I'll get all my info. together and get back to you. In the meantime, do you
happen to know any hints or tips, or things I should look for, in the
Windows Server 2003 settings that might help?
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5BC6AC.33E6%ten.xoc@.dnartreb.noraa...
> Can you show the topology? What kind of switches are you running through?
> Is it all gig fiber or is some of it bottlenecked by 100 or (gasp) 10/100?
> We have observed exemplary performance at the read/write level on the SAN
> since the day they were configured. The SAN itself should never be your
> bottleneck in my experience, but if your app is hitting a network
> throttling
> issue on the way to or from, it could look like slow performance from the
> SAN. The only servers we noticed issues with early on were the ones that
> hasn't been upgraded to fiber... they were still running 100 and my gut
> feeling is that the SAN was sitting there waiting for the data.
> So, long story short, if you're not on fiber through and through, go
> shopping. :-)
> And of course, if you have more servers in the works that are going to
> make
> use of the SAN, make sure they come equipped with gb cards.
>
>
> On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga, "Michael
> C#"
> <xyz@.abcdef.com> wrote:
>> We use EMC SAN devices. Pricy but well worth it.
>>
>> Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
>> was
>> recently set up and configured, but we're not getting nearly the
>> throughput
>> we expected.
>>
>|||Where abouts in KC? I work for Commerce Bank.
Sincerely,
Anthony Thomas
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:%23xwdT4QKFHA.2996@.TK2MSFTNGP10.phx.gbl...
I like EMC, Hitachi, and IBM. I would stay away from HP with all the issues
they're having right now. Make sure whatever storage you choose has a
service location and parts depot close to you. My company is in Kansas
City. The closest Hitachi service center and parts depot is 3 hours away in
St. Louis, so it makes more sense for us to go with EMC which is local.
IBM costs more, but they have great service. Hitachi gives you a lot of the
software that EMC charges you for; however, EMC has more to offer in overall
features. Hitachi tends to cost less than EMC. You really don't have to
worry about buying your storage solution from IBM because the servers are
IBM. All the major storage/server vendors have relationships with each
other to alleviate this. Again, stay away from HP. Their service and
turnaround times for the enterprise platforms have been horrible lately.
Dell is EMC rebranded in case you decide to check them out. The hardware is
the same, but they have their own service staff, support etc. Depending on
your location, they sometimes do a better job than the actual EMC people.
So, that's something to think about also.
"Larry David" <invalid@.bogus.bum> wrote in message
news:M4qdnaIn9c-gzKvfRVn-2w@.giganews.com...
> >
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> >
> > This site is full of information. Make sure you scroll through it and
> read
> > all of the subtitles.
> Anthony,
> That's a great link. I appreciate the tip. I'll check out those
> articles.
> Aaron,
> I'll check out EMC. I was thinking that since we are buying our
servers
> from IBM, it would be a natural decision to buy the storage from them as
> well... but after reading your post, I now see that EMC has an excellent
> reputation. Perhaps they have a solution that's more suitable. Are there
any
> other vendors that you think I should investigate? Any thoughts on Hitachi
> Data Systems (hds.com)?
> David
>|||See reply to Aaron. Thanks!
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eTSmTGRKFHA.1528@.TK2MSFTNGP09.phx.gbl...
> How is it setup? What's the pathing, array, and LUN layout? What kind of
> a
> SAN?
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:tXsZd.22624$Rc7.21810@.fe09.lga...
>> > We use EMC SAN devices. Pricy but well worth it.
>> >
>> Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
> was
>> recently set up and configured, but we're not getting nearly the
> throughput
>> we expected.
>>
>|||Here's the info I was able to get:
It's an EMC Symmetrix SAN, 8 GB cache. It's set up with 12 controllers and
40 physical Q-Logic drives (80 GB each). The drives are configured as RAID
1, 160 GB volumes. The SAN provides striping automatically, and the 160 GB
volume configuration was recommended by the vendor. Everything is on gig
fiber. Apparently there are no switches - all servers are connected
directly via fiber. Right now we have 4 servers connected, two Web servers
and two SQL Servers. The SQL Servers are set up in an active/passive
Cluster. The Web servers aren't really being utilized currently, except for
some minor testing.
The performance right now is not good at all.
Any suggestions? Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5BC6AC.33E6%ten.xoc@.dnartreb.noraa...
> Can you show the topology? What kind of switches are you running through?
> Is it all gig fiber or is some of it bottlenecked by 100 or (gasp) 10/100?
> We have observed exemplary performance at the read/write level on the SAN
> since the day they were configured. The SAN itself should never be your
> bottleneck in my experience, but if your app is hitting a network
> throttling
> issue on the way to or from, it could look like slow performance from the
> SAN. The only servers we noticed issues with early on were the ones that
> hasn't been upgraded to fiber... they were still running 100 and my gut
> feeling is that the SAN was sitting there waiting for the data.
> So, long story short, if you're not on fiber through and through, go
> shopping. :-)
> And of course, if you have more servers in the works that are going to
> make
> use of the SAN, make sure they come equipped with gb cards.
>
>
> On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga, "Michael
> C#"
> <xyz@.abcdef.com> wrote:
>> We use EMC SAN devices. Pricy but well worth it.
>>
>> Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
>> was
>> recently set up and configured, but we're not getting nearly the
>> throughput
>> we expected.
>>
>|||Just as a matter of interest, we are currently completely a project
which migrated a large Oracle database from 7-year old AIX system to
the new generation of machines. For storage we chose NAS, specifically
NetApp Filers, the connection between the host and the NAS device
being a 1Gbps ethernet.
I really did not believe a skinny 1Gbps could provide the throughput
we needed.
To start with, we developed using 4 x 146GB internal SCSI disks, and
the new system was a satisfying 9x faster than the old one (using a
smaller copy of the database).
So then we borrowed a Filer with 8 x 144GB drives from the local
supplier. This was the bottom-of-the-range FAS250 model. The result
was that it was 10x faster.
I still do not completely understand why. One thing of course is that
the I/O characteristics of an RDBMS is not like ftp'ing a file... and
in case you are wondering, our application was keeping it very busy
with some heavy-duty batch processing, so it was not like random light
user transactions.
Anyway, just thought you might like some reassurance that a 2Gbps SAN
network can hack it. I'm not promoting NAS over SAN, but the reason we
went for NAS was simply that we have a small team here and really
valued the ease of management.
Cheers
Dennis
"Larry David" <invalid@.bogus.bum> wrote in message news:<v76dndQtatJ8t6vfRVn-vg@.giganews.com>...
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?
>
> Thanks for the help,
> David|||> network can hack it. I'm not promoting NAS over SAN, but the reason we
> went for NAS was simply that we have a small team here and really
> valued the ease of management.
I don't think you'll find many people recommending NAS over SAN for
real-time RDBMS. The selling point of NAS is cheap storage, definitely not
speed. We use our NAS device for archiving our log files and we barely
trust it for that. We certainly don't trust it for anything client-facing.|||> The performance right now is not good at all.
> Any suggestions? Thanks.
Sure, get more granularity on what "not good" means, and what aspect of
performance you are measuring. Are you seeing slow query speed? Okay,
let's go deeper. Run Peformance Monitor. Is there high blocking? High I/O
or disk queue lengths? High CPU usage? High network utilization? High
memory usage?
I'll leave it as an exercise to the reader to determine which of these
indicate the SAN, and which point at other places. :-)
A|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OEdceVaKFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> The performance right now is not good at all.
>> Any suggestions? Thanks.
> Sure, get more granularity on what "not good" means, and what aspect of
> performance you are measuring. Are you seeing slow query speed?
I was trying to be nice by saying "not good." I've run Performance Monitor
and Query Analyzer on it til I'm blue in the face. I'm 110% convinced it's
*not* SQL Server. My vendor is 110% convinced it's *not* the SAN. So
basically that leaves the "gigabit" fiber, Windows, and the other non-SAN
hardware. Right now there is only one user (me) using the SQL Server. I'm
basically just loading data into the database. Very slowly, I might add.
It's taken 3 weeks to load half the amount of data which it took about 2
weeks to load previously on a test server. An old test server. And when I
was loading the data on the test server with 2 IDE Hard Drives and 1 GB of
RAM, over a 100 Mb/s LAN, I was literally writing the code to load the data
at the same time. I figure I'll be done with this initial load to the EMC
SAN around next Christmas.
I've optimized and tuned the SQL Server as much as humanly possible, run the
Best Practices Analyzer on it and went along with every single
recommendation they made, tuned all my indexes and queries and stored
procedures by hand, one at a time, checking every single query, every single
index, every execution plan, every stored procedure. The new server
connected to the SAN is 8 GB, dual processor, yadda yadda. There are no
blocking issues, CPU utilization is low to medium, memory utilization is
medium, network traffic is low to medium. I'm the only person using the SQL
Server and the EMC SAN right now, but at its current rate of efficiency, I'm
afraid to let another person use it simultaneously.
From my understanding disk queue lengths in appear high. They are averaging
about 6 - 8 per spindle with very high, and common, spikes much, much, much
higher.
According to SQLIO I'm getting a blazing 5 MB/s on the SAN. So what to do
with those results? Hmm... Print 100 copies and start a campfire? No one
seems to have any idea what else they're good for. Other than to get people
(and people's bosses) worked up over yet another set of numbers which may or
may not have relevance to anything in particular, which you may or may not
be able to do anything about; were there instructions relating these numbers
to specific, or even general, courses of action even available to begin
with.
> Okay,
> let's go deeper. Run Peformance Monitor. Is there high blocking?
Been there, done that, still got the T-Shirt. No blocking.
> High I/O
> or disk queue lengths?
Yes, high disk queue lengths.
> High CPU usage? High network utilization? High
> memory usage?
(T-Shirt, matching socks, bermuda shorts) No, no and no some more.
> I'll leave it as an exercise to the reader to determine which of these
> indicate the SAN, and which point at other places. :-)
According to SQLIO, I'm getting about 5 MB/s. This leads the reader to
believe that there's a problem with the Disk IO Subsystem; that or with the
way Windows is interfacing with the SAN. But who knows? Definitely not me.
The only decision I feel like making right now is that I'm going to
uninstall this insipid SQLIO program first thing in the morning.
Anyways, thanks for the feedback.|||> hardware. Right now there is only one user (me) using the SQL Server.
I'm
> basically just loading data into the database.
What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS, ...?
> tuned all my indexes and queries and stored
> procedures by hand,
What does this have to do with your slow data load? In fact, if you've made
significant changes to indexes on the affected table(s), this might be part
of the source of your slow load. The fastest bulk load will be into a heap
(no index), and next will probably be with a clustered index and no ncs
(assuming the data in the file is ordered the same as the clustered index).
> From my understanding disk queue lengths in appear high. They are
averaging
> about 6 - 8 per spindle with very high, and common, spikes much, much,
much
> higher.
All the drives are RAID 1? Are your logs, tempdb, and data files all on the
same drive letter? Is any of this on the local machine and not on the SAN?
Have you looked into where all the I/O is going among those three places?
A|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSfopPbKFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> hardware. Right now there is only one user (me) using the SQL Server.
> I'm
>> basically just loading data into the database.
> What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS,
> ...?
>
By basically, I mean the only activity occurring on the SQL Server right
now, at all, is that it is currently being populated with data from over 500
different data sources. Since each data source has it's own format and
level of complexity, I'm using DTS for some, and custom C#.NET and VB.NET
applications + Stored Procedures for others. I'm basically just loading
data into the database from flat files. Nothing exotic, intriguing or
special, just tedious. An INSERT statement here, a DTS there. Etc.
As I said, I tested every single one of these applications and DTS packages
by loading the data across a 100 MB LAN to a really old under-powered Test
Server here in the office. They all worked fairly well at that point.
>> tuned all my indexes and queries and stored
>> procedures by hand,
> What does this have to do with your slow data load? In fact, if you've
> made
> significant changes to indexes on the affected table(s), this might be
> part
> of the source of your slow load. The fastest bulk load will be into a
> heap
> (no index), and next will probably be with a clustered index and no ncs
> (assuming the data in the file is ordered the same as the clustered
> index).
>
What does this have to do with my slow data load? Simple: Too many indexes
just might tend to slow an insert operation down, right? I suppose it's
similar to the reason I told you about running Best Practices Analyzer and
all those other little things. So we could eliminate all the obvious
questions up front, like "how are your indexes set up, yadda, yadda." To
answer your question, I've eliminated all indexes except the clustered
indexes. And the clustered indexes are very narrow. Most are one or two
integer columns. Nothing special there. Absolutely no non-clustered
indexes.
So what does my choice of load tool have to do with the server barely
pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
answered.
>> From my understanding disk queue lengths in appear high. They are
> averaging
>> about 6 - 8 per spindle with very high, and common, spikes much, much,
> much
>> higher.
> All the drives are RAID 1? Are your logs, tempdb, and data files all on
> the
> same drive letter? Is any of this on the local machine and not on the
> SAN?
> Have you looked into where all the I/O is going among those three places?
>
All drives are RAID 1. I advised the IT guy to put the tempdb on its own
dedicated drive, and put the logs on their own dedicated drive. He said
that it was not possible, so what's happened is that he set up 160 GB
logical volumes (see previous post), and I have assigned one logical volume
for logs, one for tempdb, and the database tables are split up into
filegroups, with each filegroup having its own logical drive. The IT guy
says that the EMC SAN box automatically stripes among the physical drives.
Have I looked at where all the I/O is going among those three places? I've
looked at the physical filesizes, and I know which files are getting larger;
however, I have no way of knowing physically where anything is happening on
the SAN box. According to the vendor, the controllers automatically
distribute the data on the logical volumes among the physical drives. So
the drive J: could be physically spread around 10 physical disks for all I
know. So how do you narrow down where all the I/O is going among the logs,
tempdb and data files when they're spread all over the place? The vendor
has also said that they are not seeing large amounts of activity on the
"gigabit" fiber either, which - if it's true - would indicate that there's a
problem between SQL Server or Windows and the fiber. At least that's what I
would guess. But what do I know? Heck, I thought this wild and crazy
little joke utility would give me information I could use to address the
problem, but I was wrong there also.
And oh yeah, I went to grab the "Best Practices for SQL Server on SAN"
whitepaper from the previously posted Microsoft link in this thread. It
looked promising. But, alas, it has disappeared off the face of the earth.
Thanks|||On Tue, 15 Mar 2005 15:48:20 -0500 in comp.arch.storage, "Aaron [SQL
Server MVP]" <ten.xoc@.dnartreb.noraa> wrote:
>I don't think you'll find many people recommending NAS over SAN for
>real-time RDBMS.
^^^^^^^^^ what are the timing guarantees offered?
Don't use the term *real-time* if you don't understand it or mean it!
--
Thanks. Take care, Brian Inglis Calgary, Alberta, Canada
Brian.Inglis@.CSi.com (Brian[dot]Inglis{at}SystematicSW[dot]ab[dot]ca)
fake address use address above to reply|||> As I said, I tested every single one of these applications and DTS packages
> by loading the data across a 100 MB LAN to a really old under-powered Test
> Server here in the office. They all worked fairly well at that point.
All simultaneously, like you are doing now? Have you tested this against
the older hardware since you've been having the problems?
> So what does my choice of load tool have to do with the server barely
> pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
> answered.
I'm trying to help you narrow down the problem. If you're going to be
hostile about it, I'm not going to bother. I can't SEE your environment,
I'm not there with you, so please bear with me if I ask you a couple of
stupid questions that aren't outright and obvious. You said you "tuned your
indexes" and I know for a fact that indexes tuned for queries are almost
certainly going to slow down data load. So please don't bite my head off
because you weren't more specific.
Remember, I'm trying to help you, not the other way around.
> Have I looked at where all the I/O is going among those three places? I've
> looked at the physical filesizes, and I know which files are getting larger;
No, that's not what I meant. I meant the actual I/O part of the process
that is slowing everything down. Is it during autogrow events? Is it while
committing transactions? Is it while sorting? These can all lead to
different things, but my guess is that your IT guy's judgment of
"impossible" might be a bit questionable here. I don't know of any setup
where log, data and tempdb are all on RAID 1.
> however, I have no way of knowing physically where anything is happening on
> the SAN box. According to the vendor, the controllers automatically
> distribute the data on the logical volumes among the physical drives.
Right, I wasn't asking about that.
> know. So how do you narrow down where all the I/O is going among the logs,
> tempdb and data files when they're spread all over the place?
Perfmon. Profiler. Take each app individually and monitor, monitor,
monitor. See when the disk queue lengths spike up. Watch for autogrow
events. Watch for tempdb or any of the logs filling up. Watch the network
utilization. Maybe someone is slamming your network hard, and it's being
ignored because it's not touching SQL Server directly.
I've been staying away from touching the network stuff because the only real
analysis I've seen so far (aside from "it's slow") is disk queue length.
It's some but I'm still unconvinced that's where the fire is.
A|||> Don't use the term *real-time* if you don't understand it or mean it!
Uh, thanks for the grammar lesson, I guess. I meant production vs. storing
backup files, archived tran logs, warm/cold standby, etc. I've never seen
live databases on a NAS and our organization simply wouldn't allow it
because that's not what a NAS is for.|||Someone better tell Oracle that, not only do they promote NAS heavily,
they run their entire company on it :-)
Seriously though, my impression of NAS is directly the opposite - it
is a serious option for both transactional and analytical RDBMS.
Performance-wise, they are suprisingly good, and the features for
management and backup are outstanding. They also seem very reliable.
We have had a production system (non-RDBMS) that does 3 million
transactions per week running on NetApp Filers for about 4 years now
without problems. I have no real worries about putting an RDMBS system
on them. I guess it depends on which NAS devices we are talking about
- the NetApp gear does not rate as "cheap" that's for sure.
Dennis
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:<ODFXcBaKFHA.3788@.tk2msftngp13.phx.gbl>...
> > network can hack it. I'm not promoting NAS over SAN, but the reason we
> > went for NAS was simply that we have a small team here and really
> > valued the ease of management.
> I don't think you'll find many people recommending NAS over SAN for
> real-time RDBMS. The selling point of NAS is cheap storage, definitely not
> speed. We use our NAS device for archiving our log files and we barely
> trust it for that. We certainly don't trust it for anything client-facing.|||Well the devices we evaluated three years ago were all crap. Even the one
we bought was written off for log file storage about 30 minutes after it was
configured.
Perhaps they have come a long way since then; I'm happy to stand corrected.
But you have to agree that at a much cheaper $/gb, there must still be some
drawback to using NAS vs. SAN. Maybe that's not all performance anymore but
I'm sure that delta is still there, to some degree, in many scenarios.
Having said that, while it's true that you often get what you pay for,
that's not always the case. Take IBM servers or workstations, for example.
Or even Compaq servers, back in the day. All pretty expensive next to the
competition, and they certainly weren't worth the premium (again, in my
experience).
A
On 3/15/05 11:18 PM, in article
66edc890.0503152018.6f45e4f8@.posting.google.com, "Dennis Ingram"
<dennis.ingram@.gmail.com> wrote:
> Someone better tell Oracle that, not only do they promote NAS heavily,
> they run their entire company on it :-)
> Seriously though, my impression of NAS is directly the opposite - it
> is a serious option for both transactional and analytical RDBMS.
> Performance-wise, they are suprisingly good, and the features for
> management and backup are outstanding. They also seem very reliable.
> We have had a production system (non-RDBMS) that does 3 million
> transactions per week running on NetApp Filers for about 4 years now
> without problems. I have no real worries about putting an RDMBS system
> on them. I guess it depends on which NAS devices we are talking about
> - the NetApp gear does not rate as "cheap" that's for sure.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5D1949.351E%ten.xoc@.dnartreb.noraa...
>> As I said, I tested every single one of these applications and DTS
>> packages
>> by loading the data across a 100 MB LAN to a really old under-powered
>> Test
>> Server here in the office. They all worked fairly well at that point.
> All simultaneously, like you are doing now? Have you tested this against
> the older hardware since you've been having the problems?
>
All simultaneously? No, I've limited it to running them one at a time,
although I've successfully run as many as 6 at one time against the old
server. And yes, the first thing I did when I found out about the problem
was run further tests against the old server to make sure I wasn't imagining
it.
>> So what does my choice of load tool have to do with the server barely
>> pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
>> answered.
> I'm trying to help you narrow down the problem. If you're going to be
> hostile about it, I'm not going to bother. I can't SEE your environment,
> I'm not there with you, so please bear with me if I ask you a couple of
> stupid questions that aren't outright and obvious. You said you "tuned
> your
> indexes" and I know for a fact that indexes tuned for queries are almost
> certainly going to slow down data load. So please don't bite my head off
> because you weren't more specific.
> Remember, I'm trying to help you, not the other way around.
I understand, and I appreciate your help. I wasn't trying to bite your head
off, but I also want to give you as much information as possible considering
you're helping me. I probably gave you a lot more information than you
asked for from the jump because I wanted to try to make it a little easier,
and to give you an idea of where I'm at in this process right now.
>> Have I looked at where all the I/O is going among those three places?
>> I've
>> looked at the physical filesizes, and I know which files are getting
>> larger;
> No, that's not what I meant. I meant the actual I/O part of the process
> that is slowing everything down. Is it during autogrow events? Is it
> while
> committing transactions? Is it while sorting? These can all lead to
> different things, but my guess is that your IT guy's judgment of
> "impossible" might be a bit questionable here. I don't know of any setup
> where log, data and tempdb are all on RAID 1.
I've pre-sized the databases, and made them about 30% larger than they
really need to be in order to avoid the autogrow issues. I'm not familiar
with a hardware setup like this one either, but he's convinced that we can't
separate individual physical drives on the SAN and I don't have enough
experience with SANs to take over and tell him how to do it -- so this is
sort of what I'm stuck with.
>> however, I have no way of knowing physically where anything is happening
>> on
>> the SAN box. According to the vendor, the controllers automatically
>> distribute the data on the logical volumes among the physical drives.
> Right, I wasn't asking about that.
Wasn't sure, so I wanted to make sure I covered it just in case...
>> know. So how do you narrow down where all the I/O is going among the
>> logs,
>> tempdb and data files when they're spread all over the place?
> Perfmon. Profiler. Take each app individually and monitor, monitor,
> monitor. See when the disk queue lengths spike up. Watch for autogrow
> events. Watch for tempdb or any of the logs filling up. Watch the
> network
> utilization. Maybe someone is slamming your network hard, and it's being
> ignored because it's not touching SQL Server directly.
I've monitored quite a bit, but I'll monitor again tomorrow. The logs are
far from full - I sized them out when I sized out the data files, so there's
plenty of room; they haven't grown in a week. I'll take a look at tempdb.
I don't think it's network activity external to the SAN that's causing the
issue -- the SAN is set up in another facility, and on another network,
separate from ours, and we've monitored the network utilization pretty
closely. The only thing running on that network right now is SQL Server and
the SAN box.
> I've been staying away from touching the network stuff because the only
> real
> analysis I've seen so far (aside from "it's slow") is disk queue length.
> It's some but I'm still unconvinced that's where the fire is.
I don't have enough experience with SANs to be able to pinpoint the problem
myself. I do know some basic troubleshooting steps in general, and I've
tried just about everything I can think of... I did some straight very
large file-copies today via Windows explorer from the fiber-connected server
to the SAN drives and it was slow as molasses in January. I did some
comparison copies from SAN-drive to SAN-drive with similar results. I also
copied the file across the old 100 Mbps network using my old server, and it
was about 30% faster than the SAN copies. Just for kicks I copied the files
locally on the new server, from one internal drive to another, very
impressive results - extremely fast. I've actually done all this before
with similar results, but thought I'd try it again just to see if anything
changed. Again, I'm not sure, but it seems that SQL Server configuration
and usage might not be the central issue - I actually stopped the SQL Server
Service to completely eliminate it 100% from the equation prior to doing the
direct copies.
I'm not sure what other analysis I could give - as I mentioned, I've
measured network utilization, memory, CPU utilization. I've also monitored
several other counters, including Page Faults, Interrupts/sec, Privelege
Time, and dozens more. They all appear to be within decent tolerances,
based on the documentation I've been able to locate. I'm definitely open to
performing any further analyses you think might be useful.|||> comparison copies from SAN-drive to SAN-drive with similar results.
I think EMC is what is called for here. You're going to have to get them in
and help diagnose the problem, as you've demonstrated that it's not SQL
Server-specific. Who is "the vendor," if they are not from EMC?|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5D2D99.36B9%ten.xoc@.dnartreb.noraa...
>> comparison copies from SAN-drive to SAN-drive with similar results.
> I think EMC is what is called for here. You're going to have to get them
> in
> and help diagnose the problem, as you've demonstrated that it's not SQL
> Server-specific. Who is "the vendor," if they are not from EMC?
>
We bought the hardware from a third-party vendor -- apparently they're a
group of former EMC employees and techs who decided to form their own
business. I wasn't in on the buying decision, so I'm not too familiar with
them.
We got some other info today - apparently the SAN has a 1 gigabit card and
the servers have 2 gigabit cards in them, which we've been told can cause
issues with autonegotiation and slow up the IO, and we also got some insight
into some other BIOS configuration changes we'll try that might help resolve
the issue. If that doesn't solve it, we're going to hook the servers up via
SCSI instead of fiber and see if we have the same issue. The next step
after that, we're going to swap out some cards and see if it alleviates the
problem. It's just a lot of tedious troubleshooting trying to narrow down
the cause, but I think we've narrowed it down to somewhere between Windows
(configuration and/or drivers) and the SAN box itself. At any rate, I'll
let you know how it turns out.
Thanks again for the help.|||Larry David wrote:
> Hi,
> I'm trying to create a home for a database. The database will ultimately
> reach approximately 200Gb in size. Usage patterns will consist of
> approximately 80% read, 20% write. The vast majority of the reads will
> require random access to small chunks of data (less than 64k).
> Two SQL Server machines need to be connected to the same database. The
> primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
> Server will be a machine of lesser capabilities and will only come online if
> the primary data server fails. This concept, as it has been explained to me,
> is known as "failover."
> My job is to investigate various solutions for housing the data. I am
> considering the following storage device:
> http://www-1.ibm.com/servers/storage/disk/ds4000/ds4100/index.html
> This device holds 14 drives and transmits data via a mechanism called
> Fibre Channel, which as far as I can tell, supports a throughput of 2Gbps.
> Here are my questions and concerns:
> 1) Is this device suitable for hosting a database meeting the
> characteristics and requirements that I've described?
> 2) Assuming that I placed 14 drives in this storage device, how would I
> partition it? I've read that the transaction log should be on a separate
> RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
> and 3-14 as RAID 1+0 ? What do you recommend?
IIRC those arrays (at lest DS4300 I dealt with) don't support RAID1+0 themselves (on the hardware level).
So I'd go with 6 RAID1 LUNS (configured on the storage array level) + 2 hot spares.
Then I'd use volume manager (OS level) to stripe those luns and create RAID1+0 volume.
This way you could allocate your transaction logs on the same volume without performance penalty.
But you end up with half of the disk space being "wasted" for mirroring.
This setup is still the best in terms of performance and reliability.
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?
The array has more than one channel. You could, for example, try to use 2 channels in multi-path load balanced configuration (in
opposite to a failover scenario). The ability to use load balanced setup really depends on particular OS, volume manager, etc.,
so It is impossible recommend anything particular at this point. Your best bet is consult your vendor.
> I won't actually be the one installing the storage system. I'll
> obviously need a professional for that. I just want to do enough research to
> determine whether or not the slick salesman who sells us the solution is
> giving me accurate information regarding the suitability of various storage
> solutions.
> Thanks for the help,
> David
>|||One thing you should keep in mind when compare NAS and DAS (direct attached storage)
is that NAS offloads I/O operations that you normally would have to process locally ("wasting" precious CPU cycles on your local
box). It also caches data remotely and it does it very efficiently so using direct I/O option (which bypasses local buffers and
cache) usually gives you better results.
NAS may be a better option in some circumstances, but, of course, it depends on many factors...
Dennis Ingram wrote:
> Just as a matter of interest, we are currently completely a project
> which migrated a large Oracle database from 7-year old AIX system to
> the new generation of machines. For storage we chose NAS, specifically
> NetApp Filers, the connection between the host and the NAS device
> being a 1Gbps ethernet.
> I really did not believe a skinny 1Gbps could provide the throughput
> we needed.
> To start with, we developed using 4 x 146GB internal SCSI disks, and
> the new system was a satisfying 9x faster than the old one (using a
> smaller copy of the database).
> So then we borrowed a Filer with 8 x 144GB drives from the local
> supplier. This was the bottom-of-the-range FAS250 model. The result
> was that it was 10x faster.
> I still do not completely understand why. One thing of course is that
> the I/O characteristics of an RDBMS is not like ftp'ing a file... and
> in case you are wondering, our application was keeping it very busy
> with some heavy-duty batch processing, so it was not like random light
> user transactions.
> Anyway, just thought you might like some reassurance that a 2Gbps SAN
> network can hack it. I'm not promoting NAS over SAN, but the reason we
> went for NAS was simply that we have a small team here and really
> valued the ease of management.
> Cheers
> Dennis
> "Larry David" <invalid@.bogus.bum> wrote in message news:<v76dndQtatJ8t6vfRVn-vg@.giganews.com>...
>> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
>>equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
>>relevant concern given that most I/O will be random, not sequential, and
>>therefore I may not even be hitting the 256MBps cap anyway?
>
>>Thanks for the help,
>>David|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:<BE5D2327.36AE%ten.xoc@.dnartreb.noraa>...
> Well the devices we evaluated three years ago were all crap. Even the one
> we bought was written off for log file storage about 30 minutes after it was
> configured.
Interesting - what devices were these?
> Perhaps they have come a long way since then; I'm happy to stand corrected.
> But you have to agree that at a much cheaper $/gb, there must still be some
> drawback to using NAS vs. SAN. Maybe that's not all performance anymore but
> I'm sure that delta is still there, to some degree, in many scenarios.
Can't see any drawback to be honest. Even the cost when considering
the mythical total cost of ownership. Having said that, I've never
used FC, I've come from an IBM SSA background. Moving from SSA to
NetApp SAN is surreal, the NAS is *so* easy. I've spend enough hours
reconfiguring SSA loops to know a good thing when I see it, although I
needed to get past the initial disbelief with performance with an
RDBMS.
Anyhow, if you haven't looked at NAS recently, go have a browse on
NetApp's website, you might find it interesting to see how much they
have progressed.|||On the link that I sent you:
Scalability and Very Large Database (VLDB) Resources
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
Under the Planning section, there are several white papers describing Server
Consolidation. They go into great detail on how to layout the physical LUNs
within the SAN to properly host a DBMS. Contrary to what your IT person
says, you have to dedicate areas of the SAN to satisfy separate purposes:
data file (random access), log file (serial access), and tempdb
(separation). This is accomplished by the use of METAVOLUMES.
It sounds like EMC constructed the normal strip volume sets between two
drives and then created volume groups up to 160 GB.
As far as the EMC/Clariion link, I'm not sure what EMC has done with that
but I'm including it here for you to check out. If it doesn't come through,
let me know and I'll send it to you directly.
Also, check out your Disk Avg. sec/Read and Avg. sec/Write performance
monitor counters. If these are any higher than 10 - 20 ms, ever, then you
have SAN disk latency and the SAN is laid out incorrectly.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.yomomma.com> wrote in message
news:OT0wEnbKFHA.2764@.tk2msftngp13.phx.gbl...
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSfopPbKFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> hardware. Right now there is only one user (me) using the SQL Server.
> I'm
>> basically just loading data into the database.
> What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS,
> ...?
>
By basically, I mean the only activity occurring on the SQL Server right
now, at all, is that it is currently being populated with data from over 500
different data sources. Since each data source has it's own format and
level of complexity, I'm using DTS for some, and custom C#.NET and VB.NET
applications + Stored Procedures for others. I'm basically just loading
data into the database from flat files. Nothing exotic, intriguing or
special, just tedious. An INSERT statement here, a DTS there. Etc.
As I said, I tested every single one of these applications and DTS packages
by loading the data across a 100 MB LAN to a really old under-powered Test
Server here in the office. They all worked fairly well at that point.
>> tuned all my indexes and queries and stored
>> procedures by hand,
> What does this have to do with your slow data load? In fact, if you've
> made
> significant changes to indexes on the affected table(s), this might be
> part
> of the source of your slow load. The fastest bulk load will be into a
> heap
> (no index), and next will probably be with a clustered index and no ncs
> (assuming the data in the file is ordered the same as the clustered
> index).
>
What does this have to do with my slow data load? Simple: Too many indexes
just might tend to slow an insert operation down, right? I suppose it's
similar to the reason I told you about running Best Practices Analyzer and
all those other little things. So we could eliminate all the obvious
questions up front, like "how are your indexes set up, yadda, yadda." To
answer your question, I've eliminated all indexes except the clustered
indexes. And the clustered indexes are very narrow. Most are one or two
integer columns. Nothing special there. Absolutely no non-clustered
indexes.
So what does my choice of load tool have to do with the server barely
pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
answered.
>> From my understanding disk queue lengths in appear high. They are
> averaging
>> about 6 - 8 per spindle with very high, and common, spikes much, much,
> much
>> higher.
> All the drives are RAID 1? Are your logs, tempdb, and data files all on
> the
> same drive letter? Is any of this on the local machine and not on the
> SAN?
> Have you looked into where all the I/O is going among those three places?
>
All drives are RAID 1. I advised the IT guy to put the tempdb on its own
dedicated drive, and put the logs on their own dedicated drive. He said
that it was not possible, so what's happened is that he set up 160 GB
logical volumes (see previous post), and I have assigned one logical volume
for logs, one for tempdb, and the database tables are split up into
filegroups, with each filegroup having its own logical drive. The IT guy
says that the EMC SAN box automatically stripes among the physical drives.
Have I looked at where all the I/O is going among those three places? I've
looked at the physical filesizes, and I know which files are getting larger;
however, I have no way of knowing physically where anything is happening on
the SAN box. According to the vendor, the controllers automatically
distribute the data on the logical volumes among the physical drives. So
the drive J: could be physically spread around 10 physical disks for all I
know. So how do you narrow down where all the I/O is going among the logs,
tempdb and data files when they're spread all over the place? The vendor
has also said that they are not seeing large amounts of activity on the
"gigabit" fiber either, which - if it's true - would indicate that there's a
problem between SQL Server or Windows and the fiber. At least that's what I
would guess. But what do I know? Heck, I thought this wild and crazy
little joke utility would give me information I could use to address the
problem, but I was wrong there also.
And oh yeah, I went to grab the "Best Practices for SQL Server on SAN"
whitepaper from the previously posted Microsoft link in this thread. It
looked promising. But, alas, it has disappeared off the face of the earth.
Thanks|||What vendor are you using for your HBAs? QLogic or Emmulex? Make sure you
get updated drivers; the ones that are native to the Windows installation CD
are old.
Take a look at your Avg. sec/Read and Avg. sec/Write. If these are higher
than 10 - 20 ms then you have latency in the controllers, host or SAN, or
configuration of the physical layout within the SAN.
Make sure you DO NOT RUN network cards in AUTONEGOTIATE mode, NEVER.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.yomomma.com> wrote in message
news:OosUDQkKFHA.2604@.TK2MSFTNGP10.phx.gbl...
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE5D2D99.36B9%ten.xoc@.dnartreb.noraa...
>> comparison copies from SAN-drive to SAN-drive with similar results.
> I think EMC is what is called for here. You're going to have to get them
> in
> and help diagnose the problem, as you've demonstrated that it's not SQL
> Server-specific. Who is "the vendor," if they are not from EMC?
>
We bought the hardware from a third-party vendor -- apparently they're a
group of former EMC employees and techs who decided to form their own
business. I wasn't in on the buying decision, so I'm not too familiar with
them.
We got some other info today - apparently the SAN has a 1 gigabit card and
the servers have 2 gigabit cards in them, which we've been told can cause
issues with autonegotiation and slow up the IO, and we also got some insight
into some other BIOS configuration changes we'll try that might help resolve
the issue. If that doesn't solve it, we're going to hook the servers up via
SCSI instead of fiber and see if we have the same issue. The next step
after that, we're going to swap out some cards and see if it alleviates the
problem. It's just a lot of tedious troubleshooting trying to narrow down
the cause, but I think we've narrowed it down to somewhere between Windows
(configuration and/or drivers) and the SAN box itself. At any rate, I'll
let you know how it turns out.
Thanks again for the help.|||Looks like that file will not post to the newsgroup and your return email is
invalid. I have it if you are interested but I need some place to deliver
it.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OTkK7fvKFHA.3296@.TK2MSFTNGP15.phx.gbl...
On the link that I sent you:
Scalability and Very Large Database (VLDB) Resources
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
Under the Planning section, there are several white papers describing Server
Consolidation. They go into great detail on how to layout the physical LUNs
within the SAN to properly host a DBMS. Contrary to what your IT person
says, you have to dedicate areas of the SAN to satisfy separate purposes:
data file (random access), log file (serial access), and tempdb
(separation). This is accomplished by the use of METAVOLUMES.
It sounds like EMC constructed the normal strip volume sets between two
drives and then created volume groups up to 160 GB.
As far as the EMC/Clariion link, I'm not sure what EMC has done with that
but I'm including it here for you to check out. If it doesn't come through,
let me know and I'll send it to you directly.
Also, check out your Disk Avg. sec/Read and Avg. sec/Write performance
monitor counters. If these are any higher than 10 - 20 ms, ever, then you
have SAN disk latency and the SAN is laid out incorrectly.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.yomomma.com> wrote in message
news:OT0wEnbKFHA.2764@.tk2msftngp13.phx.gbl...
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eSfopPbKFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> hardware. Right now there is only one user (me) using the SQL Server.
> I'm
>> basically just loading data into the database.
> What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS,
> ...?
>
By basically, I mean the only activity occurring on the SQL Server right
now, at all, is that it is currently being populated with data from over 500
different data sources. Since each data source has it's own format and
level of complexity, I'm using DTS for some, and custom C#.NET and VB.NET
applications + Stored Procedures for others. I'm basically just loading
data into the database from flat files. Nothing exotic, intriguing or
special, just tedious. An INSERT statement here, a DTS there. Etc.
As I said, I tested every single one of these applications and DTS packages
by loading the data across a 100 MB LAN to a really old under-powered Test
Server here in the office. They all worked fairly well at that point.
>> tuned all my indexes and queries and stored
>> procedures by hand,
> What does this have to do with your slow data load? In fact, if you've
> made
> significant changes to indexes on the affected table(s), this might be
> part
> of the source of your slow load. The fastest bulk load will be into a
> heap
> (no index), and next will probably be with a clustered index and no ncs
> (assuming the data in the file is ordered the same as the clustered
> index).
>
What does this have to do with my slow data load? Simple: Too many indexes
just might tend to slow an insert operation down, right? I suppose it's
similar to the reason I told you about running Best Practices Analyzer and
all those other little things. So we could eliminate all the obvious
questions up front, like "how are your indexes set up, yadda, yadda." To
answer your question, I've eliminated all indexes except the clustered
indexes. And the clustered indexes are very narrow. Most are one or two
integer columns. Nothing special there. Absolutely no non-clustered
indexes.
So what does my choice of load tool have to do with the server barely
pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
answered.
>> From my understanding disk queue lengths in appear high. They are
> averaging
>> about 6 - 8 per spindle with very high, and common, spikes much, much,
> much
>> higher.
> All the drives are RAID 1? Are your logs, tempdb, and data files all on
> the
> same drive letter? Is any of this on the local machine and not on the
> SAN?
> Have you looked into where all the I/O is going among those three places?
>
All drives are RAID 1. I advised the IT guy to put the tempdb on its own
dedicated drive, and put the logs on their own dedicated drive. He said
that it was not possible, so what's happened is that he set up 160 GB
logical volumes (see previous post), and I have assigned one logical volume
for logs, one for tempdb, and the database tables are split up into
filegroups, with each filegroup having its own logical drive. The IT guy
says that the EMC SAN box automatically stripes among the physical drives.
Have I looked at where all the I/O is going among those three places? I've
looked at the physical filesizes, and I know which files are getting larger;
however, I have no way of knowing physically where anything is happening on
the SAN box. According to the vendor, the controllers automatically
distribute the data on the logical volumes among the physical drives. So
the drive J: could be physically spread around 10 physical disks for all I
know. So how do you narrow down where all the I/O is going among the logs,
tempdb and data files when they're spread all over the place? The vendor
has also said that they are not seeing large amounts of activity on the
"gigabit" fiber either, which - if it's true - would indicate that there's a
problem between SQL Server or Windows and the fiber. At least that's what I
would guess. But what do I know? Heck, I thought this wild and crazy
little joke utility would give me information I could use to address the
problem, but I was wrong there also.
And oh yeah, I went to grab the "Best Practices for SQL Server on SAN"
whitepaper from the previously posted Microsoft link in this thread. It
looked promising. But, alas, it has disappeared off the face of the earth.
Thanks|||Thanks for the tips. I'll let the IT guy know to make sure Autonegotiate is
off. We're using Q-Logic HBAs. The IT guy is supposed to be checking the
drivers on those today. Which Avg. sec/Read and Avg. sec/Write am I looking
at? Each individual drive, or the _Total counter? Thanks.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23OdRmivKFHA.3960@.TK2MSFTNGP09.phx.gbl...
> What vendor are you using for your HBAs? QLogic or Emmulex? Make sure
> you
> get updated drivers; the ones that are native to the Windows installation
> CD
> are old.
> Take a look at your Avg. sec/Read and Avg. sec/Write. If these are higher
> than 10 - 20 ms then you have latency in the controllers, host or SAN, or
> configuration of the physical layout within the SAN.
> Make sure you DO NOT RUN network cards in AUTONEGOTIATE mode, NEVER.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:OosUDQkKFHA.2604@.TK2MSFTNGP10.phx.gbl...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE5D2D99.36B9%ten.xoc@.dnartreb.noraa...
>> comparison copies from SAN-drive to SAN-drive with similar results.
>> I think EMC is what is called for here. You're going to have to get them
>> in
>> and help diagnose the problem, as you've demonstrated that it's not SQL
>> Server-specific. Who is "the vendor," if they are not from EMC?
> We bought the hardware from a third-party vendor -- apparently they're a
> group of former EMC employees and techs who decided to form their own
> business. I wasn't in on the buying decision, so I'm not too familiar
> with
> them.
> We got some other info today - apparently the SAN has a 1 gigabit card and
> the servers have 2 gigabit cards in them, which we've been told can cause
> issues with autonegotiation and slow up the IO, and we also got some
> insight
> into some other BIOS configuration changes we'll try that might help
> resolve
> the issue. If that doesn't solve it, we're going to hook the servers up
> via
> SCSI instead of fiber and see if we have the same issue. The next step
> after that, we're going to swap out some cards and see if it alleviates
> the
> problem. It's just a lot of tedious troubleshooting trying to narrow down
> the cause, but I think we've narrowed it down to somewhere between Windows
> (configuration and/or drivers) and the SAN box itself. At any rate, I'll
> let you know how it turns out.
> Thanks again for the help.
>|||I sent you my email address. I get way too much junk mail as is, so I don't
use my e-mail on posts.
Thanks
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oo0yKlvKFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Looks like that file will not post to the newsgroup and your return email
> is
> invalid. I have it if you are interested but I need some place to deliver
> it.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OTkK7fvKFHA.3296@.TK2MSFTNGP15.phx.gbl...
> On the link that I sent you:
> Scalability and Very Large Database (VLDB) Resources
> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> Under the Planning section, there are several white papers describing
> Server
> Consolidation. They go into great detail on how to layout the physical
> LUNs
> within the SAN to properly host a DBMS. Contrary to what your IT person
> says, you have to dedicate areas of the SAN to satisfy separate purposes:
> data file (random access), log file (serial access), and tempdb
> (separation). This is accomplished by the use of METAVOLUMES.
> It sounds like EMC constructed the normal strip volume sets between two
> drives and then created volume groups up to 160 GB.
> As far as the EMC/Clariion link, I'm not sure what EMC has done with that
> but I'm including it here for you to check out. If it doesn't come
> through,
> let me know and I'll send it to you directly.
> Also, check out your Disk Avg. sec/Read and Avg. sec/Write performance
> monitor counters. If these are any higher than 10 - 20 ms, ever, then you
> have SAN disk latency and the SAN is laid out incorrectly.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:OT0wEnbKFHA.2764@.tk2msftngp13.phx.gbl...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSfopPbKFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> hardware. Right now there is only one user (me) using the SQL Server.
>> I'm
>> basically just loading data into the database.
>> What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS,
>> ...?
> By basically, I mean the only activity occurring on the SQL Server right
> now, at all, is that it is currently being populated with data from over
> 500
> different data sources. Since each data source has it's own format and
> level of complexity, I'm using DTS for some, and custom C#.NET and VB.NET
> applications + Stored Procedures for others. I'm basically just loading
> data into the database from flat files. Nothing exotic, intriguing or
> special, just tedious. An INSERT statement here, a DTS there. Etc.
> As I said, I tested every single one of these applications and DTS
> packages
> by loading the data across a 100 MB LAN to a really old under-powered Test
> Server here in the office. They all worked fairly well at that point.
>> tuned all my indexes and queries and stored
>> procedures by hand,
>> What does this have to do with your slow data load? In fact, if you've
>> made
>> significant changes to indexes on the affected table(s), this might be
>> part
>> of the source of your slow load. The fastest bulk load will be into a
>> heap
>> (no index), and next will probably be with a clustered index and no ncs
>> (assuming the data in the file is ordered the same as the clustered
>> index).
> What does this have to do with my slow data load? Simple: Too many
> indexes
> just might tend to slow an insert operation down, right? I suppose it's
> similar to the reason I told you about running Best Practices Analyzer and
> all those other little things. So we could eliminate all the obvious
> questions up front, like "how are your indexes set up, yadda, yadda." To
> answer your question, I've eliminated all indexes except the clustered
> indexes. And the clustered indexes are very narrow. Most are one or two
> integer columns. Nothing special there. Absolutely no non-clustered
> indexes.
> So what does my choice of load tool have to do with the server barely
> pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
> answered.
>> From my understanding disk queue lengths in appear high. They are
>> averaging
>> about 6 - 8 per spindle with very high, and common, spikes much, much,
>> much
>> higher.
>> All the drives are RAID 1? Are your logs, tempdb, and data files all on
>> the
>> same drive letter? Is any of this on the local machine and not on the
>> SAN?
>> Have you looked into where all the I/O is going among those three places?
> All drives are RAID 1. I advised the IT guy to put the tempdb on its own
> dedicated drive, and put the logs on their own dedicated drive. He said
> that it was not possible, so what's happened is that he set up 160 GB
> logical volumes (see previous post), and I have assigned one logical
> volume
> for logs, one for tempdb, and the database tables are split up into
> filegroups, with each filegroup having its own logical drive. The IT guy
> says that the EMC SAN box automatically stripes among the physical drives.
> Have I looked at where all the I/O is going among those three places?
> I've
> looked at the physical filesizes, and I know which files are getting
> larger;
> however, I have no way of knowing physically where anything is happening
> on
> the SAN box. According to the vendor, the controllers automatically
> distribute the data on the logical volumes among the physical drives. So
> the drive J: could be physically spread around 10 physical disks for all I
> know. So how do you narrow down where all the I/O is going among the
> logs,
> tempdb and data files when they're spread all over the place? The vendor
> has also said that they are not seeing large amounts of activity on the
> "gigabit" fiber either, which - if it's true - would indicate that there's
> a
> problem between SQL Server or Windows and the fiber. At least that's what
> I
> would guess. But what do I know? Heck, I thought this wild and crazy
> little joke utility would give me information I could use to address the
> problem, but I was wrong there also.
> And oh yeah, I went to grab the "Best Practices for SQL Server on SAN"
> whitepaper from the previously posted Microsoft link in this thread. It
> looked promising. But, alas, it has disappeared off the face of the
> earth.
> Thanks
>|||I work for NovaStar.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uhMxUWWKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> Where abouts in KC? I work for Commerce Bank.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:%23xwdT4QKFHA.2996@.TK2MSFTNGP10.phx.gbl...
> I like EMC, Hitachi, and IBM. I would stay away from HP with all the
issues
> they're having right now. Make sure whatever storage you choose has a
> service location and parts depot close to you. My company is in Kansas
> City. The closest Hitachi service center and parts depot is 3 hours away
in
> St. Louis, so it makes more sense for us to go with EMC which is local.
> IBM costs more, but they have great service. Hitachi gives you a lot of
the
> software that EMC charges you for; however, EMC has more to offer in
overall
> features. Hitachi tends to cost less than EMC. You really don't have to
> worry about buying your storage solution from IBM because the servers are
> IBM. All the major storage/server vendors have relationships with each
> other to alleviate this. Again, stay away from HP. Their service and
> turnaround times for the enterprise platforms have been horrible lately.
> Dell is EMC rebranded in case you decide to check them out. The hardware
is
> the same, but they have their own service staff, support etc. Depending
on
> your location, they sometimes do a better job than the actual EMC people.
> So, that's something to think about also.
>
>
> "Larry David" <invalid@.bogus.bum> wrote in message
> news:M4qdnaIn9c-gzKvfRVn-2w@.giganews.com...
> > >
> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> > >
> > > This site is full of information. Make sure you scroll through it and
> > read
> > > all of the subtitles.
> >
> > Anthony,
> >
> > That's a great link. I appreciate the tip. I'll check out those
> > articles.
> >
> > Aaron,
> >
> > I'll check out EMC. I was thinking that since we are buying our
> servers
> > from IBM, it would be a natural decision to buy the storage from them as
> > well... but after reading your post, I now see that EMC has an excellent
> > reputation. Perhaps they have a solution that's more suitable. Are there
> any
> > other vendors that you think I should investigate? Any thoughts on
Hitachi
> > Data Systems (hds.com)?
> >
> > David
> >
> >
>|||This has become quite the thread. :) I tried to read the whole thing. Are
you still having trouble Michael? Here are some thing to check for if you
are (from what I've read on the thread):
1. Make sure the QLogic cards have the latest EMC approved BIOS and
drivers.
--You can find these at the following site:
http://www.qlogic.com/support/oem_product_list.asp?oemid=65
--I would assume you have the QLA2342-E cards from what you have told me.
2. Download SANSurfer SANblade Manager software also. Make sure your HBA
cards are configure to either:
--Loop Preferred, Point to Point or Point to Point (I'm not sure which one
Symmetrix uses, but it's not Loop Preferred)
3. Make sure your drivers are up-to-date. You can find the latest version
at the site I gave you.
4. Have your SAN engineer (if he calls himself that) verify that pathing is
active/active and all the ports are showing up.
--This sounds stupid. We had one of our people redo the pathing on the
servers. Some of the lights weren't even on.
5. It's fairly easy to setup LUNs with dedicated drives on the SAN.
--You create the RAID array as either RAID 10 or RAID 5. (array on the SAN
is the RAID of the physical disks)
--You then create a LUN from the RAID array (BIND LUN) and select the
maximum size in the little dropdown box.
--I've done this waaaaaaaaaay too many times.
--You then add that LUN to the hosts storage group.
Also, I don't believe the Symmetrix even has 80gb drives. They are probably
73gb 10k or 15k drives. If you're only getting 5mb/sec on IOMeter, then
your performance reallys sucks. Have you tried increasing the workers to
see if you just didn't throw enough at it? When you were running this, what
were your queue lengths looking like?
Here is just a wild guess on my part. You might be using MetaLUNs.
Basically, you take all those 160gb RAID 1 arrays you have and either stripe
across multiple parts of them using MetaLUNs or even worse concatenate
MetaLUNs. Do you have any individual drive letters (LUNs that are more than
160gb on these servers? If you do, then that's what you're doing if the SAN
people told you the truth. Find out what the level of flare is on the
Symmetrix. Depending on where you're at, this can have a really bad effect
on performance. Have you seen any errors in the eventviewer during the time
you're loading the files (just out of curiousity).
In case you haven't noticed, it's extremely hard to troubleshoot these kinds
of things without actually being there.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:ut#tNVzKFHA.3640@.TK2MSFTNGP12.phx.gbl...
> I sent you my email address. I get way too much junk mail as is, so I
don't
> use my e-mail on posts.
> Thanks
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:Oo0yKlvKFHA.2640@.TK2MSFTNGP09.phx.gbl...
> > Looks like that file will not post to the newsgroup and your return
email
> > is
> > invalid. I have it if you are interested but I need some place to
deliver
> > it.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> > news:OTkK7fvKFHA.3296@.TK2MSFTNGP15.phx.gbl...
> > On the link that I sent you:
> >
> > Scalability and Very Large Database (VLDB) Resources
> >
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> >
> > Under the Planning section, there are several white papers describing
> > Server
> > Consolidation. They go into great detail on how to layout the physical
> > LUNs
> > within the SAN to properly host a DBMS. Contrary to what your IT person
> > says, you have to dedicate areas of the SAN to satisfy separate
purposes:
> > data file (random access), log file (serial access), and tempdb
> > (separation). This is accomplished by the use of METAVOLUMES.
> >
> > It sounds like EMC constructed the normal strip volume sets between two
> > drives and then created volume groups up to 160 GB.
> >
> > As far as the EMC/Clariion link, I'm not sure what EMC has done with
that
> > but I'm including it here for you to check out. If it doesn't come
> > through,
> > let me know and I'll send it to you directly.
> >
> > Also, check out your Disk Avg. sec/Read and Avg. sec/Write performance
> > monitor counters. If these are any higher than 10 - 20 ms, ever, then
you
> > have SAN disk latency and the SAN is laid out incorrectly.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> >
> > --
> >
> > "Michael C#" <xyz@.yomomma.com> wrote in message
> > news:OT0wEnbKFHA.2764@.tk2msftngp13.phx.gbl...
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:eSfopPbKFHA.1396@.TK2MSFTNGP10.phx.gbl...
> >> hardware. Right now there is only one user (me) using the SQL Server.
> >> I'm
> >> basically just loading data into the database.
> >>
> >> What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS,
> >> ...?
> >>
> >
> > By basically, I mean the only activity occurring on the SQL Server right
> > now, at all, is that it is currently being populated with data from over
> > 500
> > different data sources. Since each data source has it's own format and
> > level of complexity, I'm using DTS for some, and custom C#.NET and
VB.NET
> > applications + Stored Procedures for others. I'm basically just loading
> > data into the database from flat files. Nothing exotic, intriguing or
> > special, just tedious. An INSERT statement here, a DTS there. Etc.
> >
> > As I said, I tested every single one of these applications and DTS
> > packages
> > by loading the data across a 100 MB LAN to a really old under-powered
Test
> > Server here in the office. They all worked fairly well at that point.
> >
> >> tuned all my indexes and queries and stored
> >> procedures by hand,
> >>
> >> What does this have to do with your slow data load? In fact, if you've
> >> made
> >> significant changes to indexes on the affected table(s), this might be
> >> part
> >> of the source of your slow load. The fastest bulk load will be into a
> >> heap
> >> (no index), and next will probably be with a clustered index and no ncs
> >> (assuming the data in the file is ordered the same as the clustered
> >> index).
> >>
> >
> > What does this have to do with my slow data load? Simple: Too many
> > indexes
> > just might tend to slow an insert operation down, right? I suppose it's
> > similar to the reason I told you about running Best Practices Analyzer
and
> > all those other little things. So we could eliminate all the obvious
> > questions up front, like "how are your indexes set up, yadda, yadda."
To
> > answer your question, I've eliminated all indexes except the clustered
> > indexes. And the clustered indexes are very narrow. Most are one or
two
> > integer columns. Nothing special there. Absolutely no non-clustered
> > indexes.
> >
> > So what does my choice of load tool have to do with the server barely
> > pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
> > answered.
> >
> >> From my understanding disk queue lengths in appear high. They are
> >> averaging
> >> about 6 - 8 per spindle with very high, and common, spikes much, much,
> >> much
> >> higher.
> >>
> >> All the drives are RAID 1? Are your logs, tempdb, and data files all
on
> >> the
> >> same drive letter? Is any of this on the local machine and not on the
> >> SAN?
> >> Have you looked into where all the I/O is going among those three
places?
> >>
> >
> > All drives are RAID 1. I advised the IT guy to put the tempdb on its
own
> > dedicated drive, and put the logs on their own dedicated drive. He said
> > that it was not possible, so what's happened is that he set up 160 GB
> > logical volumes (see previous post), and I have assigned one logical
> > volume
> > for logs, one for tempdb, and the database tables are split up into
> > filegroups, with each filegroup having its own logical drive. The IT
guy
> > says that the EMC SAN box automatically stripes among the physical
drives.
> >
> > Have I looked at where all the I/O is going among those three places?
> > I've
> > looked at the physical filesizes, and I know which files are getting
> > larger;
> > however, I have no way of knowing physically where anything is happening
> > on
> > the SAN box. According to the vendor, the controllers automatically
> > distribute the data on the logical volumes among the physical drives.
So
> > the drive J: could be physically spread around 10 physical disks for all
I
> > know. So how do you narrow down where all the I/O is going among the
> > logs,
> > tempdb and data files when they're spread all over the place? The
vendor
> > has also said that they are not seeing large amounts of activity on the
> > "gigabit" fiber either, which - if it's true - would indicate that
there's
> > a
> > problem between SQL Server or Windows and the fiber. At least that's
what
> > I
> > would guess. But what do I know? Heck, I thought this wild and crazy
> > little joke utility would give me information I could use to address the
> > problem, but I was wrong there also.
> >
> > And oh yeah, I went to grab the "Best Practices for SQL Server on SAN"
> > whitepaper from the previously posted Microsoft link in this thread. It
> > looked promising. But, alas, it has disappeared off the face of the
> > earth.
> >
> > Thanks
> >
> >
>|||Larry David wrote:
> Hi,
> I'm trying to create a home for a database. The database will ultimately
> reach approximately 200Gb in size. Usage patterns will consist of
> approximately 80% read, 20% write. The vast majority of the reads will
> require random access to small chunks of data (less than 64k).
> Two SQL Server machines need to be connected to the same database. The
> primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
> Server will be a machine of lesser capabilities and will only come online if
> the primary data server fails. This concept, as it has been explained to me,
> is known as "failover."
>
This is called clustering. I didn't catch which OS you were
implementing. Different OS's handle it differently. Clustering is
definitely what you are after though. Some OS's have active-passive and
some have active-active clustering.
> My job is to investigate various solutions for housing the data. I am
> considering the following storage device:
> http://www-1.ibm.com/servers/storage/disk/ds4000/ds4100/index.html
Please also consider other vendors like EMC for mid tier storage.
Sounds to me like your scenario doesn't call for high end Toshiba or the
like storage systems.
Someone mentioned avoiding EMC, but the situation they described is late
nineties mentality. Most all vendors during the boom were after as much
as they could get all the time. Most had a chip on their shoulder too.
EMC's current CEO has changed much of that mentality. If for no other
reason play IBM vs. EMC at the negotiating table. You are after all
talking about 10's of thousands of dollars here. Ultimately the vendors
are fighting for footprint. Getting their foot in the door(especially
in a shop without any storage) will greatly increase their chances of
selling more storage to you in the future.
> This device holds 14 drives and transmits data via a mechanism called
> Fibre Channel, which as far as I can tell, supports a throughput of 2Gbps.
> Here are my questions and concerns:
> 1) Is this device suitable for hosting a database meeting the
> characteristics and requirements that I've described?
> 2) Assuming that I placed 14 drives in this storage device, how would I
> partition it? I've read that the transaction log should be on a separate
> RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
> and 3-14 as RAID 1+0 ? What do you recommend?
> 3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
> equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
> relevant concern given that most I/O will be random, not sequential, and
> therefore I may not even be hitting the 256MBps cap anyway?
These numbers are way higher than what you will see. 'theoretical' <>
'actual' especially when reading sales literature. Your database IO
will ultimately determine this. You'll want to analyze latencies and
performance constantly after first installing a solution.
> I won't actually be the one installing the storage system. I'll
> obviously need a professional for that. I just want to do enough research to
> determine whether or not the slick salesman who sells us the solution is
> giving me accurate information regarding the suitability of various storage
> solutions.
>
It's not necessary for you to do the installation, but do participate
heavily in the design. Also get a training course/certification of some
kind folded into your project cost.
> Thanks for the help,
> David
>
For reference, this is what I have:
EMC CX600 which has 2 separate storage processors 4GB cache per storage
processor.
6 DAE's 11.5 TB total.
2 16port Brocade 2Gb FC switches
5 Windows 2003 servers each with 2 2Gb Qlogic HBA's
Our setup is not clustered. We use cold data/log shipping to a hot
standby MSSQL server also SAN attached.
Our production MSSQL server hosts 35 databases. 1 database is 95GB and
is spread across 3 physical LUNs. Data is spread across a 6 disk
RAID1+0 and a 4 disk RAID1+0, Logs are on 6 disk RAID 1+0. tempdb is on
a 4 disk RAID5. The other db's are much smaller and their data is on
one 4 disk RAID1+0 and their logs are on another 4 disk RAID1+0. The
server is a Quad Xeon 3.0 with 32GB memory. We typically see 80-90
Mbsec sustained data transfers on these SAN disks. This solution was
designed and implemented in the summer of 2003.
I know nothing of NAS, but this SAN setup is completely redundant. Not
sure that you can say that about a NAS head. Bad memory a blown power
supply, etc could yank the disks out from under a SQL Server. As you
can imagine this is *very* bad. The CX 600 can trespass LUNS in the
event of a disruption at any connectivity level without affecting the
data. Slight slowdown yes, data loss no. The CX 600 also has a
non-disruptive software upgrade path. In nearly 2 years since
installation we have rebooted the SAN only once due to a datacenter
power re-config. We've upgraded the software 4 times.
We considered the equivalent IBM solution, even going so far as to spend
a day in their Dallas lab to bring our own data and applications to
test. But ultimately went with EMC.
HtH
-tM-
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Good. You have multiple drives...at least from the OS' perspective.
Yes, each individual drive.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.yomomma.com> wrote in message
news:%23FwcdQzKFHA.2648@.TK2MSFTNGP14.phx.gbl...
Thanks for the tips. I'll let the IT guy know to make sure Autonegotiate is
off. We're using Q-Logic HBAs. The IT guy is supposed to be checking the
drivers on those today. Which Avg. sec/Read and Avg. sec/Write am I looking
at? Each individual drive, or the _Total counter? Thanks.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23OdRmivKFHA.3960@.TK2MSFTNGP09.phx.gbl...
> What vendor are you using for your HBAs? QLogic or Emmulex? Make sure
> you
> get updated drivers; the ones that are native to the Windows installation
> CD
> are old.
> Take a look at your Avg. sec/Read and Avg. sec/Write. If these are higher
> than 10 - 20 ms then you have latency in the controllers, host or SAN, or
> configuration of the physical layout within the SAN.
> Make sure you DO NOT RUN network cards in AUTONEGOTIATE mode, NEVER.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:OosUDQkKFHA.2604@.TK2MSFTNGP10.phx.gbl...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE5D2D99.36B9%ten.xoc@.dnartreb.noraa...
>> comparison copies from SAN-drive to SAN-drive with similar results.
>> I think EMC is what is called for here. You're going to have to get them
>> in
>> and help diagnose the problem, as you've demonstrated that it's not SQL
>> Server-specific. Who is "the vendor," if they are not from EMC?
> We bought the hardware from a third-party vendor -- apparently they're a
> group of former EMC employees and techs who decided to form their own
> business. I wasn't in on the buying decision, so I'm not too familiar
> with
> them.
> We got some other info today - apparently the SAN has a 1 gigabit card and
> the servers have 2 gigabit cards in them, which we've been told can cause
> issues with autonegotiation and slow up the IO, and we also got some
> insight
> into some other BIOS configuration changes we'll try that might help
> resolve
> the issue. If that doesn't solve it, we're going to hook the servers up
> via
> SCSI instead of fiber and see if we have the same issue. The next step
> after that, we're going to swap out some cards and see if it alleviates
> the
> problem. It's just a lot of tedious troubleshooting trying to narrow down
> the cause, but I think we've narrowed it down to somewhere between Windows
> (configuration and/or drivers) and the SAN box itself. At any rate, I'll
> let you know how it turns out.
> Thanks again for the help.
>|||Oops. I might have deleted it...accidently, of course. I get way too much
junk mail also. Sorry.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.yomomma.com> wrote in message
news:ut%23tNVzKFHA.3640@.TK2MSFTNGP12.phx.gbl...
I sent you my email address. I get way too much junk mail as is, so I don't
use my e-mail on posts.
Thanks
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:Oo0yKlvKFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Looks like that file will not post to the newsgroup and your return email
> is
> invalid. I have it if you are interested but I need some place to deliver
> it.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OTkK7fvKFHA.3296@.TK2MSFTNGP15.phx.gbl...
> On the link that I sent you:
> Scalability and Very Large Database (VLDB) Resources
> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> Under the Planning section, there are several white papers describing
> Server
> Consolidation. They go into great detail on how to layout the physical
> LUNs
> within the SAN to properly host a DBMS. Contrary to what your IT person
> says, you have to dedicate areas of the SAN to satisfy separate purposes:
> data file (random access), log file (serial access), and tempdb
> (separation). This is accomplished by the use of METAVOLUMES.
> It sounds like EMC constructed the normal strip volume sets between two
> drives and then created volume groups up to 160 GB.
> As far as the EMC/Clariion link, I'm not sure what EMC has done with that
> but I'm including it here for you to check out. If it doesn't come
> through,
> let me know and I'll send it to you directly.
> Also, check out your Disk Avg. sec/Read and Avg. sec/Write performance
> monitor counters. If these are any higher than 10 - 20 ms, ever, then you
> have SAN disk latency and the SAN is laid out incorrectly.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:OT0wEnbKFHA.2764@.tk2msftngp13.phx.gbl...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eSfopPbKFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> hardware. Right now there is only one user (me) using the SQL Server.
>> I'm
>> basically just loading data into the database.
>> What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS,
>> ...?
> By basically, I mean the only activity occurring on the SQL Server right
> now, at all, is that it is currently being populated with data from over
> 500
> different data sources. Since each data source has it's own format and
> level of complexity, I'm using DTS for some, and custom C#.NET and VB.NET
> applications + Stored Procedures for others. I'm basically just loading
> data into the database from flat files. Nothing exotic, intriguing or
> special, just tedious. An INSERT statement here, a DTS there. Etc.
> As I said, I tested every single one of these applications and DTS
> packages
> by loading the data across a 100 MB LAN to a really old under-powered Test
> Server here in the office. They all worked fairly well at that point.
>> tuned all my indexes and queries and stored
>> procedures by hand,
>> What does this have to do with your slow data load? In fact, if you've
>> made
>> significant changes to indexes on the affected table(s), this might be
>> part
>> of the source of your slow load. The fastest bulk load will be into a
>> heap
>> (no index), and next will probably be with a clustered index and no ncs
>> (assuming the data in the file is ordered the same as the clustered
>> index).
> What does this have to do with my slow data load? Simple: Too many
> indexes
> just might tend to slow an insert operation down, right? I suppose it's
> similar to the reason I told you about running Best Practices Analyzer and
> all those other little things. So we could eliminate all the obvious
> questions up front, like "how are your indexes set up, yadda, yadda." To
> answer your question, I've eliminated all indexes except the clustered
> indexes. And the clustered indexes are very narrow. Most are one or two
> integer columns. Nothing special there. Absolutely no non-clustered
> indexes.
> So what does my choice of load tool have to do with the server barely
> pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
> answered.
>> From my understanding disk queue lengths in appear high. They are
>> averaging
>> about 6 - 8 per spindle with very high, and common, spikes much, much,
>> much
>> higher.
>> All the drives are RAID 1? Are your logs, tempdb, and data files all on
>> the
>> same drive letter? Is any of this on the local machine and not on the
>> SAN?
>> Have you looked into where all the I/O is going among those three places?
> All drives are RAID 1. I advised the IT guy to put the tempdb on its own
> dedicated drive, and put the logs on their own dedicated drive. He said
> that it was not possible, so what's happened is that he set up 160 GB
> logical volumes (see previous post), and I have assigned one logical
> volume
> for logs, one for tempdb, and the database tables are split up into
> filegroups, with each filegroup having its own logical drive. The IT guy
> says that the EMC SAN box automatically stripes among the physical drives.
> Have I looked at where all the I/O is going among those three places?
> I've
> looked at the physical filesizes, and I know which files are getting
> larger;
> however, I have no way of knowing physically where anything is happening
> on
> the SAN box. According to the vendor, the controllers automatically
> distribute the data on the logical volumes among the physical drives. So
> the drive J: could be physically spread around 10 physical disks for all I
> know. So how do you narrow down where all the I/O is going among the
> logs,
> tempdb and data files when they're spread all over the place? The vendor
> has also said that they are not seeing large amounts of activity on the
> "gigabit" fiber either, which - if it's true - would indicate that there's
> a
> problem between SQL Server or Windows and the fiber. At least that's what
> I
> would guess. But what do I know? Heck, I thought this wild and crazy
> little joke utility would give me information I could use to address the
> problem, but I was wrong there also.
> And oh yeah, I went to grab the "Best Practices for SQL Server on SAN"
> whitepaper from the previously posted Microsoft link in this thread. It
> looked promising. But, alas, it has disappeared off the face of the
> earth.
> Thanks
>|||Cool. Say hello to Fang for me. We have a common association, David
Williams at DeMarche Associates, I use to work Fang's old job after he
started at NovaStar and before I went to Commerce.
Here you're doing big things there. How's the new corporate digs?
Bets wishes,
Anthony Thomas
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:ux$kbi2KFHA.732@.TK2MSFTNGP12.phx.gbl...
I work for NovaStar.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uhMxUWWKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> Where abouts in KC? I work for Commerce Bank.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:%23xwdT4QKFHA.2996@.TK2MSFTNGP10.phx.gbl...
> I like EMC, Hitachi, and IBM. I would stay away from HP with all the
issues
> they're having right now. Make sure whatever storage you choose has a
> service location and parts depot close to you. My company is in Kansas
> City. The closest Hitachi service center and parts depot is 3 hours away
in
> St. Louis, so it makes more sense for us to go with EMC which is local.
> IBM costs more, but they have great service. Hitachi gives you a lot of
the
> software that EMC charges you for; however, EMC has more to offer in
overall
> features. Hitachi tends to cost less than EMC. You really don't have to
> worry about buying your storage solution from IBM because the servers are
> IBM. All the major storage/server vendors have relationships with each
> other to alleviate this. Again, stay away from HP. Their service and
> turnaround times for the enterprise platforms have been horrible lately.
> Dell is EMC rebranded in case you decide to check them out. The hardware
is
> the same, but they have their own service staff, support etc. Depending
on
> your location, they sometimes do a better job than the actual EMC people.
> So, that's something to think about also.
>
>
> "Larry David" <invalid@.bogus.bum> wrote in message
> news:M4qdnaIn9c-gzKvfRVn-2w@.giganews.com...
> > >
> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> > >
> > > This site is full of information. Make sure you scroll through it and
> > read
> > > all of the subtitles.
> >
> > Anthony,
> >
> > That's a great link. I appreciate the tip. I'll check out those
> > articles.
> >
> > Aaron,
> >
> > I'll check out EMC. I was thinking that since we are buying our
> servers
> > from IBM, it would be a natural decision to buy the storage from them as
> > well... but after reading your post, I now see that EMC has an excellent
> > reputation. Perhaps they have a solution that's more suitable. Are there
> any
> > other vendors that you think I should investigate? Any thoughts on
Hitachi
> > Data Systems (hds.com)?
> >
> > David
> >
> >
>|||Yes, it's a heckuva thread, and I appreciate all you guys' help!
I'm going down to the hosting facility today myself. I'll double-check the
QLogic BIOS/drivers today. I think they've configured it for Loop
Preferred, Point to Point, but I can double-check that also.
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:%23mWk1z2KFHA.1280@.TK2MSFTNGP09.phx.gbl...
> This has become quite the thread. :) I tried to read the whole thing.
> Are
> you still having trouble Michael? Here are some thing to check for if you
> are (from what I've read on the thread):
> 1. Make sure the QLogic cards have the latest EMC approved BIOS and
> drivers.
> --You can find these at the following site:
> http://www.qlogic.com/support/oem_product_list.asp?oemid=65
> --I would assume you have the QLA2342-E cards from what you have told me.
I think you're right, but I'll check on this today. Getting info from these
guys is like pulling teeth sometimes.
> 2. Download SANSurfer SANblade Manager software also. Make sure your HBA
> cards are configure to either:
> --Loop Preferred, Point to Point or Point to Point (I'm not sure which
> one
> Symmetrix uses, but it's not Loop Preferred)
> 3. Make sure your drivers are up-to-date. You can find the latest
> version
> at the site I gave you.
>
Will do this today.
> 4. Have your SAN engineer (if he calls himself that) verify that pathing
> is
> active/active and all the ports are showing up.
> --This sounds stupid. We had one of our people redo the pathing on the
> servers. Some of the lights weren't even on.
Really? I'll definitely check this.
> 5. It's fairly easy to setup LUNs with dedicated drives on the SAN.
> --You create the RAID array as either RAID 10 or RAID 5. (array on the
> SAN
> is the RAID of the physical disks)
> --You then create a LUN from the RAID array (BIND LUN) and select the
> maximum size in the little dropdown box.
> --I've done this waaaaaaaaaay too many times.
> --You then add that LUN to the hosts storage group.
I'll point this out to them. I'm thinking we're going to have to re-do this
part.
> Also, I don't believe the Symmetrix even has 80gb drives. They are
> probably
> 73gb 10k or 15k drives. If you're only getting 5mb/sec on IOMeter, then
> your performance reallys sucks. Have you tried increasing the workers to
> see if you just didn't throw enough at it? When you were running this,
> what
> were your queue lengths looking like?
Yes, you're right, 73GB 10k drives.
> Here is just a wild guess on my part. You might be using MetaLUNs.
> Basically, you take all those 160gb RAID 1 arrays you have and either
> stripe
> across multiple parts of them using MetaLUNs or even worse concatenate
> MetaLUNs. Do you have any individual drive letters (LUNs that are more
> than
> 160gb on these servers? If you do, then that's what you're doing if the
> SAN
> people told you the truth. Find out what the level of flare is on the
> Symmetrix. Depending on where you're at, this can have a really bad
> effect
> on performance. Have you seen any errors in the eventviewer during the
> time
> you're loading the files (just out of curiousity).
No drive letters are over 160 GB, based on the vendor's recommendations. No
errors in EventViewer. Someone here did mention "MetaLUNs" the other day.
Of course, I had no idea what they were talking about. Should we not have
MetaLUNs?
> In case you haven't noticed, it's extremely hard to troubleshoot these
> kinds
> of things without actually being there.
>
Tell me about it. It's almost as hard to troubleshoot from here when the
guy who's supposed to know what's going on won't give up the info, except in
very tiny pieces...
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:ut#tNVzKFHA.3640@.TK2MSFTNGP12.phx.gbl...
>> I sent you my email address. I get way too much junk mail as is, so I
> don't
>> use my e-mail on posts.
>> Thanks
>> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
>> news:Oo0yKlvKFHA.2640@.TK2MSFTNGP09.phx.gbl...
>> > Looks like that file will not post to the newsgroup and your return
> email
>> > is
>> > invalid. I have it if you are interested but I need some place to
> deliver
>> > it.
>> >
>> > Sincerely,
>> >
>> >
>> > Anthony Thomas
>> >
>> >
>> > --
>> >
>> > "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
>> > news:OTkK7fvKFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> > On the link that I sent you:
>> >
>> > Scalability and Very Large Database (VLDB) Resources
>> >
> http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
>> >
>> > Under the Planning section, there are several white papers describing
>> > Server
>> > Consolidation. They go into great detail on how to layout the physical
>> > LUNs
>> > within the SAN to properly host a DBMS. Contrary to what your IT
>> > person
>> > says, you have to dedicate areas of the SAN to satisfy separate
> purposes:
>> > data file (random access), log file (serial access), and tempdb
>> > (separation). This is accomplished by the use of METAVOLUMES.
>> >
>> > It sounds like EMC constructed the normal strip volume sets between two
>> > drives and then created volume groups up to 160 GB.
>> >
>> > As far as the EMC/Clariion link, I'm not sure what EMC has done with
> that
>> > but I'm including it here for you to check out. If it doesn't come
>> > through,
>> > let me know and I'll send it to you directly.
>> >
>> > Also, check out your Disk Avg. sec/Read and Avg. sec/Write performance
>> > monitor counters. If these are any higher than 10 - 20 ms, ever, then
> you
>> > have SAN disk latency and the SAN is laid out incorrectly.
>> >
>> > Sincerely,
>> >
>> >
>> > Anthony Thomas
>> >
>> >
>> >
>> >
>> > --
>> >
>> > "Michael C#" <xyz@.yomomma.com> wrote in message
>> > news:OT0wEnbKFHA.2764@.tk2msftngp13.phx.gbl...
>> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> > news:eSfopPbKFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> >> hardware. Right now there is only one user (me) using the SQL
>> >> Server.
>> >> I'm
>> >> basically just loading data into the database.
>> >>
>> >> What do you mean by "basically"? Are you using BCP, BULK INSERT, DTS,
>> >> ...?
>> >>
>> >
>> > By basically, I mean the only activity occurring on the SQL Server
>> > right
>> > now, at all, is that it is currently being populated with data from
>> > over
>> > 500
>> > different data sources. Since each data source has it's own format and
>> > level of complexity, I'm using DTS for some, and custom C#.NET and
> VB.NET
>> > applications + Stored Procedures for others. I'm basically just
>> > loading
>> > data into the database from flat files. Nothing exotic, intriguing or
>> > special, just tedious. An INSERT statement here, a DTS there. Etc.
>> >
>> > As I said, I tested every single one of these applications and DTS
>> > packages
>> > by loading the data across a 100 MB LAN to a really old under-powered
> Test
>> > Server here in the office. They all worked fairly well at that point.
>> >
>> >> tuned all my indexes and queries and stored
>> >> procedures by hand,
>> >>
>> >> What does this have to do with your slow data load? In fact, if
>> >> you've
>> >> made
>> >> significant changes to indexes on the affected table(s), this might be
>> >> part
>> >> of the source of your slow load. The fastest bulk load will be into a
>> >> heap
>> >> (no index), and next will probably be with a clustered index and no
>> >> ncs
>> >> (assuming the data in the file is ordered the same as the clustered
>> >> index).
>> >>
>> >
>> > What does this have to do with my slow data load? Simple: Too many
>> > indexes
>> > just might tend to slow an insert operation down, right? I suppose
>> > it's
>> > similar to the reason I told you about running Best Practices Analyzer
> and
>> > all those other little things. So we could eliminate all the obvious
>> > questions up front, like "how are your indexes set up, yadda, yadda."
> To
>> > answer your question, I've eliminated all indexes except the clustered
>> > indexes. And the clustered indexes are very narrow. Most are one or
> two
>> > integer columns. Nothing special there. Absolutely no non-clustered
>> > indexes.
>> >
>> > So what does my choice of load tool have to do with the server barely
>> > pushing 5 MB/s over "gigabit" fiber? I have no idea, but you asked, I
>> > answered.
>> >
>> >> From my understanding disk queue lengths in appear high. They are
>> >> averaging
>> >> about 6 - 8 per spindle with very high, and common, spikes much,
>> >> much,
>> >> much
>> >> higher.
>> >>
>> >> All the drives are RAID 1? Are your logs, tempdb, and data files all
> on
>> >> the
>> >> same drive letter? Is any of this on the local machine and not on the
>> >> SAN?
>> >> Have you looked into where all the I/O is going among those three
> places?
>> >>
>> >
>> > All drives are RAID 1. I advised the IT guy to put the tempdb on its
> own
>> > dedicated drive, and put the logs on their own dedicated drive. He
>> > said
>> > that it was not possible, so what's happened is that he set up 160 GB
>> > logical volumes (see previous post), and I have assigned one logical
>> > volume
>> > for logs, one for tempdb, and the database tables are split up into
>> > filegroups, with each filegroup having its own logical drive. The IT
> guy
>> > says that the EMC SAN box automatically stripes among the physical
> drives.
>> >
>> > Have I looked at where all the I/O is going among those three places?
>> > I've
>> > looked at the physical filesizes, and I know which files are getting
>> > larger;
>> > however, I have no way of knowing physically where anything is
>> > happening
>> > on
>> > the SAN box. According to the vendor, the controllers automatically
>> > distribute the data on the logical volumes among the physical drives.
> So
>> > the drive J: could be physically spread around 10 physical disks for
>> > all
> I
>> > know. So how do you narrow down where all the I/O is going among the
>> > logs,
>> > tempdb and data files when they're spread all over the place? The
> vendor
>> > has also said that they are not seeing large amounts of activity on the
>> > "gigabit" fiber either, which - if it's true - would indicate that
> there's
>> > a
>> > problem between SQL Server or Windows and the fiber. At least that's
> what
>> > I
>> > would guess. But what do I know? Heck, I thought this wild and crazy
>> > little joke utility would give me information I could use to address
>> > the
>> > problem, but I was wrong there also.
>> >
>> > And oh yeah, I went to grab the "Best Practices for SQL Server on SAN"
>> > whitepaper from the previously posted Microsoft link in this thread.
>> > It
>> > looked promising. But, alas, it has disappeared off the face of the
>> > earth.
>> >
>> > Thanks
>> >
>> >
>>
>|||Thanks for the excellent post. I appreciate the detailed info!! This helps
a lot!|||Pretty good. :) We're on the fifth floor. When I got here, we didn't even
have 2 and 3 full. I'll tell Fang you said Hi.
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:#HWzIP5KFHA.2596@.TK2MSFTNGP10.phx.gbl...
> Cool. Say hello to Fang for me. We have a common association, David
> Williams at DeMarche Associates, I use to work Fang's old job after he
> started at NovaStar and before I went to Commerce.
> Here you're doing big things there. How's the new corporate digs?
> Bets wishes,
> Anthony Thomas
>
> --
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:ux$kbi2KFHA.732@.TK2MSFTNGP12.phx.gbl...
> I work for NovaStar.
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:uhMxUWWKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> > Where abouts in KC? I work for Commerce Bank.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> > news:%23xwdT4QKFHA.2996@.TK2MSFTNGP10.phx.gbl...
> > I like EMC, Hitachi, and IBM. I would stay away from HP with all the
> issues
> > they're having right now. Make sure whatever storage you choose has a
> > service location and parts depot close to you. My company is in Kansas
> > City. The closest Hitachi service center and parts depot is 3 hours
away
> in
> > St. Louis, so it makes more sense for us to go with EMC which is local.
> >
> > IBM costs more, but they have great service. Hitachi gives you a lot of
> the
> > software that EMC charges you for; however, EMC has more to offer in
> overall
> > features. Hitachi tends to cost less than EMC. You really don't have
to
> > worry about buying your storage solution from IBM because the servers
are
> > IBM. All the major storage/server vendors have relationships with each
> > other to alleviate this. Again, stay away from HP. Their service and
> > turnaround times for the enterprise platforms have been horrible lately.
> >
> > Dell is EMC rebranded in case you decide to check them out. The
hardware
> is
> > the same, but they have their own service staff, support etc. Depending
> on
> > your location, they sometimes do a better job than the actual EMC
people.
> > So, that's something to think about also.
> >
> >
> >
> >
> > "Larry David" <invalid@.bogus.bum> wrote in message
> > news:M4qdnaIn9c-gzKvfRVn-2w@.giganews.com...
> > > >
> >
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
> > > >
> > > > This site is full of information. Make sure you scroll through it
and
> > > read
> > > > all of the subtitles.
> > >
> > > Anthony,
> > >
> > > That's a great link. I appreciate the tip. I'll check out those
> > > articles.
> > >
> > > Aaron,
> > >
> > > I'll check out EMC. I was thinking that since we are buying our
> > servers
> > > from IBM, it would be a natural decision to buy the storage from them
as
> > > well... but after reading your post, I now see that EMC has an
excellent
> > > reputation. Perhaps they have a solution that's more suitable. Are
there
> > any
> > > other vendors that you think I should investigate? Any thoughts on
> Hitachi
> > > Data Systems (hds.com)?
> > >
> > > David
> > >
> > >
> >
> >
>|||Michael,
If you can, download and install a trial offer of Quest's SiteScope for
SQLServer. (www.quest.com) While your performance issues are happening,
monitor SQL IO. The graphs will show weather you have an issue at the
physical disk level. This will give you a graphical insite into what's
happening, realtime, instead of JBD (just-a-bunch-of-data). If the graphs
don't help much, you could end up with JFG (just-a-flashy-graph) 8-)
-Rob.
"Michael C#" wrote:
> It's Gig fiber. Hmm as for the topology, I'll have to get all that info
> from the IT guy. I'll be the first to admit I don't know a darn thing about
> the SAN (so please bear with me...), other than it's EMC and right now it
> seems slow as heck :(
> Right now all I've gleaned is that we're running like 40 Q-Logic LUNs (?),
> although the EMC automatically makes it RAID 1+0, over 12 controllers.
> We've configured the drives as several 160 GB RAID 1 drives per EMC's
> recommendation.
> I've run tons of tests, including SQLIO and the results are uggglllyyy. I
> think I've eliminated SQL Server as the problem, and the vendor says that
> the SAN is not the problem... Which leads me to believe that the problem is
> somewhere between Windows 2003 and the SAN.
> I'll get all my info. together and get back to you. In the meantime, do you
> happen to know any hints or tips, or things I should look for, in the
> Windows Server 2003 settings that might help?
> Thanks
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE5BC6AC.33E6%ten.xoc@.dnartreb.noraa...
> > Can you show the topology? What kind of switches are you running through?
> > Is it all gig fiber or is some of it bottlenecked by 100 or (gasp) 10/100?
> >
> > We have observed exemplary performance at the read/write level on the SAN
> > since the day they were configured. The SAN itself should never be your
> > bottleneck in my experience, but if your app is hitting a network
> > throttling
> > issue on the way to or from, it could look like slow performance from the
> > SAN. The only servers we noticed issues with early on were the ones that
> > hasn't been upgraded to fiber... they were still running 100 and my gut
> > feeling is that the SAN was sitting there waiting for the data.
> >
> > So, long story short, if you're not on fiber through and through, go
> > shopping. :-)
> >
> > And of course, if you have more servers in the works that are going to
> > make
> > use of the SAN, make sure they come equipped with gb cards.
> >
> >
> >
> >
> > On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga, "Michael
> > C#"
> > <xyz@.abcdef.com> wrote:
> >
> >>
> >> We use EMC SAN devices. Pricy but well worth it.
> >>
> >>
> >> Can I ask you a few questions about EMC SAN configuration? Our EMC SAN
> >> was
> >> recently set up and configured, but we're not getting nearly the
> >> throughput
> >> we expected.
> >>
> >>
> >
>
>|||Do you know if they've changed the name? They have a lot of tools up there,
but none named "SiteScope". Maybe it's "Spotlight for SQL"?
Thanks, appreciate the help.
"Rob's SQL" <Rob's SQL@.discussions.microsoft.com> wrote in message
news:0282D3B3-B725-424D-BF89-1153CCB69F88@.microsoft.com...
> Michael,
> If you can, download and install a trial offer of Quest's SiteScope for
> SQLServer. (www.quest.com) While your performance issues are happening,
> monitor SQL IO. The graphs will show weather you have an issue at the
> physical disk level. This will give you a graphical insite into what's
> happening, realtime, instead of JBD (just-a-bunch-of-data). If the graphs
> don't help much, you could end up with JFG (just-a-flashy-graph) 8-)
> -Rob.
> "Michael C#" wrote:
>> It's Gig fiber. Hmm as for the topology, I'll have to get all that info
>> from the IT guy. I'll be the first to admit I don't know a darn thing
>> about
>> the SAN (so please bear with me...), other than it's EMC and right now it
>> seems slow as heck :(
>> Right now all I've gleaned is that we're running like 40 Q-Logic LUNs
>> (?),
>> although the EMC automatically makes it RAID 1+0, over 12 controllers.
>> We've configured the drives as several 160 GB RAID 1 drives per EMC's
>> recommendation.
>> I've run tons of tests, including SQLIO and the results are uggglllyyy.
>> I
>> think I've eliminated SQL Server as the problem, and the vendor says that
>> the SAN is not the problem... Which leads me to believe that the problem
>> is
>> somewhere between Windows 2003 and the SAN.
>> I'll get all my info. together and get back to you. In the meantime, do
>> you
>> happen to know any hints or tips, or things I should look for, in the
>> Windows Server 2003 settings that might help?
>> Thanks
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:BE5BC6AC.33E6%ten.xoc@.dnartreb.noraa...
>> > Can you show the topology? What kind of switches are you running
>> > through?
>> > Is it all gig fiber or is some of it bottlenecked by 100 or (gasp)
>> > 10/100?
>> >
>> > We have observed exemplary performance at the read/write level on the
>> > SAN
>> > since the day they were configured. The SAN itself should never be
>> > your
>> > bottleneck in my experience, but if your app is hitting a network
>> > throttling
>> > issue on the way to or from, it could look like slow performance from
>> > the
>> > SAN. The only servers we noticed issues with early on were the ones
>> > that
>> > hasn't been upgraded to fiber... they were still running 100 and my gut
>> > feeling is that the SAN was sitting there waiting for the data.
>> >
>> > So, long story short, if you're not on fiber through and through, go
>> > shopping. :-)
>> >
>> > And of course, if you have more servers in the works that are going to
>> > make
>> > use of the SAN, make sure they come equipped with gb cards.
>> >
>> >
>> >
>> >
>> > On 3/14/05 10:43 PM, in article tXsZd.22624$Rc7.21810@.fe09.lga,
>> > "Michael
>> > C#"
>> > <xyz@.abcdef.com> wrote:
>> >
>> >>
>> >> We use EMC SAN devices. Pricy but well worth it.
>> >>
>> >>
>> >> Can I ask you a few questions about EMC SAN configuration? Our EMC
>> >> SAN
>> >> was
>> >> recently set up and configured, but we're not getting nearly the
>> >> throughput
>> >> we expected.
>> >>
>> >>
>> >
>>|||Yes, SiteScope is a different product / company. Useful tool, but not quite
the same thing as Quest's stuff.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uQhrM78MFHA.432@.TK2MSFTNGP10.phx.gbl...
> Do you know if they've changed the name? They have a lot of tools up
there,
> but none named "SiteScope". Maybe it's "Spotlight for SQL"?|||Hey Aaron,
We've backed up our databases and moved them off to temporary servers, and I
think I've finally convinced them that we just have to bite the bullet and
re-build/re-configure the SAN box. I've informed them that we need to split
up the physical disks into separate groupings (bear with me, I'm still short
on SAN lingo here). I have a question - we downloaded the GUI SAN
management tool from QLogic, and it indicates that a controller can be
"bound" to a set of disks on the SAN. Is this something that we should do,
or are there any guidelines for when to "bind" the controllers?
Thanks again
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23KcZFP$MFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Yes, SiteScope is a different product / company. Useful tool, but not
> quite
> the same thing as Quest's stuff.
>
>
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:uQhrM78MFHA.432@.TK2MSFTNGP10.phx.gbl...
>> Do you know if they've changed the name? They have a lot of tools up
> there,
>> but none named "SiteScope". Maybe it's "Spotlight for SQL"?
>