Microsoft SQL Server I/O subsystem requirements for the tempdb database (917047)

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.

I/O requirement	Brief description	System or user	tempdb
Write ordering Dependent write consistency	The ability of the subsystem to maintain the correct order of write operations. This can be especially important for mirroring solutions, group consistency requirements, and SQL Server WAL protocol use.	Required	Recommended
Read after write	The ability of the subsystem to service read requests with the latest data image when the read is issued after any write is successfully completed.	Required	Required
Survival across outage	The ability for data to remain fully intact (Durable) across an outage, such as a system restart.	Required	Not applicable
Torn I/O prevention	The ability of the system to avoid splitting individual I/O requests.	Required	Recommended
Sector rewrite	The sector can only be written in its entirety and cannot be rewritten because of a write request on a nearby sector.	* Discouraged, only permitted if transactional	* Discouraged, only permitted if transactional
Hardened data	The expectation that when a write request or a FlushFileBuffers operation is successfully completed, data has been saved to stable media.	Required	Not applicable
Physical sector alignment and size	SQL Server interrogates the data and log file storage locations. All devices are required to support sector attributes permitting SQL Server to perform writes on physical sector-aligned boundaries and in multiples of the sector size.	Required	Required

* 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.

Indicator	Description/usage
Page reads and writes	Improving the performance of the tempdb database I/Os may change the rate of page reads and writes for the user databases because of the reduced latency associated with the tempdb database I/O. For user database pages, the overall number should not vary across the same workload.
Physical read and write bytes to the tempdb database	If moving the tempdb database to a device, such as a RAM disk, increases the actual I/O for the tempdb database, it indicates that the memory taken away from the buffer pool is causing increased tempdb database activity to occur. This pattern is an indicator that the page life expectancy of database pages may also be affected in a negative way.
Page life expectancy	A decline in page life expectancy can indicate an increase in the physical I/O requirements for a user database. The rate decrease could likely indicate that the memory taken away from the buffer pool is forcing database pages to exit the buffer pool prematurely. Combine with the other indicators and test to fully understand the parameter boundaries.
Overall throughput CPU usage Scalability Response time	The primary goal of a tempdb database configuration change is to increase the overall throughput. Your testing should include a mix of repeatable workloads that can be scaled out to determine how throughput is affected. Something like a compression-based RAM disk implementation may work well with 10 users. However, with increased workload, this may push CPU levels beyond desired levels and have negative effects on response time when the workloads are high. True stress tests and future load prediction tests are strongly encouraged.
Work files and work table creation actions	If moving the tempdb database to a device, such as a RAM disk, changes the query plan by increasing the number or size of work files or work tables, it indicates that the memory taken away from the buffer pool is causing increased tempdb database activity to occur. This pattern is an indication that the page life expectancy of database pages may also be affected in a negative way.

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.

Action

Sector 1 is written to the device and is compressed to save space.

Sector 2 is written to the device and is compressed with sector 1 to save space.

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

Action

Block all writes to sectors 1 and 2.

Uncompress sector 1 into a scratch area, leaving current sector 1 storage as the active data to be retrieved.

Compress sectors 1 and 2 into a new storage format.

Block all reads and writes of sectors 1 and 2.

Exchange old storage for sectors 1 and 2 with new storage.

If the exchange attempt fails (rollback):

Restore the original storage for sectors 1 and 2.
Remove the sectors 1 and 2 combined data from the scratch area.
Fail the sector 2 write operation.

Unblock reads and writes for sectors 1 and 2.

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.

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

826433 PRB: Additional SQL Server diagnostics added to detect unreported I/O problems

828339 Error message 823 may indicate hardware problems or system problems in SQL Server

234656 Using disk drive caching with SQL Server

110352 Optimizing Microsoft SQL Server performance

304261 Description of support for network database files in SQL Server

913945 Microsoft does not certify that third-party products will work with Microsoft SQL Server

910716 Requirements for SQL Server 2005 and SQL Server 2000 to support remote mirroring of user databases

917043 Key factors to consider when evaluating third-party file cache systems with SQL Server

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2006 Microsoft Corporation. All rights reserved.

Microsoft, Windows, Windows Server, and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.