Tuesday, 17 June 2008

SQL Profiler & Duration - 2000 VS 2005

I'm not a DBA by any stretch, but like to think I know a thing or two when it comes to Microsoft SQL Server as I have used it extensively in just about all of my work experiences.

With that in mind, I wrote a basic insert statement wrapped in a stored procedure. Ran a load against it and watched SQL Profiler. To my surprise, the duration on the query was 64000+.

However, after a little research I found the reason. It was not query design, database schema, RAM, storage, etc. etc. but rather a change to the metric used in SQL Profiler for duration.

In the SQL Profiler that shipped SQL Server 2000, duration was recorded in milliseconds. BUT, in the SQL Profiler that shipps with SQL Server 2005 duration is recorded in microseconds. 64k microsecods worked out to about .06 seconds. I can live with that.

Here is the MSDN article that set me straight.

http://msdn.microsoft.com/en-us/library/ms175848.aspx

No comments:

Post a Comment