Post image

Max Server Memory and SQL Server 2016 Service Pack 1

Everything changed for SQL Server Standard Edition on 16 November 2016, and how memory limits work. On that day, a slew of Enterprise Edition features made their way into editions across the board, including Express Edition and LocalDB. The memory limit of 128GB RAM applies only to the buffer pool (the 8KB data pages that[…]


Temporal Tables and Hidden Period Columns

In my November 2015 post, An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean, I wrote: The HIDDEN property is optional and will hide these columns from a standard SELECT statement for backward compatibility with our application and queries. You cannot apply the HIDDEN property to an existing column. It turns out[…]

Post image

Wait For Service Pack 1

Conventional wisdom tells us that when Microsoft releases a new version of any server product, we should wait until Service Pack 1 before deploying it to production. This hasn’t been true for a while now, since Microsoft recommended that Cumulative Updates for SQL Server carry the same confidence: SQL Server CUs are certified to the same levels[…]

Post image

How to make your T-SQL thirty percent faster

Last week, I mentioned a feature in SQL Server, and now in Azure SQL Database, called Memory-Optimized Table-Valued Parameters, which makes use of In-Memory OLTP structures to work around a dependency on tempdb, and thereby drastically improve performance. Short version: We can see more than double speed improvement by switching from temp tables to Memory-Optimized TVPs,[…]

Post image

Should I upgrade SQL Server 2008 / R2, or migrate to Azure?

[Last updated July 13, 2018] Brent Ozar Unlimited runs a website called SQL Server Updates which comes in really handy for keeping your on-premises SQL Server up to date. In October 2016 I noticed something interesting: if you’re running SQL Server 2008 or 2008 R2, Microsoft’s extended support for it ends on 9 July 2019.[…]

Post image

The Data Migration Assistant

I’ve written quite a lot about Azure SQL Database recently, but that doesn’t mean I’ve forgotten about the on-premises version of SQL Server. What could be better than Microsoft announcing a new tool for upgrading to SQL Server 2016? The Data Migration Assistant (DMA) for SQL Server was announced on 26 August 2016. Data Migration Assistant[…]

Post image

Should I move my on-premises database to Azure SQL Database?

This week is a shorter post than last week, but it answers a very important question: Should I move my on-premises database to Azure SQL Database? The short answer is: It depends. Firstly, why do you want to move your database to the cloud? Is it a business requirement? A better way to ask this[…]

Post image

Basic Availability Groups in SQL Server 2016

One of the new features in SQL Server 2016 Standard Edition, which may appeal to users of the deprecated Database Mirroring feature, is Basic Availability Groups, a variation of Availability Groups found in the Enterprise Edition. At a high level, Availability Groups are an amalgamation of Database Mirroring and Windows Failover Clustering. The Windows Servers[…]

Temporal Tables – When To Use Them

This is the final part of my Temporal Table series. You can read parts one, two and three here. Last week I demonstrated how to modify temporal tables in SQL Server 2016. This week I’m answering a very simple question. When should you use Temporal Tables? I see two main reasons for using this feature:[…]