disk I/O tool

There is also SQLIO and IOMeter.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Jay" wrote in message

> SQLIOsim, which the spell checker wants to change to Solipsism
>
> "Jay" wrote in message
>
>> What is the name of that disk I/O tool?
>>
>
>
 >> Stay informed about: disk I/O tool 

LOL

"Jay" wrote in message

> SQLIOsim, which the spell checker wants to change to Solipsism
>
> "Jay" wrote in message
>
>> What is the name of that disk I/O tool?
>>
>
>
 >> Stay informed about: disk I/O tool 

It depends on what you want to accomplish as there are several. Can you tell
us what your goal is?

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Jay" wrote in message
news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
> SQLIOsim, which the spell checker wants to change to Solipsism
>
> "Jay" wrote in message
>
>> What is the name of that disk I/O tool?
>>
>
>
 >> Stay informed about: disk I/O tool 

SQLIO++ will do.

I'm in a discussion where a guy is saying msdb is so busy, that local SCSI
drives don't have enough throughput and it has to be on the SAN (which is
RAID 5 BTW). I was suggesting to him to run SQLIOsim to see what the
monitors look like when the drive is hammered and to compare it to what he
sees in his production environment.

Jay

"Andrew J. Kelly" wrote in message

> It depends on what you want to accomplish as there are several. Can you
> tell us what your goal is?
>
> --
>
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" wrote in message
> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>> SQLIOsim, which the spell checker wants to change to Solipsism
>>
>> "Jay" wrote in message
>>
>>> What is the name of that disk I/O tool?
>>>
>>
>>
 >> Stay informed about: disk I/O tool 

Why would MSDB be that busy? If it really is you might want to see why
because it shouldn't be under normal circumstances. SQLIOSIM simulates SQL
IO patterns but is not really a measurement tool per say. It is more to
hammer the array and see where it breaks. If you want to see how it actually
performs in general reads & writes use SQLIO.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Jay" wrote in message

> SQLIO++ will do.
>
> I'm in a discussion where a guy is saying msdb is so busy, that local SCSI
> drives don't have enough throughput and it has to be on the SAN (which is
> RAID 5 BTW). I was suggesting to him to run SQLIOsim to see what the
> monitors look like when the drive is hammered and to compare it to what he
> sees in his production environment.
>
> Jay
>
> "Andrew J. Kelly" wrote in message
>
>> It depends on what you want to accomplish as there are several. Can you
>> tell us what your goal is?
>>
>> --
>>
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" wrote in message
>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>
>>> "Jay" wrote in message
>>>
>>>> What is the name of that disk I/O tool?
>>>>
>>>
>>>
>
>
 >> Stay informed about: disk I/O tool 

Sigh, that has basically been my argument. However, the guy has stuck to his
guns that his msdb is so active that the local drives aren't enough and that
it must be on the SAN for performance reasons.

The best I could think of was to stress a similar drive (and monitor it) to
show what off the shelf SCSI's can do on an internal controller and that
msdb activity just doesn't come close to the drive capacity. Perhaps SQLIO,
being simpler, would be better.

His DB seems to have a very large number of databases and each of these DB's
seem to have a lot of SQL Agent jobs that fire frequently. This combined
with heavy Message Broker activity is supposedly generating log writes,
reads and I have no clue what, to produce the "heavy activity" that requires
the SAN - which is RAID 5 BTW.


"Andrew J. Kelly" wrote in message

> Why would MSDB be that busy? If it really is you might want to see why
> because it shouldn't be under normal circumstances. SQLIOSIM simulates
> SQL IO patterns but is not really a measurement tool per say. It is more
> to hammer the array and see where it breaks. If you want to see how it
> actually performs in general reads & writes use SQLIO.
>
> --
>
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" wrote in message
>
>> SQLIO++ will do.
>>
>> I'm in a discussion where a guy is saying msdb is so busy, that local
>> SCSI drives don't have enough throughput and it has to be on the SAN
>> (which is RAID 5 BTW). I was suggesting to him to run SQLIOsim to see
>> what the monitors look like when the drive is hammered and to compare it
>> to what he sees in his production environment.
>>
>> Jay
>>
>> "Andrew J. Kelly" wrote in message
>>
>>> It depends on what you want to accomplish as there are several. Can you
>>> tell us what your goal is?
>>>
>>> --
>>>
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>> "Jay" wrote in message
>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>
>>>> "Jay" wrote in message
>>>>
>>>>> What is the name of that disk I/O tool?
>>>>>
>>>>
>>>>
>>
>>
 >> Stay informed about: disk I/O tool 

Your best option to begin with is to look at the virtual file stats to see
how much physical I/O msdb is actually doing over a given time period. It
will also tell you how long it is taking to read and write those I/O's and
you can see if it is too long or not. I guess he really doesn't understand
SAN's very well either because SAN's are notoriously slow for writing small
but frequent I/O's and direct attached drives will often outperform them
hands down. If you are processing lots of Service Broker messages then that
can explain some of the activity. But until you look at the file stats it is
hard to say if it is handling it properly or not.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Jay" wrote in message
news:#iwZMWxiKHA.1648@TK2MSFTNGP05.phx.gbl...
> Sigh, that has basically been my argument. However, the guy has stuck to
> his guns that his msdb is so active that the local drives aren't enough
> and that it must be on the SAN for performance reasons.
>
> The best I could think of was to stress a similar drive (and monitor it)
> to show what off the shelf SCSI's can do on an internal controller and
> that msdb activity just doesn't come close to the drive capacity. Perhaps
> SQLIO, being simpler, would be better.
>
> His DB seems to have a very large number of databases and each of these
> DB's seem to have a lot of SQL Agent jobs that fire frequently. This
> combined with heavy Message Broker activity is supposedly generating log
> writes, reads and I have no clue what, to produce the "heavy activity"
> that requires the SAN - which is RAID 5 BTW.
>
>
> "Andrew J. Kelly" wrote in message
>
>> Why would MSDB be that busy? If it really is you might want to see why
>> because it shouldn't be under normal circumstances. SQLIOSIM simulates
>> SQL IO patterns but is not really a measurement tool per say. It is more
>> to hammer the array and see where it breaks. If you want to see how it
>> actually performs in general reads & writes use SQLIO.
>>
>> --
>>
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" wrote in message
>>
>>> SQLIO++ will do.
>>>
>>> I'm in a discussion where a guy is saying msdb is so busy, that local
>>> SCSI drives don't have enough throughput and it has to be on the SAN
>>> (which is RAID 5 BTW). I was suggesting to him to run SQLIOsim to see
>>> what the monitors look like when the drive is hammered and to compare it
>>> to what he sees in his production environment.
>>>
>>> Jay
>>>
>>> "Andrew J. Kelly" wrote in message
>>>
>>>> It depends on what you want to accomplish as there are several. Can you
>>>> tell us what your goal is?
>>>>
>>>> --
>>>>
>>>> Andrew J. Kelly SQL MVP
>>>> Solid Quality Mentors
>>>>
>>>> "Jay" wrote in message
>>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>>
>>>>> "Jay" wrote in message
>>>>>
>>>>>> What is the name of that disk I/O tool?
>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>
 >> Stay informed about: disk I/O tool 

Do you know of a Microsoft whitepaper that talks about SAN's being slow on
small & frequent writes?

"Andrew J. Kelly" wrote in message

