January 24, 2018 at 1:29 am
Currently I have two distinct periods of the working day. 6AM-6PM "working hours", 6PM-6AM "out-of-hours".
Because of this, I have a sizable window to run resource intensive tasks outside of working hours, like CHECKDB, full backups, ETLs, etc.
Due to these two distinct working periods, I currently collect two different "sets" of wait stats; in-hours and out-of-hours. I use one of Paul Randall's scripts to collect wait stats.
However, after collecting my wait stats I run DBCC SQLPERF to clear my waits stats for the start of the next period.
I was recently listening to Brent Ozar's GroupBy session, where he talks about using his sp_BlitzFirst scripts to collect wait stats. One of his parameters is @SinceStartUp = 1, where it, obviously, looks at the total wait stats since start up. sp_Blitz also has a warning in to say 'somebody has run DBCC SQLPERF'.
So to the question... what are the risks in resetting wait stats?
I appreciate that the standard response is "it depends on the situation", but I feel that my current working periods allow for me to take these distinct measures.
If there were any contention, I would think twice about resetting the stats so often, and look to take deltas to calculate values for each time period.
I am also the only DBA so there is no risk of effecting other peoples work... or my own for that matter!
What do other people do?
I've been in my current post for sometime, so have been using this method for a few years now, but I'm always keen to hear other people's methods and experiences.
January 24, 2018 at 4:32 am
So, collective wait stats, everything since the dawn of time, are somewhat interesting, but not terribly useful really. They will tell you where the principal waits are on your system as a whole. But that's all. They don't tell you when or where those waits are occurring.
Wait statistics get really interesting when you do one of two things. Capture them before and after X (you define X). Use extended events to capture them during X (you define X). Capturing them, for example, at the start and end of your work day will give you specific information about the work day. Capturing them before and after a data load will give you specific information about that data load. Etc.
So, you're right, it depends, but I'd say it's not even remotely a big deal to reset them. As long as you know that you're tossing that "dawn of time" measure, which, again, I don't find all that useful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 24, 2018 at 5:06 am
I don't reset them when doing monitoring. I record them at intervals and calculate what changed in those intervals
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2018 at 6:11 am
Thanks both for your response.
I guess part of it is down to administrator preference, but so long as one can rationalise their behaviour each method has its pluses and minuses.
And, as always, it depends on what the situation demands at the time...
January 24, 2018 at 7:03 am
Keep in mind that if you have any monitoring tools that calculate the change in wait stats over periods in time, clearing them will break the tool.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply