??? ??????? ????? ??????? ??????? ??? ???? ??????? ?? SQL Server ???? ??? ?????? ?? ????? ????? ????????

?????? ????????? ?????? ?????????
???? ???????: 234656 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

??????

???? ????? ?????? ????? foremost ?????? ?? ????? ????? ??????? ????????? ??? ?? ???? ?? ??? ?????? ??? ?????.

??? ?? ???? ?????? atomicity ? durability ????????? ????? ???????? ??????? ?????? "?" ????????? ?????? "?" ???? ????? ????????. ????? ????? ??? ??? ?????? ???? (Atomicity ????? ? ??? ? Durability).

?????? ??? ??????? ?????? ?? ???? ??????? ??????? ??????. ?? ???????? ????? ???????? ??????? ?? ????? ?????Microsoft ?????? ??? ????? ?????? ??? ??????? ?????? ? ??????? ??? ??? ?????????:
86903SQL Server ? ??????? ?????? ?????? ????????
46091??????? ????? ?????? ??????? ?????? ???? ???? ?? ???? SQL
230785????? SQL Server 7.0 ? SQL Server 2000 ? ????????? ????? ???????? ????? ?????? ????????
?? ???????? ??????? ????????? ??????? ?????:

??????? ????

Microsoft SQL Server 2005 ? SQL Server 2000 SQL Server 7.0 ??????? SQL Server ? ?????? ?? ?????? ??????? ????? ???????? ?? ????? ????? ??????? ?????????? ??????? ????? ????? (WAL).
????? ????? ????? ???????? (WAL)
???????? ????? ????? ?????? ???? WAL. ??? ???? ??? ????? ?????? ????? ?? ????? ??????? ???????? ????? ???????? ? ??????? ???? ???? ?? ???? ??????? ??? ???? ?????? ?? ???? ???. ??? ???????? ????? ?????? ???????? ?????? ??????? ? ??????? ???? ????? ??? ???? ????? ???? ??? WAL ??? ?????????? ????? ????????.
??? ???? ??????? SQL Server ??? ?????? ???????? ???????? ?????? Win32 CreateFile. ????? ??????? dwFlagsAndAttributes ?????? FILE_FLAG_WRITE_THROUGH ??? ??? ?????? SQL Server.
FILE_FLAG_WRITE_THROUGH
???? ?????? ??? ?????? ??????? ??? ????? ??????? ?????? ?? ???????? ????????? ?????? ??? ?????. ?? ??? ?????? ????? ?????? ??????? ?????? ???? ?? lazily ???? ???.

???? ?????? FILE_FLAG_WRITE_THROUGH ??? ??????? ??????? ????? ????? ????? ????? ???????? ???? ???? ?? ?????? ??????????. ?????? ??? ?? ??????? ???????? ????? ?????? ??????? (WAL) ?????? ?? ????????.
????? ?????? ?? ?????? ??????? (SATA ? ATA, SCSI ? IDE ?????) ??? ?????? ?????? ??????? ?????? ?? 512 ???? ????, 1 ???????? ? ?????. ????? ?????? ??????? ?????? ???? ??????? ????? ??? ???? ? ??? ????? ??? ??????? ???????? ????. ?? ???? ??? ??????? ?????? ????? ???? ?????? ??????? ??? ???? ?? ??? ?????? ???????. ???? ???? ????? ?????? ??????? ???? ???. ????? ?????? ?????? ??????? ?? ????? ?? ??? ? ???? ?????? ??????? ?????? ???? ?? ???? ????? ????? ????? ?? ???????? ????? ???.

????? ???? ??????? ?????? ?????? ????? ????? ??????? ?????? ??? ????? ????? ??? ???????? ???????? - ????? ??????? ?????? ????. ?????? ??????? ?????? ??? ???? ???????? ????????? ?? ????? ??????? ?????? ???? ???? ??????? ??? ????? ??????? ?????? ??? ?? ??????? ????. ??? ??????? ?????? ???? ????, ??? ??? ???????? unwritten ???? ???? ??? ?????? ?? ???? ?????? ??????. ?????? ?? ??? ???? ?????? ??????? ??????? ????? ??????? ??? ????? ??????? ?????? ????? ?????? ??? ?????? ??????. ????? ??? ????? ??????? ????? ?????. ?? ???????? ????? ???? ???? ?? ????? ? ???? ??? ????? ??????? ????? "????? ????? ????????" ??????? ?????? ????? ?????? ???????? ?? ????? ??????? ??????. ??? ???? ????? ???? ????? ???????? ???? ?????.

