This post is my first T-SQL Tuesday post since starting the blog a couple of months back.
T-SQL Tuesday was originally set up by Adam Machanic, original creator of the essential sp_whoisactive stored procedure. The concept is that once a month, a different host puts out an invitation to the community for bloggers to write about a collective SQL Server topic. This month’s host is the great Brent Ozar and the topic is:
“When I’m looking at a query, I bet it’s bad if I see ____.”
It seems apt to be starting T-SQL Tuesday on a nice round number and also when the host is someone who I have learned so much from over the years.
In terms of the topic at hand, there are a million things I can think of but given I only have a week to write this (which sounds a lot but I only really dedicate 3-4 hours a week to blogging) I thought I would write about something where I can quickly and clearly get a message across and so I chose…
Scalar UDFs
Scalar UDFs (Scalar User Defined Functions) are something I’ve seen numerous times on poorly performing queries and straight away thought “I bet that is the problem”. It’s one of those things I just see and immediately want to rip out and pretty much every time I have done that, it has improved the performance problem I have been dealing with.
Rather than coming up with an example of this myself, I asked Claude to come up with something creative, after all, when I see complicated UDFs, I think “Why would anyone think of doing it that way” as though I have never been guilty of such sins earlier on in my career.
The Code
My Claude prompt is below. I used the Sonnet 5 model set to high effort.
Imagine you are a new SQL Server developer and you have just discovered scalar functions.
You think they are great for encapsulating and re-using complex business logic but you don’t realise the performance problems that come with them.
Write a query against the StackOverflow database that includes a Scalar UDF with such complex logic, something that would make an experienced SQL developer feel ill
Claude came up with the UDF and calling query below, which I have left as-is, including formatting and sarcastic comments!
Note that I didn’t have to provide any schema to create this code – Claude seems to know the StackOverflow database inside and out which is presumably because it has been a well documented demo database over the years.
-- "Look how clean this is! No more repeating logic everywhere!"
CREATE OR ALTER FUNCTION dbo.fn_GetUserEngagementScore (@UserId INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @Score DECIMAL(10,2) = 0;
DECLARE @PostCount INT;
DECLARE @AnswerCount INT;
DECLARE @QuestionCount INT;
DECLARE @AcceptedAnswers INT;
DECLARE @TotalScore INT;
DECLARE @CommentCount INT;
DECLARE @BadgeCount INT;
DECLARE @Reputation INT;
DECLARE @AvgAnswerScore DECIMAL(10,2);
DECLARE @DaysSinceLastPost INT;
DECLARE @AccountAgeDays INT;
SELECT
@Reputation = Reputation,
@AccountAgeDays = DATEDIFF(DAY, CreationDate, GETDATE())
FROM dbo.Users
WHERE Id = @UserId;
SELECT @PostCount = COUNT(*)
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND PostTypeId IN (1,2);
SELECT @QuestionCount = COUNT(*)
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND PostTypeId = 1;
SELECT @AnswerCount = COUNT(*)
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND PostTypeId = 2;
SELECT @AvgAnswerScore = AVG(CAST(Score AS DECIMAL(10,2)))
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND PostTypeId = 2;
SELECT @AcceptedAnswers = COUNT(*)
FROM dbo.Posts p
WHERE p.OwnerUserId = @UserId
AND p.PostTypeId = 2
AND EXISTS (
SELECT 1
FROM dbo.Posts q
WHERE q.AcceptedAnswerId = p.Id
);
SELECT @TotalScore = SUM(Score)
FROM dbo.Posts
WHERE OwnerUserId = @UserId;
SELECT @CommentCount = COUNT(*)
FROM dbo.Comments
WHERE UserId = @UserId;
SELECT @BadgeCount = COUNT(*)
FROM dbo.Badges
WHERE UserId = @UserId;
SELECT @DaysSinceLastPost = DATEDIFF(DAY, MAX(CreationDate), GETDATE())
FROM dbo.Posts
WHERE OwnerUserId = @UserId;
-- the "secret sauce" weighting formula
SET @Score =
ISNULL(@Reputation, 0) * 0.01
+ ISNULL(@PostCount, 0) * 2
+ ISNULL(@QuestionCount, 0) * 1.5
+ ISNULL(@AnswerCount, 0) * 3
+ ISNULL(@AcceptedAnswers, 0) * 5
+ ISNULL(@AvgAnswerScore, 0) * 1.2
+ ISNULL(@TotalScore, 0) * 0.5
+ ISNULL(@CommentCount, 0) * 0.3
+ ISNULL(@BadgeCount, 0) * 4
- ISNULL(@DaysSinceLastPost, 0) * 0.1
+ CASE
WHEN @AccountAgeDays > 365 * 5 THEN 50
WHEN @AccountAgeDays > 365 * 2 THEN 25
WHEN @AccountAgeDays > 365 THEN 10
ELSE 0
END;
IF @Score < 0 SET @Score = 0;
RETURN @Score;
END
GO
-- "Reusable, readable, maintainable!"
SELECT
u.Id,
u.DisplayName,
u.Reputation,
dbo.fn_GetUserEngagementScore(u.Id) AS EngagementScore
FROM dbo.Users u
WHERE u.Reputation > 1000
ORDER BY dbo.fn_GetUserEngagementScore(u.Id) DESC;
Claude’s response was quite creative and is the sort of thing I’ve seen scalar functions used for many times. The UDF is outputting an engagement score based on some business logic. This is the sort of query we may see on a month-end report – show us the engagement on our platform for a particular set of users based on some methodology, Claude’s comments refer to this methodology as the “secret sauce”
As it happens, this worked a bit too well – I was waiting far too long for a result. When writing a post like this, I need something that illustrates the real-world problem but at the same time doesn’t run for hours and hours. I cut it down a bit, removing some of the calculations from the UDF and tweaking the call query a bit. OK, I changed the secret sauce recipe but the actual detail of that recipe is not important here, I am just demonstrating how UDFs are used to encapsulate complex logic.
The amended version we’ll work with in this post is below:
CREATE OR ALTER FUNCTION dbo.fn_GetUserEngagementScore (@UserId INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @Score DECIMAL(10,2) = 0;
DECLARE @PostCount INT;
DECLARE @AnswerCount INT;
DECLARE @QuestionCount INT;
DECLARE @AvgAnswerScore DECIMAL(10,2);
SELECT @PostCount = COUNT(*)
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND
PostTypeId IN (1,2);
SELECT @QuestionCount = COUNT(*)
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND
PostTypeId = 1;
SELECT @AnswerCount = COUNT(*)
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND
PostTypeId = 2;
SELECT @AvgAnswerScore = AVG(CAST(Score AS DECIMAL(10,2)))
FROM dbo.Posts
WHERE OwnerUserId = @UserId AND
PostTypeId = 2;
/* the "secret sauce" weighting formula */
SET @Score =
+ ISNULL(@PostCount, 0) * 2
+ ISNULL(@QuestionCount, 0) * 1.5
+ ISNULL(@AnswerCount, 0) * 3
+ ISNULL(@AvgAnswerScore, 0) * 1.2;
IF @Score < 0 SET @Score = 0;
RETURN @Score;
END
GO
It’s easy to see why people think of using scalar UDFs. In programming, we are taught the DRY (Don’t Repeat Yourself) principle meaning we write code once so when we change it, we change it once – it makes code more readable and maintainable.
However, in SQL Server, sometimes constructs that support DRY principles don’t always optimise well and can lead to poor query performance.
Benchmarks
For this test, we’ll use both the StackOverflow2010 and StackOverflow2013 databases. Both databases are available under cc-by-sa 4.0 licence from the Stack Exchange Data Dump. I have started both databases in compatibility level 130 and they are hosted on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM. I started with no indexes – the only indexes in place are those that are created in this post.
Let’s create the amended scalar function and then execute the calling query against the StackOverflow2010 database where the data is cut off at 31st December 2010. I’ll also gather some benchmarks.
Firstly, I’ll create an index which supports the query which calls the UDF:
CREATE INDEX IX_Reputation ON dbo.Users
(
Reputation
)
INCLUDE
(
DisplayName
);
I’ll enable the actual execution plan as well as enable the output of IO and CPU STATISTICS by adding SET STATISTICS IO ON to the calling query.
SET STATISTICS IO, TIME ON;
SELECT
u.Id,
u.DisplayName,
u.Reputation,
dbo.fn_GetUserEngagementScore(u.Id) AS EngagementScore
FROM dbo.Users u
WHERE u.Reputation > 700000;
The entire actual execution plan is below:
The (slightly tidied for ease of reading) STATISTICS IO, TIME output is:
Table 'Users'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 41701 ms, elapsed time = 10364 ms.
The query took 10 seconds to execute but only performed a mere 3 reads due to our covering index (ahem, something doesn’t seem right here – read on to find out). We can also see from the operator times in the plan that 10 seconds was spent in the Compute Scalar.
Let’s now imagine our data has grown over a number of years of application use and we still have this query in production. I’ll run against the larger StackOverflow2013 database where the data cut off is 31st December 2013, which simulates 3 years of data growth. I’ll run the same query again:
SET STATISTICS IO, TIME ON;
SELECT
u.Id,
u.DisplayName,
u.Reputation,
dbo.fn_GetUserEngagementScore(u.Id) AS EngagementScore
FROM dbo.Users u
WHERE u.Reputation > 700000;
The actual execution plan is below. The shape hasn’t changed from the 2010 version:
The STATISTICS IO output is:
Table 'Users'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 564547 ms, elapsed time = 315885 ms.
We can see the performance has slipped – we are now at 315 seconds elapsed time and our CPU time is up to 564 seconds, though our reads still appear to be the same. This is one of the problems of scalar UDFs – they don’t scale well. They may work fine on a few hundred rows in development but once deployed to a production environment where the data begins to grow, the performance starts to suffer.
It is also worth noting that the CPU time in the statistics above is higher than the execution time, which suggests parallelism, though we don’t see any parallelism operators in the plan – more on that later.
Digging In
Why do we see this performance degradation?
Well, the answer is in how SQL Server implements the scalar UDF. We saw in both actual execution plans that all that appears to have taken place is an index seek on the Users table to get the users in the reputation range we are looking for, then there is a Compute Scalar.
Well, about that Compute Scalar… all of the “secret sauce” logic in the UDF is just expressed in the execution plan as a single Compute Scalar operator which very much trivialises all the work it has to do within that UDF. We can confirm this by observing the Defined Values property for the Compute Scalar operator in the execution plan.
This is consistent with what SQL Server would do with other functions in the select list – DATEADD, DATEDIFF etc – all would show in the execution plan as a Compute Scalar.
Also note that the STATISTICS IO output from our test queries only showed activity against the Users table and only a small number of reads despite such a long-running query. That is another problem of scalar UDFs – their performance issues are hidden from the tooling we often use to analyse performance – we don’t see the reads on all of the tables in the scalar UDF and we don’t see any of those tables being accessed in the execution plan – it is all hidden in the Compute Scalar operator. As a slight curve ball, however, we can see the UDF logic in an estimated execution plan as below:
We can expose the true reads using Profiler (yes, I know – it’s deprecated but it’s still very easy to use to set up quick analysis such as this)
Below is a screenshot of a Profiler trace showing the true number of reads against both the 2010 and 2013 databases respectively:
There are some pretty big reads there – 22,861,337 for 2010 and 134,258,218 for 2013 – that’s a huge increase, imagine what that will be like in 2016, 2019 etc!
Hidden performance troubleshooting information aside, we have an issue that this query is not particularly performant and doesn’t scale well as data grows. Why does it get worse as the data grows? A Compute Scalar operator computes some value for each input row (the rows output from the previous operator) therefore the logic in the UDF is run for every row passed to the Compute Scalar. As the number of rows from the previous operator (the index seek finding all the users in the reputation range) increases, the UDF logic is executed more times.
We can use the function sys.dm_exec_function_stats to show how many times the function has been called:
USE StackOverflow2010;
SELECT DB_NAME() AS DatabaseName,
s.name,
o.name,
f.execution_count,
f.total_logical_reads,
f.total_elapsed_time,
f.total_worker_time
FROM sys.dm_exec_function_stats f
JOIN sys.objects o
ON o.object_id = f.object_id
JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE f.database_id = DB_ID();
USE StackOverflow2013;
SELECT DB_NAME() AS DatabaseName,
s.name,
o.name,
f.execution_count,
f.total_logical_reads,
f.total_elapsed_time,
f.total_worker_time
FROM sys.dm_exec_function_stats f
JOIN sys.objects o
ON o.object_id = f.object_id
JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE f.database_id = DB_ID();
We can see the function has been called multiple times for both databases and was called more times for the 2013 database than 2010 (as there are more users with reputation > 700000 in the 2013 database). We also see how the IO and elapsed time have suffered. This is a bit of a double-edged sword as it is not only the fact that extra calls to the function are made but also the fact that the queries in the UDF are now reading more as the data in the tables those queries are reading from has grown over time also.
One final issue to mention with Scalar UDFs, which I hinted at earlier, is that they inhibit parallelism, Microsoft’s documentation states
Scalar UDFs typically perform poorly for the following reasons…SQL Server doesn’t allow intra-query parallelism in queries that invoke UDFs.
To me, that suggests the entire query will run at MAXDOP 1, though Erik Darling states:
Non-inlineable scalar UDFs will prevent the calling query from using a parallel execution plan.
I interpret this as the calling query but not necessarily the work inside the UDF, which would explain why we saw higher CPU than elapsed time on the STATISTICS TIME for the queries with the Compute Scalar operator above.
I am not 100% sure which of the above is true, I would say my understanding of Erik Darling’s explanation would make sense given the STATISTICS TIME output but I have not found any other documentation to confirm this. That said, it is definitely the case that a UDF will restrict parallelism which of course isn’t normally something we would want so is another reason to be cautious of scalar UDFs.
The execution plan does expose the fact that the plan has not gone parallel and we can see this as a property of the Select operator:
Resolution
There are a few ways we could resolve this problem: we’ll run through them and I’ll tabulate the results at the end. If you are following along, any indexes created in the examples below must be dropped before advancing to the next option.
Option 1 – Indexing
We could index some or all of the individual queries in the UDF which in this example are actually fairly simple. One index will probably do most of the heavy lifting for us:
CREATE INDEX IX_OwnerUserId ON dbo.Posts
(
OwnerUserId,
PostTypeId
)
INCLUDE
(
Score
);
I’ll execute the query again and look at just the Profiler trace on both databases, given we know STATISTICS IO is hiding information from us.
This has improved both queries massively as the lookups inside the UDF can now use a seek.
Option 2 – Rewrite
This option involves removing the UDF and adding the logic directly into our query. My version is below. There will almost certainly be [better] alternatives but this is the first one I came up with that gave an improvement over the original (which is often the goal in real life – a quick, acceptable improvement is normally better than a delayed, perfect improvement). I’ll execute it on both versions of the database after reverting the index change above:
WITH Agg AS
(
SELECT PostTypeId,
OwnerUserId,
COUNT(*) AS PostCount,
AVG(CAST(Score AS DECIMAL(10,2))) AS AvgAnswerScore
FROM dbo.Posts p
JOIN dbo.Users u
ON u.Id = p.OwnerUserId
WHERE p.PostTypeId IN (1,2) AND
u.Reputation > 700000
GROUP BY
p.PostTypeId, /* 1 = question, 2 = answer */
p.OwnerUserId
)
SELECT Id,
DisplayName,
Reputation,
MAX(PostCount) * 2 +
MAX(QuestionCount) * 1.5 +
MAX(AnswerCount) * 3 +
MAX(AvgAnswerScore) * 1.2
FROM (
SELECT u.Id,
u.DisplayName,
u.Reputation,
SUM(PostCount) OVER (PARTITION BY u.Id) AS PostCount,
CASE WHEN a.PostTypeId = 1 THEN PostCount ELSE 0 END AS QuestionCount,
CASE WHEN a.PostTypeId = 2 THEN PostCount ELSE 0 END AS AnswerCount,
CASE WHEN a.PostTypeId = 2 THEN AvgAnswerScore ELSE 0 END AS AvgAnswerScore
FROM dbo.Users u
JOIN Agg a
ON u.Id = a.OwnerUserId
GROUP BY u.Id,
u.DisplayName,
u.Reputation,
PostCount,
PostTypeId,
AvgAnswerScore
) a
GROUP BY
Id,
DisplayName,
Reputation;
As this brings the logic inline, we can now see the true IO cost by adding SET STATISTICS IO, TIME ON to the top and get the IO output. The stats are as follows:
StackOverflow2010:
Table 'Users'. Scan count 1, logical reads 3, physical reads 3, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 9, logical reads 804492, physical reads 2, page server reads 0, read-ahead reads 799464, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 21, logical reads 61, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4187 ms, elapsed time = 4065 ms.
StackOverflow2013:
Table 'Users'. Scan count 2, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 21, logical reads 67, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 48, logical reads 35120, physical reads 4150, page server reads 0, read-ahead reads 30970, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 9, logical reads 4200767, physical reads 5933, page server reads 0, read-ahead reads 3261110, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 42096 ms, elapsed time = 11439 ms.
We can also see the actual execution plan reveals all that is actually happening to us now:
The times and IO numbers are a big improvement over the original but we can improve further if we add the index to support this query:
CREATE INDEX IX_OwnerUserId ON dbo.Posts
(
OwnerUserId,
PostTypeId
)
INCLUDE
(
Score
);
Now the statistics are:
StackOverflow2010:
Table 'Worktable'. Scan count 3, logical reads 57, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 72, physical reads 1, page server reads 0, read-ahead reads 12, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 14, logical reads 174, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 19 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
StackOverflow2013:
Table 'Worktable'. Scan count 3, logical reads 63, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 44581, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 16, logical reads 391, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 259 ms.
We are now orders of magnitude better than the original and on the 2010 database, slightly better than the indexing alone.
Option 3 – Scalar UDF inlining
The third option is something that is sometimes possible on Scalar UDFs. Introduced in SQL Server 2019 was the Scalar UDF inlining feature which sought to automatically do the inlining we achieved in Option 2 without us having to change any code. To see if this works for us, we need to change the database compatibility to 2019 or above. I’ll change to 2019 on StackOverflow2010 to begin with:
ALTER DATABASE StackOverflow2010 SET COMPATIBILITY_LEVEL = 150;
Now I’ll run the original UDF query on StackOverflow2010:
USE StackOverflow2010;
SET STATISTICS IO, TIME ON;
SELECT
u.Id,
u.DisplayName,
u.Reputation,
dbo.fn_GetUserEngagementScore(u.Id) AS EngagementScore
FROM dbo.Users u
WHERE u.Reputation > 700000;
The STATISTICS IO, TIME output shows this is actually worse than the original (the true figures we saw through Profiler)
StackOverflow2010:
Table 'Worktable'. Scan count 28, logical reads 44909616, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 4, logical reads 3203424, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 43328 ms, elapsed time = 43343 ms.
StackOverflow2013:
Table 'Worktable'. Scan count 32, logical reads 216375815, physical reads 7334, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 4, logical reads 16738228, physical reads 9827, page server reads 0, read-ahead reads 16096523, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 280375 ms, elapsed time = 303907 ms.
The plan does look different, however:
We can see that it is doing each lookup in the UDF as a separate operation. Whilst this has been inlined, the reads, IO and elapsed time are still huge so in this case, the inlining alone hasn’t got us over the line.
However, once again, if we add the same index as above:
CREATE INDEX IX_OwnerUserId ON dbo.Posts
(
OwnerUserId,
PostTypeId
)
INCLUDE
(
Score
);
We see an improvement across both databases:
StackOverflow2010:
Table 'Posts'. Scan count 28, logical reads 444, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 21 ms.
StackOverflow2013:
Table 'Posts'. Scan count 32, logical reads 1080, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 4, physical reads 2, page server reads 0, read-ahead reads 1, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 57 ms.
Results
The table below shows the difference the various changes have made. I wasn’t expecting this to be the case when I started out with this post but in real life, I think the index option would probably be the best in this case due to not requiring a code change which can often be more work than a simple index deployment. Whilst inlining with an index gives a large improvement, this requires a database compatibility level change which is far more invasive than just adding an index, and it gives very little benefit in this case over just the index on its own. However, it all depends on the parameters of your environment and local policies. I have seen cases in real life where UDFs had far more complicated queries in them than the ones we were dealing with here and indexing them was not enough and they needed to be replaced completely with inline code. Maybe next time I will ask Claude to come up with a UDF that has some queries with joins and other complications!
| Option | Database | Reads Captured Via | Logical Reads | CPU Time (ms) | Elapsed (ms) |
|---|---|---|---|---|---|
| Before | 2010 | Profiler | 22,861,337 | 41,701 | 10,364 |
| 2013 | Profiler | 134,258,218 | 564,547 | 315,885 | |
| Option 1 – Indexing | 2010 | Profiler | 544 | 31 | 30 |
| 2013 | Profiler | 1,228 | 63 | 67 | |
| Option 2 – Rewrite | 2010 | STATISTICS IO | 804,556 | 4,187 | 4,065 |
| 2013 | STATISTICS IO | 4,235,960 | 42,096 | 11,439 | |
| Option 2 – Rewrite (with IX_OwnerUserId) | 2010 | STATISTICS IO | 303 | 31 | 19 |
| 2013 | STATISTICS IO | 45,035 | 250 | 259 | |
| Option 3 – Scalar UDF Inlining | 2010 | STATISTICS IO | 48,113,043 | 43,328 | 43,343 |
| 2013 | STATISTICS IO | 233,114,047 | 280,375 | 303,907 | |
| Option 3 – Scalar UDF Inlining (with IX_OwnerUserId) | 2010 | STATISTICS IO | 447 | 16 | 21 |
| 2013 | STATISTICS IO | 1,084 | 63 | 57 |
Conclusion
The purpose of this post was to answer the question “When I’m looking at a query, I bet it’s bad if I see ____.” I chose to answer this with “Scalar UDFs” and looked at some of the problems with Scalar UDFs and some of the options available to resolve them. Of course, each case is different depending on the Scalar UDF, the environment and the real-world constraints so your mileage may vary.
References / Further Reading
Erik Darling – Learn T-SQL With Erik: Scalar UDFs
Greg Larsen – Get Your Scalar UDFs to Run Faster Without Code Changes
Microsoft – Scalar UDF Inlining