Microsoft KB Archive/917047

= Microsoft SQL Server I/O subsystem requirements for the tempdb database =

Article ID: 917047

Article Last Modified on 11/2/2007

-

APPLIES TO


 * 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

-





SUMMARY
Microsoft SQL Server requires that the I/O subsystem used to store system and user databases fully honor Write-Ahead Logging (WAL) requirements through specific I/O principals. These requirements are necessary in order to honor the ACID properties of transactions: Atomic, Consistent, Isolated, and Durable. Details about I/O subsystem compliance requirements are provided in the following references:  SQL Server 2000 I/O basics

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

Note This article also applies to SQL Server 2005. SQL Server 7.0 and SQL Server 2000 logging and data storage algorithms extend data reliability

http://support.microsoft.com/default.aspx?scid=kb;en-us;230785



The following list is a quick summary of the requirements:
 * Write ordering must be maintained.
 * Dependent write consistency must be maintained.
 * Writes must always be secured in/on stable media.
 * Torn I/O prevention must occur.

Durability maintenance remains critical for all other databases but may be relaxed for the tempdb database. The following table summarizes several of the critical I/O requirements for SQL Server databases.


 * Transactional sector rewrites involve fully logged operations by the subsystem permitting a sector to be fully moved, replaced, or rolled back to the original image. These rewrites are typically discouraged because of the additional overhead required to perform such actions. An example of this would be a defragmentation utility that is moving the file data. The original sector in the file cannot be replaced with the new sector location until the new sector and data are completely secured. The remapping of the sector must occur in a transactional manner so that any failure, including a power failure, causes the re-establishment of the original data. Make sure that you have locking mechanisms available during this kind of process to prevent invalid data access, thereby upholding the other tenants of SQL Server I/O.

Survival across outage
The tempdb database is a scratch area for SQL Server and is rebuilt on every SQL Server startup. The initialization supersedes any need for data to survive a restart.

Transactional sector rewrite operations
To guarantee the success of the recovery processes, such as rollback and crash recovery, the log records must be correctly stored on stable media before the data page is stored and cannot be rewritten without honoring transactional properties. This requires the subsystem and SQL Server to maintain specific attributes, such as write ordering, sector aligned and sized writes, and other such I/O safety attributes outlined in the previously-mentioned documents. For the tempdb database, the crash recovery is unnecessary because the database is always initialized during SQL Server startup. However, the tempdb database still requires rollback capabilities. Therefore, some attributes of the WAL protocol can be relaxed.

The storage location for the tempdb database must act in strict accordance with established disk drive protocols. In all ways, the device on which the tempdb database is stored must appear and act as a physical disk providing read after write capabilities. Transaction sector rewrite operations may be an additional requirement of specific implementations. For example, SQL Server does not support database modifications by using NTFS file system compression because NTFS compression can rewrite sectors of the log that have already been written and considered hardened. A failure during this type of rewrite can cause the database to be unusable, damaging data that SQL Server already considered secure.

Note SQL Server 2005 extended support or compression to read only databases and file groups. See the SQL Server 2005 Books Online for complete details.

Transactional sector rewrite operations are pertinent to all SQL Server databases that include the tempdb database. A growing variety of extended storage technologies use devices and utilities that can rewrite data that SQL Server considers secure. For example, some of the emerging technologies perform in-memory caching or data compression. In order to avoid severe database damage, any sector rewrite must have full transactional support in such a way that if a failure occurs, the data is rolled back to the previous sector images. This guarantees that SQL Server is never exposed to an unexpected interruption or data damage condition.

You may be able to put the tempdb database on specialty subsystems, such as RAM disks, solid state, or other high speed implementations that cannot be used for other databases. However, the key factors presented in the “More Information” section must be considered when you evaluate these options.



MORE INFORMATION
Several factors should be carefully studied when you evaluate the storage location of the tempdb database. For example, the tempdb database usage involves, but is not limited to, memory footprint, query plan, and I/O decisions. The appropriate tuning and implementation of the tempdb database can improve the scalability and responsiveness of a system. This section discusses the key factors in determining the storage needs for the tempdb database.

High speed subsystems
There are various high speed subsystem implementations available on the market that provide SQL Server I/O subsystem protocol requirements but that do not provide durability of the media.

Important Always confirm with the product vendor to guarantee full compliance with SQL Server I/O needs.

A RAM disk is one common example of such an implementation. RAM disks install the necessary drivers and enable part of the main RAM disk to appear as and function like any disk drive that is attached to the system. All I/O subsystems should provide full compliance with the SQL Server I/O requirements. However, it is obvious that a RAM disk is not durable media. Therefore, an implementation such as a RAM disk may only be used as the location of the tempdb database and cannot be used for any other database.

Keys to consider before implementation and deployment
There are various points to consider before deployment of the tempdb database on this kind of subsystem. This section uses a RAM disk as the basis for discussion, but similar outcomes occur in other high speed implementations.

I/O safety
Compliance of read after write and transactional sector writes is a must. Never deploy SQL Server on any system that does not fully support the SQL Server I/O requirements, or you risk damage and loss of your data.