?????? ??? I/O ???? ??? ??????? ???? ??????? ????? ??????? ?????? ???? ?? ???? ???? ???? ????? ????? ?????? ????? ?? ????? ???? ??????? ???????, ????????? ????? ??????? ???? ???? ???? ??????? ??????? ?????? ?????. ??? ????? ?????? ????? SQL Server ?? ??????? ???? ???? ????? ???? ??????? ??????. ????? ????? ??????? ??????? ??? ????? ????? ?????? ??? ????? ?????? ??????? ?????????? ?????? ??????? I/O ???? SQL. ???? ???? ?????? ?????? ???????? ????? ????? ???? ???????? ???????? ??????? ?????? ?? ???? ???? ??????? ??????.

???? ??? ????? ?? ??? ????? ?????? ??????. RAID ? SAN ????? ????? ?? ??? ??????? ?? ??????? ?????? ??????. ??? ??????? ????? ????? ?? ?????? ??????? ???? ????? ??? SCSI. ???? ????? ????? ????. ??? ????? ?????? ??? ???????? ???? ??????? ????? ????.

?????? ????? SCSI:
  • ????? ????? ????????? duty ????.
  • ??????? ????? ?? ??????? ?????? ?????????? ? ????? ??? ??????.
  • ???? ???? meantime ???? ??? ?????? ??? ?? ??????? ????.
  • ????? ??? ???????? ??????? ????? ???????? ?? ???? ??? imminent.
???? ???? ????????? ??? IDE ATA ? SATA:
  • ????? ????? ????????? duty ???? ?????????.
  • ??????? ????? ?? ????????? ???????? ??? ?????? ????.
  • ????? ??? ????????? ?????? ??? ???????? ??????? ????? ???????? ?? ???? ??? imminent.
???? SCSI ????? ????? ?????? ???? ????? ??? ??? ?????? ??????? ??????? ?? ??? ?????? ??????? (CPU) ??? ?????? ???? ????? ?????? ??? ??? ????. ??? ???? ??????? ????? ??? ??? ???? ????? SCSI ??? ???? ????? ? ????? ???? ??????? ???????? ????? ??????. ???? ?????? ATA ?????? ???. ??? ?????? ATA ??????? 2 ???? ???? ?? ???? ??? ???? ??? ???. ??? ???? ???? ????? ???? ??????? ????? ???? ????? ??? ????? ????? ?????. ????? raid ??????? ??? ??? ?????? ?????? ???? ???? ????? ??? ??????? ??? ?? ????? ???????? ???? ???? ????. ?? ?? ?????? ??? ??????? ??????? ????????, ?? ???? ?????? ?????? ??? ????? ??? ???? ?????? ??????? ???????? ??? SCSI.

???? ??????? ???? ????? ??? ??? ?????? ???? ??????? ?? ???? ?? ?????? ????? ??????. ??? ???? ??????? ??? ??? ?????? ????? ?????? ??????? ??? ??????? ?? ??? ?? ?? ????? ?????? ?? ??????? ???? ????? ?????? OLTP ??? ????? ??????? ?????? ?????.

???? ??????? ?????? ????? ??????. ???? ?? ???? ?????? ??????? ??? ?????? ??????? ? ????? ???? ????. ???? ??? ????? ???? ??????? ?? SQL Server ? ????????? ??? ??????? ????? ??? ?????? ?????? ???????? ???????:
  • ????? ???? ??????
  • ??????? ????? ????? ??????? ?????? ??? ?????
The following table provides high level comments. The comment information is based on common manufacture configurations.

