Accelerated Database Recovery in SQL Server 2019: Choose your own filegroup for the version store

An exciting new feature in SQL Server 2019 is Accelerated Database Recovery (ADR). Resulting from a combination of magic beans and smart software developers (I might be wrong about the beans), there is a good chance that you will find yourself using it. Not only can ADR dramatically improve recovery time with database restores, but
-> Continue reading Accelerated Database Recovery in SQL Server 2019: Choose your own filegroup for the version store

Better SQL Server memory defaults in 2019

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias. SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has
-> Continue reading Better SQL Server memory defaults in 2019

Better SQL Server CPU defaults in 2019

SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version. On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server
-> Continue reading Better SQL Server CPU defaults in 2019

SQL Server 2008 and 2008 R2 is end of life on 9 July 2019

Next month, Microsoft is ending five years of extended support on SQL Server 2008 and SQL Server 2008 R2. This follows five years of mainstream support before that. You really should be upgrading to SQL Server 2017 at the very least, with some serious consideration to the unreleased SQL Server 2019. My reasoning for suggesting
-> Continue reading SQL Server 2008 and 2008 R2 is end of life on 9 July 2019

Containers and data: you gotta keep ’em separated

There was an interesting conversation on Twitter recently, between Grant Fritchey (blog | twitter), Kenneth Fisher (blog | twitter), Anthony E. Nocentino (blog | twitter), Vicky Harp (twitter), and me about containers and SQL Server. Here’s the summary tweet: Already mentioned, you can use a persisted storage volume to keep your databases around (thanks @_randolph_west
-> Continue reading Containers and data: you gotta keep ’em separated

Ship Containers

SQL Server containers no longer being updated in the Docker registry

In October 2018, Microsoft announced a change to the source of their Docker containers. You should be using the new Microsoft Container Registry (MCR) as the source for official Docker container images for Microsoft products. While existing container images in the Docker Hub are not affected, you may not get updated images unless you switch.
-> Continue reading SQL Server containers no longer being updated in the Docker registry

Proposed SQL Server defaults: max server memory

A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable lightweight pooling if it is enabled Disable priority boost if it is enabled Set optimize for ad hoc workloads to enabled Set max server memory (MB) to a
-> Continue reading Proposed SQL Server defaults: max server memory

The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

A short post this week. On a mailing list recently, someone noticed that a .NET application writing to SQL Server did not have the expected behaviour with UTF-8 collation and data types. To refresh our memories, UTF-8 is newly supported in SQL Server 2019, and provides potential savings of up to 50% when storing strings,
-> Continue reading The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019

A short Azure SQL Database Edge explainer

On Thursday May 2nd, 2019, Microsoft announced a new edition of SQL Server targeting Internet of Things (IoT) edge devices. That means SQL Server can now run almost anywhere. From the Venturebeat article: It supports ARM and x64-based edge gateways and machines, and offers low-latency analytics that combine data streaming and time-series data, with in-database
-> Continue reading A short Azure SQL Database Edge explainer

The case of scalar UDF inlining, where context is everything

Here’s an interesting story for you this week. As part of the new Intelligent Query Processing improvements introduced in the upcoming SQL Server 2019, we find a new feature called scalar UDF inlining. This post is not about scalar UDF inlining exactly, but IQP-adjacent if you like. It works by taking a typical scalar user-defined
-> Continue reading The case of scalar UDF inlining, where context is everything