> Your best option to begin with is to look at the virtual file stats to see
> how much physical I/O msdb is actually doing over a given time period. It
> will also tell you how long it is taking to read and write those I/O's and
> you can see if it is too long or not. I guess he really doesn't understand
> SAN's very well either because SAN's are notoriously slow for writing
> small but frequent I/O's and direct attached drives will often outperform
> them hands down. If you are processing lots of Service Broker messages
> then that can explain some of the activity. But until you look at the file
> stats it is hard to say if it is handling it properly or not.
>
> --
>
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" wrote in message
> news:#iwZMWxiKHA.1648@TK2MSFTNGP05.phx.gbl...
>> Sigh, that has basically been my argument. However, the guy has stuck to
>> his guns that his msdb is so active that the local drives aren't enough
>> and that it must be on the SAN for performance reasons.
>>
>> The best I could think of was to stress a similar drive (and monitor it)
>> to show what off the shelf SCSI's can do on an internal controller and
>> that msdb activity just doesn't come close to the drive capacity. Perhaps
>> SQLIO, being simpler, would be better.
>>
>> His DB seems to have a very large number of databases and each of these
>> DB's seem to have a lot of SQL Agent jobs that fire frequently. This
>> combined with heavy Message Broker activity is supposedly generating log
>> writes, reads and I have no clue what, to produce the "heavy activity"
>> that requires the SAN - which is RAID 5 BTW.
>>
>>
>> "Andrew J. Kelly" wrote in message
>>
>>> Why would MSDB be that busy? If it really is you might want to see why
>>> because it shouldn't be under normal circumstances. SQLIOSIM simulates
>>> SQL IO patterns but is not really a measurement tool per say. It is more
>>> to hammer the array and see where it breaks. If you want to see how it
>>> actually performs in general reads & writes use SQLIO.
>>>
>>> --
>>>
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>> "Jay" wrote in message
>>>
>>>> SQLIO++ will do.
>>>>
>>>> I'm in a discussion where a guy is saying msdb is so busy, that local
>>>> SCSI drives don't have enough throughput and it has to be on the SAN
>>>> (which is RAID 5 BTW). I was suggesting to him to run SQLIOsim to see
>>>> what the monitors look like when the drive is hammered and to compare
>>>> it to what he sees in his production environment.
>>>>
>>>> Jay
>>>>
>>>> "Andrew J. Kelly" wrote in message
>>>>
>>>>> It depends on what you want to accomplish as there are several. Can
>>>>> you tell us what your goal is?
>>>>>
>>>>> --
>>>>>
>>>>> Andrew J. Kelly SQL MVP
>>>>> Solid Quality Mentors
>>>>>
>>>>> "Jay" wrote in message
>>>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>>>
>>>>>> "Jay" wrote in message
>>>>>>
>>>>>>> What is the name of that disk I/O tool?
>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
 >> Stay informed about: disk I/O tool 

I don't think you will find an MS whitepaper on something like that. For one
there are too many variables and performance depends greatly on
configuration and load type. But there is no question that disk for disk a
SAN will never beat the performance of direct attached storage. The
advantages of the SAN are that it is more flexible and scalable in terms of
number of spindles and such. But the biggest down side is that it is
overrated and usually shared with other heavy loads. You might want to have
a look at these:

http://sqlblogcasts.com/search/SearchResults.aspx?q=san+performance

But don't underestimate the virtual file stats and what that can give you.
It will tell you if you are waiting or not.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Jay" wrote in message
news:#h52Hq6iKHA.2160@TK2MSFTNGP02.phx.gbl...
> Do you know of a Microsoft whitepaper that talks about SAN's being slow on
> small & frequent writes?
>
> "Andrew J. Kelly" wrote in message
>
>> Your best option to begin with is to look at the virtual file stats to
>> see how much physical I/O msdb is actually doing over a given time
>> period. It will also tell you how long it is taking to read and write
>> those I/O's and you can see if it is too long or not. I guess he really
>> doesn't understand SAN's very well either because SAN's are notoriously
>> slow for writing small but frequent I/O's and direct attached drives will
>> often outperform them hands down. If you are processing lots of Service
>> Broker messages then that can explain some of the activity. But until you
>> look at the file stats it is hard to say if it is handling it properly or
>> not.
>>
>> --
>>
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" wrote in message
>> news:#iwZMWxiKHA.1648@TK2MSFTNGP05.phx.gbl...
>>> Sigh, that has basically been my argument. However, the guy has stuck to
>>> his guns that his msdb is so active that the local drives aren't enough
>>> and that it must be on the SAN for performance reasons.
>>>
>>> The best I could think of was to stress a similar drive (and monitor it)
>>> to show what off the shelf SCSI's can do on an internal controller and
>>> that msdb activity just doesn't come close to the drive capacity.
>>> Perhaps SQLIO, being simpler, would be better.
>>>
>>> His DB seems to have a very large number of databases and each of these
>>> DB's seem to have a lot of SQL Agent jobs that fire frequently. This
>>> combined with heavy Message Broker activity is supposedly generating log
>>> writes, reads and I have no clue what, to produce the "heavy activity"
>>> that requires the SAN - which is RAID 5 BTW.
>>>
>>>
>>> "Andrew J. Kelly" wrote in message
>>>
>>>> Why would MSDB be that busy? If it really is you might want to see why
>>>> because it shouldn't be under normal circumstances. SQLIOSIM simulates
>>>> SQL IO patterns but is not really a measurement tool per say. It is
>>>> more to hammer the array and see where it breaks. If you want to see
>>>> how it actually performs in general reads & writes use SQLIO.
>>>>
>>>> --
>>>>
>>>> Andrew J. Kelly SQL MVP
>>>> Solid Quality Mentors
>>>>
>>>> "Jay" wrote in message
>>>>
>>>>> SQLIO++ will do.
>>>>>
>>>>> I'm in a discussion where a guy is saying msdb is so busy, that local
>>>>> SCSI drives don't have enough throughput and it has to be on the SAN
>>>>> (which is RAID 5 BTW). I was suggesting to him to run SQLIOsim to see
>>>>> what the monitors look like when the drive is hammered and to compare
>>>>> it to what he sees in his production environment.
>>>>>
>>>>> Jay
>>>>>
>>>>> "Andrew J. Kelly" wrote in message
>>>>>
>>>>>> It depends on what you want to accomplish as there are several. Can
>>>>>> you tell us what your goal is?
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Andrew J. Kelly SQL MVP
>>>>>> Solid Quality Mentors
>>>>>>
>>>>>> "Jay" wrote in message
>>>>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>>>>
>>>>>>> "Jay" wrote in message
>>>>>>>
>>>>>>>> What is the name of that disk I/O tool?
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>
 >> Stay informed about: disk I/O tool 

Andy is spot on here, as usual. Most companies I have been to have
completely butchered their SAN implementations AND oversubscribe them. Then
they bitch about SQL Server being slow and call it unscalable!

You can also check out avg disk sec/read and /write in the physical disk
perfmon counter set.

I would also check the growth rate for the msdb database. It could still be
set at the 1MB default and you could have a kajillion tiny fragments spread
all over the physical disk. I would check for OS file fragmentation
anyway - severe file fragmentation (which I have seen at numerous clients)
can have a dramatic effect on IO performance.

Didn't catch all this thread, but I would check for queries that are hitting
MSDB and see if some indexes might help - or data archival. Job and backup
histories can chew up a lot of space and then even index seeks/scans can get
slow due to additional btree depths. I also know that the backup/restore
tables are missing a bunch of indexes that are very helpful for things like
history pruning, etc. Geoff Hiten has a script online to create these
missing indexes (although he is missing a few that should likely be added
in).


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Andrew J. Kelly" wrote in message

