Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

SQL Profiler 2005 and Duration Data Column

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.

Print | posted on Wednesday, October 04, 2006 11:46 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# 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
10/13/2006 3:28 AM | pankaj
Gravatar

# 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.
10/13/2006 9:43 AM | Tara
Gravatar

# 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
10/26/2006 6:41 AM | Mladen Andrijasevic
Gravatar

# 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.
12/14/2006 8:44 AM | Anders Pedersen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET