If there is a large (10 tables and some temp tables), complex query and in the select is a calculation with a lot of column values from different tables and the execution plans shows nested loops inside nested loops .. you can sit and wait and collect your $$.
Why? Just show somethings to keep in mind fixing queries.
The user defined function does some string concatenation of the user’s earned badges.
A nice way to encapsulate business logic and things stay readable, but how does the database engine handle with this.. (Credits go to Erik Darlings Plan Cache Liars series and for his tools, see Github erikdarlingdata (Erik Darling) ).
In the past Erik Darling worked with Brent Ozar and made sp_BlitzCache, we see “High CPU en low costs.”
Open de query in Performance Monitor Dashboard, you get great additional information.
The udf is executed row after row, the Execution plan does not go parallel.
Trivial plan, this cannot be done parallel.. we wait 6 seconds. You can rewrite the statement to get a better plan (130ms versus 6 seconds).
Solution is to re-write the query a little bit, so it get a fully optimized and parallel plan.
Even get some nice tips and warnings in the Performance Studio.
USE StackOverflow2013;
GO
EXEC dbo.DropIndexes;
DBCC FREEPROCCACHE;
CREATE OR ALTER PROC dbo.pcl_ScalarFunction
AS
BEGIN
SELECT TOP (100)
u.DisplayName
, dbo.Fake_String_Agg(u.Id) AS FakeString_Agg
FROM dbo.Users AS u
where u.Reputation > 100000
-- and u.DisplayName = 'Aaron Bertrand'
END;
GO
CREATE OR ALTER FUNCTION dbo.Fake_String_Agg (@UserId INT)
RETURNS NVARCHAR(4000)
WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING
AS
BEGIN
DECLARE @WickedBadIdeaDude NVARCHAR(4000)
SELECT @WickedBadIdeaDude = STUFF((SELECT N', ' + b2.Name
FROM dbo.Badges AS b2
WHERE b2.UserId = @UserId
GROUP BY b2.Name
FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(4000)'), 1, 2, N'')
RETURN @WickedBadIdeaDude
END
GO
EXEC dbo.pcl_ScalarFunction;
EXEC sp_BlitzCache @StoredProcName = 'pcl_ScalarFunction';
EXEC sp_BlitzCache @StoredProcName = 'Fake_String_Agg';
SELECT TOP (100) gg.DisplayName
,string_agg(gg.name, ' ')
FROM (
SELECT u.DisplayName
,b2.name
FROM dbo.Users AS u
INNER JOIN dbo.Badges AS b2 ON b2.UserId = u.id
WHERE u.Reputation > 100000
-- and u.DisplayName = 'Aaron Bertrand'
GROUP BY u.DisplayName
,b2.name
) gg
GROUP BY gg.DisplayName