Why do database servers have different time_source values

Posted on

Question :

I have two SQL Server 2008 R2 Enterprise SP1 servers that have different values for sys.dm_os_sys_info.time_source.

The time_source column of sys.dm_os_sys_info indicates the API that SQL Server is using to retrieve wall clock time:


What causes the difference and can I change SQL Server to use the more precise QUERY_PERFORMANCE_COUNTER value?

|                          | Original                  | New                     |
| time_source              | QUERY_PERFORMANCE_COUNTER | MULTIMEDIA_TIMER        |
| os install               | clean                     | clean                   |
| virtual/physical         | physical                  | virtual                 |
| hypervisor               | n/a                       | vmware, ESXi 5.5        |
| VMware hardware Version  | n/a                       | 7                       |
| bcd nx                   | optin                     | optout                  |
| bcd recovery enabled     | no                        | yes                     |
| bcd /usepmtimer          | no                        | no                      |
| sql sku                  | sql standard 2008 r2      | sql standard 2008 r2    |
| sql upgrade from express | no                        | no                      |
| sql auto-close any db    | no                        | no                      |
| power plan               | high performance          | balanced                |
| dm_os_performance_counter| yes                       | yes                     |


My coworker sent this very interesting link: How It Works: Timer Outputs in SQL Server 2008 R2

VMWare uses a different API for time source depending on the OS version and the VM Hardware version. They also have a whitepaper with lots of details.

Answer :

According to MSDN, SQL Server uses the following logic to decide which timer to use:

Windows does not have an API to indicate what source QueryPerformanceCounter is using at this time so SQL Server 2008 R2 startup times the invocation of QueryPerformanceCounter (10 times) and when it exhibits repeated, small cycle behavior (< 600 cycles) the QueryPerformanceCounter will be used to accept invariant TSC timings instead of the GetTickCount/timeBeginPeriod interrupt timing behavior. If the timing exceeds 600 cycles the multi-media timer behavior of SQL Server is used instead.

Note: Windows is exposing the timer source API for QueryPerformanceCounter in a Windows 2008 R2 based fix so the timing source determination by SQL Server will be replaced with the API call in a future build. Target releases are the SP1 of Windows 2008 R2 and SQL Server 2008 R2.

The above explanation indirectly answers your questions:

  1. SQL Server uses an internal process to determine which timer to use.
  2. You cannot tell SQL Server 2008 R2 which timer to use, other than by using trace flags 8049 and 8038. In my testing, neither of those trace flags made any difference to the timer_source column of sys.dm_os_sys_info.

We rebooted the majority of our servers over the weekend. I compared the list of machines using MULTIMEDIA_TIMER from before and after the reboot. I can now report that some of the servers that were using the MULTIMEDIA_TIMER are now using QUERY_PERFORMANCE_COUNTER and vice-versa.

Since our servers are virtualized, I presume simultaneously booting machines in our virtualized environment is resulting in some machines taking longer than 600 CPU cycles to return a value through the QueryPerformanceCounter API. This effect appears to be randomly distributed across our environment.

Leave a Reply

Your email address will not be published.