Controlling Intelligent Query Processing Features

I always look forward to new T-SQL features and optimizer enhancements with every new version of SQL Server. Starting in 2017, Microsoft came up with a set of features called Intelligent Query Processing (IQP). These features work to improve performance without changing any code. If you’d like to learn more about these features, take a look at Microsoft’s page on the topic. There are also several articles on Simple Talk by Greg Larsen on the features.

These features improve the performance of some typical patterns; for example, one feature is inlined scalar user-defined functions (UDFs) available in 2019. Depending on the situation, this can lead to a significant performance boost without changing any code. Say you have a function that accepts a couple of parameters and then runs a query to return a sum. As long as the function meets the criteria for inlining, SQL Server treats it as if you included your code in the query instead of using a function. Some folks don’t realize that the query using a UDF in the SELECT list calls the UDF once for every row. Just think about the impact if the query inside the UDF happens to do a scan on a large table!

Just like any new feature, you must do some testing to see if it helps or causes unanticipated problems. All the IQP features can be toggled off and on by changing the database compatibility level. If you turn it below 150 (SQL Server 2019), you disable all the 2019 IQP features. If you wish to disable just one of the features, set the compatibility to 150 and then flip the switch for the database for that particular feature. Here's the command to turn off UDF inlining:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

But what if you need this feature except that you have one UDF that causes issues when inlining is turned on? For example, UDFs use deferred resolution. That means that an object doesn’t have to exist at function creation time. In a case like this, inlining the UDF could cause an error.

Here’s a simple example:

CREATE OR ALTER FUNCTION dbo.SwitchTable()
RETURNS INT AS
BEGIN
    DECLARE @Count INT;
    IF OBJECT_ID('testTable','U') IS NOT NULL BEGIN
        SELECT @Count = COUNT(*)
        FROM testTable;
    END
    ELSE BEGIN
        SELECT @Count = COUNT(*)
        FROM Production.Product;
    END
    RETURN @Count;
END;
GO
SELECT ProductID, Name, dbo.switchTable() AS productCount
FROM Production.Product;

When you run the query with scalar UDF inlining enabled, you’ll see this error if testTable doesn’t exist:

Msg 208, Level 16, State 1, Procedure SwitchTable, Line 7 [Batch Start Line 21] Invalid object name 'testTable'.

To get around this while still allowing the feature to be used for other scalar UDFs, add this hint to the query:

SELECT ProductID, Name, dbo.switchTable() AS productCount
FROM Production.Product
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

The team at Microsoft is doing some great things with SQL Server! If you use a lot of scalar UDFs, an upgrade to 2019 with scalar UDF inlining could solve some of your issues, and you have control over when to use it.

Kathi Kellenberger

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

Louis Davidson provides a pair of SQL Prompt snippets that will help you deal with dependencies, whenever you need to drop columns or tables.

If some of your database constraints have system-generated names, they can cause 'false positives' when comparing schemas and generating build scripts using SQL Compare or SQL Change Automation. Phil Factor explains the difficulties, and the Compare option you need to enable to avoid them.

David Atkinson describes how developers who were previously writing and managing SQL source code in SSMS can make a smooth transition to Azure Data Studio.

Could you spare 3 minutes to help us out at SQL Server Central? Take this short survey, tell us about who you are and why you come to SQL Server Central, and you could win a $50 Amazon voucher.

Video game enthusiasts expect that they can make changes to the game’s environment and that the setting changes will be saved. In this article, Lance Talbert shows how it can be done in Unity.

One thing that has come up several times in the last few weeks is a misconception that you cannot process multiple partitions on the same table or tables in parallel as it would cause a lock.

This month, 15 community folks contributed code – I think that’s an all-time high for a single month’s release! Good work, y’all. Thanks for payin’ it forward. The bad...

Over the years I have been asked to complete asses...

I asked this question myself: Is there a way to use Extended Events to capture the T-SQL of a prepared statement? Why would I be concerned with prepared statements?...

