SQL Server Timer Source

Recently, I’ve been looking for a way to better understand timings on SQL Server, and have been intrigued to see that SQL Server can use one of several different mechanisms to determine precision and resolution of time.

Windows operating systems support an API named QueryPerformanceCounter which can return microsecond timings. Windows can be configured to use the so-called multimedia timer, via the binary boot configuration data, or BCD, with the /USEPMTIMER option, which returns resolution of up to 10-15 milliseconds.

The following script allows you to see what timer_source SQL Server is using. Since this information is only visible on SQL Server 2008 R2 and above, the script returns no results for SQL Server 2008 and below.

SET NOCOUNT ON;

DECLARE @time_source INT;
DECLARE @time_source_desc VARCHAR(255);
DECLARE @st DATETIME2;
DECLARE @ms INT;
DECLARE @msvc VARCHAR(10);
DECLARE @prec INT = 0;
DECLARE @loop INT = 0;
DECLARE @maxprec INT = 0;
DECLARE @res TABLE (
	ServerName SYSNAME
	, time_source INT
	, sys_date_time DATETIME2
	, MaxPrecision INT
);

WHILE @loop < 50
BEGIN
	SELECT @time_source = dosi.time_source
		, @time_source_desc = dosi.time_source_desc
	FROM sys.dm_os_sys_info dosi;
	SET @ms = 0;
	WHILE @ms < 100000
	BEGIN
		SET @st = SYSDATETIME();
		SET @ms = DATEPART(NANOSECOND, @st);
	END

	DECLARE @msvc1 VARCHAR(10) = '';
	DECLARE @a INT = 0;
	DECLARE @b INT = 0;
	DECLARE @c CHAR(1);
	SET @msvc = REVERSE(CONVERT(VARCHAR(10), (@ms)));
	SET @b = LEN(@msvc);
	WHILE @a < @b
	BEGIN
		SET @a += 1;
		SET @c = SUBSTRING(@msvc, @a, 1);
		IF ISNUMERIC(@c) = 1
		BEGIN
			IF CONVERT(INT, @c) > 0 
				SET @a = LEN(@msvc);
			ELSE 
				SET @msvc1 = @msvc1 + @c;
		END
	END
	SELECT @prec = 9 - LEN(@msvc1);

	INSERT INTO @res
	SELECT @@SERVERNAME
		, time_source = @time_source
		, [sysdatetime] = @st
		, [precision] = @prec;


	IF @prec > @maxprec
		SET @maxprec = @prec;

	SET @loop += 1;

	WAITFOR DELAY '00:00:00.009';
END

SELECT ServerName = @@SERVERNAME
	, time_source = @time_source
	, time_source_desc = @time_source_desc
	, [sysdatetime] = @st
	, [precision] = @maxprec;

/*
	Uncomment this for diagnostics
*/
--IF @maxprec = 6
--SELECT *
--FROM @res r;

On my system, this returns the following:

+-------------+-------------+---------------------------+------------------+-----------+
| SERVERNAME  | TIME_SOURCE |     TIME_SOURCE_DESC      | SYSDATETIME      | PRECISION |
+-------------+-------------+---------------------------+----------------- +-----------+
|             |             |                           |                  |           |
| MySQLServer |    0        | QUERY_PERFORMANCE_COUNTER | 2015-06-09       |    7      |
|             |             |                           | 16:29:42.0130018 |           |
+-------------+-------------+---------------------------+------------------+-----------+