SQL Server Profiler is a great tool for analyzing performance but I never see CPU or duration values between 0 and 10 ms—zero shows up and ten shows up, but nothing in between. Is there a way to get more granular timing for online transaction processing (OLTP) systems that do a massive number of fast queries?
Related: SQL Server Profiler: 2005 vs. 2000
Readers might wonder why they should worry about statements that are so fast they take only 0 to 10 ms. In many cases, you can safely ignore the effect of statements in this performance range. But sometimes (and it happens more often than you might think) the performance of your system is affected by a workload that executes a large number of statements that individually are fast but in aggregate can chew up resources. A CPU only has 1000 ms of processing time per second, right? If you have 100 users all try to execute a statement that takes 9 ms all at the same time, you're chewing up a lot of CPU. Profiler analysis might make it look like you have no CPU problem at all because some of those statements could have a 0 ms processing time in SQL Server. You can see how a problem could develop if you ignore the fast statements. SQL Server doesn't have a documented or supported way to track execution times down to the millisecond.
SQL Server's reliance on the Windows GetTickCount() function causes this behavior. A full discussion of Windows API programming is beyond the scope of this Q&A, but there's a way around this problem. Microsoft Research created a solution called DETOURS, which changes GetTickCount()'s behavior to let you report down to a millisecond level. Getting Profiler to use the improved behavior can't happen directly through the GUI. However, Gert Drapers has published an extended stored procedure called xp_hrtimer that lets sysprocesses and other sources of SQL Server timing information take advantage of the more granular timing that DETOURS provides. Using this extended stored procedure lets Profiler report more granular information as well. DETOURS is available for download at http://research.microsoft.com/sn/detours/ and Drapers's procedure is available from his Web site at http://www.sqldev.net/xp/xphrtimer.htm.
So, if you want Profiler to show timings between 0 and 10 ms, all you have to do is tell your boss that you want to install a non-supported extended stored procedure that's based on non-supported technology from Microsoft Research. Simple! Obviously, I'm not recommending that you use this procedure frequently in a production environment, but it can be a handy tool to have in your bag of tricks when you need to analyze the performance of a SQL Server workload that includes a large number of very fast statements.