TEMPDB – Files and Trace Flags and Updates, Oh My!

TL;DR – Update to the latest CU, create multiple tempdb files, if you’re on SQL 2014 or earlier enable TF 1117 and 1118, if you’re on SQL 2016 enable TF 3427. And now it’s time for everyone’s favorite SQL Server topic – tempdb! In this article, I’d like to cover some recent changes that you…

1

Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler

Indirect checkpoint was first introduced in SQL Server 2012 to provide predictable database recovery governed by target_recovery_time option set at the database level. In SQL Server 2016, indirect checkpoint is ON by default with target_recovery_time set to 60 seconds for model database. The conventional or automatic checkpoint algorithm assumes fixed time interval to recover each…


SQL Server Mysteries: The Case of TDE and Permanent Tempdb Encryption

I’m a huge Sherlock Holmes fan (I’ve read all the books, watch Elementary on CBS every week, and loved the most recent season Four of Sherlock) so when I recently got a question about some unexplained behavior for SQL Server, I thought of the idea of posting some of these as I get and solve…


SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases

Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications. The following link (https://support.microsoft.com/en-us/kb/2964518) outlines how to configure SQL Server 2012 and SQL Server 2014 for optimal scalability and performance. A SQL Server 2016 primary goal was ‘It Just Works.’ Out of the box a customer should not have to engage…