I have been blogging for over 13 years and have been working with SQL Server Performance Tuning for an over a decade and everyone always ask me where do I get new content to write from every single day. My answer is very simple, I actually get new things to learn from my clients when I am working on Comprehensive Database Performance Health Check. Today we will learn how I recently helped a client by Disabling Scalar UDF Inlining in SQL Server 2019.
Brief History
Recently I got an urgent call from a client who is a large eCommerce organization. They recently upgraded to SQL Server 2019. As soon as they upgraded to SQL Server 2019, they started to face lots of performance issues. They engaged the same organizations who helped them with migration to help them tune their performance. For most of the part, the organizations were successful in bringing their performance back to the original performance. However, there were still areas in their application which were struggling and they wanted to me to help them tune the server in those specific areas.
When I looked at their system and the area which was running slow, I realized this is because they were using a Scalar User-defined Function (UDF). Now in SQL Server 2019, there is a feature which does Scalar UDF Inlining. For the most part, I have received a good response to this feature. However, there are moments when I have seen outliers for even a proven technology.
Disabling Scalar UDF Inlining
After careful evolution, we decided that we will try to disable the scalar UDF inline and see how the server behaves. We ran the following code and their server performance improved big time.
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
This proved that the feature which is actually giving a good positive response to most of the organizations actually did not work well for my client. However, my client is happy so we decided to leave this off.
Here is the script to enable the same feature.
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;
Now, I strongly recommend that you do not play around with this configuration unless you have a confirmed performance problem with this feature. If you are not sure why your server is running slow in SQL Server 2019, just reach out to me and I will be happy to help.
Reference: Pinal Dave (https://blog.sqlauthority.com)
