Wait Stats on SQL2005
in [Server Configuration]
|
Prev: Database Mirroring and Insert Statement
Next: BCP
From: TheSQLGuru on 12 Jan 2010 14:45 If no one is complaining and things aren't 'breaking' due to poor performance then you probably have more important things to do that hunt for a problem that isn't likely a problem. :-) Having said that, if you DO want to work on performance, start using profiler and/or query stats DMVs to look for things that ARE taking a long time (or lots of resources) to run and start working them in worst-first order. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "RG" <RG(a)discussions.microsoft.com> wrote in message news:79591C14-60E8-410B-A6B6-F9331DC78101(a)microsoft.com... >I just realized that those wait times are average per minute as I took 60 > samples during the course of 1 hour. I would imagine 12 seconds per > minute > of waiting is substantial. > > "Andrew J. Kelly" wrote: > >> I have no idea what that sp does and why you would want to poll it every >> minute and take an average. Just take a snapshot at the beginning and one >> at >> the end and do a delta. That is what the waits will be for that hour. >> Don't >> make it any more complicated than that. As for the other comments I am >> talking about a number of system waits that you canusually safely ignore >> that can make up a good part of the overall wait times. Have a look at >> this >> post for some details: >> >> http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b92ab214-8676-428a-9bdc-e693ec059897/ >> >> http://technet.microsoft.com/en-us/library/cc966413.aspx >> >> -- >> >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "RG" <nobody(a)nowhere.com> wrote in message >> news:65E5BE9A-6039-4176-BBF4-F37407B0114B(a)microsoft.com... >> > Actually, I used publically available stored procedure that tracks >> > waitstats by periodically querying dynamic performance views and >> > averages >> > the final numbers. Specifically, in my case, the waitstats were polled >> > every minute for an hour. The sum of all durations of all wait types >> > was >> > around 11 seconds. >> > >> >> seconds for an hour that's probably not bad. But we have no way of >> >> knowing if you filtered out any of the system waits, what was >> >> happening >> >> during that time etc. Etc. >> > >> > Could you elaborate on this. >> > >> > Thanks for your help. >> > >> > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >> > news:%23OHx8mxkKHA.2188(a)TK2MSFTNGP04.phx.gbl... >> >> Total waits by itself is useless. But if all the waits total less than >> >> 11 >> >> seconds for an hour that's probably not bad. But we have no way of >> >> knowing if you filtered out any of the system waits, what was >> >> happening >> >> during that time etc. Etc. >> >> >> >> -- >> >> >> >> Andrew J. Kelly SQL MVP >> >> Solid Quality Mentors >> >> >> >> "RG" <RG(a)discussions.microsoft.com> wrote in message >> >> news:524F5559-5AE8-4901-B797-9FCD47F066B3(a)microsoft.com... >> >>> Is 11 seconds of total waits during the course of an hour, >> >>> negligable? >> >>> >> >>> If not, why? >> >>> >> >>> Thanks in advance >> >> >> > >> . >>
From: Jay on 12 Jan 2010 19:17 "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:p4idnfENk8E_TdHWnZ2dnUVZ_h-dnZ2d(a)earthlink.com... > If no one is complaining and things aren't 'breaking' due to poor > performance then you probably have more important things to do that hunt > for a problem that isn't likely a problem. :-) > Kevin, I have to agree and disagree with this thinking. On the one hand, there are many things that a DBA needs to be doing. So, applying enegry where it isn't necessary is counterproductive. On the other hand, I absoultly hate administration-by-complaint and I can't stand the thought of not knowing what is going on in my systems. It sounds to me like the OP is just trying to learn to be proactive. > Having said that, if you DO want to work on performance, start using > profiler and/or query stats DMVs to look for things that ARE taking a long > time (or lots of resources) to run and start working them in worst-first > order. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "RG" <RG(a)discussions.microsoft.com> wrote in message > news:79591C14-60E8-410B-A6B6-F9331DC78101(a)microsoft.com... >>I just realized that those wait times are average per minute as I took 60 >> samples during the course of 1 hour. I would imagine 12 seconds per >> minute >> of waiting is substantial. >> >> "Andrew J. Kelly" wrote: >> >>> I have no idea what that sp does and why you would want to poll it every >>> minute and take an average. Just take a snapshot at the beginning and >>> one at >>> the end and do a delta. That is what the waits will be for that hour. >>> Don't >>> make it any more complicated than that. As for the other comments I am >>> talking about a number of system waits that you canusually safely ignore >>> that can make up a good part of the overall wait times. Have a look at >>> this >>> post for some details: >>> >>> http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b92ab214-8676-428a-9bdc-e693ec059897/ >>> >>> http://technet.microsoft.com/en-us/library/cc966413.aspx >>> >>> -- >>> >>> Andrew J. Kelly SQL MVP >>> Solid Quality Mentors >>> >>> "RG" <nobody(a)nowhere.com> wrote in message >>> news:65E5BE9A-6039-4176-BBF4-F37407B0114B(a)microsoft.com... >>> > Actually, I used publically available stored procedure that tracks >>> > waitstats by periodically querying dynamic performance views and >>> > averages >>> > the final numbers. Specifically, in my case, the waitstats were >>> > polled >>> > every minute for an hour. The sum of all durations of all wait types >>> > was >>> > around 11 seconds. >>> > >>> >> seconds for an hour that's probably not bad. But we have no way of >>> >> knowing if you filtered out any of the system waits, what was >>> >> happening >>> >> during that time etc. Etc. >>> > >>> > Could you elaborate on this. >>> > >>> > Thanks for your help. >>> > >>> > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >>> > news:%23OHx8mxkKHA.2188(a)TK2MSFTNGP04.phx.gbl... >>> >> Total waits by itself is useless. But if all the waits total less >>> >> than 11 >>> >> seconds for an hour that's probably not bad. But we have no way of >>> >> knowing if you filtered out any of the system waits, what was >>> >> happening >>> >> during that time etc. Etc. >>> >> >>> >> -- >>> >> >>> >> Andrew J. Kelly SQL MVP >>> >> Solid Quality Mentors >>> >> >>> >> "RG" <RG(a)discussions.microsoft.com> wrote in message >>> >> news:524F5559-5AE8-4901-B797-9FCD47F066B3(a)microsoft.com... >>> >>> Is 11 seconds of total waits during the course of an hour, >>> >>> negligable? >>> >>> >>> >>> If not, why? >>> >>> >>> >>> Thanks in advance >>> >> >>> > >>> . >>> > >
From: Andrew J. Kelly on 13 Jan 2010 18:28 One thing you have to remember with waitstats are these are the total waits for all the threads during that time period. So even though it was only a minute you could have have hundreds of mostly concurrent threads. So remember it isn't simply 60 seconds of time it much more than that when you add up all the threads. But again it is hard to say if that is a a lot or not without knowing what was the waiting on. If it was mostly waitfor then that is a perfectly good reason to wait as that is what it does. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "RG" <RG(a)discussions.microsoft.com> wrote in message news:79591C14-60E8-410B-A6B6-F9331DC78101(a)microsoft.com... > I just realized that those wait times are average per minute as I took 60 > samples during the course of 1 hour. I would imagine 12 seconds per > minute > of waiting is substantial. > > "Andrew J. Kelly" wrote: > >> I have no idea what that sp does and why you would want to poll it every >> minute and take an average. Just take a snapshot at the beginning and one >> at >> the end and do a delta. That is what the waits will be for that hour. >> Don't >> make it any more complicated than that. As for the other comments I am >> talking about a number of system waits that you canusually safely ignore >> that can make up a good part of the overall wait times. Have a look at >> this >> post for some details: >> >> http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b92ab214-8676-428a-9bdc-e693ec059897/ >> >> http://technet.microsoft.com/en-us/library/cc966413.aspx >> >> -- >> >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> "RG" <nobody(a)nowhere.com> wrote in message >> news:65E5BE9A-6039-4176-BBF4-F37407B0114B(a)microsoft.com... >> > Actually, I used publically available stored procedure that tracks >> > waitstats by periodically querying dynamic performance views and >> > averages >> > the final numbers. Specifically, in my case, the waitstats were polled >> > every minute for an hour. The sum of all durations of all wait types >> > was >> > around 11 seconds. >> > >> >> seconds for an hour that's probably not bad. But we have no way of >> >> knowing if you filtered out any of the system waits, what was >> >> happening >> >> during that time etc. Etc. >> > >> > Could you elaborate on this. >> > >> > Thanks for your help. >> > >> > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message >> > news:%23OHx8mxkKHA.2188(a)TK2MSFTNGP04.phx.gbl... >> >> Total waits by itself is useless. But if all the waits total less than >> >> 11 >> >> seconds for an hour that's probably not bad. But we have no way of >> >> knowing if you filtered out any of the system waits, what was >> >> happening >> >> during that time etc. Etc. >> >> >> >> -- >> >> >> >> Andrew J. Kelly SQL MVP >> >> Solid Quality Mentors >> >> >> >> "RG" <RG(a)discussions.microsoft.com> wrote in message >> >> news:524F5559-5AE8-4901-B797-9FCD47F066B3(a)microsoft.com... >> >>> Is 11 seconds of total waits during the course of an hour, >> >>> negligable? >> >>> >> >>> If not, why? >> >>> >> >>> Thanks in advance >> >> >> > >> . >>
From: RG on 14 Jan 2010 17:26
I have produced the stats below. wait_type waiting_tasks_count Resource wt (T1-T0) res_wt_% Signal wt (T2-T1) sig_wt_% Total wt (T2-T0) wt_% ------------------------- -------------------- -------------------- ---------- -------------------- --------- -------------------- ------- ***total*** 0 11615404 100.0 905353 100.0 12520757 100.0 SQLTRACE_BUFFER_FLUSH 879 3516000 30.3 0 0.0 3516000 28.1 ASYNC_NETWORK_IO 205935 3446609 29.7 38953 4.3 3485562 27.8 CXPACKET 1091445 2653891 22.8 362562 40.0 3016453 24.1 SLEEP_BPOOL_FLUSH 116755 734594 6.3 968 0.1 735562 5.9 WRITELOG 54300 710094 6.1 8796 1.0 718890 5.7 PAGEIOLATCH_EX 26796 258218 2.2 78 0.0 258296 2.1 PAGEIOLATCH_SH 19279 207657 1.8 93 0.0 207750 1.7 IO_COMPLETION 12046 40563 0.3 46 0.0 40609 0.3 LATCH_EX 50908 35406 0.3 12484 1.4 47890 0.4 LATCH_DT 0 0 0.0 0 0.0 0 0.0 PAGELATCH_NL 0 0 0.0 0 0.0 0 0.0 PAGELATCH_KP 1 0 0.0 15 0.0 15 0.0 PAGELATCH_SH 2449 125 0.0 234 0.0 359 0.0 PAGELATCH_UP 24 0 0.0 0 0.0 0 0.0 PAGELATCH_EX 46010 141 0.0 10765 1.2 10906 0.1 "Andrew J. Kelly" wrote: > One thing you have to remember with waitstats are these are the total waits > for all the threads during that time period. So even though it was only a > minute you could have have hundreds of mostly concurrent threads. So > remember it isn't simply 60 seconds of time it much more than that when you > add up all the threads. But again it is hard to say if that is a a lot or > not without knowing what was the waiting on. If it was mostly waitfor then > that is a perfectly good reason to wait as that is what it does. > > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "RG" <RG(a)discussions.microsoft.com> wrote in message > news:79591C14-60E8-410B-A6B6-F9331DC78101(a)microsoft.com... > > I just realized that those wait times are average per minute as I took 60 > > samples during the course of 1 hour. I would imagine 12 seconds per > > minute > > of waiting is substantial. > > > > "Andrew J. Kelly" wrote: > > > >> I have no idea what that sp does and why you would want to poll it every > >> minute and take an average. Just take a snapshot at the beginning and one > >> at > >> the end and do a delta. That is what the waits will be for that hour. > >> Don't > >> make it any more complicated than that. As for the other comments I am > >> talking about a number of system waits that you canusually safely ignore > >> that can make up a good part of the overall wait times. Have a look at > >> this > >> post for some details: > >> > >> http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b92ab214-8676-428a-9bdc-e693ec059897/ > >> > >> http://technet.microsoft.com/en-us/library/cc966413.aspx > >> > >> -- > >> > >> Andrew J. Kelly SQL MVP > >> Solid Quality Mentors > >> > >> "RG" <nobody(a)nowhere.com> wrote in message > >> news:65E5BE9A-6039-4176-BBF4-F37407B0114B(a)microsoft.com... > >> > Actually, I used publically available stored procedure that tracks > >> > waitstats by periodically querying dynamic performance views and > >> > averages > >> > the final numbers. Specifically, in my case, the waitstats were polled > >> > every minute for an hour. The sum of all durations of all wait types > >> > was > >> > around 11 seconds. > >> > > >> >> seconds for an hour that's probably not bad. But we have no way of > >> >> knowing if you filtered out any of the system waits, what was > >> >> happening > >> >> during that time etc. Etc. > >> > > >> > Could you elaborate on this. > >> > > >> > Thanks for your help. > >> > > >> > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > >> > news:%23OHx8mxkKHA.2188(a)TK2MSFTNGP04.phx.gbl... > >> >> Total waits by itself is useless. But if all the waits total less than > >> >> 11 > >> >> seconds for an hour that's probably not bad. But we have no way of > >> >> knowing if you filtered out any of the system waits, what was > >> >> happening > >> >> during that time etc. Etc. > >> >> > >> >> -- > >> >> > >> >> Andrew J. Kelly SQL MVP > >> >> Solid Quality Mentors > >> >> > >> >> "RG" <RG(a)discussions.microsoft.com> wrote in message > >> >> news:524F5559-5AE8-4901-B797-9FCD47F066B3(a)microsoft.com... > >> >>> Is 11 seconds of total waits during the course of an hour, > >> >>> negligable? > >> >>> > >> >>> If not, why? > >> >>> > >> >>> Thanks in advance > >> >> > >> > > >> . > >> > . > |