Wait Stats on SQL2005
in [Server Configuration]
|
Prev: Database Mirroring and Insert Statement
Next: BCP
From: RG on 11 Jan 2010 18:07 Is 11 seconds of total waits during the course of an hour, negligable? If not, why? Thanks in advance
From: Andrew J. Kelly on 11 Jan 2010 18:56 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 12 Jan 2010 00:17 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 12 Jan 2010 11:38 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 12 Jan 2010 14:16
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 > >> > > > . > |