Microsoft sql server I/o basics: Chapter 2

Terms

This section defines new terms that are used both in this paper and in documents that are referenced by this paper.

Subsystem / Path

The I/O subsystem or path includes those components that are used to support an I/O operation. These include, but are not limited to, the operating system, drivers, disk drives, controller cards, and caches.

Checksum

Checksum is an error-detection scheme that uses a formulated numeric value to represent a grouping of bits. The same formula is applied to the grouping to verify that the accompanying numeric value is the same. If it is not, the data is considered to be changed or damaged.

In other words, a formula is applied to a given set of bytes. The same formula can later be used to examine the data for damage.

As a simple example, take the following 2-byte string value of “AB” and apply a formula to it (Value = Position + ASCII value). Checksum is the sum of the values.

Character	POS	ASCII Value	Formula Value
A	1	65	67
B	2	66	68
		Checksum	135

Now assume that the string was stored on disk but when read back it was “AC”.

Character	POS	ASCII Value	Formula Value
A	1	65	67
C	2	67	69
		Checksum	136

Comparing the checksum values indicates that the values do not match and damage has occurred to the data.

Clearly this simple example would not handle a change of the string to “BA.” However, the routines that are used by SQL Server and Microsoft Exchange are more sophisticated and detect position reversal as a damaged data error condition.

Read Retry

When a read from stable media returns an error, the read operation is tried again. Under certain conditions, issuing the same read returns the correct data. This is a serious I/O subsystem problem that should be addressed immediately to avoid SQL Server and overall system stability problems and data loss. Obtain assistance from your I/O vendor if you encounter retries.

DBCC Page Auditing

DBCC page auditing is the process of executing common DBCC consistency checks as pages are read from disk.

Time of Last Access

The time of last access is a caching algorithm that enables cache entries to be ordered by their access times. SQL Server 2005 changed its lazy writer so that it returns data pages to the free list based on the time they were last accessed. This is different from the reference count design that was used in SQL Server 7.0 and 2000.

Idle Server

An idle server provides the ability, for certain SQL Server SKUs, to become silent; the idle server suspends the execution of core tasks such as checkpoint and lazy writer. The suspension takes place when no user requests are present and wakes when a new request arrives. This is similar to the suspend and resume operations performed by the operating system.

Copy-On-Write / Snapshots

Copy-on-write is the act of making a copy of the data before a modification is complete. SQL Server copy-on-write actions are used to maintain snapshot databases in SQL Server 2005. Before a data page in the database can be modified, the unchanged image of the page is written (copied) to the snapshot database. The snapshot contains only the original images of data pages that have been modified after snapshot creation. A union of the unmodified database pages and the snapshot pages provides the point-in-time view of the data.

SQL Server 2005 introduces the snapshot database feature for point-in-time databases and online DBCC operations. For more information, see Database Snapshots in SQL Server 2005 Books Online.

Sparse Files

Sparse files provide a way to save disk space for files that contain large sections of data that is composed of zeros. An NTFS file marked as sparse only allocates disk clusters for the data explicitly written by the application. Ranges of the file that have not been written to are represented by non-allocated space. When data is read from allocated ranges, the data is returned as it was stored. Data read from non-allocated ranges is returned as zeros.

For information on sparse file behavior, see Understanding Sparse File Sizes in Database Snapshots (http://msdn2.microsoft.com/en-us/library/ms175823.aspx) and Sparse Files (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/sparse_files.asp) on the Microsoft Developer Network (MSDN).

For example, you can create a sparse file of 100 GB on a 20-GB drive. The storage space that is required for the sparse file is only that of the actual bytes written to the file and not the maximum file size.

SQL Server 2005 uses sparse files for snapshot database files and online DBCC operations against files stored on NTFS volumes.

Streams

NTFS volumes enable data files to have one or more secondary storage streams. These streams are all part of the same file; if the file is copied, all streams are copied. Each stream works independently, enabling individual data storage attributes and sizing.

SQL Server 2005 online DBCC checks are based on the database snapshot technology by using transient, secondary file streams to store point-in-time images of data pages when the DBCC is in progress.

Note: Many common utilities do not expose the secondary stream information. This includes size.

For more information, see: File Streams on MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/file_streams.asp)

Harden or Hardening

To harden is to require that data be stored on stable media during a specific event; the data is not just in a cache that could be lost if power was lost. For example, when checkpoint is finished, the dirty data pages have been written to stable media and are considered to be hardened. As soon as the hardened state is declared, activities such as truncating the inactive part of the transaction log can occur because the log records are no longer needed for crash recovery.

Memory Scribbler

The SQL Server support team uses the term scribbler to indicate an unexpected memory change for memory where the caller is not the true owner.

The following code example shows the setting of values in illegal array positions.
char gchArray[10];

gchArray[-1] = ‘B’; -- Underflows the array bounds

gchArray[10] = ‘R’; -- Overflows the array bounds
The first example puts the value ‘B’ in the memory location just in front of the array allocation and the second puts an ‘R’ in the memory location just after the array allocation. In both cases, they ‘scribble’ in memory that the array does not own.

Another example of a scribbler is using a stale pointer.

char * pchData = new char[10];
DELETE [] pchData;

memcpy(pchData, 0, sizeof(char) * 10)); -- pchData is not valid

The memory controlled by the pointer was released with the DELETE [] operation and is no longer owned by the current code line. The memory could be reused by another thread and the memcpy would damage memory it did not own, thereby scribbling on it.

Scribbling occurs because of bugs. Some of these are SQL Server bugs but many others involve other components. Be cautious of third-party extended stored procedures, COM objects, and Linked Servers that may be present in the SQL Server address space.