Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cas_o
Posting Yak Master
154 Posts |
 Posted - 2009-10-30 : 08:00:16
|
| I have a large trace file taken during a consulting session with a potential customer experiencing performance issues with their system. It's a Sql Server 2005 Enterprise Edition instance. My theory is contention is their biggest problem. I want to present this as part of a report by showing lock stats and that cpu is a particularly small percent of duration in a lot of transactions indicating big wait times.I saved the trace file as a table. Some bright spark (bonus points for anyone who can supply the reason for this) Decided that for Sql Server 2005 onwards event duration would now be reported in microseconds, but CPU remains in milliseconds. BUT just in case that's not confusing enough the Profiler GUI shows this converted to milliseconds but once you put it in a table it's raw microseconds.[url=http://msdn.microsoft.com/en-us/library/ms175848.aspx]msdn online[/url] suggests in an example "... where CPU < (Duration * 1000) ..."Either I am mad or very stupid shouldn't that be: "... where CPU < (Duration / 1000) ..." ???Anyway the long and short of it is I don't know what I'm looking at and wondered if anyone else had worked this out already. Also I know that the customers database is running in 2k compatibility mode !! so I have no idea if that effects these trace events (prior to Sql Server 2005 duration was in milliseconds) Does anyone know?For bonus tickles I am not alone read the comment at the end of the msdn article :)Finally some interesting trivia: there is a batch complete event in the trace with a duration so long it overflowed bigint when being saved to the table, ending up as -6050 microseconds (or maybe it's milliseconds who knows) therefore I would never have seen it in my analysis but for blind luck during a random scroll (what are the chances my trace is just short of 1 million rows )EDIT: I just realised I have this in the 2008 forum. Doh! sorry. sure some mod will clear it up... hopefully.cas_o AKA: Holly Styles, Paul Alcon ;-]... Quack Waddle |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2009-10-30 : 13:26:37
|
| Thanks tkizer I did find your blog before.What's still unclear is what the deal is when the database is in Sql 2k Compatability mode?I am sure it must be milliseconds. Because otherwise all my batches have durations much much smaller than the CPU time (I am looking at the trace saved to a table not the GUI)cas_o AKA: Holly Styles, Paul Alcon ;-]... Quack Waddle |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-30 : 14:50:44
|
| mine shows microseconds regardless of compatibility mode |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Profiler trace - duration millisecond/microsecond?