Waitstats Performance on sql 2005
in [Server Configuration]
|
Prev: Mirroring issue, error 1418
Next: Memory DMV's
From: Chris Wood on 3 Dec 2009 13:49 Thanks Andrew. I have attended a number of your sessions at the 5 PASS Summits I have attended and the subjects are always good. If I go again next year I will try and ask questions of the presenters rather than dash to the next session. Your scheme was my takeaway this year and so I am trying to dig deeper to understand what the waitstats are really telling me. Thanks for correcting my terminology. I am also trying to understand which wait types should be thrown away and have had help from Jonathan Kehayias and Bob Ward amd am waiting for Bob's gathering together of all the wait types and their meanings. Chris "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:%23n5Al46cKHA.2160(a)TK2MSFTNGP02.phx.gbl... > Chris, > > You should have introduced yourself at the end of the session, it would > have been nice to say hi in person. I am not 100% sure I understand your > explanation but I think we are mixing a few terms so let me go over it > again. You imply that a thread is a resource and when it comes to waits > that is not quite accurate. When a task needs to be done it is assigned to > a thread on a scheduler. During the execution of that task if the thread > has to wait on a resource such as physical disk it starts tracking the > time and the thread gets pulled off the processor so another runnable > thread can run. When the resource, in this case the pages from disk are > available the thread must put back onto a scheduler to run again. The time > it started the wait until the time the resource was available is > considered the resource wait time. The time from when the resource was > again available until it actually gets scheduled onto the processor is > considered the signal wait. The higher the signal waits the more time it > spent waiting to get onto a processor to do the work. High signal waits > typically indicate CPU pressure such as too few cores for the given work > load. But if you have tasks that keep getting waits such as CXPacket waits > you can also get lots of signal waits since the threads may be swapped on > and off the CPU a lot before it is all said and done. So signal waits will > happen on a busy system when you have other waits since the threads will > have to swap on and off. But very high signal waits is usually a sign of > too workload for a given number of cores where the threads have to wait a > while to get back on the procs after they were pulled off. Hope that > cleared it up and if not just ask again, eventually we will have to > give:). > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "Chris Wood" <anonymous(a)microsoft.com> wrote in message > news:OKHB5F5cKHA.5796(a)TK2MSFTNGP06.phx.gbl... >> Andrew, >> >> I attended your PASS2009 session on file and wait stats and am >> implementing your scheme currently. If I have got this right the signal >> waits are the time spent, when you have the resources(threads) and are >> actually waiting because of the wait rather than the time of issuing the >> function and it actually starting. You mentioned that sometimes a wait >> will lose its resource(thread) and have to wait to get another one before >> it could actual perform what it wanted to perform. The signal wait was >> the time after starting the wait and getting a resource. >> >> If I have this right then in some circumstances the signal wait would >> help to understand the reason for the original wait. >> >> Chris >> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >> news:uCg%23$phcKHA.1640(a)TK2MSFTNGP06.phx.gbl... >>> Percentages are pretty useless by themselves. It is really the amount of >>> waits not the % that is most important. By the way Sleep_Bpool_Flush can >>> for the most part be ignored as it is a natural internal process. But >>> one thing you do want to pay attention to % wise is the ratio of signal >>> waits to resource waits so u know if the wait is caused by CPU pressure >>> or the wait itself. >>> >>> -- >>> >>> Andrew J. Kelly SQL MVP >>> Solid Quality Mentors >>> >>> "RG" <RG(a)discussions.microsoft.com> wrote in message >>> news:C2E8D912-B6F0-4571-BDE1-572010FAD9EA(a)microsoft.com... >>>> If one was to track wait times for span of an hour, what should be an >>>> acceptable wait percentage based on total waits for the following >>>> resources: >>>> >>>> ASYNC_NETWORK_IO >>>> CXPACKET >>>> SLEEP_BPOOL_FLUSH >>>> WRITELOG >>>> PAGEIOLATCH_EX >>>> PAGEIOLATCH_SH >>>> >>>> Thanks in advance >>> >> >>
From: Andrew J. Kelly on 3 Dec 2009 18:49
Chris, Thanks, so are you saying I should pay you for now for attending my sessions :). Yes we are all waiting for Bobs wait stat paper. In particular the preemptive waits. In the waitstats report example code from my session I filter out a fair number of waits that you might want to have a look at. It probably comes close to matching Jonathans. There are probably some more preemptive waits that can also be filtered out that I may not have but luckily they are not as common and don't interfere as much. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Chris Wood" <anonymous(a)microsoft.com> wrote in message news:OByUjlEdKHA.4708(a)TK2MSFTNGP02.phx.gbl... > Thanks Andrew. > > I have attended a number of your sessions at the 5 PASS Summits I have > attended and the subjects are always good. If I go again next year I will > try and ask questions of the presenters rather than dash to the next > session. Your scheme was my takeaway this year and so I am trying to dig > deeper to understand what the waitstats are really telling me. > > Thanks for correcting my terminology. > > I am also trying to understand which wait types should be thrown away and > have had help from Jonathan Kehayias and Bob Ward amd am waiting for Bob's > gathering together of all the wait types and their meanings. > > Chris > > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:%23n5Al46cKHA.2160(a)TK2MSFTNGP02.phx.gbl... >> Chris, >> >> You should have introduced yourself at the end of the session, it would >> have been nice to say hi in person. I am not 100% sure I understand your >> explanation but I think we are mixing a few terms so let me go over it >> again. You imply that a thread is a resource and when it comes to waits >> that is not quite accurate. When a task needs to be done it is assigned >> to a thread on a scheduler. During the execution of that task if the >> thread has to wait on a resource such as physical disk it starts tracking >> the time and the thread gets pulled off the processor so another runnable >> thread can run. When the resource, in this case the pages from disk are >> available the thread must put back onto a scheduler to run again. The >> time it started the wait until the time the resource was available is >> considered the resource wait time. The time from when the resource was >> again available until it actually gets scheduled onto the processor is >> considered the signal wait. The higher the signal waits the more time it >> spent waiting to get onto a processor to do the work. High signal waits >> typically indicate CPU pressure such as too few cores for the given work >> load. But if you have tasks that keep getting waits such as CXPacket >> waits you can also get lots of signal waits since the threads may be >> swapped on and off the CPU a lot before it is all said and done. So >> signal waits will happen on a busy system when you have other waits since >> the threads will have to swap on and off. But very high signal waits is >> usually a sign of too workload for a given number of cores where the >> threads have to wait a while to get back on the procs after they were >> pulled off. Hope that cleared it up and if not just ask again, eventually >> we will have to give:). >> >> -- >> >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "Chris Wood" <anonymous(a)microsoft.com> wrote in message >> news:OKHB5F5cKHA.5796(a)TK2MSFTNGP06.phx.gbl... >>> Andrew, >>> >>> I attended your PASS2009 session on file and wait stats and am >>> implementing your scheme currently. If I have got this right the signal >>> waits are the time spent, when you have the resources(threads) and are >>> actually waiting because of the wait rather than the time of issuing the >>> function and it actually starting. You mentioned that sometimes a wait >>> will lose its resource(thread) and have to wait to get another one >>> before it could actual perform what it wanted to perform. The signal >>> wait was the time after starting the wait and getting a resource. >>> >>> If I have this right then in some circumstances the signal wait would >>> help to understand the reason for the original wait. >>> >>> Chris >>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>> news:uCg%23$phcKHA.1640(a)TK2MSFTNGP06.phx.gbl... >>>> Percentages are pretty useless by themselves. It is really the amount >>>> of waits not the % that is most important. By the way Sleep_Bpool_Flush >>>> can for the most part be ignored as it is a natural internal process. >>>> But one thing you do want to pay attention to % wise is the ratio of >>>> signal waits to resource waits so u know if the wait is caused by CPU >>>> pressure or the wait itself. >>>> >>>> -- >>>> >>>> Andrew J. Kelly SQL MVP >>>> Solid Quality Mentors >>>> >>>> "RG" <RG(a)discussions.microsoft.com> wrote in message >>>> news:C2E8D912-B6F0-4571-BDE1-572010FAD9EA(a)microsoft.com... >>>>> If one was to track wait times for span of an hour, what should be an >>>>> acceptable wait percentage based on total waits for the following >>>>> resources: >>>>> >>>>> ASYNC_NETWORK_IO >>>>> CXPACKET >>>>> SLEEP_BPOOL_FLUSH >>>>> WRITELOG >>>>> PAGEIOLATCH_EX >>>>> PAGEIOLATCH_SH >>>>> >>>>> Thanks in advance >>>> >>> >>> > > |