October 5, 2011 at 10:53 am
SQLRNNR (10/5/2011)
2 of 3 servers I get arithmetic overflow and the 3rd server differs by 35 seconds.
Yeah great, so do I come back for the second interview or what? 😀
October 5, 2011 at 10:57 am
You get a difference because the query adds the milliseconds since the computer started to the time that SQL started. So if SQL Server started 27 seconds after the computer did, the query will show a 27 second difference from getdate().
This is closer (because it subtracts the tick at which SQL started) but it's still off by a few ms. (about 150 on my machine)
SELECT DATEADD(MILLISECOND, dosi.ms_ticks-dosi.sqlserver_start_time_ms_ticks, dosi.sqlserver_start_time)
FROM sys.dm_os_sys_info AS dosi
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
October 5, 2011 at 10:59 am
SQL Kiwi (10/5/2011)
SQLRNNR (10/5/2011)
2 of 3 servers I get arithmetic overflow and the 3rd server differs by 35 seconds.Yeah great, so do I come back for the second interview or what? 😀
If you want to take that many interviews;-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 5, 2011 at 11:00 am
GilaMonster (10/5/2011)
You get a difference because the query adds the milliseconds since the server started to the time that SQL started. So if SQL Server started 27 seconds after the computer did, the query will show a 27 second difference from getdate().This is closer (because it subtracts the tick at which SQL started, but it's still off by a few ms.
SELECT DATEADD(MILLISECOND, dosi.ms_ticks-dosi.sqlserver_start_time_ms_ticks, dosi.sqlserver_start_time)FROM sys.dm_os_sys_info AS dosi
Lovely info. Maybe we should turn that into a os boot time script or something (with better name).
This could be handy when you get into the 4+ 9s uptime SLA!
That way, in theory, you can figure out the boot time without actually doing one.
October 5, 2011 at 11:03 am
GilaMonster (10/5/2011)
You get a difference because the query adds the milliseconds since the computer started to the time that SQL started. So if SQL Server started 27 seconds after the computer did, the query will show a 27 second difference from getdate().This is closer (because it subtracts the tick at which SQL started) but it's still off by a few ms. (about 150 on my machine)
SELECT DATEADD(MILLISECOND, dosi.ms_ticks-dosi.sqlserver_start_time_ms_ticks, dosi.sqlserver_start_time)FROM sys.dm_os_sys_info AS dosi
Interesting - run it multiple times and I get a range of 0 - 450ms difference. The same is valid without doing the subtraction bit - range there is 20sec to 45sec.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 5, 2011 at 11:33 am
GilaMonster (10/5/2011)
You get a difference because the query adds the milliseconds since the computer started to the time that SQL started. So if SQL Server started 27 seconds after the computer did, the query will show a 27 second difference from getdate().This is closer (because it subtracts the tick at which SQL started) but it's still off by a few ms. (about 150 on my machine)
SELECT DATEADD(MILLISECOND, dosi.ms_ticks-dosi.sqlserver_start_time_ms_ticks, dosi.sqlserver_start_time)FROM sys.dm_os_sys_info AS dosi
Well that's taken the fun out of it. Deep breath and trying again:
SELECT
GETDATE(),
DATEADD(MILLISECOND, dow.wait_resumed_ms_ticks - dow.task_bound_ms_ticks, der.start_time)
FROM sys.dm_os_workers AS dow
JOIN sys.dm_os_tasks AS dot ON
dot.task_address = dow.task_address
JOIN sys.dm_exec_requests AS der ON
der.task_address = dot.task_address
CROSS JOIN sys.dm_os_sys_info AS dosi
WHERE
der.session_id = @@SPID
This really is just for fun (looking at you Remi) but it would probably benefit from Lowell's / 1000 technique too.
October 5, 2011 at 11:36 am
Oh Jeff, look what your "simple" question has done!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 5, 2011 at 11:38 am
SQL Kiwi (10/5/2011)[hr
This really is just for fun (looking at you Remi) but it would probably benefit from Lowell's / 1000 technique too.
I know this is just a geeky thing. Not going to ask thing in any interview (except maybe to test the interviewer :-D).
I just love to strech out as much as possible ;-).
October 5, 2011 at 11:39 am
SQL Kiwi (10/5/2011)
SQLRNNR (10/5/2011)
2 of 3 servers I get arithmetic overflow and the 3rd server differs by 35 seconds.Yeah great, so do I come back for the second interview or what? 😀
Second Interview? Isn't that the time when they go "Sign here for benefits and salary agreements?" Didn't know that was an interview....
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2011 at 11:42 am
Evil Kraig F (10/5/2011)
Second Interview? Isn't that the time when they go "Sign here for benefits and salary agreements?" Didn't know that was an interview....
No idea. Never been asked back after the first one 😉
@stefan: Yes you'll find Jeff is responsible for most of the code that appears on the Thread (ha ha)
October 5, 2011 at 1:12 pm
SQL Kiwi (10/5/2011)
GilaMonster (10/5/2011)
You get a difference because the query adds the milliseconds since the computer started to the time that SQL started. So if SQL Server started 27 seconds after the computer did, the query will show a 27 second difference from getdate().This is closer (because it subtracts the tick at which SQL started) but it's still off by a few ms. (about 150 on my machine)
SELECT DATEADD(MILLISECOND, dosi.ms_ticks-dosi.sqlserver_start_time_ms_ticks, dosi.sqlserver_start_time)FROM sys.dm_os_sys_info AS dosi
Well that's taken the fun out of it. Deep breath and trying again:
SELECT
GETDATE(),
DATEADD(MILLISECOND, dow.wait_resumed_ms_ticks - dow.task_bound_ms_ticks, der.start_time)
FROM sys.dm_os_workers AS dow
JOIN sys.dm_os_tasks AS dot ON
dot.task_address = dow.task_address
JOIN sys.dm_exec_requests AS der ON
der.task_address = dot.task_address
CROSS JOIN sys.dm_os_sys_info AS dosi
WHERE
der.session_id = @@SPID
This really is just for fun (looking at you Remi) but it would probably benefit from Lowell's / 1000 technique too.
Still isn't the same everytime 😀
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 5, 2011 at 1:14 pm
Jack Corbett (10/5/2011)
Still isn't the same everytime 😀
I give up! 😛
October 5, 2011 at 1:16 pm
Jack Corbett (10/5/2011)
SQL Kiwi (10/5/2011)
GilaMonster (10/5/2011)
You get a difference because the query adds the milliseconds since the computer started to the time that SQL started. So if SQL Server started 27 seconds after the computer did, the query will show a 27 second difference from getdate().This is closer (because it subtracts the tick at which SQL started) but it's still off by a few ms. (about 150 on my machine)
SELECT DATEADD(MILLISECOND, dosi.ms_ticks-dosi.sqlserver_start_time_ms_ticks, dosi.sqlserver_start_time)FROM sys.dm_os_sys_info AS dosi
Well that's taken the fun out of it. Deep breath and trying again:
SELECT
GETDATE(),
DATEADD(MILLISECOND, dow.wait_resumed_ms_ticks - dow.task_bound_ms_ticks, der.start_time)
FROM sys.dm_os_workers AS dow
JOIN sys.dm_os_tasks AS dot ON
dot.task_address = dow.task_address
JOIN sys.dm_exec_requests AS der ON
der.task_address = dot.task_address
CROSS JOIN sys.dm_os_sys_info AS dosi
WHERE
der.session_id = @@SPID
This really is just for fun (looking at you Remi) but it would probably benefit from Lowell's / 1000 technique too.
Still isn't the same everytime 😀
Well of course not, time passes! 😀
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 5, 2011 at 1:38 pm
instance time v os time? Sheesh, is there a technical discussion taking place again?
October 5, 2011 at 1:40 pm
Steve Jones - SSC Editor (10/5/2011)
instance time v os time? Sheesh, is there a technical discussion taking place again?
Perhaps, but it is in the guise of a smartass discussion.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 30,646 through 30,660 (of 66,742 total)
You must be logged in to reply to this topic. Login to reply