Total: 2 Average: 5Maintenance plans in SQL Server give us an easy way to organize, configure, and schedule tasks that ensure that the database engine and the databases that...

Let’s be honest here, Microsoft doesn’t want you to read this.

SQL Server has given us the option to backup our databases directly to Azure BLOB storage for a while now but it’s not something that I’ve had all that... The...

You can read about all the new role based qualifications from Microsoft

On a few occasions I’ve referred to GCP (Google ...

On the heels of More Azure Blob Storage enhancemen...

Click to learn more about author Chris Lynch. As we have all experienced recently, change is the only constant that continues to be true, decade in and decade out....

In this webinar discover how Database DevOps and Octopus Deploy enable your organization to increase efficiency, reduce errors and get the most from your customer infrastructures. Date and time: Wednesday July 15 & Friday July 17. Can't join us live? register to receive the recording.

Some years ago I did a post on NULLs where I detailed some of the issues you can get with them.

In this article, Diogo Souza explains GitFlow, a branching model for Git. He demonstrates how to work with GitFlow to create and deploy a feature and a hotfix to GitHub.

Teams implementing DevOps often encounter a significant obstacle: database folks are not all used to working with Version Control Systems (VCS). It’s important to overcome this obstacle because a...

When deploying new versions of a centralized application like a web service, there’s a strategy you can use to direct production traffic to the new version only after it...

Near the end of June 2020, I delivered a free Enterprise Data & Analytics Summer o’ ADF webinar titled Using the Azure Data Factory ForEach Activity. My friend Meagan...

When you start learning about SQL Server security, the first thing you read is that SQL Server (on Windows) can use either SQL Server Authentication

Max Degree of Parallelism, or MAXDOP, is one of the most known settings in the SQL Database Engine.

A few days ago I ran a Twitter poll:

I’ll start with the smallest Stack Overflow 2010...

Continuing his series on table expressions in SQL Server, Itzik Ben-Gan discusses more considerations for performance and efficiency. The post Fundamentals of table expressions, Part 4 – Derived tables, optimization...

When you import data from a relational database like SQL Server in Power BI you have the option of entering your own SQL query to use as a starting...

Thanks for watching this week's Power BI news roun...

I’m excited to give you a run down of fresh new online training, open to all on Redgate University. We’ve got a great mix of courses on how to...

Welcome to part 2 of my series reviewing my first year as a DBA. If you missed the first post, you can read it here. Moar Training! Reflecting back...

How many times have you been afraid to hear “No”? We have all been there because we have been trained over the years to perceive “No” as a rejection,... The...

In this blog post, I will run through 3 different ways to execute a delayed Python loop. In these examples, we will aim to run the loop once every...

Over the years I have created a number of SQL Serv...

Dear Vendors that encrypt stored procedures in SQL Server, Stop It! We were having a discussion on Twitter about vendors encrypting stored procedures recently, and this justification came up...

Click to learn more about author David Balaban. Data anonymization is a process aimed at eliminating personally identifiable clues so that it’s impracticable, or at least very challenging, to...

Local temporary tables – tables with names that begin with a single # character – are dropped automatically by SQL Server when they are no longer in scope.

This article is the fourth part in a series on table expressions. In Part 1 and Part 2 I covered the conceptual treatment of derived tables.

Phil Factor explains how to use SQL Prompt, or SQL Change Automation, to detect use of deprecated SQL Server syntax, during development, and Dynamic Management Views and Extended Events...

There’s more to the VALUES clause in T-SQL than meets the eye.

Every once in a while I see something in SQL Serve...

Taking it back to SQL 101 today because I recently...

There’s more to the VALUES clause in T-SQL than meets the eye. We’ve all used the most basic INSERT syntax: But did you know that you can create multiple...

Number One In case you missed it for some reason, check out this post of mine about local variables. Though it’s hard to imagine how you missed it, since it’s...

Another post for me that is simple and hopefully s...

If you are working on any module or routine such as a procedure, function or batch that produces a result, then you will want to compare it with something...

No, not that kind of volume! Over the past couple ...