SQL Server 7.0, SQL Server 2000 ?? SQL Server 2005 ?????? ?? ???? ?????? ?????????? ??????? ???? ?? ???????????

???? ?????? ???? ??????
???? ID: 230785 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

SQL Server 7.0, SQL Server 2000 ?? SQL Server 2005 restructure ?? ?? ????? Microsoft SQL Server ????????? ???? ?? ??????????? ?? ??????? ?? ??????? ?? ??? ?????? ?? ???? ?????????? redesign ???

SQL Server 7.0 ?? SQL Server 2000 ????? ?? ?????????? ????????? ?? ???? ??? ???? ????? ?? ??? ????? "ARIES (?????????? ????????????? ?? Isolation Exploiting Semantics ?? ???): ??? ????????? ????????????? ???? Supporting ????-Granularity Locking ?? ????? Rollbacks Using ????-??? ??????", ??????? ???????? ?? ACM ????????? ??? ?? ???????? ?? Chunder Mohan ?????? ???? ??? ???

?? ???????? ?? ???? ?? ??????????? ?? ??????? ???????? ?? ??? ?? ??? ??? ?? ??????? ?? ??????? ???? ?? ??? SQL Server 7.0, SQL Server 2000 ?? SQL Server 2005 ??????? ?? ???

?? ???????? ?? ?? ?? ????? ?????? ?? Microsoft ???????? ??? ????? ?????? ?? ??? clarification ?? ??? ????? ?? ?????? ??? ??????? ????????:
86903SQL ????? ??? ????? ???????? ?????? ?? ?????
46091SQL ????? ?? ??? ????? ????? ???????? ?????? ?? ????? ?? ??? ??
234656SQL ????? ?? ??? ?????? ????? ?????? ?? ????? ?? ??? ??

???? ???????

In-depth ????? ????, ???? ?? ???? ??? ?????? ???? ?? ???? ?? ????? ????? ?? ??? ??? ????? ??? ??? ????????? ????
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
?????????
????? ???????? ?? ?????????? ????? ????? ?????? ???? ?????? ?? ?????? ??????? ?????? controlled ???? ???? ????? ?? ????
???:?? ?? ???????????? ???? ??????? (UPS) ???? ??? ???? UPS ??? ???? ?? ?????????? ?? ?????? ???? ?? ?? ?????? ?????? ?? ?????????? ?? ???? ????
???Intermediary ?????? ??????? ????? i/O ???????? ????????? ???? ?? ???????? ?? ??????? ?? ??? ????? ???? ???? ???
???? ???????? ?? ????? ?????? ???? ?? ??? ???? ???? ?? ??? ?? ?????? ???? ???? ?????? ???? ??????? ?? ??? pertaining ???? ????? ?????? ?? ???, SQL Server ???????? ?????? ?? ???????? ??????
????????? ?? SQL ????? ????????? ?? ?? ????????? ?????? ?? ???? ?? ???? ??? ???????? ??? ????? ???: ??????, ???????? ?????, ?????? ?????????, ???? ????????? ????????, ????? ????????, ?????? outages, OS ???????? ?? ??? forth ???? ???
??????????? stable ???? ?? ??? ??? ??? ???? ?? ??? ???
Latch????????????? ???????? ???? ?????? ?? ????? ?????? ?? ???????? ???? ?? ??? ????? ???? ???? ???
Nonvolatile ?????????? ?? ????? ?? ?????? ???????? ?? ??? ?????? ???? ???
??? ??? ?? ?????????? ?? ???? ??? ??? ??? ?? ???? ???? ?? ???? ????? ????? ?????? ???? ?? ??? ?? ?? ?? ??? ??????? ??? ??????? ??? ????? ??????? ????? ???????? ????
????? ?????????? nonvolatile ?????? ?? ??? ????
???????? ??????????? ?? ???? ?? ????????????????? ?? ??? ?? ?????? ???? ???
SQL ????? 2005, SQL Server 2000, SQL Server 7.0, SQL ????? ?? ????? ?????????, ?? ?? mainstream ??????? ???????? ?? ?????? ?? ?? ????-??? ?????? (WAL) ????????? ?? ??????

