Backup Compression for TDE-enabled Databases: Important fixes in SQL 2016 SP1 CU4 and SQL 2016 RTM CU7

Backup Compression and Transparent Data Encryption (TDE) have been two immensely valuable and popular features for customers in SQL Server. In SQL Server 2016, backup compression was enabled for TDE-enabled databases, where if you backup a TDE-enabled database with compression and MAXTRANSFERSIZE > 64K, backup compression will kick in, reducing backup size and improving overall…


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…


Upgrading a Replication Topology to SQL Server 2016

SQL Server Replication provides multi-faceted data movement capabilities across SQL Server releases which has been used by customers across the globe for a large number of years. When moving from one major release of SQL Server to another, replication topology upgrade has been a constant topic of lengthy discussions. In this post, we shall outline…

15

Columnstore index: Why do we refer to it as In-Memory Analytics?

Columnstore index is part of In-Memory technologies released in SQL Server 2014 and SQL Server 2016. Please refer to https://blogs.msdn.microsoft.com/sqlserverstorageengine/tag/columnstore-index/ for a series of blogs on columnstore index. As the name In-Memory Analytics implies, you may think that columnstore index is entirely memory resident but it is not really the case for columnstore indexes created on…

0

Remote Blob Storage (RBS) client library setup requirements in SQL Server 2016

Remote BLOB Store (RBS) is a client library with SQL Server which allows developers to store, access, retrieve binary large objects (BLOBs) outside SQL Server database files while still maintaining the ACID properties and transactional consistency of data in Blob store. RBS allows you to efficiently utilize storage and IO resources by managing both structured…


Memory grant related diagnostics

Back in March I blogged about Addressing large memory grant requests from optimized Nested Loops. To further enhance discoverability of memory grant related issues, in SQL Server 2016 we released a new xEvent (query_memory_grant_usage). This fires at the end of query processing for all queries with a memory grant over 5MB, and provides insight into…


New DMF for retrieving input buffer in SQL Server

A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available in SQL Server 2016 RC0. This is functionally equivalent to DBCC INPUTBUFFER. However, this has a few advantages over DBCC INPUTBUFFER. Directly returns a rowset. Can be conveniently used with sys.dm_exec_sessions or sys.dm_exec_requests by doing a CROSS APPLY. Can use…

2

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…

0

Several improvements to DBCC CHECKDB to make it run faster

SQL Server 2016 has introduced several improvements to DBCC CHECKDB to make it run faster. Here are links to relevant posts that talk about these changes in detail: CheckScanner using lock free design – https://blogs.msdn.microsoft.com/psssql/2016/02/25/sql-2016-it-just-runs-faster-dbcc-scales-7x-better/ Improvements under Extended Logical Checks – https://blogs.msdn.microsoft.com/psssql/2016/03/01/sql-2016-it-just-runs-faster-dbcc-extended-checks/ MAXDOP option – https://msdn.microsoft.com/en-us/library/ms176064.aspx CHECKDB now introduces a MAXDOP switch that overrides server-wide…

2