SQL Server Profiler: 2005 vs. 2000

As I explained in "Duration and CPU Values" SQL Server Profiler captures duration times in microseconds and milliseconds. How you work with Profiler is different in SQL Server 2005 and 2000. Profiler aficionados know that Profiler running under SQL Server 2000 never shows a duration value that's between 1ms and 9ms, but you might see a 0 or a 10 and higher (for an explanation of why, see "Granular Timing Statistics From Profiler," August 2005). In contrast, Profiler running under SQL Server 2005 does capture duration values between 1ms and 9ms. To test this concept, you can play around with the following SQL batch code:

DECLARE @LoopMe int,@StopAt intSET @LoopMe = 1SET @StopAt = 425 WHILE @LoopMe < = @StopAt  SET @LoopMe = @LoopMe+1

This code is designed to make it easy for you to vary how long it should take to run a SQL batch. Simply vary the @StopAt column and run several tests.When I played around with this batch code under SQL Server 2005, I was able to generate duration values that included 1, 5, 6, 7, 8, and 9 milliseconds. Interestingly, the CPU value in SQL Server 2005 is still limited to values of 0ms or 10ms and higher.

You might wonder if anyone really needs to see values at that level of granularity in Profiler data.The short answer is yes.For example, let's say ProcedureXYZ runs fast—it consistently takes 9ms. Sure, that's fast, but let's also assume that the procedure is going to scan a table that has a few hundred rows,so you add an index to improve the runtime to about 1ms. Adding an index might not matter in most cases, but what if you're running ProcedureXYZ 100 times per second? Well, 100 executions per second times 9ms per execution means you're using 900ms of duration time per second. That's 90 percent of your CPU on a single-processor box and 45 percent on a dual-processor machine. Adding the index gets processing time down to 1ms per execution and results in dramatically lower CPU utilization. I, for one, will put the extra duration granularity in Profiler to good use. I hope you do too!