????-??? ?????? (WAL) ?????????

?? ????????? WAL ?? ????? ???? ?? ??? excellent ????? ??? ?? ?? ??????? ?? ?? ???? ?? ????????? ???? ?? ??? ?????? ??????????? ????? ?? ??? ?? ????????? ???????? ?? ?? ???? ??? ?? ???? ?? ?? ?????? ?? ???? ??? ??? ????? ?????? ?? ??? ???????????? ?? ???? ???? ??? ??????? ??? ????? ??? ???? ?? ???????? ???, ???? ?????? ???? ?? ??? ?? ????????? ????????? ?? ??? ??? ???? ???? ?? WAL ????? ???? ?? ???????? ???? ?? ??? ??????????

ARIES ???????? ?? ??? ??? WAL ?? ????????? ???? ??:
WAL ????????? asserts ?? ?? ???????? ???? ?? ??? ??? ???? ???????????? ???? ???? ??? ??????? ???? ?? ?? ???? ????? ????? ?????? ??? ???? nonvolatile ?????? ??? ???? ?? ????? ??????? ?? ???????????? ???? ?? ??? ???? ??? ???? ?? ?????? ??? ??????, ?????? ?????? ???? ?? ?? ?? ?? ?? ?? ?? ????? ?? nonvolatile ?????? ??????? ??? ??? ?????? ???? ??? ??? ????? ?? ??? ??????? ????? ?? ??? ??????? ?? ????? ?? ??? ???????? ?? ????? ???? ?? ?????? stable ???? ?? ??? ???? ??? ???
????-??? ?????? ?? ???? ??? ???? ??????? ?? ??? SQL Server ???????? ?????? ?? ???????? ??????

SQL ????? ?? WAL

SQL ????? 2005, SQL Server 2000, SQL Server 7.0 ?? ????? SQL Server ????????? WAL ????????? ?? ????? ????? ????????? ?? ???? committal ?? ????????? ???? ?? ??? ????????? ?? ??????? ??? ??? ??????? ????? ?????? ??? ???????? ???? ?????? ???

?? clarify ???? ?? ??? ??????? ????? ?????? ?? ?? ????? (?? ??? ?? ??? ??????????? ???? ?? ?? ????? ???????? ?? ????? 150 ?? ?????? ????)?
BEGIN TRANSACTION
   INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION
				
?? ??? ??????? simplistic ?????? ????? ???:
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
???????? ????? ????????
????????? ??????? ??????? ??? ??????? ????? ???? ?? ???? SQL Server ????? ???????? ???? ???? ??? ??????? ?????? ?? ????? ???? ?? ??? ???? ???? ?? ???????? ???? ???
TblTest ??? ???????? ????1. ???? ????? 150 SQL Server ???? ??? ??? ???? ?? ??? ???? ?? ?????? ???? ???

2. ??? ??latched,??? ???? ???, ?????? ??????? ????, ?? ??????? locks ??????? ???? ????

3. ??? ??????? ??? ??????? ????? ?? ?? ??? ??? ??? ????? ???

4. ??? ?? ?????? ???? ????? ?? ????? ???

5. Latch ???? ???? ??? ???

6. ??? ??????? ????????? ?? ??? ?????? ?? ?? ?? ????? ?? ????? ?? ??? ???????? ???????? ?????? ??? ???? ?? ??????? ???? ?? ??? ????? ?? ???????? ???? ???
????????? ?? ???? ????1. ???? ???? ??? ??????? formed ?? ??? ??????? ????????? ?? ??? ?????? ?????? stable ???? ?? ??? ?????? ???? ?? ???? ??? The transaction is not considered committed until the log records are correctly assigned to stable storage.

2. Data page 150 remains in SQL Server data cache and is not immediately flushed to stable storage. When the log records are properly secured recovery can redo the operation if necessary.

3. Transactional locks are released.
Do not be confused with locking and logging. Although important, locking and logging are separate issues when dealing with the WAL. In the example above, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 generally hold the latch on page 150 for the time necessary to perform the physical insert changes on the page, not the entire time of the transaction. The appropriate lock type is established to protect the row, range, page, or table as necessary. Refer to the SQL Server Books Online locking sections for more details on lock types.