Pages already cached (Double RAM cache)
Temporary tables are like all other tables in a database. They are cached by the buffer pool and handled by lazy write operations. Storing temporary table pages on a RAM disk causes double RAM caching, one in the buffer pool and one on the RAM disk. This directly takes away from the buffer pool’s total possible size and generally decreases the performance of SQL Server.

Giving up RAM
The RAM disk designates a part of main RAM as the name implies. There are several implementations of RAM disks and RAM-based files caches available. Some also enable physical I/O backing operations. The key element of the RAM-based file cache is that it directly takes away from the physical memory that can be used by SQL Server. Always have strong evidence that adding a RAM-based file cache improves the application performance and does not decrease other query or application performance.

Tune first
An application should tune to remove unnecessary and unwanted sorts and hashes that could cause the use of the tempdb database. Many times the addition of an index can remove the need for the sort or hash in the plan completely, leading to optimal performance without requiring the use of the tempdb database.

Possible benefit points
The benefits of putting the tempdb database on a high speed system can only be determined through rigorous testing and measurements of the application workloads. The workload has to be studied carefully for the characteristics that the tempdb database may benefit from, and the I/O safety must be confirmed before deployment.

The sort and hash operations work together with the SQL Server memory managers to determine the size of the in-memory scratch area for each sort or hash operation. As soon as the sort or hash data exceeds the allocated in-memory scratch area, data may be written to the tempdb database. This algorithm has been expanded in SQL Server 2005, reducing the tempdb database usage requirements over earlier versions of SQL Server. For example, by using a pure forced sort of a table, no indexes, descending order, and the same hardware configuration, SQL Server 2005 shows noticeable improvements over SQL Server 2000.

Caution SQL Server is designed to account for memory levels and current query activities when making query plan decisions that involve the use of tempdb database operations. Therefore, the performance gains vary significantly based on workloads and application design. We strongly recommend that you complete testing with the preferred solution to determine possible gains and evaluate I/O safety requirements before such a deployment.

SQL Server uses the tempdb database to handle various activities involving sorts, hashes, the row version store, and temp tables:
 * Temporary tables are maintained by the common buffer pool routines for data pages and generally do not exhibit performance benefits from specialty subsystem implementations.
 * The tempdb database is used as a scratch area for hashes and sorts. Reducing I/O latency for such operations may be beneficial. However, know that adding an index to avoid a hash or a sort may provide a similar benefit.

Run baselines with and without the tempdb database stored on the high speed subsystem to compare benefits. Part of the testing should include queries against the user database that do not involve sorts, hashes, or temporary tables, and then confirm that these queries are not adversely affected. When you evaluate the system, the following performance indicators can be helpful.

Transactional sector rewrite example
The following example elaborates the data security that is required by SQL Server databases.

Assume a RAM disk vendor uses an in-memory compression implementation. The implementation must be correctly encapsulated by providing the physical appearance of the file stream as if the sector was aligned and sized so SQL Server is unaware and correctly secured from the underlying implementation. Look at the compression example closer.

The device may perform the following actions to help secure sector 1’s data when it is combined with sector 2’s data.

The ability to provide locking mechanisms around the sector modifications and roll back the changes when the sector exchange attempt fails is considered transitionally compliant. For implementations that use physical storage for extended backing, it would include the appropriate transaction log aspects to help secure and roll back changes that were applied to the on-disk structures to maintain the integrity of the SQL Server database files.

Any device that enables the rewrite of sectors must support the rewrites in a transactional way so that SQL Server is not exposed to data loss.

Note The instance of SQL Server is restarted when online I/O and rollback failures occur in the tempdb database.

Be careful when you move the tempdb database
Be careful when you move the tempdb database because if the tempdb database cannot be created, SQL Server will not start. If the tempdb database cannot be created, start SQL Server by using the (-f) startup parameter and move the tempdb database to a valid location.

To change the physical location of the tempdb database, follow these steps:   Use the ALTER DATABASE statement and the MODIFY FILE clause to change the physical file names of each file in the tempdb database to refer to the new physical location, such as the new disk. Alter database tempdb modify file (name = tempdev, filename = 'C:\MyPath\tempdb.mdf')

Alter database tempdb modify file (name = templog, filename = 'C:\MyPath\templog.ldf')  Stop and then restart SQL Server.

Partner product certifications are not a guaranty of compatibility or safety
A third-party product or a particular vendor can receive a Microsoft logo certification. However, partner certification or a specific Microsoft logo does not certify compatibility or fitness for a particular purpose in SQL Server.

Support
If you use a subsystem with SQL Server that supports the I/O guarantees for transactional database use as described in this article, Microsoft will provide support for SQL Server and SQL Server-based applications. However, issues with, or caused by, the subsystem will be referred to the manufacturer.

For tempdb database-related issues, Microsoft Support Services will ask you to relocate the tempdb database. Contact your device vendor to verify that you have correctly deployed and configured the device for transactional database use.

Microsoft does not certify or validate that third-party products work correctly with SQL Server. Additionally, Microsoft does not provide any warranty, guaranty, or statement of any third-party product’s fitness for use with SQL Server.

