This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
I have found almost every day of my career at Microsoft, I learn something new about Microsoft SQL. It is one of the reasons I enjoy my job. In April of 2023, I was speaking at the MVP Summit in Redmond, Washington. One day I was spending time in a side conversation with MVP Rob Farley discussing some of his "wish list" items for SQL. One of these wish list items was delayed transaction durability for tempdb.
Delayed transaction durability is the concept of lazily flushing changes from the transaction log on disk after a transaction commit. This concept can significantly speed up transaction throughput with the cost of possible data loss if SQL Server crashes or is shutdown before the log records are flushed. But full durability is not needed for tempdb because it is recreated each time on SQL Server startup.
It seemed to me like a very reasonable request we could include in updates in Azure SQL or future versions of SQL Server. So I started talking to several SQL "veterans" within our engineering team about this idea. Well, it turns out that even though this database option is not enabled for tempdb, our code by default lazily flushes log changes on commit. So basically in all supported versions of SQL Server and in Azure SQL, the behavior for tempdb is delayed transaction durability. You can see where we have amended our documentation to state this behavior.
As part of my investigation, I also found an additional optimization we have made starting in SQL Server 2019 (and in Azure SQL) for disk I/O for tempdb. SQL Server normally uses an option called FILE_FLAG_WRITE_THROUGH for database and log files to ensure disk consistency in case of situations like power failures, especially for disk systems with caching. But since tempdb is recreated each time on server startup, we don't need to use this option which can result in faster I/O performance for writes to tempdb. We have also amended our documentation to note this behavior.
These may seem like small optimizations but can result in really nice performance gains for your application's use of tempdb.
Bob Ward
Microsoft