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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s