Slow scalar function that used to be fast I have a scalar function that defined as below:

CREATE FUNCTION GetUserFullname(@Id INT)
RETURNS NVARCHAR(500)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @fullname NVARCHAR(500)

    -- Add the T-SQL statements to compute the return value here
    SELECT @fullname = (Firstname + ' ' + Lastname)
    FROM Account_Profiles
    WHERE UserId = @Id

    -- Return the result of the function
    RETURN @fullname
END


This function used to be fast when I used it in a query:

SELECT dbo.GetUserFullname(CurrentRefId) FROM OrderItems


The query above has a result of ~50,000 rows. the query used to be executed in 2 seconds but now all of sudden it takes 22 seconds to run!

It is slow for all distinct values of CurrentRefId in OrderItems, when I query SELECT dbo.GetUserFullname(CurrentRefId) FROM OrderItems. There are statistics updated every night. There is also an index with Total Fragmentation 75.

I also have a copy of this database in my local SQL Server and it does run very fast in ~1 second.

My production database server has 512 GB memory and 64 processors. It is an Always On Failover Cluster server and Availability Group. The database version is 2012 SP2.
You are requerying the Account_Profiles table for every single row in OrderItems.  If OrderItems has 50,000 rows then you are going to hit Account_Profiles 50,000 times.  If OrderItems grows larger you will query it more times.  If Account_Profiles grows larger then each of those times will take longer.  You will see a large difference by inlining the concatenation. (making assumptions on the join column names here)

Select Firstname + ' ' + Lastname FullName --other data here
FROM OrderItems oi INNER JOIN Account_Profiles ap
ON oi.userid=ap.userid