September 28, 2007 at 7:25 am
Based on the foillowing information published by Microsoft, am I correct to render SQL 2005 trace DURATION by dividing the DURATION value by 1,000,000 - to render seconds:
http://msdn2.microsoft.com/en-us/library/ms175848.aspx
In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.
**** My trace process includes:
Start Server side trace at 7am (via sp_trace_create)
Server side Trace auto-stops at 5pm
Use fn_trace_gettable to load SQL 2005 trace data into SQL 2005 table
The DURATION stored in the SQL 2005 table should be presented as DURATION\1,000,000 to reflect seconds, correct?
September 28, 2007 at 11:59 am
Yes Whe you review the "table" the number is in MICROSECONDS and you must divide by 1000000 (one million) Profiler UI is being nice to you and showing it in MILLISECONDS so that you get the numbers in a consistent unit.
This was done because M$ wanted to give DBA tunning opportunities on those queries that even though the duration was very small the frequency was pretty high.
Cheers,
* Noel
September 28, 2007 at 12:11 pm
Noel - thx for the feedback. Just wanted to make sure I read this correctly.
(Are you sure Microsoft didn't do this to catch up w/ DB2 and Oracle - of which both DBMS's have been using microseconds for the last 20+ years.. lol)
September 28, 2007 at 12:25 pm
DBASkippack (9/28/2007)
Noel - thx for the feedback. Just wanted to make sure I read this correctly.(Are you sure Microsoft didn't do this to catch up w/ DB2 and Oracle - of which both DBMS's have been using microseconds for the last 20+ years.. lol)
Yes I know the feeling ... 😀
But If you have ever developed DB2 stored procedures and tried to move them from DEV to TEST to PROD you would definitely have to thank M$ for how easy they made it 😉
I am not going to say that M$ is not catching up... because they *ARE*. But not on everything. There are others also playing catch up. DB2 Viper Version 9 is introducing some self tunning features ( years way back since M$ had that ) 😀
* Noel
September 29, 2007 at 2:51 am
There's an option in the profiler gui that sets whether the duration displays as milliseconds or microseconds. It measures in microseconds regardless, as do some of the performance related DMVs
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
September 29, 2007 at 5:09 am
Ya that's a cool feature. Do you know if there are some whitepapers on Microsecond tuning? I was thinking that Hala from MySpace.com could come up with one, but I don't think there's one at this point.
October 1, 2007 at 11:14 am
Actually microsecond tunning is the as millisecond tunning the actual thing is that previously a very large amount of very low duration queries would escape under the radar and now you can catch those and try to squeeze milliseconds out of them. The real thing is that all is tied with Batch/sec counter and you could use the counter to trigger an sqltrace recording.
Cheers,
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply