SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies
- CPU stepping
- AMD Cool'n'Quiet technology
- Various power schemes
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:
Configure the power scheme on the computer to force the CPUs to remain at maximum frequencyTo do this, follow these steps:
- Click Start, click Run, type Powercfg.cpl, and then click OK.
- In the Power Options Properties dialog box, click Always On in the Power schemes list.
- Click OK.
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.
|CPU number||Statements to enable processor affinity|
|02 CPUs||exec sp_configure 'affinity mask', 0x00000003|
|04 CPUs||exec sp_configure 'affinity mask', 0x0000000F|
|08 CPUs||exec sp_configure 'affinity mask', 0x000000FF|
|16 CPUs||exec sp_configure 'affinity mask', 0x0000FFFF|
|32 CPUs||exec sp_configure 'affinity mask', 0xFFFFFFFF|
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.
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:
- The SET STATISTICS TIME statement
- The sysprocesses system table
The text of the error message resembles one of the following error messages:
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.
|Action||RDTSC Ticks||Ticks per millisecond (Frequency)||Wall clock time|
|Frequency Step Down||200||100||1ms|
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
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.
|Action||Windows scheduled CPU||CPU 1 RDTSC||CPU 2 RDTSC||Wall clock time|
|End Batch||2||900||1900||4 ms|
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
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
- Processes are running as expected.
- SQL Server queries are not running in strange durational patterns.
- You do not see signs of other bottlenecks.
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.
Id. de artículo: 931279 - Última revisión: 12/16/2008 14:32:10 - Revisión: 7.0
- kbtshoot kbexpertiseadvanced kbsql2005engine kbprb KB931279