Article ID: 917047 - View products that this article applies to.
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:
* 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.
Collapse this tableExpand this table
Survival across outageThe 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 operationsTo 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.
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 subsystemsThere 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 deploymentThere 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 safetyCompliance 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 RAMThe 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 firstAn 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 pointsThe 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:
Collapse this tableExpand this table
Transactional sector rewrite exampleThe 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.
Collapse this tableExpand this table
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.
Collapse this tableExpand this table
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 databaseBe 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:
Partner product certifications are not a guaranty of compatibility or safetyA 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.
SupportIf 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.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/826433/ )PRB: Additional SQL Server diagnostics added to detect unreported I/O problems
(https://support.microsoft.com/kb/828339/ )Error message 823 may indicate hardware problems or system problems in SQL Server
(https://support.microsoft.com/kb/234656/ )Using disk drive caching with SQL Server
(https://support.microsoft.com/kb/304261/ )Description of support for network database files in SQL Server
(https://support.microsoft.com/kb/913945/ )Microsoft does not certify that third-party products will work with Microsoft SQL Server
(https://support.microsoft.com/kb/910716/ )Requirements for SQL Server 2005 and SQL Server 2000 to support remote mirroring of user databases
917043Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions
(https://support.microsoft.com/kb/917043/ )Key factors to consider when evaluating third-party file cache systems with SQL Server
Performance Best Practices for SQL Server in Azure Virtual Machines
Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
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.
SQL Server requires systems to support ‘ guaranteed delivery to stable media ’ as outlined under the SQL Server I/O Reliability Program Requirements
(http://download.microsoft.com/download/F/1/E/F1ECC20C-85EE-4D73-BABA-F87200E8DBC2/SQL_Server_IO_Reliability_Program_Review_Requirements.pdf). For more information about the input and output requirements for the SQL Server database engine, click the following article number to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/967576/ )Microsoft SQL Server Database Engine Input/Output Requirements
Article ID: 917047 - Last Review: May 12, 2015 - Revision: 5.0