Looking at the example in more detail, you might ask what happens when the LazyWriter or CheckPoint processes execute. SQL Server 7.0, SQL Server 2000, and SQL Server 2005 issue all appropriate flushes to stable storage for transactional log records associated with the dirty and pinned page. This ensures the WAL protocol a data page can never be written to stable storage until the associated transactional log records have been flushed.

SQL Server and stable storage

SQL Server 7.0, SQL Server 2000, and SQL Server 2005 enhance log and data page operations by including the knowledge of disk sector sizes (commonly 512 bytes).

To maintain the ACID properties of a transaction, the SQL Server must account for failure points. During a failure many disk drive specifications only guarantee a limited amount of sector write operations. Most specifications guarantee completion of a single sector write when a failure occurs.

SQL Server 7.0, SQL Server 2000, and SQL Server 2005 use 8-KB data pages and the log (if flushed) on multiples of the sector size. (Most disk drives use 512 bytes as the default sector size.) In the case of a failure, SQL Server can account for write operations larger than a sector by employing log parity and torn write techniques.

Torn ????? ?? ??? ?????

????? ??? SQL Server 7.0 ???????? ?????? torn ????? ?? ??? ????? ???? ????? ???? ???? ?? ??? ??:
?? ?????? ?????? i/O ???????? ???? ???????? ?? ???? ?????? outages ?? ???? ?? ??? ????? ?? ??? SQL ????? ?? ?????? ???? ??? ???? ??, ?? ??? ???? 8-???????? (KB) ??????? ????? ??? ???????? 512-???? ?????? ?? ??? flipped ???? ?? ????? ?? ???? ???? ?? ??? ??? ?? ???? ????? ?? ???? ??? ?? ??? ?? ??? ?????? ??? ????? ?? ??? ???? ?????? ??? SQL ?????, ???? ??? ????? ???? ??? ?? ??? ??; ??? torn ????? ?? ??? ????? ??? Torn ????? ????????????? ?? ????? ??? ????? ?? ??? ???? ??? ?? ???? ??? ?? ?? ???? ?? ????? ?? ????????????? ????? ?? ??? ?????? ????? ?? ????????

?????? SQL Server ??????? ??????? ?? 8 KB ???, ?? ????? ???? i/O ???????? 512-???? ?????? ?? ?? ????? ?? ??? ??? ?????, 16 ???????? ??????? ????? ????? ???? ???? ??? torn ????? ?????? ???? (?????? ?? ???, ?????? ?? ???? ????) ??? ???????? ?????? ?? ????? ?? ???? 512-???? ?????? ????? ?? ?? 8 KB i/O ???????? ?? ???? ???? ?? ??? ?? ???? ??? ?????? ?? ???? ???? ??????? ????? ?? ???? ?????? ??????????? ???? ???? ??, ??? ????? ?? ??????? ????? ????? ???? ?? ??? ??? ??????, ?????? ?? ??? ???? ?? ???? ???

?? ???? ??????????? ???? ?? ?? ???? ????? ?? ??? ????????? ?? ???? ??? ????? ????? ????? ???????? caches ?? ????? ???? ??? ?? ???? ????? ?? ?????? ???, ???? ???? ??? torn ????? ??? ???? ?? ??? true, ??? ?? ?????? ???? ???
???:SQL Server 7.0 ??? ???????? ??? ?? torn ????? ?? ??? ????? ????? ???? ??? ?????sp_dboption?? ??? ???? ?????? ?? ??? ????? ????? ???? ?? ????

??? ??????

??? ?????? ???? ???? torn ????? ?? ??? ????? ?? ???? ???? ??? ???????? 512-???? ?????? ??? ?????? ????? ??? ?? ?????? ????? ??? ????? ??? ??????? ?? ??? ???? ?? ??? ??????? ??????? ???? ??? ?????????? ????? ????? ??? ?? ?????? ?? ?? ???? 512-???? ????, SQL Server 7.0 SQL Server 2000 ?? SQL Server 2005 ???? ????????? ?? committal ???????? ??? ???? ??? ?? ???? ????? ????? ???????? ?? ??? ?????

