Using Current_timestamp or sysdatetime

Question

Post reply

Using Current_timestamp or sysdatetime

mike 57299

SSCrazy

Points: 2529
More actions
June 24, 2010 at 1:18 pm

#234250

AHi All:
When you run the code below, you get the same datetime for each line. Is there a way to get it to refresh? I am trying to find the sql in a stored proc that is slow. Also, same issue occurs if you substitute sysdatetime() for current_timestamp.
Code:
declare @x datetime2
set @x = CURRENT_TIMESTAMP
print '1:' + convert(char(30),current_timestamp)
set @x = CURRENT_TIMESTAMP
print '2:' + convert(char(30),current_timestamp)
set @x = CURRENT_TIMESTAMP
print '3:' + convert(char(30),current_timestamp)
set @x = CURRENT_TIMESTAMP
print '4:' + convert(char(30),current_timestamp)
set @x = CURRENT_TIMESTAMP
print '5:' + convert(char(30),current_timestamp)
set @x = CURRENT_TIMESTAMP
print '6:' + convert(char(30),current_timestamp)
My results:
1:Jun 24 2010 12:16PM
2:Jun 24 2010 12:16PM
3:Jun 24 2010 12:16PM
4:Jun 24 2010 12:16PM
5:Jun 24 2010 12:16PM
6:Jun 24 2010 12:16PM
Thank you All!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

You can't really expect the value to change - the query will complete before it could....

First, I will help you a little... add ",109" to the convert to show to the millisecond level and add a delay into your test query to see the value change...

Convert the datetime value to char, including milliseconds....

CONVERT(char(30),getdate(),109)

Add a delay...

WAITFOR DELAY '00:00:01'

Now, the tools you really want to use are Profiler - which will show you the duration of each statement in the stored proc (as long as you select to capture SP:StmtCompleted) and SSMS - Show actual execution plan - which will show you the cost of each statement in the stored proc after you run it.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Johan Bijnens SSC Guru Points: 135506 More actions · Answer 2

Use a format parameter with convert !

Code:

declare @x datetime2

set @x = CURRENT_TIMESTAMP

print '1:' + convert(char(30),current_timestamp, 121)

check books online http://msdn.microsoft.com/en-us/library/ms187928.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

I guess I am a little confused by what you mean by refresh? based on the query you are running I would suspect that it would take so little time that the value would never be different. now you may be able to get a unique value on each line by adding in the nano seconds but I am not sure that is what you are talking about. could you clarify the refresh request?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

mike 57299 SSCrazy Points: 2529 More actions · Answer 4

mister.magoo:

I don't understand how to use the profiler. Could you point me to a good resource.

Thanks.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

mike 57299 (6/24/2010)
mister.magoo:
I don't understand how to use the profiler. Could you point me to a good resource.
Thanks.

Not really, sorry... I just learnt by trial and error. It's not something I think I should advise anyone else on in case I give them bad habits, but I will show the basics because they are simple :-

1) start a query session in SSMS to run your stored proc and note the SPID

2) launch SQL Profiler from your menu and start a new trace

3) modify the trace to include Stored Procedures -> SP:StmtCompleted

4) add a filter on SPID to only include the SPID of the query session in which you will run the stored proc.

5) start the trace

6) run the stored proc

7) stop the trace

8) review the trace to see which statements took the longest

See below for a quick shot of the elements I mentioned here:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Using Current_timestamp or sysdatetime

Cookies on SQLServerCentral