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…


Improving Concurrency & Scalability of SQL Server workload by optimizing database containment check in SQL 2014\SQL 2016

Starting SQL 2012, database containment property is introduced in SQL Server database to support contained databases. As described in MSDN article here, database collation not only applies to data stored in user tables but also applies to variables, stored procedure parameters, GOTO labels. In case of contained databases, the collation of the metadata, variables, parameters,…