Waitstats Performance on sql 2005
in [Server Configuration]
|
Prev: Mirroring issue, error 1418
Next: Memory DMV's
From: RG on 30 Nov 2009 14:19 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: TheSQLGuru on 30 Nov 2009 15:20 There is to my knowledge no 'ratios' given as best practices between the listed wait types. It has been my experience (which is VERY deep on this topic) that it is almost always completely obvious which one(s) of the waits is really the problem indicator. I assume you are intimately familiar with SQL Server 2005 waits and queues best practices article, right?? -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "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 30 Nov 2009 19:09 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: Chris Wood on 2 Dec 2009 15:53 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 2 Dec 2009 19:18
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 >> > > |