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…

0

Transactional Replication to Azure SQL DB is now in public preview.

  As announced yesterday during my presentation at Pass Summit 2015, you can now configure Azure SQL DB as a subscriber of SQL Server Transactional Replication. This new capacity of Transactional Replication is available starting with the following versions\updates of SQL Server: Community Technology Preview (CTP) 3.0 of SQL Server 2016 SQL Server 2014 Service…

1

How we moved from on-premise to Azure Virtual Machine

We had previously blogged about moving some of our on-premise SQL Server environments which manages critical functions necessary for releasing the SQL Server builds to all our customers. We announced SQL Server 2016 CTP 2.4 which was made available on September 30th, 2015. You can download the preview from the Microsoft TechNet Evaluation Center site….

0

Multi-fold increase in throughput for big gateway query compiles in SQL Server

There are some exciting new changes in the database engine that dramatically improves query compilation scalability in SQL Server 2014 and SQL Server 2016. These changes are yet another reason to upgrade your SQL Server versions. When a query first starts and has to be compiled in SQL Server, it needs to acquire memory for…

5