Problems displaying this newsletter? View online.
Database Weekly
The Complete Weekly Roundup of SQL Server News
Hand-picked content to sharpen your professional edge

How SQL Server Can Just Go Faster

It’s rare to get a call from a customer telling you that the application is fast today and thanks for taking such good care of the database. Instead, you are more likely to hear complaints when things go wrong like slow running queries and timeouts. There is a lot to consider when trying to figure out performance issues, from hardware resources to server settings to query and index tuning. Improving performance can be a lot of work and requires some expertise.

In recent editions of SQL Server, Microsoft has added features that make things better (i.e., go faster) without requiring code changes or even spending a lot of time figuring out what to do on your own. Beginning with 2017, Microsoft began adding to a feature set called “Intelligent query processing” that overcomes some common problems. These features can make queries run faster without changes to code. Note that the compatibility level of your database may need to be adjusted, and that some of the features are only available with Enterprise edition.

Here’s a list of the features released so far:


  • Adaptive joins (batch mode): The optimizer can choose the join type (i.e., hash join or nested loop) during query execution based on row count. The feature applies only to queries using a columnstore index. This is an Enterprise Edition feature.
  • Interleaved Execution for multi-statement table valued functions (MSTVFs): This feature enables the optimizer to determine the cardinality of MSTVFs during query execution instead of assigning a guess of 100 rows.
  • Memory Grant Feedback (batch mode): When the optimizer has determined to use batch mode, it can adjust the memory grant used in subsequent executions of the query. This decreases the likelihood of spilling to disk or underusing the memory assigned. In 2017, batch mode is used only on queries that involve a columnstore index. This is an Enterprise Edition feature.


  • Approximate Count Distinct: Use a new function called APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT) when responsiveness is more important than precision.
  • Batch Mode on Rowstore: Queries that do not involve a columnstore index can now take advantage of batch mode. This is especially helpful when calculating aggregations over large numbers of rows. This is an Enterprise Edition feature.
  • Memory Grant Feedback (row mode): This feature extends the memory grant feature to queries that do not include a columnstore index. This is an Enterprise Edition feature.
  • Scalar UDF Inlining: This feature removes some of the pain when including a multi-value UDF in a query by making the operation set-based instead of iterative.
  • Table Variable Deferred Compilation: Allows the optimizer to correctly estimate the number of rows in a table variable instead of estimating 1 row.

I have to admit that some of these optimizations make me want to say “don’t do those bad things!” I’ve seen both scalar and multi-statement table valued UDFs cause so many problems over the years, and the optimizations won’t fix everything that you can throw at them.

Today’s Microsoft is serious about SQL Server, and I can’t wait to see what’s next!

Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

The Weekly News
All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit.
AI/Machine Learning/Cognitive Services

Data Science in 90 Seconds: Natural Language Processing

This is Lesson 18 in the Data Science in 90 Seconds video blog series from host Laura Kahn....

Back for More with SQL, Azure, Machine Learning, Big Data!!!!

Let me start with an apology for being missing so ...

Free Microsoft Cognitive Services Resources: Transforming Business With AI

Artificial Intelligence (AI) is changing how busin...


Failing SQL Server 2019 Installation

Paul Randal walks through an installation error wi...

Resuming Index Operations but Using Different Options

John Morehouse has an interesting use case for res...

Checking and Configuring File Autogrowth for SQL Server

One thing that I seem to come across often enough ...

Collecting Diagnostic data from multiple SQL Server instances with dbatools

Keeping their SQL Server instances under control i...

How to use switching to make metadata changes online

Metadata changes, like modifying a clustered index...

How to Fix SQL Database Logical Consistency Based I/O Error

There can be several reasons that cause Database L...

How to Use CrystalDiskMark 7 to Test Your SQL Server’s Storage

I bet you wanna know whether your storage is hot o...

Getting Started with Azure Arc-Servers

Microsoft announced Azure Arc at Ignite 2019 provi...

Install ALL Things SQL Server… What?

Does your server look like this? Many of us have i...

How Does Accelerated Database Recovery Work?

Accelerated Database Recovery is new with SQL Serv...

SDU Tools: Check Instant File Initialization (IFI) State for SQL Server

When SQL Server requests storage space from the op...

Azure SQL Database

How Serverless is Working in Azure SQL Database

When Azure SQL Database introduced the serverless ...


Building an Azure Sandbox – Part 1 – The Basics

I recently posted that I was doing my annual Azure...

How Error Handling Works in PowerShell 7

How Error Handling Works in PowerShell 7 Among the...

Data Mining / Data Analysis

How Can My Business Get the Most Out of Self-Serve Advanced Analytics?

How does an organization help the self-serve advanced analytics model grow and thrive?...

Database Design, Theory and Development

Partition Switching to Make Table Changes

Daniel Hutmacher shows a couple things you can cha...

DevOps and Continuous Delivery (CI/CD)

How to fork a GitHub repository and contribute to an open source project

I enjoying maintaining open source GitHub reposito...

Machine Learning

Manipulating Machine Learning Systems by Manipulating Training Data

Recent work has identified that classification models implemented as neural networks are vulnerable to data-poisoning ...

Diabetes Prediction Using Support Vector Machines

A step-by-step approach to using Support Vector Ma...

Performance Tuning SQL Server

SQL SERVER Management Studio – Enable Statistics Time and IO for Every Query

I was asked a very interesting question about stat...


Slicer with AND condition in Power BI

The default behaviour of the slicer in Power BI is...

Professional Development

Do something else, become better at what you love

Time for a short "professional development" post t...


Bad Idea Cowboy Hat: BEGIN TRANSACTION; GO 1000;

One of my favorite presentations is Revenge the SQ...

But I Need To Compare Two Date Columns In My Where Clause

Tell It To The Judge A common dilemma is when you ...

Filtering Data in T-SQL

In most scenarios when working with data, users do not require all the data in each instance of a query. T-SQL (and SQL of course) ...

T-SQL 101: #45 Upper and lower case conversion of strings in SQL Server

If you need to convert strings to all upper case o...

When a SQL UPDATE Statement DELETES Rows

Watch this week’s episode on YouTube. At first I...

Tools for Development

Is it true that editing a single row in Management Studio empties and reloads the entire table?

TL;DR: No. A customer recently brought up an inter...

Virtualization and Containers/Kubernetes

Containers: Upgrading SQL Server from 2017 to 2019 RTM

Throughout the pre-release of SQL Server 2019, I w...

RSS FeedTwitter
This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the This newsletter was sent to you because you signed up at Note: This is not the daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -