How are the queries gathered for each wait type?
BrentOzar
Posts: 2
Congrats on v4, looks cool - especially the wait stats stuff. Reading the documentation though, something struck me as odd in this section:
http://documentation.red-gate.com/displ ... tedqueries
It's saying that for the server's top waits during a time span, I can see the affected queries. However, in that screenshot, it shows queries with durations of 6ms-7ms. If you were able to capture both that query's wait, AND the duration, that would imply that you're sampling at least every 5-6 milliseconds.
How often is this sampling the waiting queries? I'm guessing that what it's really doing is sampling every X minutes, looking at the queries waiting at that exact moment, and then saying they were always waiting on that particular wait. But if the queries aren't caught at the exact moment of sampling, they won't show up - and if we're only sampling every X minutes, it's pretty likely that we're not going to catch the biggest culprit queries just on a random basis.
Am I reading too much into this, or is that really how it works? I read through the installation instructions and it looks like there's no agent on the machine, and I don't think RG would really run DMV queries every 5ms.
http://documentation.red-gate.com/displ ... tedqueries
It's saying that for the server's top waits during a time span, I can see the affected queries. However, in that screenshot, it shows queries with durations of 6ms-7ms. If you were able to capture both that query's wait, AND the duration, that would imply that you're sampling at least every 5-6 milliseconds.
How often is this sampling the waiting queries? I'm guessing that what it's really doing is sampling every X minutes, looking at the queries waiting at that exact moment, and then saying they were always waiting on that particular wait. But if the queries aren't caught at the exact moment of sampling, they won't show up - and if we're only sampling every X minutes, it's pretty likely that we're not going to catch the biggest culprit queries just on a random basis.
Am I reading too much into this, or is that really how it works? I read through the installation instructions and it looks like there's no agent on the machine, and I don't think RG would really run DMV queries every 5ms.
Comments
Thanks for the questions. Let me try to shed some further light:
Firstly, I can confirm that there is no agent installed on the monitored servers.
The 6-7ms values you've referenced aren't waits, but are the average duration per query. These are generated statistically, so don't require sampling at a very high frequency: for eg, if we burn 6 seconds of CPU on a query that runs 1,000 times in a minute, we’d generate a Duration value of 6ms in the "Affected queries" table.
We're not sampling waits every 5ms, but they are sampled very frequently. We're combining data from different places and with different sampling frequencies to generate the data. In general we're careful to show where the data is estimated because it's sampled, particular around wait times. We've done lots of testing, and we’re confident our approach will have minimal impact on performance.
Happy to talk this through in more detail, but would need to take this offline, because it's too complex for a forum exchange, and strays into areas of commercial sensitivity. Message me back if you want to dig into this further.
Cheers,
Colin.
as I could see in code (caught in dm_exec_requests) the frequency is 200ms (run in 5 minutes cycles).
Is it possible to configure this frequency or the length of cycle?
It is not only quite often from my point of view but it also influences the output (like long running queries or even wait stats itself) of other monitoring tools we use (scom or sql sentry which is used for heavily loaded databases).
USE [RedGateMonitor] UPDATE [settings].[KeyValuePairs] SET KeyValue = 500 WHERE KeyName = 'WaitStatsCollection-QueryWaitInterval'
... would change the default from 200ms to 500ms (for example).Note that you need to restart the "SQL Monitor 4 Base Monitor" service for this change to take effect.