?? ??? ??????????? ??? ??????
Drive type System baseComments
IDE and ATA
  • Spin at 7,200 RPM.
  • Targeted at desktops and single user applications.
  • By default, the disk cache is enabled. Use the 'Disk Properties', Hardware tab to access the 'Properties', 'Policy' tab to control the disk cache setting.

    Note Some drives do not honor this setting. These drives require a specific manufacturer utility to disable cache.
  • Disk caching should be disabled in order to use the drive with SQL Server.
  • IDE and ATA-based systems can postpone host commands when they perform activities such as bad block adjustment. This could lead to periods of stalled I/O activity.
IDE/Portable computer
  • Spin at 5,200 RPM.
  • Targeted at portable computers.
  • See other comments in IDE and ATA section.
  • ReadyDrive and NAND Flash storage may provide stable I/O capabilities.
SATA
  • Spin at 7,200 RPM.
  • Targeted at desktops and medium range servers.
  • By default, the disk cache is enabled. Use the 'Disk Properties', Hardware tab to access the 'Properties', 'Policy' tab to control the disk cache setting.

    Note Some drives do not honor this setting. These drives require a specific manufacturer utility to disable cache.
  • Larger storage capacity and larger caches than IDE and ATA counter parts.
  • Disk caching should be disabled in order to use the drive with SQL Server.
  • Smaller cabling and interconnects.
  • Easy interconnect with Serial Attached SCSI (SAS) drives and installations.
  • Some include failure prediction constructs.
SCSI
  • Spin at 10,000 and 15,000 RPM.
  • Designed for servers and multiuser applications.
  • Designed for heavier duty cycles and decreased failure rates.
  • By default, the disk cache is enabled. Use the 'Disk Properties', Hardware tab to access the 'Properties', 'Policy' tab to control the disk cache setting.

    Note Some drives do not honor this setting. These drives require a specific manufacturer utility to disable cache.
  • Disk caching should be disabled in order to use the drive with SQL Server.
  • Array and SAN systems typically use smaller cabling and interconnects.
  • Include failure prediction constructs.
Serial Attached SCSI (SAS) includes advanced queuing up to 256 levels. SAS also includes features such as head of queue and out of order queuing. The SAS backplane is designed in a way that enables the use of both SAS and SATA drives within the same system. The benefits of the SAS design are obvious.

Your SQL Server installation depends on the controller's ability to disable the on-disk cache and to provide a stable I/O cache. Writing data out of order to various disks is not a hindrance to SQL Server as long as the controller provides the correct stable media caching capabilities. The complexity of the controller design increases with advanced data security techniques such as mirroring.

To fully secure your data, you should ensure that all data caching is properly handled. In many situations, this means you must disable the write caching of the disk drive.

?????? Ensure that any alternate caching mechanism can properly handle multiple types of failure.

Microsoft has performed testing on several SCSI and IDE drives using the SQLIOStress utility. This utility simulates heavy asynchronous read/write activity to a simulated data device and log device. Test performance statistics show the average write operations per second between 50 and 70 for a drive with disabled write caching and an RPM range between 5,200 and 7,200.

For additional information and details about SQLIOStress, see the following article in the Microsoft Knowledge Base:
231619INF: SQLIOStress Utility to Stress Disk Subsystem Like SQL Server
Many PC manufactures (for example, Compaq, Dell, Gateway, or HP) order the drives with the write cache disabled. However, testing shows that this may not always be the case so you should always test it completely.

?????? If there is any question as to the caching status of your disk drive, please contact the manufacturer and obtain the proper utility or jumper settings to disable write caching operations.

?????

For complete details on SQL Server I/O requirements, visit the following Microsoft Web site: http://www.microsoft.com/sql/alwayson/overview.mspx
SQL Server requires systems to support ? guaranteed delivery to stable media ? as outlined under the Microsoft SQL Server Always-On Storage Solution Review program. FoFor more information about the input and output requirements for the SQL Server database engine, click the following article number to view the article in the Microsoft Knowledge Base:
967576Microsoft SQL Server Database Engine Input/Output Requirements

???????

???? ???????: 234656 - ????? ??? ??????: 21/????/1428 - ??????: 3.7
????? ???
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
????? ??????: 
kbmt kbinfo KB234656 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????234656

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

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com