Microsoft sql server I/o basics: Chapter 2
Microsoft SQL Server 2005 Enhancements
Yüklə 0,61 Mb.
|
- Bu səhifədəki naviqasiya:
- Writes
- Damage
- PAGE_VERIFY usage
Microsoft SQL Server 2005 EnhancementsThe following section outlines the core I/O enhancements made in SQL Server 2005. ChecksumSQL Server 2005 introduces the ability to checksum data pages, log blocks, and backups. For details on checksum capabilities and usage, see the ALTER DATABASE topic in the PAGE_VERIFY section in SQL Server 2005 Books Online. The expansion of hardware capabilities along with the increased use of virus protection, caching mechanisms, and other advanced filter drivers increase the complexity of the I/O subsystem and expand the point-of-failure possibilities. Microsoft SQL Server 2005 and Microsoft Exchange Server products provide checksum capabilities to enhance data protection. The checksum algorithm used by SQL Server 2005 is the same algorithm used by Microsoft Exchange Server. The SQL Server algorithm has an additional rotation to detect sector swaps. Microsoft Exchange Server introduced checksum capabilities several years ago with great success. Search the Microsoft Knowledge Base for more information about error message -1018, which indicates a checksum failure for the Exchange Server product. The following is an excerpt from the Exchange Server Knowledge Base article KB151789. “When you perform a transaction with the Jet database, the information store or the directory store writes the transaction to a transaction log file (Edb*.log in Mdbdata or Dsadata). The transaction is then committed to the Jet database. During this process, the Jet engine calculates the page's checksum value to be written, records it in the page header, and then requests that the file system writes the 4-KB page of data to the database on disk. … Even after you restore from a known good backup, however, the -1018 errors may appear again unless the root causes of the physical data write problems are resolved.” The checksum algorithm is not an ECC or CRC32 implementation but a much less CPU-intensive calculation that avoids affecting database throughput. The data page and log throughput affects are limited by the buffer pool caching and read-ahead designs. This enables the writes and reads to be done out-of-critical-band when it is possible. WritesSQL Server data pages are typically written to disk by the checkpoint or lazy writer processing.
Checksums are calculated immediately before the data page or log block is written to disk. SQL Server tries to perform writes in groups and in a background manner whenever possible to avoid directly affecting user queries. The caching of data pages and grouping of log records helps remove much, if not all, of the command latency associated with a write operation. As described, the checksum calculation activity can frequently be done out-of-band from the original request, thereby reducing any direct affect checksum may add to the write. Note: The model database is checksum (page audit) enabled. Therefore, all new databases created in SQL Server 2005 are checksum enabled to maximize data protection. ReadsWhen a page or log block is read from disk, the checksum (page audit) value is calculated and compared to the checksum value that was stored on the page or log block. If the values do not match, the data is considered to be damaged and an error message is generated. SQL Server uses read-ahead logic to avoid query stalls caused by I/O waits. The read-ahead design tries to keep the physical reads and checksum comparisons out of the critical path of the active query, decreasing the performance effects of checksum activity. DamageThe checksum is designed to detect whether one or more bits of the data unexpectedly changed; it was not designed to correct problems. The checksum is calculated immediately before the write to disk and verified immediately after the physical read from disk. If damage is detected, this indicates a serious I/O subsystem data integrity problem and the I/O subsystem should be thoroughly checked for problems. A failure indicates that data being written to and retrieved from stable media did not maintain its integrity. Disk drives, caches, filter drivers, memory, CPUs, and other components should be reviewed in complete detail if the system reports checksum failures. Be cautious of power outages as well.
Similar checksumming activity occurs for log block writes and reads when CHECKSUM protection is enabled. Log writes and reads always use a parity bit design (torn protection) to mark the valid blocks in the log. An additional checksum of the log block is new and is applied only when the database checksum verification option is enabled. The following table outlines the verification actions SQL Server 2005 performs based on the database’s PAGE_VERIFY option and the page’s status value, which is located in the page header. Some of the actions in this table might not seem correct because the page’s status value on a read appears to override the database’s current setting. However, on a read the possible verify action is determined from the page header status and not from the current database setting. For example, a checksum cannot be checked on the read if the checksum wasn’t calculated and stored during the write of the page.
Note: SQL Server does not rewrite all database pages in response to an ALTER DATABASE PAGE_VERIFY change. The PAGE_VERIFY option can be changed over time, and pages written to disk will reflect the option that was in effect at the time they were written. Therefore, the database can have pages in any one of the three available verification states. There is no single command that establishes a PAGE_VERIFY option and applies it to all pages of the database. This includes backup and restore.
The only way to make sure that all user data pages contain the desired page verification protection is to copy all data, at the row level, to a new database that was created by using the appropriate page verification option. Yüklə 0,61 Mb. Dostları ilə paylaş:
|