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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
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:
|
1 2 3 4 5 6 7 |
+-------------+-------------+---------------------------+------------------+-----------+ | SERVERNAME | TIME_SOURCE | TIME_SOURCE_DESC | SYSDATETIME | PRECISION | +-------------+-------------+---------------------------+----------------- +-----------+ | | | | | | | MySQLServer | 0 | QUERY_PERFORMANCE_COUNTER | 2015-06-09 | 7 | | | | | 16:29:42.0130018 | | +-------------+-------------+---------------------------+------------------+-----------+ |