SQL ????? ??? ??? ??? ?? ???? ?? ?? ?? ?????????? ?? CPU ?????????? ????????? ??????? ?? ?????

???? ID: 931279 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

??? ?? ?????? ?? ???????? microsecond ?????? ???? ?? ??? Microsoft SQL Server 2005 high-resolution CPU ?????? ?? ????? ???? ??? ?? ????? ?? ?? millionth (?? ???? millisecond ?? ?? thousandth) ?? microsecond ??? ???????, SQL Server ??? ??? CPU ?????????? ????????? ??????? ?? ????? ???? ??? ??? ?? ???? ??? ?????? ?? ???, ?? ?????? ???? ????? ??????? ?? ????? ???? ??? ?? ?? ???? ??:
  • CPU stepping
  • AMD Cool'n ' ????? Quiet
  • ??????? ???? ???????
?? ???? ??? ???? ?? ?? ?????? ?? ?? ???? ??? ??? ???? ?? ??? ???????? ??????? ???
????? ?? ???? ???? | ??????????? ???

?????

????? ????????, ????? ?? compilation ??? ????????? ???? ?? ??? ?? ?????? SET ??? ??? ?? ????? ????, ?? ?? ??? ??? ??????? ?? ???? ??? ?????? ?? ???, ?? ???? ????? ???? ??? SQL ????? ???????? ??? ?? ???? CPU ?? ??? ?? ??? ?? ?????? ?? ???????? ???????? ?? ??????? ?? ???????? ?? ???? ??? ?? ?????? ?? ???? ?? ?? ?? ?? ????? ???????? ??????? ?? ?? ????? ?? "??????" ??? ??? ???
????? ?? ???? ???? | ??????????? ???

????

?? ?????? ???? ?? ??????? CPU ?????????? ????????? ??? ?? ?? ?? ??????? ?? ????? ????? SQL ????? 2005 microsecond ??? ?? ?????? ?? ?????? ?????? ???? ?? ??? high-resolution CPU ?????? ?? ????? ???? ??? ??? heat ?????? ?? ?? ???? ?? ????? ????? ?? ??? CPU ?????????? ????????? ?? ?? ???, ?? ??????? durations ??? ?? ???? ???
????? ?? ???? ???? | ??????????? ???

????????

?????? ??? ???????

?? ?????? ?? ?? ???? ?? ??? SQL Server 2005 ?? ??? ?????? ?????? ??? ??????? ????? ???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
913089
(http://support.microsoft.com/kb/913089/ )
SQL ????? 2005 ?? ??? ?????? ?????? ??? ?? ??????? ???? ?? ??? ???? ????
???:SQL ????? 2005 ?????? ??? 3 ??? ?? ??? ??? ?????? ??? ???, ???????? ??? ??????? ????? ???? ??? SQL ????? 2005 ?? ?? ????????? ?? 1 millisecond ?????? ??????? ???? ?? ?? ???? ???????? ????? ?? ????? ?????
????? ?? ???? ???? | ??????????? ???

??????

?? ?????? ?? ???? ???? SQL Server 2005 ?????? ??? 3 ??? ??? ???? ??? ???
????? ?? ???? ???? | ??????????? ???

????? ?????????

SQL ????? 2005 ???? ???????? ???????? ???? ?? ??? ????? ?? ????? ???? ??????? ?? ???????? ??? ???????? CPU ??????? ??????? ?? ???????? ?? ????? ???, ??? ?? ????? ?? ???? ?????? ????? CPU steady ??????? ?? ?? ???? ???? ?? ??????? ?? SQL ????? ??????????? ????? ???? ??????? ???? ?? ????? ??? ???? ?? ???, ????? ??????? ?? ????? ?????

???? ????? ?? CPU ?????? ??????? ?? ???? ?? ??? ????? ???? ?? ??? ???????? ?? ???????? ????

??? ???? ?? ???, ????? ????? ?? ???? ????::
  1. ????? ????,???????????? ????,???????????:Powercfg.cpl?? ????-????? ????, ?? ???? ???OK.
  2. ????????? ?????? ???????? ????? ???, ????? ????????? ????????????? ??????????? ????
  3. ????? ????,OK.
???? ?????? ?? ???? ??? ???? ?????? ?? ?? divergence CPU ??????? ??? ?? ??? ??? ???? ??????? ?? ???, "??????" ??? ?????? ?? ?????? ???, ?? ???? ????? ?? ????? ?? ??? ??? CPU ?? ?????????? ??? ?? ?????????? ???? ?? ??? Microsoft Windows ???????? ???? ?????

??? ?? ???????? ?? ???????? ???? ???? ?? ????, SQL Server ?????? ???? SQL Server ????? ????? ?? CPU ?? ??? ?? ???? ?? ????? ?? ??? ????? ????? ?? ?? ?? ???? ???, ?? ???? ??? ??? divergence CPU ??????? ??? ?? ??? ???? ?? ?? ?? ???????? ?? ???????? ???? ?? ???? ??? ????? ?? ??? CPU ?? ??? ?????? ???? SQL ????? ?? ????? ???? ?? ??? ???? ???? ????? ?????, ????? ?? ??????? ?????????? ?? ?????? ?? ???? ?? ?? ????? ???? ??????

????? ?????? ?????? ?????????? ?? ???????? ???? ???
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
CPU ???????????? ???? ?? ????? ???? ?? ??? ???
02 CPUexec sp_configure '???? ?????', 0x00000003
?? ????
???: ???????? ????
?? ????
04 CPUexec sp_configure '???? ?????', 0x0000000F
?? ????
???: ???????? ????
?? ????
08 CPUexec sp_configure '???? ?????', 0x000000FF
?? ????
???: ???????? ????
?? ????
16 CPUexec sp_configure '???? ?????', 0x0000FFFF
?? ????
???: ???????? ????
?? ????
32 CPUexec sp_configure '???? ?????', 0xFFFFFFFF
?? ????
???: ???????? ????
?? ????
???:?? BIOS ???? ?? CPU ??????? variation ???????? ?? ????? ???? ?? ??? ????????? ?? ???? ??? ?????-???? ?? ??????? ?????????? CPU ?????????? ???????? ?? ???? ??? ??? ??????? ??????? ??????? ????? ???? ?? ?? ?? ?????? ???? ????? ???????? ?? ??????? CPU ???? ???? ?? ?????? ???, ???? ?????? ????? ?? ?????-???? ?????????? ?? ?? SQL Server 2005 ??? ???????? ?? ???????? ???? ????

CPU ?????????? ?? CPU ???? ???????? ?? ?????????? ???? ?? ??? ?????-???? ?????????? ?? ??????? ?? ????? ????

?????? occasions ?? ?? ?????? CPU ??????? ???????? ?? ??? ???? ?? ??? ???????? ?? ?????? ?? ???????? ?? ???? ??? ??? ???? ????? ?? ?? ?? ?????? ??? ?????? ?? ???? ??, ?? ?????? BIOS, microcode ?? ???????? ???????? ?? ??? ?????? ?? ???? ???? ?? ??? ?? ????? ?????? ???
????? ?? ???? ???? | ??????????? ???

???? ???????

Microsoft SQL Server 2000 ?? ????? ????????? SQL ????? ?? ????? Windows ??? ?? ?????? ?? ??????? ?? ????? ????? ??? ?? ?????? ??????? millisecond ??????? ????? ?? ????? ????? ??????????, ?? ??????? ?? 10 ???? ?? ??? 15 ms. ???????, ??????? 55 ms ???? ?? ???? ??? SQL ????? ?????? ???-??? millisecond ??? ????? ????? ?? ?? microsecond ??? ?? ??????? ??? ???? high-resolution ????? ?? ?? ??????? ?? ???????? ??? ?????, SQL Server ??????? 0 ms. Therefore ?? ??? ??? ??? ???????? ?? ???? ?? ?? ????????? ?? ??????????? ??????? ?? SQL ????? ?? ????? ????????? ??? SQL ????? ??????????? ????? ???? ???? ?? ??? ???? ???? ???

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:
    • TheAttention?????
    • 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
  • Thesysprocessessystem table
SQL Server 2005 ?????? ??? 2 (SP2) ??????? ???? ?? ??? SQL ????? ??? ?????? ????? ?????? ??? ??? ?? SQL ????? ?? ??? ?? ?? ???? ??????????? ??????? ?????? ?? ???? CPU ?? ??? ??? ?????? ????? ?? ????? ???? ?? ?? ???????? ??????? ????? ? ??, ?? ????????????, ??????????? ???? ?? ??????? ?? ??? ?? ????? ???? ??????

?????? ????? ?? ??? ??? ?? ?? ?????? ????? ????? ??:
?????? ????? 1
??? ??????? ?????? CPU ?? scheduler id 2 ?? ???? CPU ?? ??? ??????????? ???? ???
?????? ????? 2
????? millisecond 1794177 ticks ???? ?? ??? 191469 ?? CPU ??? ??????? ??????? ??? ??? ??? ??? ??????? ???? ?????
????? ?? ???? ???? | ??????????? ???
SQL ????? 64-??? CPU ??? ?????? ?? ??????? ???? ?? ??? ?? ??? ?????? ?????? (RDTSC) ??????? ?? ????? ???? ??? ?? ?? ??? ??? millisecond ????? ??? ?????? ???? ?? ??? CPU ??????? ?? ??????? ?? ???? ???? ??? ?? ?????? variations ?? ???? ?? ?? CPU ??????? ???????? ?? ?????? ???? ???

CPU stepping

CPU CPU ??????? ??? ??? deliberate ???????? ?? ??? ??? ???????? ?? stepping. CPU stepping ?? ?? ?? ????? Intel SpeedStep ????? ?? AMD PowerNow ?? ??? ???! ????? ??? ?? CPU stepping ????, CPU ??? ?????? ?? ????? ????? ?? heat ?????? ?? ?? ???? ?? ??? 50 MHz ???? ??????? ??? ?? ?? ???? ??? ?? ???-uniform ?????? ????? (NUMA) ??? ?? ??????? ??? ?? CPU ?????????? ???????? ??? ?? ???????? ?????

????? ?????? illustrates CPU stepping ???????? ??? ?? ?????? ?? ???????? ???? ?? ???? ???
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
??????RDTSC TicksMillisecond (???????) ????? ticks????? ???? ???
??? ??????? ????12000
???? ??????? ???2001001ms
??? ???5003ms
???5004ms
SQL ????? ?? ??????? ?? ??????? ????? RDTSC ticks RDTSC ticks ?? ?????? ???? ??? ???, SQL Server divides ticks ?????? ????? ????

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 anRPC:Completedtrace event, the????, ??end timedata columns are reported incorrectly. TheRPC:Completedevent captures the starting wall clock time and CPU tick count. To obtain higher resolution timing than Windows supplies in SQL Server 2005, the????, ??end timedata columns in a SQL Server trace are calculated by using the elapsed CPU tick count. Theend timecolumn is calculated by adding the????column to thestart time?????? ?? ?????? ???,end timecolumn is calculated by incorrectly adding either 2.5 ms or 5 ms to the start time.

??????

?????? ??? ?? divergence CPU ???? ??? ??? ??? ?? ?? ???? CPU ?? ??? ?????? ??? ??? ?? ?? ????? ?? ??? ????? CPU ???? ??? ?????? ?? ???? ???? ?? ??????? ???? ??, ?????? CPU ???? separation ?? ??? ?? ????? ?? ?????

????? ?????? ????????? ???? ?? ?? ?????? ???????? ?? outcome ?? ???????? ???? ?? ???? ?????????? SQL Server ????? ?? ????? ????? ?????? ????? ?? ?? CPU ??????? steady millisecond ????? 200 ticks ?? ???? ??? ????? ?????? illustrates ?? ???????? ??? ????? ???? ????
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
??????Windows ??????? ??? ?? CPU?????? 1 RDTSC?????? 2 RDTSC????? ???? ???
??? ??????? ????110011000
??? ???290019004 ms
???4 ms
SQL ????? ?? ??????? ????? ?? ????? ??????? ?? RDTSC ticks ?????? ???? ??? ???, SQL Server divides RDTSC ticks ?????? ????? ???? ?? ?????? ???, Windows ?? ????? CPU ?? SQL Server ????? ????? ??????? ???? ??? ??? ???? SQL Server ????? ????? ???? ???? CPU (CPU 1) ?? ???? ?? ?? ???

???????, ??? ????? ?? ??? ???????? ????? ???? ??? ??, ?? SQL Server ????? ?????? ?? ??? ??? ???????? ???? ???? SQL ????? ?? ??? runnable ???? ?? ???: ???? SQL Server ????? ????? ?? ???? ??? ??, ?? Windows dispatched ????? ????? CPU (CPU 2) ?? ????? ?? ???? SQL ????? ????? ????? ????? CPU 2 ?? ?? ??? ??? CPU ?? ??????, ?? ???? CPU 2 ?? ?????? ???? ??? ?? ?? ??? ??? ??? 1900 ?? 900 ?? ????? ?? ???? ??? ??? ??? SQL Server ?????? ???? ????? ???, ?? ???? ?? ??????? ?? ???? ?? ???? ????

?? ?????? ??? ????? ??? ?? ?????? ?? ????? ??? ???? ???:
  • ????? ??????? ??? ???:(1900 100 = 1800) / 200 = 9 ms
  • ??? ???:(900 100 = 800) / 200 = 4 ms
?? ????????? ??? ?????RPC: ?????????? ???? ??????? 4 ms. ?? ????? ?? 9 ms ?? ??? ??? ?? ?????? 4 ms ?? ????? ?? ???? ??? ??? ???

?????? ??????? ????? ????? ???? ??? ?? ???? ???????? ??? ?? ??????????? ?????? ???? ?? ???? ?? ???????? ???? ?? ??? SQL Server 2005 ??? ???? ???? ???? ??? uncovered ????????? ??? SQL Server 2005 SP2 ???? ??????? ??????? ????? ????? ?? ???? ???:
  • ?????? ?? ???? ??????? ?????
  • ?????? ?? ???? ??? noticeable ?????? ?? ?????? ?? ???? ???????????? ?? tens ?? ???? ???
?? ???? careful ?? ?? ???????? ?? ??????? ?????? ????????? ?? ?? ?? ????? ???? ??????? ?? ???????? ?? ??????? ?????? ????? ??? ??? ??? ???? ???????? ???????? ?? ??? signs ???, ?? ?? ?????? ??????? ????? ?????????? ??????? ?? ???? ???? ?????? ?? ???, ?? ?? ???? ??? ?????????? ??????? ?????? ??????? ????? ????? ????????? ???:
  • ??????????? ??? ???????? ?? ??? ??? ?? ??? ????
  • SQL ????? ?????? strange durational ???????? ??? ???? ?? ??? ????
  • ???? bottlenecks ?? signs ? ??????
???????, ?????? ??????? ????? ?? ???, ?? ??????? ???? ??? ?? ?? ?????? ?? ????? ? ???? ?? ???? ????????? ???? ?? ??? ????? RDTSC issues ???? ?? ??? ???? ???????? ????? ???

????? ?? ???? ??? ????? ???? 8033 (?T8033) ?????????? SQL Server 2005 ?? ??? ?????? ??????? ??? ?? SQL Server 2005 SP1 ??? ??????? ??? ???? ?? ???? SQL ????? 2005 ?? SQL Server 2005 SP1 ?? ??? ?????? ??????? ?????? ??????? ????? ?? ??????? ????? ??? ?? ??? ?? ?????? ??????? SQL Server 2005 ?? SQL Server 2005 SP1 ???????? ?? ???? ??? ??? ???, ?? ?? ????? ?????????? ??????? ?? ???? ????

??? ????? WAITFOR ????? ??? ?? ??? ????? ?????? ?????? ?? ???? ??? ???? ??????? ???? ???

????-??? ??????? high-resolution ??????? ?? ?????? ???????? ???? ???? SQL ????? ?????-?????? ?????????? ?? ??? high-resolution ????? ???? ?? ????? ???? ??? ??? ????-??? ?????????? ?? ????? ???? ?? ?? ????? ??????????? ????? ?? ?????? ???GetTickCount?????? ?? ??? ???? ???.. ?? ????-??? ?????????? ??? ????? ????-???, WAITFOR ????? ??? ?? deadlock ??? ????? ????? ???
????? ?? ???? ???? | ??????????? ???
???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ???????? ????? ????::
938448
(http://support.microsoft.com/kb/938448/ )
Windows Server 2003-?????? ????? ??? ??????? ?????? ?????? ????? ?? ???? ?? ??? ????-??? AMD Opteron ???????? ?? ??????????? AMD Opteron ?????????? ????? ?? ?????
895980
(http://support.microsoft.com/kb/895980/ )
QueryPerformanceCounter ?????? ?? ????? ???? ???? ????????? ?? ???? ?? ????? poorly ??? Windows XP ?? Windows Server 2003 ???
?? ?????-???? ?????? Microsoft ?? ???????? ???????? ?????? ??????? ??? ?? ??? ????? ?? ???? ????? ???? ??.. Microsoft ?? ???????? ?? ???????? ?? ??????????? ?? ???? ??? ??? ?????? ???? ???? ??, ????? ?? ??????..
????? ?? ???? ???? | ??????????? ???

???

???? ID: 931279 - ????? ???????: 06 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
??????: 
kbtshoot kbexpertiseadvanced kbsql2005engine kbprb kbmt KB931279 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:931279
(http://support.microsoft.com/kb/931279/en-us/ )
????? ?? ???? ???? | ??????????? ???

??????????? ???

 
????? ?? ???? ????????? ?? ???? ????