Is there a time discrepancy in the SQL to get the current time?

Question

Typically, when there are multiple web servers running under a load balancer, the current time on server (A) and server (B) may not be identical. This is due to a potential drift in the Network Time Protocol (NTP). Therefore, if a Web API is developed to retrieve the current server time, the API responses are expected to be unstable.

It is hypothesized that the Azure SQL Database may have a similar instance configuration to increase its availability. If that's the case, I'm considering whether the result of an SQL command that retrieves the current time, such as SYSDATETIMEOFFSET, could contain some degree of error.

Question

1. Can we confirm if this speculation is correct?
2. If it is, how can we estimate potential time discrepancies?

Answer 1

Computers use a tick counter and a hardware timer to keep track of the time. On a regular basis they will sync the calculated time to internet time.

as latency is involved in getting internet time, it is not exact.

while there are redundant servers, azure sql queries are always a single instance. So if your webapi call azure sql to get the time they will be consistent source, but off by different latencies.

the server time on the webapi servers will be close but not exactly match. but again latency will probably hide the difference.

Answer 2

It's even worse. You can get this time difference even on a single computer. I've more than once run performance tests with things like:

DECLARE @d = sysdatetime()
-- Run something
SELECT datediff(ms, @d, sysdatetime())

And the result has come back with a negative value. My assumption is that there is a certain amount of clock drift between different cores, not the least when you have a balanced power plan.

Now, I don't know how big the time drift is in the case of web servers that you mention, but since in case we are talking about distinct computers, I would not be surprised to hear that they can be seconds apart. The differences I have observed inside a single instance of SQL Server have been in the range of a few milliseconds.

When it comes to Azure SQL Database, that is more akin to the case of a single computer. Your Azure SQL database is served by one computer at the time. Well, at least in the simple case. If you set up read-only replicas, it is a different matter. Then again, you have two individual computers that could drift away from each other.