Scalar functions are the butt of everybody’s jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they’re in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, I could go on and on.
Recently, we ran across yet another scenario where scalar UDFs were killing performance.
Someone happened to add a scalar UDF to an indexed view, and any query that touched that view couldn’t go parallel – even if it didn’t need query the function-based field!
Check it out, using my friend the Stack Overflow database.
First, let’s create a function that just returns the number 1. That’s it. No data access, no worries about how much work the function’s doing – it just returns 1, that’s it:
CREATE OR ALTER FUNCTION dbo.fn_Returns1 ( @Id INT )
WITH RETURNS NULL ON NULL INPUT,
Then let’s create an indexed view that includes that function:
CREATE VIEW dbo.vwVotes WITH SCHEMABINDING AS
SELECT Id, dbo.fn_Returns1(1) AS One
CREATE UNIQUE CLUSTERED INDEX IX_Id ON dbo.vwVotes(Id);
Then run a query on that view:
SELECT COUNT(*) FROM dbo.vwVotes;
We scan the index, and the cost is above my Cost Threshold for Parallelism, but it still goes single-threaded:
Why? Well, right-click on the plan, click View XML, and check out the highlighted area (emphasis mine):
Doh. Scalar functions strike again.
But check out WITH (NOEXPAND)
Add that query hint, and look what happens: the query goes parallel!
Both plans have full optimization. Both hit the same index. I have no idea why you suddenly get parallelism when you use that hint – I just stumbled on it accidentally.
This may be related to a fix Paul White found in SQL Server 2016 last year – look for the “A New Option” section in that post. That one requires a trace flag, but I’m certainly not using that trace flag here. It might be that WITH (NOEXPAND) now triggers the same behavior in computed fields that the trace flag used to.
I have no idea what version/build this was introduced in – my testing was done on 2017 CU3. Feel free to talk amongst yourselves and try other versions/builds if you’re having the scalar-in-indexed-views problem.
Of course, the best answer isn’t to use the WITH (NOEXPAND) hint – as long as you’re in there mucking with the query, if you can, just get rid of the scalar function.