Activity overview
Latest activity by Ben C
Just to update on this issue: the behaviour has been changed for the next version of SQL Monitor (2.3). If SQL Server gives us an invalid local time (e.g. 01:13 when the clocks actually jumped from 00:59 to 02:00) we now find the next valid time (e.g. 02:00) and convert that to UTC instead. We decided that this behaviour was better than the alternative of just dropping the data. / comments
Just to update on this issue: the behaviour has been changed for the next version of SQL Monitor (2.3). If SQL Server gives us an invalid local time (e.g. 01:13 when the clocks actually jumped from...
Hi Jeff
For reliability, SQL Monitor internally represents all times in UTC. This has several advantages:
- Times from different servers can easily be compared with each other (as long as your clocks are synchronized!)
- UTC does not have DST so there are no ambiguous times (when time repeats itself in the fall) or invalid times (when time jumps ahead in the spring)
Unfortunately, some sources of data only give us local times, and we have to convert these to UTC before storing them. While UTC to local conversion always succeeds, local to UTC conversion will fail if the data source provides us with an invalid time.
Most of the timestamped data we collect from SQL Server is subject to this conversion, including SQL Server error log events, job history, backup history, integrity check history, etc.
For example, one of my SQL Server instances has a job which it says ran at 2010-28-03 01:00:00 (GMT Standard Time). However, this time never existed, as the clocks actually went from 00:59:59 to 02:00:00 on that date.
When we encounter such data there are two approaches that we could take: either guess which of 00:59:59 and 02:00:00 SQL Server meant, or just drop the data. We currently just drop the data.
This should not result in a permanent error, but there was a bug in the SQL Server error log reader in 2.2 that prevented us from reading further error log lines. The bug has already been fixed for 2.3, but it should correct itself when the error log cycles. This might be what happened to you.
Does that make sense?
Thanks
Ben / comments
Hi Jeff
For reliability, SQL Monitor internally represents all times in UTC. This has several advantages:
- Times from different servers can easily be compared with each other (as long as your cloc...
We try to account for the network latency in the clock skew measurement.
Even so, as the default threshold is 15 seconds, it seems unlikely that network latency would contribute much to the skew.
Thanks
Ben / comments
We try to account for the network latency in the clock skew measurement.
Even so, as the default threshold is 15 seconds, it seems unlikely that network latency would contribute much to the skew.
T...
Hi Stephen
Good to hear that it has improved.
If opening the Long Running Query alert is much faster the second time round, it sounds like the second page load is benefiting from SQL Server's cache. The first page load should probably be faster than that, but if you were loading data for a historical alert it had probably dropped out of the SQL cache. The SQL process data is quite hard to cache as the rows are quite wide (lots of strings).
I am also going away until the New Year. Could you send an email to support@red-gate.com and mention my name (Ben Challenor) please? Then I or someone else on the team can continue the investigation.
Thanks, and happy Christmas
Ben / comments
Hi Stephen
Good to hear that it has improved.
If opening the Long Running Query alert is much faster the second time round, it sounds like the second page load is benefiting from SQL Server's cache...
Hi PDinCA
We might have to ask for an ANTS Performance Profiler trace of your installation, but first, the following questions would help narrow down the problem.
Where do you have the Base Monitor and Data Repository installed? Are they also on your IIS machine? What is the CPU utilization of RedGate.Response.Engine.Alerting.Base.Service.exe and the repository SQL Server - do they show the same spike when you use the UI?
Is the rest of the UI slow or is it just the alert pages?
Did you change any part of your configuration (other than the 2.0 to 2.1 upgrade) before these performance problems occurred?
Thanks
Ben / comments
Hi PDinCA
We might have to ask for an ANTS Performance Profiler trace of your installation, but first, the following questions would help narrow down the problem.
Where do you have the Base Monitor...
Hi Rhys
There was a bug in long running query alerting in the 2.0 EAPs but it should have been fixed from 2.0 onwards.
Every time we see a running query, we make a note of the date. The duration of the query is simply the difference between the current date and the first date that we saw the query.
We determine that it is the same query by using a combination of the process ID, the login date, and the last batch date.
Hope this helps.
Ben / comments
Hi Rhys
There was a bug in long running query alerting in the 2.0 EAPs but it should have been fixed from 2.0 onwards.
Every time we see a running query, we make a note of the date. The duration of...
The script turned out to be quite long. Please email me at ben.challenor@red-gate.com to get it.
Thanks
Ben / comments
The script turned out to be quite long. Please email me at ben.challenor@red-gate.com to get it.
Thanks
Ben
Hi
We do not collect any information from the Physical Disk counters.
We currently have a bug open for excluding "HarddiskVolume1" from the overviews - is this the same system partition that you are seeing?
Thanks
Ben / comments
Hi
We do not collect any information from the Physical Disk counters.
We currently have a bug open for excluding "HarddiskVolume1" from the overviews - is this the same system partition that you ar...
Hi
There are several different uptime estimates that we could make:
1) Uptime of the machine (based on whether we could ping it)
2) Uptime of the SQL instance (based on whether we could execute SQL)
3) Uptime of the SQL instance (based on whether the service was running)
However, the estimate would only be valid if SQL Monitor was running successfully over the same time period. If SQL Monitor or the machine running SQL Monitor failed then the value would be incorrect.
If that is OK, we could suggest some SQL to calculate one or more of these estimates. Which would be most useful?
Thanks
Ben / comments
Hi
There are several different uptime estimates that we could make:
1) Uptime of the machine (based on whether we could ping it)
2) Uptime of the SQL instance (based on whether we could execute SQL...
Though you can't add or remove COPY_ONLY in the UI, SQL Backup 6 will now recognize manually scripted jobs with this keyword and allow you to open them in the UI. It will preserve the COPY_ONLY keyword as it was scripted.
This applies to most of the other keywords too, e.g. NAME, DESCRIPTION, etc. / comments
Though you can't add or remove COPY_ONLY in the UI, SQL Backup 6 will now recognize manually scripted jobs with this keyword and allow you to open them in the UI. It will preserve the COPY_ONLY key...