Why do database servers have different time_source values

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:

0 = QUERY_PERFORMANCE_COUNTER
1 = MULTIMEDIA_TIMER

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

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 (

• 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.

StackExchange Database Administrators Q#103364, answer score: 2