Article ID: 931279 - View products that this article applies to.
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:
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 informationTo 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:
913089Note 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.
(http://support.microsoft.com/kb/913089/ )How to obtain the latest service pack for SQL Server 2005
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 frequencyTo do this, follow these steps:
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.
Collapse this tableExpand this table
Use third-party utilities and drivers to synchronize CPU frequencies and CPU clock countersOn 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:
The text of the error message resembles one of the following error messages:
Error message 1
The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
Error message 2
CPU time stamp frequency has changed from 191469 to 1794177 ticks per millisecond. The new frequency will be used
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 steppingCPU 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.
Collapse this tableExpand this table
In this example, the following timing calculations occur when you use a frequency value of 200 or 100:
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.
DriftDrift 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).
Collapse this tableExpand this table
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:
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:
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:
(http://support.microsoft.com/kb/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
895980The 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.
(http://support.microsoft.com/kb/895980/ )Programs that use the QueryPerformanceCounter function may perform poorly in Windows Server 2003 and in Windows XP
Article ID: 931279 - Last Review: December 16, 2008 - Revision: 7.0
Contact us for more help