October 4, 2017 at 11:43 am
I recently started to use datetime2 data type in SQL Server 2012, and found something interesting. The time value in last 3 or 4 digits tends to be the same. See my data sample below, and they all ended at 5911. The value was retrieved using sysdatetime(). Any logic explanations on this?
Updated Date
2017-10-04 10:57:14.6625911
2017-10-04 11:00:05.7925911
2017-10-04 11:02:31.8865911
2017-10-04 11:06:06.1565911
2017-10-04 11:07:07.3915911
2017-10-04 11:09:00.0495911
2017-10-04 11:10:59.2585911
2017-10-04 11:14:28.3485911
2017-10-04 11:16:16.7225911
2017-10-04 12:09:10.3245911
2017-10-04 12:10:49.6695911
2017-10-04 12:12:29.8375911
2017-10-04 12:17:34.7585911
2017-10-04 12:18:13.2455911
2017-10-04 12:21:15.6745911
2017-10-04 12:21:15.6795911
2017-10-04 12:39:37.5655911
2017-10-04 12:41:49.1635911
2017-10-04 12:47:29.1965911
2017-10-04 12:50:21.8815911
2017-10-04 13:03:51.2255911
2017-10-04 13:15:12.3915911
2017-10-04 13:15:12.3935911
October 4, 2017 at 12:03 pm
jay-125866 - Wednesday, October 4, 2017 11:43 AMI recently started to use datetime2 data type in SQL Server 2012, and found something interesting. The time value in last 3 or 4 digits tends to be the same. See my data sample below, and they all ended at 5911. The value was retrieved using sysdatetime(). Any logic explanations on this?
I've never noticed that before. I was able to replicate it on 2014 (only repeating last 3 digits).
October 4, 2017 at 12:10 pm
Interesting. What exact T-SQL is producing this result? I just tried select on sysdatetime(), and never repeated the last 3 or 4 digits, and then I tried converting it to datetime2(7), and still wasn't getting that kind of result.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 12:15 pm
This is what I tried.
CREATE TABLE #Dates( SomeDate datetime2)
DECLARE @n int = 0;
WHILE @n < 10000
BEGIN
INSERT INTO #Dates VALUES(SYSDATETIME())
SET @n = @n+1;
WAITFOR DELAY '00:00:00.001'
END
SELECT DISTINCT *
FROM #Dates
GO
DROP TABLE #Dates
October 4, 2017 at 12:16 pm
[/quot
Here is the code and the data is populated from a web app.
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbProduct](
[Primary_Key] [int] IDENTITY(1,1) NOT NULL,
[ProductionID] [int] NOT NULL,
[ProductName] [varchar](50) NOT NULL,
[ProductStatus] [varchar](50) NOT NULL,
[LastUpdated] [datetime2](7) NOT NULL,
CONSTRAINT [PK_tbProduct] PRIMARY KEY CLUSTERED
(
[Primary_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbProduct] ADD CONSTRAINT [DF_tbProduct_LastUpdated] DEFAULT (sysdatetime()) FOR [LastUpdated]
GO
October 4, 2017 at 12:30 pm
Figuring I might as well post the @@VERSION value for the server I'm working on:
Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)
Aug 17 2017 12:07:38
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )mptoms founds
EDIT: No symptoms found on this server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 12:41 pm
Hmm.... I wasn't able to get that to happen in 2016 or 2008.
October 4, 2017 at 3:18 pm
Luis Cazares - Wednesday, October 4, 2017 12:15 PMThis is what I tried.
CREATE TABLE #Dates( SomeDate datetime2)DECLARE @n int = 0;
WHILE @n < 10000
BEGIN
INSERT INTO #Dates VALUES(SYSDATETIME())
SET @n = @n+1;
WAITFOR DELAY '00:00:00.001'
ENDSELECT DISTINCT *
FROM #DatesGO
DROP TABLE #Dates
I tried your script earlier and was able to produce the records with same last 4-digit value. And then later when I tried to show it to my colleagues, I was unable to re-produce it anymore. Is it possible a server cache issue?
October 4, 2017 at 4:22 pm
ZZartin - Wednesday, October 4, 2017 12:41 PMHmm.... I wasn't able to get that to happen in 2016 or 2008.
Me neither. No luck on 2008R2, 2012, 2014.
October 5, 2017 at 7:07 am
Checked production database this morning, and found total of 242 records. Only the first 141 rows showed the same last 4-digit value. After that, no more "same-value" issues.
Also talked to DBA, and they did not re-boot the server when changing the data type. Not sure if it would made the difference.
Our server is Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
October 5, 2017 at 7:15 am
jay-125866 - Wednesday, October 4, 2017 3:18 PMI tried your script earlier and was able to produce the records with same last 4-digit value. And then later when I tried to show it to my colleagues, I was unable to re-produce it anymore. Is it possible a server cache issue?
I tried it again today and it's not happening any more. Something weird might happen at times.
October 5, 2017 at 8:02 am
Luis Cazares - Thursday, October 5, 2017 7:15 AMjay-125866 - Wednesday, October 4, 2017 3:18 PMI tried your script earlier and was able to produce the records with same last 4-digit value. And then later when I tried to show it to my colleagues, I was unable to re-produce it anymore. Is it possible a server cache issue?
I tried it again today and it's not happening any more. Something weird might happen at times.
It is still happening. Is it due to some refresh interval for sysdatetime? To make sure it is not the impact of batch, i used a function to get the value of SYSDATETIME. the value changes after certain interval, dont know what is the interval. extended the script you provided. Tested in SQL2016
USE Test
GO
CREATE FUNCTION getCurSysDt()
RETURNS datetime2
AS
BEGIN
--DECLARE @DT1 AS DATETIME2 = SYSDATETIME()
RETURN SYSDATETIME();
END
GO
CREATE TABLE #Dates( SomeDate1 datetime2, SomeDate2 datetime2, SomeDate3 datetime2)
DECLARE @n int = 0;
DECLARE @dt1 as datetime2 = SYSDATETIME()
WHILE @n < 500
BEGIN
INSERT INTO #Dates VALUES(SYSDATETIME(),@dt1, dbo.getCurSysDt())
SET @n = @n+1;
SET @Dt1 = dateadd(millisecond,1,@dt1)
WAITFOR DELAY '00:00:00.001'
END
SELECT *
FROM #Dates
GO
--clean up
DROP TABLE #Dates
DROP FUNCTION getCurSysDt
October 5, 2017 at 8:11 am
Avi1 - Thursday, October 5, 2017 8:02 AMLuis Cazares - Thursday, October 5, 2017 7:15 AMjay-125866 - Wednesday, October 4, 2017 3:18 PMI tried your script earlier and was able to produce the records with same last 4-digit value. And then later when I tried to show it to my colleagues, I was unable to re-produce it anymore. Is it possible a server cache issue?
I tried it again today and it's not happening any more. Something weird might happen at times.
It is still happening. Is it due to some refresh interval for sysdatetime? To make sure it is not the impact of batch, i used a function to get the value of SYSDATETIME. the value changes after certain interval, dont know what is the interval. extended the script you provided. Tested in SQL2016
And the behavior is back. This is all a mystery for me.
October 5, 2017 at 8:36 am
Luis Cazares - Thursday, October 5, 2017 8:11 AMAvi1 - Thursday, October 5, 2017 8:02 AMLuis Cazares - Thursday, October 5, 2017 7:15 AMjay-125866 - Wednesday, October 4, 2017 3:18 PMI tried your script earlier and was able to produce the records with same last 4-digit value. And then later when I tried to show it to my colleagues, I was unable to re-produce it anymore. Is it possible a server cache issue?
I tried it again today and it's not happening any more. Something weird might happen at times.
It is still happening. Is it due to some refresh interval for sysdatetime? To make sure it is not the impact of batch, i used a function to get the value of SYSDATETIME. the value changes after certain interval, dont know what is the interval. extended the script you provided. Tested in SQL2016
And the behavior is back. This is all a mystery for me.
That's really strange. I still remember the days before there was a separate clock chip, and if the cpu was busy enough, the clock wouldn't be able to keep proper time, but that was back in the early PC days. Back then there was an OS routine that would update the time value, but I have to believe they would have externalized the circuitry necessary and made the clock info readable from either an I/O port or a memory mapped location. Unless your clock chip on your motherboard is overheating and thus acting strangely, it's really odd that it could, even in an overheating scenario, behave in any kind of controlled manner unless there are specific bits that have either opened or shorted circuitry that is forcing specific bit values. Of course, that would likely be a permanent condition as opposed to an occasional one.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2017 at 8:37 am
I tried this on SQL 2008 R2, 2012 and 2016 (slightly modified script) and could not reproduce this.
My script was a lot more simple:SELECT SYSDATETIME()
GO 5
harder to post results into this window, but I had no visible pattern. I even tried 3 different SSMS versions (2008, 2012 and 2016) just in case it was SSMS doing some odd conversion. I cannot seem to reproduce this.
EDIT - sorry... replied before refreshing. Loaded this thread yesterday and got busy. Ignore my post.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply