disk I/O tool
in [Server Configuration]
|
Prev: Create Account
Next: sys.dm_exec_query_stats
From: Andrew J. Kelly on 2 Jan 2010 09:45 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" <spam(a)nospam.org> wrote in message news:#h52Hq6iKHA.2160(a)TK2MSFTNGP02.phx.gbl... > Do you know of a Microsoft whitepaper that talks about SAN's being slow on > small & frequent writes? > > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:ujVlsEyiKHA.5520(a)TK2MSFTNGP06.phx.gbl... >> 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" <spam(a)nospam.org> wrote in message >> news:#iwZMWxiKHA.1648(a)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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>> news:upeu61uiKHA.1236(a)TK2MSFTNGP04.phx.gbl... >>>> 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" <spam(a)nospam.org> wrote in message >>>> news:OgPyrOniKHA.1536(a)TK2MSFTNGP06.phx.gbl... >>>>> 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>> news:OPl$zIiiKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>>>>> 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" <spam(a)nospam.org> wrote in message >>>>>> news:#HfRsQciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism :) >>>>>>> >>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>> news:OhTGHEciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>> What is the name of that disk I/O tool? >>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>> >>> > >
From: Jay on 2 Jan 2010 14:52 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:unM9wo7iKHA.2188(a)TK2MSFTNGP04.phx.gbl... >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" <spam(a)nospam.org> wrote in message > news:#h52Hq6iKHA.2160(a)TK2MSFTNGP02.phx.gbl... >> Do you know of a Microsoft whitepaper that talks about SAN's being slow >> on small & frequent writes? >> >> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >> news:ujVlsEyiKHA.5520(a)TK2MSFTNGP06.phx.gbl... >>> 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" <spam(a)nospam.org> wrote in message >>> news:#iwZMWxiKHA.1648(a)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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>> news:upeu61uiKHA.1236(a)TK2MSFTNGP04.phx.gbl... >>>>> 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" <spam(a)nospam.org> wrote in message >>>>> news:OgPyrOniKHA.1536(a)TK2MSFTNGP06.phx.gbl... >>>>>> 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>>> news:OPl$zIiiKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>>>>>> 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" <spam(a)nospam.org> wrote in message >>>>>>> news:#HfRsQciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism :) >>>>>>>> >>>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>>> news:OhTGHEciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>> What is the name of that disk I/O tool? >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>> >>>> >> >>
From: Jay on 2 Jan 2010 15:53 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:unM9wo7iKHA.2188(a)TK2MSFTNGP04.phx.gbl... >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" <spam(a)nospam.org> wrote in message > news:#h52Hq6iKHA.2160(a)TK2MSFTNGP02.phx.gbl... >> Do you know of a Microsoft whitepaper that talks about SAN's being slow >> on small & frequent writes? >> >> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >> news:ujVlsEyiKHA.5520(a)TK2MSFTNGP06.phx.gbl... >>> 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" <spam(a)nospam.org> wrote in message >>> news:#iwZMWxiKHA.1648(a)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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>> news:upeu61uiKHA.1236(a)TK2MSFTNGP04.phx.gbl... >>>>> 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" <spam(a)nospam.org> wrote in message >>>>> news:OgPyrOniKHA.1536(a)TK2MSFTNGP06.phx.gbl... >>>>>> 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>>> news:OPl$zIiiKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>>>>>> 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" <spam(a)nospam.org> wrote in message >>>>>>> news:#HfRsQciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism :) >>>>>>>> >>>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>>> news:OhTGHEciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>> What is the name of that disk I/O tool? >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>> >>>> >> >>
From: TheSQLGuru on 3 Jan 2010 13:37 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" <spam(a)nospam.org> wrote in message news:u%23w6h2%23iKHA.5608(a)TK2MSFTNGP05.phx.gbl... > 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:unM9wo7iKHA.2188(a)TK2MSFTNGP04.phx.gbl... >>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" <spam(a)nospam.org> wrote in message >> news:#h52Hq6iKHA.2160(a)TK2MSFTNGP02.phx.gbl... >>> Do you know of a Microsoft whitepaper that talks about SAN's being slow >>> on small & frequent writes? >>> >>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>> news:ujVlsEyiKHA.5520(a)TK2MSFTNGP06.phx.gbl... >>>> 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" <spam(a)nospam.org> wrote in message >>>> news:#iwZMWxiKHA.1648(a)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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>> news:upeu61uiKHA.1236(a)TK2MSFTNGP04.phx.gbl... >>>>>> 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" <spam(a)nospam.org> wrote in message >>>>>> news:OgPyrOniKHA.1536(a)TK2MSFTNGP06.phx.gbl... >>>>>>> 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>>>> news:OPl$zIiiKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>>>>>>> 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" <spam(a)nospam.org> wrote in message >>>>>>>> news:#HfRsQciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism :) >>>>>>>>> >>>>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>>>> news:OhTGHEciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>>> What is the name of that disk I/O tool? >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>> >>> > >
From: TheSQLGuru on 3 Jan 2010 13:39
What I recommend is doing a differential analysis of the io stalls. They are cumulative, and thus can mask real problems if those are of an intermittent nature (which they almost always are). Put your values in a temp table, waitfor delay 'someperiod', now join current io stall output to the temp table and do a diff based on the time spread. gets you actual period-based values which are much more meaningful because you can run for say 5 or 10 minutes during a period you know to be high-activity. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Jay" <spam(a)nospam.org> wrote in message news:OMgkFU%23iKHA.2132(a)TK2MSFTNGP05.phx.gbl... > 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:unM9wo7iKHA.2188(a)TK2MSFTNGP04.phx.gbl... >>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" <spam(a)nospam.org> wrote in message >> news:#h52Hq6iKHA.2160(a)TK2MSFTNGP02.phx.gbl... >>> Do you know of a Microsoft whitepaper that talks about SAN's being slow >>> on small & frequent writes? >>> >>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>> news:ujVlsEyiKHA.5520(a)TK2MSFTNGP06.phx.gbl... >>>> 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" <spam(a)nospam.org> wrote in message >>>> news:#iwZMWxiKHA.1648(a)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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>> news:upeu61uiKHA.1236(a)TK2MSFTNGP04.phx.gbl... >>>>>> 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" <spam(a)nospam.org> wrote in message >>>>>> news:OgPyrOniKHA.1536(a)TK2MSFTNGP06.phx.gbl... >>>>>>> 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" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>>>>>> news:OPl$zIiiKHA.5604(a)TK2MSFTNGP04.phx.gbl... >>>>>>>> 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" <spam(a)nospam.org> wrote in message >>>>>>>> news:#HfRsQciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>> SQLIOsim, which the spell checker wants to change to Solipsism :) >>>>>>>>> >>>>>>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>>>>>> news:OhTGHEciKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>>>>>>>> What is the name of that disk I/O tool? >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>> >>> > > |