The changes provide increased data consistency, even over prior versions of SQL Server.

Versions of SQL Server earlier than 7.0

Versions of SQL Server earlier than 7.0 did not provide log parity or torn bit detection facilities. In fact, those versions can write the same log page multiple times until the log records fill the 2-KB log page. This can expose transactions that have successfully committed. If the log page is being rewritten during a failure, a sector with the committed transaction may not get rewritten properly.

Performance impacts

All versions of SQL Server open the log and data files using the Win32CreateFile?????? ?? ??? ???? ???.. ThedwFlagsAndAttributesmember includes theFILE_FLAG_WRITE_THROUGHoption when opened by SQL Server.
FILE_FLAG_WRITE_THROUGH
Instructs the system to write through any intermediate cache and go directly to disk. The system can still cache write operations, but cannot lazily flush them.

The FILE_FLAG_WRITE_THROUGH option ensures that when a write operation returns successful completion the data is correctly stored in stable storage. This aligns with the WAL protocol ensuring the data.
Many disk drives (SCSI and IDE) contain onboard caches of 512 KB, 1 MB, or larger. However, the drive caches usually rely on a capacitor and not a battery-backed solution. These caching mechanisms cannot guarantee writes across a power cycle or similar failure point. They only guarantee the completion of the sector write operations. This is specifically why the torn write and log parity detection were built into SQL Server 7.0, SQL Server 2000, and SQL Server 2005. As the drives continue to grow in size, the caches become larger, and they can expose larger amounts of data during a failure.

Many hardware vendors provide battery-backed disk controller solutions. These controller caches can maintain the data in the cache for several days and even allow the caching hardware to be placed in a second computer. When power is properly restored, the unwritten data is completely flushed before the further data access is allowed. Many of them allow a percentage of read versus write cache to be established for optimal performance. Some contain large memory storage areas. In fact, for a very specific segment of the market, some hardware vendors provide high-end battery-backed disk caching controller systems with 6 GB of cache. These can significantly improve database performance.

Advanced caching implementations will handle theFILE_FLAG_WRITE_THROUGHrequest by not disabling the controller cache because they can provide true rewrite capabilities in the event of a system reset, power failure, or other failure point.

I/O transfers without the use of a cache can be significantly longer due to the mechanical time needed to move the drive heads, spin rates, and other limiting factors.

Sector ordering

A common technique used to increase I/O performance is sector ordering. To avoid mechanical head movement the read/write requests are sorted, allowing a more consistent motion of the head to retrieve or store data.

The cache can hold multiple log and data write requests at the same time. The WAL protocol and the SQL Server implementation of the WAL protocol require flushing of the log writes to stable storage before the page write can be issued. However, use of the cache might return success from a log write request without the data being written to the actual drive (that is, written to stable storage). This may lead to SQL Server issuing the data page write request.

With the write cache involvement, the data is still considered to be in volatile storage. However, from the Win32 APIWriteFilecall, exactly how SQL Server sees the activity, a successful return code was obtained. SQL Server or any process using theWriteFileAPI call can only deduce the data has correctly obtained stable storage.

For discussion purposes, assume that all sectors of the data page are sorted to write before the sectors of the matching log records. This immediately violates the WAL protocol. The cache is writing a data page before the log records. Unless the cache is fully battery-backed, a failure can cause catastrophic results.

When evaluating the optimal performance factors for a database server, there are many factors to consider. The foremost of these considerations is"Does my system allow valid FILE_FLAG_WRITE_THROUGH capabilities?"

???:Any cache you are using??????fully support a battery-backed solution. All other caching mechanisms are suspect to data corruption and data loss. SQL Server makes every effort to ensure the WAL by enabling FILE_FLAG_WRITE_THROUGH.

Testing has shown that many disk drive configurations may contain write caching without proper battery backup. SCSI, IDE, and EIDE drives take full advantage of write caches.

In many configurations, the only way to properly disable the write caching of an IDE or EIDE drive is with a specific manufacturer utility or by using jumpers located on the drive itself. To ensure that the write cache is disabled for the drive itself, contact the drive manufacturer.

SCSI drives also have write caches but these caches can commonly be disabled by the operating system. If there is any question, contact the drive manufacturer for appropriate utilities.

??? Stacking ?????

???? ??? Stacking ?????? ???? ?? ???? ??? ????? ??????? ?? ?? ?????? IDE ?????? ???????? ??? ???? ?? ???? ??? ??:
??????? ??? ??, ?? ??? ?????? ??? ??? ??? ????? ???? ????? ??? ???? ??? ???? ??? ?? ?? ????? ?????????? ????? ???? ?? ??????? ??????

????? ?? ??? ????? ???? ?????? ???? ?? ??? ??? ????? ???? ????? ???? ???? ??? ????? ???? ?? ???? ???? ??????? ???? ?? ???? ?? ?? ???? ??????????? ???? ?? ?? ?? ???? ???? ????? ???? ??? ?? ?? ???? ??? ???? ??? ??? ?????? ?????? ?????? ????????? ???? ?? ??? ???? ?? ???? ?? ???: ????????? ?? ???? ???

????: ???? Reallocation (AWR)

???? ??????? ????? ????? ???? ?? ???????? ???? ?? ??? ???? manipulation ?? ????? ????? ???????? ?? ??? ????? ?? ??? ??? ????? ?? ???????? ?? ?????? IDE ?????? ???????? ??? ???? ?? ??? ??:
?? ?????? ??? ???? ?? ?? ??? ?? ?? ?????? ???? ???????? ?? ????? ???? ?? ????? ?? ????? ?? ?? ???? ??? ??? ????? ???? ????????? ?? ????? ??? ????? ?????? ???? ??, ?? ????? ?? ????? ????? ?? suspect ?????? ?? reallocated ?????? ?? ??? ??? ????? ???????? ???????? ?? ?? ??? ?? ???? Reallocation ????? ????? ???? ????? ?????? ?? ????? ???
??? ????? ????? ?? ????, ??? ?? ??? ???????? ?? ?????? ?????? ???? ?? ??? ?? ?? ?? ???? ????? ?????? ?? ???? ??? ????? ?????? ?? ??? ????? ?? ??? preferable ??, ????? WAL ????????? ?? ???? ?? ??????? ????-???? ??? ??? ???? ?? ??? ?? ?????? ???: ???? ?? ?????? ????? ?? ??? ???? ??? WAL ???????? ??? ???? ?????? ?????? ?? ???? ??? ??? ???? ???? ??? ????? ?????? ??? ??? ??, ?? ?????? ?? ??? AWR ??????? ???? ???? ??? ??????? ????? ?????? ?? ???? ??? ??? ????? ???? ????? ???? ????????? ?? ???? ?? ???? ??? ?? ??????, ?????????? ?? ????? ?? ???? ???, ?? ???? ????? ?? ???? ?? ???????? ?? ?????? ?????? ?? ?????? ?? ??? ???? ?? ??? ???? ??? ???

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

???? ?? ??????? ????????? ???? ?? ??? ???? ??????? ?????????? ???? ????? ?? precautions ????
  • ?? ????? ????? ?????? ???? catastrophic ?????? ?? ???????????? ???? ?? ??? ???????? ?? ?? ?? ????????? ???? ?? ??? ??? ????? ????? ??? Offsite ?????? ?? ???? precautions ??????? ????
  • ??????? ??? ??? secondary ???????????? ???????? ?? ???? ???? ??? ?? ???? ?? ??????? ??????? ?? ??????? ?????
  • ????????? ???? ?? ??? ?? ?????? ?????? (???? ??????, ????? ????????, ??? ????????, ?????? ??????, lockups, ???? ?????? ?? ??? forth) ??? ?????? ????????? ????? ?? ???? ????
  • ????????? ???? ?? ?? ???? ?????? ??????:
    • ????? ????? integrated ???
    • ????? ???? reissue ?? ???? ???
    • ??? ???? ??? ????? ???? ?? ???? ??? ?????? ???
    • ????? ?????? re-mapping realtime ??????? ???
  • Torn ????? ?? ??? ????? ????? ????; ????? ?? ????? ???????? ???????? ???
  • ??? ???? ??, ??? ??? ????? ?????? ?? ???? ??? ????? ?? ??? ?????? ???? RAID ?????? ???????? ?????
  • ?? ?????? ???????? OS ???????? ??? ???? ???? ????? ????? ?? ?????? ?? ?????? ??? ?? ????? ????? ?? ?? ideal ?????? ?? ???? ???

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

