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

Microsoft SQL Server 2005 uses the high-resolution CPU counter to provide microsecond timing capabilities. A microsecond is one millionth of a second (or one thousandth of a millisecond). However, SQL Server timing values may be incorrect if you use technologies that change CPU frequencies. For example, this issue may occur when you use any of the following technologies:

• CPU stepping
• AMD Cool'n'Quiet technology
• Various power schemes

This article contains methods and additional information to help you work around this issue.

When you use the SET STATISTICS TIME statement to display server execution, parse, and compilation times, you may obtain incorrect values. For example, you may notice that the elapsed time of the SQL Server execution time is much more than the CPU time. This issue may affect the accuracy of performance tuning. This issue occurs when you use one of the technologies that are listed in the "Summary" section on the server.

This issue occurs because CPU frequencies are changed when you use these technologies. SQL Server 2005 uses the high-resolution CPU counter to provide microsecond timing capabilities. If CPU frequencies are changed to conserve energy and reduce heat output, calculated durations may be incorrect.

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

This problem was first corrected in SQL Server 2005 Service Pack 3.

SQL Server 2005 requires known and stable data points to perform accurate performance tuning. If dynamic CPU frequency adjustments are enabled on the computer, you can disable them so the CPUs maintain a steady frequency rate before you start to monitor and tune SQL Server performance. To do this, use the following methods.

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.

Microsoft SQL Server 2000 and earlier versions of SQL Server use the Windows timing mechanisms. The timing mechanisms use millisecond-precision values. Typically, this precision is 10 to 15 ms. However, the precision may be as large as 55 ms. SQL Server queries frequently complete within single-digit millisecond or microsecond time spans. This precision requires a high-resolution timer. Therefore, these versions of SQL Server report the duration of some queries as 0 ms. Therefore, it is difficult to monitor performance and tune SQL Server performance in earlier versions of SQL Server.

SQL Server 2005 improves accuracy by using the high-resolution CPU counter to provide microsecond timing capabilities. When you use the technologies that are listed in the "Summary" section, the reported timing values may be incorrect.

This issue may affect the following objects and features:

• Trace events:
  • The Attention event
  • Events in the Stored Procedures node
  • Events in the TSQL node
  • Events in the Objects node
  • Events in the Transactions node
• Dynamic management views:
  • sys.dm_exec_query_stats
  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.dm_io_pending_io_requests
  • sys.dm_os_ring_buffers
  • sys.dm_os_sys_info
  • sys.dm_io_virtual_file_stats
  • sys.dm_os_wait_stats
• The SET STATISTICS TIME statement
• The sysprocesses system table

After you install SQL Server 2005 Service Pack 2 (SP2), SQL Server logs an error message in the error log when SQL Server detects that the high resolution timers are out of sync between the CPUs. The error message indicates that performance timings may not be accurate, and users should use performance data with caution.

The text of the error message resembles one of the following error messages:

SQL Server uses the Real Time Stamp Counter (RDTSC) instruction to acquire the 64-bit CPU tick count. You can divide this value by the CPU frequency to convert the value to millisecond values. Timing variations can occur when the CPU frequency changes or drift occurs.

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