SQL Profiler 2005 and Duration Data Column

Wed Oct 4, 2006 by Tara Kizer in sql-server-database-administration

For those of you who have been using SQL Profiler 2005 for a while, you probably are already aware of this. We've been using SQL Server 2005 for a few months now, but I hadn't touched SQL Profiler 2005 very much yet. Today I had to run a trace to check if any of the queries in our performance environment were taking too long. I always save the trace data to a table, so that I can easily run queries against it. Here is my typical query:

SELECT TOP 100 Duration, TextData
FROM TraceTable
WHERE TextData IS NOT NULL
ORDER BY Duration DESC

At first I was shocked to see Duration values of 1879000. In SQL Server 2000, this would have been 1879 seconds since Duration is stored in milliseconds. That's over 30 minutes! This was shocking to see, so I decided to check SQL Server 2005 Books Online. According to 2005 BOL, Duration is now stored in microseconds. Due to this, my query times were all under two seconds as expected.

Legacy Comments


pankaj 2006-10-13	re: SQL Profiler 2005 and Duration Data Column Same happened with can you provide me the link from where you come to know that sql server 2005 duration stored in microseconds

Tara 2006-10-13	re: SQL Profiler 2005 and Duration Data Column Just like my post says, you can find this information in SQL Server 2005 Books Online. If you installed the client tools for 2005, then you have BOL on your machine already.

Mladen Andrijasevic 2006-10-26	re: SQL Profiler 2005 and Duration Data Column I find it rather strange that the duration is now stored in microseconds in the profiler trace whereas the accuracy of datetime remains 3.33 milliseconds

Anders Pedersen 2006-12-14	re: SQL Profiler 2005 and Duration Data Column BOL for 2005 has different informatin depending on where you look, in "Viewing and Analyzing Traces with SQL Server Profiler " is is correct, but when they talk about the set events in "sp_trace_setevent (Transact-SQL)" it is incorrect. Also need at least the December 2005 update to BOL to have the information corrected.