>I don't think you will find an MS whitepaper on something like that. For
>one there are too many variables and performance depends greatly on
>configuration and load type. But there is no question that disk for disk a
>SAN will never beat the performance of direct attached storage. The
>advantages of the SAN are that it is more flexible and scalable in terms of
>number of spindles and such. But the biggest down side is that it is
>overrated and usually shared with other heavy loads. You might want to have
>a look at these:
>
> http://sqlblogcasts.com/search/SearchResults.aspx?q=san+performance
>
> But don't underestimate the virtual file stats and what that can give you.
> It will tell you if you are waiting or not.
>
> --
>
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" wrote in message
> news:#h52Hq6iKHA.2160@TK2MSFTNGP02.phx.gbl...
>> Do you know of a Microsoft whitepaper that talks about SAN's being slow
>> on small & frequent writes?
>>
>> "Andrew J. Kelly" wrote in message
>>
>>> Your best option to begin with is to look at the virtual file stats to
>>> see how much physical I/O msdb is actually doing over a given time
>>> period. It will also tell you how long it is taking to read and write
>>> those I/O's and you can see if it is too long or not. I guess he really
>>> doesn't understand SAN's very well either because SAN's are notoriously
>>> slow for writing small but frequent I/O's and direct attached drives
>>> will often outperform them hands down. If you are processing lots of
>>> Service Broker messages then that can explain some of the activity. But
>>> until you look at the file stats it is hard to say if it is handling it
>>> properly or not.
>>>
>>> --
>>>
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>> "Jay" wrote in message
>>> news:#iwZMWxiKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>> Sigh, that has basically been my argument. However, the guy has stuck
>>>> to his guns that his msdb is so active that the local drives aren't
>>>> enough and that it must be on the SAN for performance reasons.
>>>>
>>>> The best I could think of was to stress a similar drive (and monitor
>>>> it) to show what off the shelf SCSI's can do on an internal controller
>>>> and that msdb activity just doesn't come close to the drive capacity.
>>>> Perhaps SQLIO, being simpler, would be better.
>>>>
>>>> His DB seems to have a very large number of databases and each of these
>>>> DB's seem to have a lot of SQL Agent jobs that fire frequently. This
>>>> combined with heavy Message Broker activity is supposedly generating
>>>> log writes, reads and I have no clue what, to produce the "heavy
>>>> activity" that requires the SAN - which is RAID 5 BTW.
>>>>
>>>>
>>>> "Andrew J. Kelly" wrote in message
>>>>
>>>>> Why would MSDB be that busy? If it really is you might want to see
>>>>> why because it shouldn't be under normal circumstances. SQLIOSIM
>>>>> simulates SQL IO patterns but is not really a measurement tool per
>>>>> say. It is more to hammer the array and see where it breaks. If you
>>>>> want to see how it actually performs in general reads & writes use
>>>>> SQLIO.
>>>>>
>>>>> --
>>>>>
>>>>> Andrew J. Kelly SQL MVP
>>>>> Solid Quality Mentors
>>>>>
>>>>> "Jay" wrote in message
>>>>>
>>>>>> SQLIO++ will do.
>>>>>>
>>>>>> I'm in a discussion where a guy is saying msdb is so busy, that local
>>>>>> SCSI drives don't have enough throughput and it has to be on the SAN
>>>>>> (which is RAID 5 BTW). I was suggesting to him to run SQLIOsim to see
>>>>>> what the monitors look like when the drive is hammered and to compare
>>>>>> it to what he sees in his production environment.
>>>>>>
>>>>>> Jay
>>>>>>
>>>>>> "Andrew J. Kelly" wrote in message
>>>>>>
>>>>>>> It depends on what you want to accomplish as there are several. Can
>>>>>>> you tell us what your goal is?
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> Andrew J. Kelly SQL MVP
>>>>>>> Solid Quality Mentors
>>>>>>>
>>>>>>> "Jay" wrote in message
>>>>>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>>>>>
>>>>>>>> "Jay" wrote in message
>>>>>>>>
>>>>>>>>> What is the name of that disk I/O tool?
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
 >> Stay informed about: disk I/O tool 

I'll check the link out later, for now, I'm looking at:

select db_name(mf.database_id) as databaseName, divfs.file_id,
mf.physical_name,
num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes,
num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes
from sys.dm_io_virtual_file_stats(null,null) as divfs
inner join sys.master_files as mf
on mf.database_id = divfs.database_id
and mf.file_id = divfs.file_id

While it is clear this is what you're refering to, I would like to be sure
I'm reading the output right. Unfortunatly, I'm having a little trouble. But
that may be because I only have my home system to look at right now.

"Andrew J. Kelly" wrote in message