???? ??? ???? ???? ?? ????????, ?? ??? ???? ?? ????????? ???? ????? ?? ?? ??? ???? ?????? ?? ??? ?????? ?? ???????? ?? ????????? ??? ???? ???? ?? ????? ?????? ?? ???? ?????? ????? ???? ?????? ???

???:????????? ???? ?? ??? ???????? ?????? ??????? ?? ???? ??? ??? ?? ??????? ?????? ?? ?????? ???????

Microsoft ?? ?? SCSI ?? IDE ?????? ?? ????? ???? ??? ??????? ???? ???SQLIOStress?????? ?? ???? ?? ?????? simulates ???????? ???? ?????? ?? ??? ?????? ?? ??? ???? ?????????? ???/???? ??????? ??? ??????? ???????? ????? ??? ??? ???? ?? ????? ???? ?? ??? ?? ?????? ?? ??? 50 ?? 70 ?? ??? ????? ?? 5,200 ?? 7,200 ?? ??? ?? RPM ?????? ?? ????? ??? ???? ???????? ???????

???? ??????? ?? ?? ???? ??? ????? ?? ???SQLIOStress?????? ?? ???, ????? ???? ?? Microsoft ???????? ??? ?????:
231619SQL ????? ???? ???? ????? ???????? stress ???? ?? ??? SQLIOStress ?????? ?? ????? ???? ????
(?????? ?? ???, Compaq, Dell, ?????, HP, ?? ??? ???) PC ?????????? ???? ???????? ?? ???? ??? ????? ?? ??? ???? ???? ???????, ???? ?????? ?? ?? ?? ???? ????? ?? ???? ?? ?????? ????? ????? ??????? ???? ??? ?? ?????

Data devices

In all but non-logged situations, SQL Server will require only the log records to be flushed. When doing non-logged operations, the data pages must also be flushed to stable storage; there are no individual log records to regenerate the actions in the case of a failure.

The data pages can remain in cache until the LazyWriter or CheckPoint process flushes them to stable storage. Using the WAL protocol to ensure that the log records are properly stored ensures that recovery can recover a data page to a known state.

This does not mean that it is advisable to place data files on a cached drive. When the SQL Server flushes the data page(s) to stable storage, the log records can be truncated from the transaction log. If the data pages are stored on volatile cache, it is possible to truncate log records that would be used to recover a page in the event of a failure. Ensure that both your data and log devices accommodate stable storage properly.

Increasing performance

The initial question that arises is: "I have an IDE drive that was caching but when I disabled it, my performance became significantly less than expected -- why?"

5,200 ?? ?? RPM ?? ?? ?? IDE ?????? ?? ?? Microsoft ?????? ??????? ?????, ?? SCSI 7,200 ?? ?? RPM ?? ?????? ??? ???? ????? ???? ?? IDE ?????? ?? ??? ??? mechanical ???????? ?? ???? ??? ?? ??? ????????

???????? ?? ???? ?? ??? ???? ?? ??? ???? ??????? ???? ?????? ??: "????????? ?? ???."

?? ???? ????????? ?? ?? ???????? (OLTP) ?????? ?????? ????? ?????? ????????? ???? ?? ???????? ?? ??? ??? ?????? ???????? ?? ?? ?? ???? ??? ??? ?? ??? ???? ??? ?????? ?? ???? ??????? ensuring ???? ??? ???????? ????? ?????? ????? ?? ????? ?? ??? ?? ?? ????? ?????

???????????? ?????? ?????? ?? SQL Server ?? ??? ??????????? ???????? ?, ?? ??? ????????? ?? ?? increased ???? ????????? ?? ????? ?? ??? ???

??????? ?? ??? ?? ?? ???? ???? ??????? ?? ??? 2 MB ?? 512 KB ?? ???? ?????? ?? ???? ??????????? ???? ?? ???? ????
????? ?????? ?? ????? ????:
CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
   INSERT INTO tblTest VALUES ('Test')
				
????? ??????? ?????? SQL ????? ?? ??? ????? ???:
SCSI(7200 RPM) 84 ?????
SCSI(7200 RPM) 15 ????? (Caching ????????)

IDE(5200 RPM) 14 ????? (?????? ??? ?????)
IDE(5200 RPM) 160 ?????
??? ??????????? ??? ???? 4 ????? ??? ???????? ???? ???????? ?? ???? ?????? ?? ?? ??? ????????? ??? ?????? ????? ???

????, ??? ????? ?????? ?? ?????? ??? ????????? ?? ???? ?? ??? ?? ????? ?? ????????? ?? ???????? ???????? ????, ?? ?? ??? ????????? ?? ??? ??? ??????? ???? ?????? ?? ?????? ???????? flush, 512 ?????? ?? ????, ?? ?????????? mechanical ?????? ?? ????????? ?? ???????? ???

???? ??? ????????? ????? ???? ???? ??, ?? ????????? ?? ??? ??? ??????? ???? ???? ?? ???? ?? ?? ??? ???, ??? ???? gathered ??? ??????? ?? ???? ???? ?? ??? ????? ???? ?? ???? ??? Mechanical ????????? ???????????? ?? ?? ?? ???

???????:?? ????????? ??????? ???? ????? ?? ???? ???? ??? ???? ???? ?? ????????? ?? ??????? ?? ??????? ???????? ???? ?? ??? ?? ???? ??? ??? ?? increased overhead. SQL Server 7.0, SQL Server 2000 ?? SQL Server 2005 ??????????? ?????? ?? ????? ????SQL ?????: ???????????????? ???-?????? ???????? ?? ?????? ????? ??? ??,??? ???? ?????/??????????? ???? mechanical ????? ??????? ?? ??? ?? ???? ????????? ?? ????? ?? ???? ????

??? flush ?? ??????? ??? ?? ????? ?? ????????? ???? ?? ??? ??? ????? ?? ?????? ?? ?? ???? ?? ???? ??? ??? ?????? ???, ???? ??? ????????? ???? ???? ??? ??? ???????, ?? ?????????? ??? ?? ?? ???? ??? ??????? undesired ?????? ??????? ???? ?? ???? ??????? ?? ????????? ?? ?????? Perhaps ??? ???? ????? ???? ???? ?? ?? ???? ??? ?? ?? ???? ?? ??? batches ???? ?? ??? ????? ??????:
BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
BEGIN
   INSERT INTO tblTest VALUES ('Test')

   if(0 = cast(@@IDENTITY as int) % 10)
   BEGIN
      PRINT 'Commit tran batch'
      COMMIT TRAN
      BEGIN TRAN
   END
END
GO

COMMIT TRAN
GO
				
SQL ????? 6?x???? ???? ?? ?? ???? ????????? ??? writes ?? ???? ??????????? ???????? ???? ??? ???? ???? SQL ????? 6?x?? ?? 2 KB ??? ????? rewrites ?? ??? ??? ?????? ?? ???? ???? ?? ??? ???????????? SQL Server 7.0, SQL Server 2000 ?? SQL Server 2005 ??? 512-???? ?????? ???? ????? ???? ?? ??? ????? ?? ???? ?? ?? ???? ???? Mechanical ?????? ??????? ?? ?????? ?? relates ???? ??? ?? ???? ?? ????? ???????, ?? ???, SQL Server 6 explained.x????????? ????????? ?? ???? ?????? ?? ???? ???
SQL ????? ?? ??? ?????? ?????? ?? ?????? ???? ?? ??? ? ????? ?????? ?? ????? ???? ?? ??? ????????? ? ?? ??? ??? Microsoft SQL Server Always-on ??????? ?????? ??????? ????????? ?? ???? ??? ??? FoSQL Server ??????? ????? ?? ????? ?? ?????? ?????????? ?? ???? ??? ???? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
967576Microsoft SQL Server ??????? ????? ?????/?????? ??????????

???

???? ID: 230785 - ????? ???????: 03 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
??????: 
kbhowto kbinfo kbmt KB230785 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:230785

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

 

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