Scalar functions can be a real headache when you’re performance tuning. For one, they don’t parallelize. In fact, if you use a scalar function in a computed column, it will prevent any query that uses that table from going parallel – even if you don’t reference that column at all!
Let’s set up a table:
CREATE TABLE dbo.lots_of_rows_computed ( a int NOT NULL, b int NOT NULL, c AS 2*a, PRIMARY KEY CLUSTERED (a) );
Note that we have a computed column that doubles the value of “a”. We’ll use UPDATE STATISTICS to force SQL Server to think that this is a really large table, hoping that this might persuade the optimizer to choose a parallel plan.
UPDATE STATISTICS dbo.lots_of_rows_computed WITH ROWCOUNT=1000000000;
Now, let’s try an identical table, except the computed column is calculated using a scalar function. The calculation and the data, however, should be identical. First, set up the function:
CREATE FUNCTION dbo.[double] (@int int) RETURNS int AS BEGIN; -- Simple enough. RETURN(@int*2); END; GO
.. and the table with some fake statistics:
CREATE TABLE dbo.lots_of_rows_function ( a int NOT NULL, b int NOT NULL, c AS dbo.[double](a), PRIMARY KEY CLUSTERED (a) ); UPDATE STATISTICS dbo.lots_of_rows_function WITH ROWCOUNT=1000000000;
Now, let’s see what it does with a heavy query (that doesn’t reference any computed column).
SELECT SUM(b) FROM dbo.lots_of_rows_computed; SELECT SUM(b) FROM dbo.lots_of_rows_function;
Because both tables have a billion rows each according to their statistics, this should be an easy sell for parallel processing. So how did we do?
The computed column (a*2) parallelizes nicely. The one with scalar function, however..
How to find them
Rounding off this quick post, here’s a handy query to find out which tables contain computed columns using scalar functions.
SELECT s.[name]+'.'+o.[name] AS [table], p.[rows], cc.[name] AS [computed column], cc.[definition], sref.[name]+'.'+ref.[name] AS referenced_object, ref.[type_desc] AS referenced_type FROM sys.schemas AS s INNER JOIN sys.objects AS o ON s.[schema_id]=o.[schema_id] INNER JOIN ( SELECT [object_id], SUM([rows]) AS [rows] FROM sys.partitions WHERE index_id IN (0, 1) GROUP BY [object_id] ) AS p ON o.[object_id]=p.[object_id] INNER JOIN sys.computed_columns AS cc ON o.[object_id]=cc.[object_id] INNER JOIN sys.sql_expression_dependencies AS dep ON cc.[object_id]=dep.referencing_id AND cc.column_id=dep.referencing_minor_id INNER JOIN sys.objects AS ref ON dep.referenced_id=ref.[object_id] AND ref.[type]='FN' -- Scalar function INNER JOIN sys.schemas AS sref ON sref.[schema_id]=ref.[schema_id] WHERE dep.referenced_class=1 -- OBJECT_OR_COLUMN ORDER BY s.[name], o.[name], sref.[name], ref.[name];
9 thoughts on “Computed columns with scalar functions”
I ran these scripts on MSSQL 2017 (RTM CU1) and both statements gave the same (bad) execution plan (query optimizer mode was disabled). On which version did you ran these scripts?
That’s odd. I developed them on a SQL2014 SP2, but they should work on any current version.
If you get the single-threaded plan on both queries, I would start checking all the prerequisites for parallel execution: MAXDOP setting on the server (and database for SQL 2017), how many CPU cores you have assigned to SQL Server, possibly even the parallelism threshold – I use UPDATE STATISTICS to fake a billion rows in the demo tables, so I imagine this should go parallel on most rigs.
I’ll check …
Found! It was caused by MAXDOP=1 on the 2017 instance. Switching the maxdop to zero gives the same described behaviour
Pingback: Avoid Scalar Functions In Computed Columns – Curated SQL
does the same thing happen if the computed column is persisted? I’d hope that having the column physically stored would allow parallel processing to take place.
Please, replace in script
INNER JOIN sys.sql_expression_dependencies AS dep ON cc.[object_id]=dep.referencing_id
INNER JOIN sys.sql_expression_dependencies AS dep ON cc.column_id=dep.referencing_minor_id and cc.[object_id]=dep.referencing_id
to avoid duplication when there are several calculated fields in the table
Nice catch, thanks!