SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies

Service pack information

To resolve this problem, obtain the latest service pack for SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

913089 How to obtain the latest service pack for SQL Server 2005
Note In SQL Server 2005 Service Pack 3 and in later service packs, the processor time stamp is not used. These versions of SQL Server 2005 use a more reliable timer that has a maximum precision of 1 millisecond.

Configure the power scheme on the computer to force the CPUs to remain at maximum frequency

To do this, follow these steps:

1. Click Start, click Run, type Powercfg.cpl, and then click OK.
   

2. In the Power Options Properties dialog box, click Always On in the Power schemes list.
   

3. Click OK.

A drift may occur. A drift is a divergence between CPU frequency values. For more information, see the "Drift" section. In this case, you must restart Microsoft Windows to resynchronize frequencies of all CPUs after you change the power scheme.

If you cannot restart the computer, enable SQL Server processor affinity to prevent SQL Server worker threads from moving between CPUs. When you do this, you do not have to restart the computer even if a divergence between CPU frequency values occurs. To enable SQL Server processor affinity for all CPUs on the server, you must use a different mask, depending on the number of logical processors that are on the server.

The following table lists example scenarios.

Note It may be insufficient to disable CPU frequency variation features at the BIOS level. Various third-party utilities can alter CPU frequencies. Some implementations enable frequency adjustments even when the CPUs are under maximum power scheme settings. In this case, you must disable these third-party utilities when you perform performance tuning in SQL Server 2005.

Use third-party utilities and drivers to synchronize CPU frequencies and CPU clock counters

On rare occasions, a system may require an update from the manufacturer to correct CPU frequency issues. It is a best practice to check the system for the latest BIOS, microcode, and firmware updates if you suspect that the system may have an issue.

CPU stepping

CPU stepping is defined as a deliberate change in CPU frequency. CPU stepping may also be known as Intel SpeedStep technology or AMD PowerNow! technology. When CPU stepping occurs, the CPU speed may increase or decrease in increments as small as 50 MHz to conserve energy and reduce heat output. CPUs that are within the same non-uniform memory access (NUMA) node do not independently adjust frequencies.

The following table illustrates how CPU stepping changes may affect timing calculations.

SQL Server captures the RDTSC ticks at both the start and end RDTSC ticks. Then, SQL Server divides the ticks by the frequency value.

In this example, the following timing calculations occur when you use a frequency value of 200 or 100:

• Frequency 200: 500/200 = 2.5 ms
  

• Frequency 100: 500/100 = 5 ms
  

Neither of the timing calculations matches the actual wall clock time of 4 ms.

If this calculation is used in an RPC:Completed trace event, the duration and end time data columns are reported incorrectly. The RPC:Completed event captures the starting wall clock time and CPU tick count. To obtain higher resolution timing than Windows supplies in SQL Server 2005, the duration and end time data columns in a SQL Server trace are calculated by using the elapsed CPU tick count. The end time column is calculated by adding the duration column to the start time column. In this example, the end time column is calculated by incorrectly adding either 2.5 ms or 5 ms to the start time.

Drift

Drift is a divergence in CPU clock values. Systems that have multiple CPUs can produce different CPU clock values for the same point in time. Although it is not common, CPUs may experience clock separation over time.

The following example demonstrates how drift changes can affect the outcome of the duration data column in a SQL Server trace. The example assumes that the CPU frequency remains steady at 200 ticks per millisecond. The following table illustrates the events in this scenario.

SQL Server captures the RDTSC ticks at both the start points and the end points. Then, SQL Server divides the RDTSC ticks by the frequency value. In this example, Windows scheduled the SQL Server worker thread on two different CPUs. The SQL Server worker thread that services the batch first ran on the first CPU (CPU 1).

However, the batch execution was interrupted at some point, and SQL Server sent the batch execution to the pending queue. When SQL Server sent the SQL Server worker thread that services this batch to the runnable queue again, Windows dispatched the thread to run on the second CPU (CPU 2). The SQL Server worker thread completed running on CPU 2. Because of CPU drift, the end tick value that was captured from CPU 2 was 1900 instead of 900. You can avoid this behavior if you enable SQL Server processor affinity.

The following timing calculations are used in this example:

• The incorrect but reported value: (1900 – 100 = 1800) / 200 = 9 ms

• Correct value: (900 – 100 = 800) / 200 = 4 ms

The value of the duration column for the RPC:Completed event would be reported as 9 ms instead of 4 ms. This result is more than double the correct value of 4 ms.

Drift warning messages are added to SQL Server 2005 to indicate that the performance outputs that are mentioned earlier may not be reliable. In some uncovered situations, SQL Server 2005 SP2 may report warning messages about the following:

• False drift warning messages

• Drift can become tens of milliseconds without causing a noticeable system effect

You must be careful when you evaluate the performance-related outputs and when you compare the performance-related outputs to wall clock timings. If there are no signs of other performance problems, you can typically ignore the drift warning messages. For example, you can typically ignore the drift warning messages in the following situations:

• Processes are running as expected.

• SQL Server queries are not running in strange durational patterns.

• You do not see signs of other bottlenecks.

However, before you ignore the drift warning messages, we recommend that you contact your manufacturer to make sure that no known RDTSC issues exist.

You can use trace flag 8033 (–T8033) to return to the reporting behavior in the original release version of SQL Server 2005 and in SQL Server 2005 SP1. The original release version of SQL Server 2005 and SQL Server 2005 SP1 do not report drift warning messages. If you are running the original release version of SQL Server 2005 or SQL Server 2005 SP1 without problems, you can typically ignore the messages.

Why does the WAITFOR DELAY statement work correctly? What about periodic system processes?

Time-out mechanisms are not affected by the high-resolution design. SQL Server does not use the high-resolution timer for timer-based activities. Some time-out activities are based on the reduced resolution timer that uses the GetTickCount function. These time-out activities include lock time-out, the WAITFOR DELAY statement, and deadlock detection.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

938448 A Windows Server 2003-based server may experience time-stamp counter drift if the server uses dual-core AMD Opteron processors or multiprocessor AMD Opteron processors

895980 Programs that use the QueryPerformanceCounter function may perform poorly in Windows Server 2003 and in Windows XP

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.