>I don't think you will find an MS whitepaper on something like that. For
>one there are too many variables and performance depends greatly on
>configuration and load type. But there is no question that disk for disk a
>SAN will never beat the performance of direct attached storage. The
>advantages of the SAN are that it is more flexible and scalable in terms of
>number of spindles and such. But the biggest down side is that it is
>overrated and usually shared with other heavy loads. You might want to have
>a look at these:
>
> http://sqlblogcasts.com/search/SearchResults.aspx?q=san+performance
>
> But don't underestimate the virtual file stats and what that can give you.
> It will tell you if you are waiting or not.
>
> --
>
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" wrote in message
> news:#h52Hq6iKHA.2160@TK2MSFTNGP02.phx.gbl...
>> Do you know of a Microsoft whitepaper that talks about SAN's being slow
>> on small & frequent writes?
>>
>> "Andrew J. Kelly" wrote in message
>>
>>> Your best option to begin with is to look at the virtual file stats to
>>> see how much physical I/O msdb is actually doing over a given time
>>> period. It will also tell you how long it is taking to read and write
>>> those I/O's and you can see if it is too long or not. I guess he really
>>> doesn't understand SAN's very well either because SAN's are notoriously
>>> slow for writing small but frequent I/O's and direct attached drives
>>> will often outperform them hands down. If you are processing lots of
>>> Service Broker messages then that can explain some of the activity. But
>>> until you look at the file stats it is hard to say if it is handling it
>>> properly or not.
>>>
>>> --
>>>
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>> "Jay" wrote in message
>>> news:#iwZMWxiKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>> Sigh, that has basically been my argument. However, the guy has stuck
>>>> to his guns that his msdb is so active that the local drives aren't
>>>> enough and that it must be on the SAN for performance reasons.
>>>>
>>>> The best I could think of was to stress a similar drive (and monitor
>>>> it) to show what off the shelf SCSI's can do on an internal controller
>>>> and that msdb activity just doesn't come close to the drive capacity.
>>>> Perhaps SQLIO, being simpler, would be better.
>>>>
>>>> His DB seems to have a very large number of databases and each of these
>>>> DB's seem to have a lot of SQL Agent jobs that fire frequently. This
>>>> combined with heavy Message Broker activity is supposedly generating
>>>> log writes, reads and I have no clue what, to produce the "heavy
>>>> activity" that requires the SAN - which is RAID 5 BTW.
>>>>
>>>>
>>>> "Andrew J. Kelly" wrote in message
>>>>
>>>>> Why would MSDB be that busy? If it really is you might want to see
>>>>> why because it shouldn't be under normal circumstances. SQLIOSIM
>>>>> simulates SQL IO patterns but is not really a measurement tool per
>>>>> say. It is more to hammer the array and see where it breaks. If you
>>>>> want to see how it actually performs in general reads & writes use
>>>>> SQLIO.
>>>>>
>>>>> --
>>>>>
>>>>> Andrew J. Kelly SQL MVP
>>>>> Solid Quality Mentors
>>>>>
>>>>> "Jay" wrote in message
>>>>>
>>>>>> SQLIO++ will do.
>>>>>>
>>>>>> I'm in a discussion where a guy is saying msdb is so busy, that local
>>>>>> SCSI drives don't have enough throughput and it has to be on the SAN
>>>>>> (which is RAID 5 BTW). I was suggesting to him to run SQLIOsim to see
>>>>>> what the monitors look like when the drive is hammered and to compare
>>>>>> it to what he sees in his production environment.
>>>>>>
>>>>>> Jay
>>>>>>
>>>>>> "Andrew J. Kelly" wrote in message
>>>>>>
>>>>>>> It depends on what you want to accomplish as there are several. Can
>>>>>>> you tell us what your goal is?
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> Andrew J. Kelly SQL MVP
>>>>>>> Solid Quality Mentors
>>>>>>>
>>>>>>> "Jay" wrote in message
>>>>>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>>>>>
>>>>>>>> "Jay" wrote in message
>>>>>>>>
>>>>>>>>> What is the name of that disk I/O tool?
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
 >> Stay informed about: disk I/O tool 

Just got though some of the links, OH MY!

Just one question, when building a cluster, is there an alternative to a
SAN?

"Andrew J. Kelly" wrote in message

