Performance Trace in SQL 2005

I’ve always used a server side trace to keep track of what’s going on for my servers. It’s something that helps me to answer when someone says what’s going on the server. Typically I have this track everything that takes over 2 seconds. It depends on the environment and the load as to what I actually use. I’m not going to go through my whole method in this post but I wanted to point out an interesting fact I recently learned on SQL 2005 different from SQL 2000. The duration column is stored in MicroSeconds when you write it to a Table or file. It’s no longer stored in Milliseconds. So in 2000 we could do this.

SQL 2000
Select duration * .0001 as Seconds from PerformanceTraceTable

SQL 2005
Select duration * .000001 as Seconds from PerformanceTraceTable

You will not see this when running in profiler GUI interface since it converts to milliseconds for you. It’s only an issue if you save the data down to a table or file when you run a server side trace.

I’m also finding some negative durations which I’m still looking into but will post more when I find more information on those.


Happy tracing!