>I don't think you will find an MS whitepaper on something like that. For
>one there are too many variables and performance depends greatly on
>configuration and load type. But there is no question that disk for disk a
>SAN will never beat the performance of direct attached storage. The
>advantages of the SAN are that it is more flexible and scalable in terms of
>number of spindles and such. But the biggest down side is that it is
>overrated and usually shared with other heavy loads. You might want to have
>a look at these:
>
> http://sqlblogcasts.com/search/SearchResults.aspx?q=san+performance
>
> But don't underestimate the virtual file stats and what that can give you.
> It will tell you if you are waiting or not.
>
> --
>
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" wrote in message
> news:#h52Hq6iKHA.2160@TK2MSFTNGP02.phx.gbl...
>> Do you know of a Microsoft whitepaper that talks about SAN's being slow
>> on small & frequent writes?
>>
>> "Andrew J. Kelly" wrote in message
>>
>>> Your best option to begin with is to look at the virtual file stats to
>>> see how much physical I/O msdb is actually doing over a given time
>>> period. It will also tell you how long it is taking to read and write
>>> those I/O's and you can see if it is too long or not. I guess he really
>>> doesn't understand SAN's very well either because SAN's are notoriously
>>> slow for writing small but frequent I/O's and direct attached drives
>>> will often outperform them hands down. If you are processing lots of
>>> Service Broker messages then that can explain some of the activity. But
>>> until you look at the file stats it is hard to say if it is handling it
>>> properly or not.
>>>
>>> --
>>>
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>> "Jay" wrote in message
>>> news:#iwZMWxiKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>> Sigh, that has basically been my argument. However, the guy has stuck
>>>> to his guns that his msdb is so active that the local drives aren't
>>>> enough and that it must be on the SAN for performance reasons.
>>>>
>>>> The best I could think of was to stress a similar drive (and monitor
>>>> it) to show what off the shelf SCSI's can do on an internal controller
>>>> and that msdb activity just doesn't come close to the drive capacity.
>>>> Perhaps SQLIO, being simpler, would be better.
>>>>
>>>> His DB seems to have a very large number of databases and each of these
>>>> DB's seem to have a lot of SQL Agent jobs that fire frequently. This
>>>> combined with heavy Message Broker activity is supposedly generating
>>>> log writes, reads and I have no clue what, to produce the "heavy
>>>> activity" that requires the SAN - which is RAID 5 BTW.
>>>>
>>>>
>>>> "Andrew J. Kelly" wrote in message
>>>>
>>>>> Why would MSDB be that busy? If it really is you might want to see
>>>>> why because it shouldn't be under normal circumstances. SQLIOSIM
>>>>> simulates SQL IO patterns but is not really a measurement tool per
>>>>> say. It is more to hammer the array and see where it breaks. If you
>>>>> want to see how it actually performs in general reads & writes use
>>>>> SQLIO.
>>>>>
>>>>> --
>>>>>
>>>>> Andrew J. Kelly SQL MVP
>>>>> Solid Quality Mentors
>>>>>
>>>>> "Jay" wrote in message
>>>>>
>>>>>> SQLIO++ will do.
>>>>>>
>>>>>> I'm in a discussion where a guy is saying msdb is so busy, that local
>>>>>> SCSI drives don't have enough throughput and it has to be on the SAN
>>>>>> (which is RAID 5 BTW). I was suggesting to him to run SQLIOsim to see
>>>>>> what the monitors look like when the drive is hammered and to compare
>>>>>> it to what he sees in his production environment.
>>>>>>
>>>>>> Jay
>>>>>>
>>>>>> "Andrew J. Kelly" wrote in message
>>>>>>
>>>>>>> It depends on what you want to accomplish as there are several. Can
>>>>>>> you tell us what your goal is?
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> Andrew J. Kelly SQL MVP
>>>>>>> Solid Quality Mentors
>>>>>>>
>>>>>>> "Jay" wrote in message
>>>>>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>>>>>
>>>>>>>> "Jay" wrote in message
>>>>>>>>
>>>>>>>>> What is the name of that disk I/O tool?
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
 >> Stay informed about: disk I/O tool 

You need shared disk of some sort for a cluster. IIRC HP's MSA series are
certified for this kind of implementation, as are other
'semi-direct-attached' enclosures.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Jay" wrote in message

> Just got though some of the links, OH MY!
>
> Just one question, when building a cluster, is there an alternative to a
> SAN?
>
> "Andrew J. Kelly" wrote in message
>
>>I don't think you will find an MS whitepaper on something like that. For
>>one there are too many variables and performance depends greatly on
>>configuration and load type. But there is no question that disk for disk a
>>SAN will never beat the performance of direct attached storage. The
>>advantages of the SAN are that it is more flexible and scalable in terms
>>of number of spindles and such. But the biggest down side is that it is
>>overrated and usually shared with other heavy loads. You might want to
>>have a look at these:
>>
>> http://sqlblogcasts.com/search/SearchResults.aspx?q=san+performance
>>
>> But don't underestimate the virtual file stats and what that can give
>> you. It will tell you if you are waiting or not.
>>
>> --
>>
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" wrote in message
>> news:#h52Hq6iKHA.2160@TK2MSFTNGP02.phx.gbl...
>>> Do you know of a Microsoft whitepaper that talks about SAN's being slow
>>> on small & frequent writes?
>>>
>>> "Andrew J. Kelly" wrote in message
>>>
>>>> Your best option to begin with is to look at the virtual file stats to
>>>> see how much physical I/O msdb is actually doing over a given time
>>>> period. It will also tell you how long it is taking to read and write
>>>> those I/O's and you can see if it is too long or not. I guess he really
>>>> doesn't understand SAN's very well either because SAN's are notoriously
>>>> slow for writing small but frequent I/O's and direct attached drives
>>>> will often outperform them hands down. If you are processing lots of
>>>> Service Broker messages then that can explain some of the activity. But
>>>> until you look at the file stats it is hard to say if it is handling it
>>>> properly or not.
>>>>
>>>> --
>>>>
>>>> Andrew J. Kelly SQL MVP
>>>> Solid Quality Mentors
>>>>
>>>> "Jay" wrote in message
>>>> news:#iwZMWxiKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>> Sigh, that has basically been my argument. However, the guy has stuck
>>>>> to his guns that his msdb is so active that the local drives aren't
>>>>> enough and that it must be on the SAN for performance reasons.
>>>>>
>>>>> The best I could think of was to stress a similar drive (and monitor
>>>>> it) to show what off the shelf SCSI's can do on an internal controller
>>>>> and that msdb activity just doesn't come close to the drive capacity.
>>>>> Perhaps SQLIO, being simpler, would be better.
>>>>>
>>>>> His DB seems to have a very large number of databases and each of
>>>>> these DB's seem to have a lot of SQL Agent jobs that fire frequently.
>>>>> This combined with heavy Message Broker activity is supposedly
>>>>> generating log writes, reads and I have no clue what, to produce the
>>>>> "heavy activity" that requires the SAN - which is RAID 5 BTW.
>>>>>
>>>>>
>>>>> "Andrew J. Kelly" wrote in message
>>>>>
>>>>>> Why would MSDB be that busy? If it really is you might want to see
>>>>>> why because it shouldn't be under normal circumstances. SQLIOSIM
>>>>>> simulates SQL IO patterns but is not really a measurement tool per
>>>>>> say. It is more to hammer the array and see where it breaks. If you
>>>>>> want to see how it actually performs in general reads & writes use
>>>>>> SQLIO.
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Andrew J. Kelly SQL MVP
>>>>>> Solid Quality Mentors
>>>>>>
>>>>>> "Jay" wrote in message
>>>>>>
>>>>>>> SQLIO++ will do.
>>>>>>>
>>>>>>> I'm in a discussion where a guy is saying msdb is so busy, that
>>>>>>> local SCSI drives don't have enough throughput and it has to be on
>>>>>>> the SAN (which is RAID 5 BTW). I was suggesting to him to run
>>>>>>> SQLIOsim to see what the monitors look like when the drive is
>>>>>>> hammered and to compare it to what he sees in his production
>>>>>>> environment.
>>>>>>>
>>>>>>> Jay
>>>>>>>
>>>>>>> "Andrew J. Kelly" wrote in message
>>>>>>>
>>>>>>>> It depends on what you want to accomplish as there are several. Can
>>>>>>>> you tell us what your goal is?
>>>>>>>>
>>>>>>>> --
>>>>>>>>
>>>>>>>> Andrew J. Kelly SQL MVP
>>>>>>>> Solid Quality Mentors
>>>>>>>>
>>>>>>>> "Jay" wrote in message
>>>>>>>> news:#HfRsQciKHA.1648@TK2MSFTNGP05.phx.gbl...
>>>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism
>>>>>>>>>
>>>>>>>>> "Jay" wrote in message
>>>>>>>>>
>>>>>>>>>> What is the name of that disk I/O tool?
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>
 >> Stay informed about: disk I/O tool