UDF performance suddenly degraded

Question

SQL Server 2008 R2

I know this is a common problem (eg: http://connect.microsoft.com/SQLServer/feedback/details/524983/user-defined-function-performance-is-unacceptable) - but ive only just learned of it.

We have a live production database that has performed fine for months, specifically a stored procedure that contains a large CTE that selects into a table variable. Then, based on a parameter on the procedure - data is selected from the table variable in various ways (column sequence, order by clause etc).

This temporary table only has a few hundred rows inserted into it from the CTE - the source data is huge, but the resulting rows are always low in number)

The select satements used on the table variable use 2 very simple UDF's:

CREATE FUNCTION [dbo].[format_timeV2] ( @Time INT )
RETURNS VARCHAR(20)
AS 
    BEGIN
        DECLARE @Time_Varchar VARCHAR(20)
        SET @Time_Varchar = CONVERT(VARCHAR, @Time / 3600) + 'h ' + CONVERT(VARCHAR, ROUND(( ( CONVERT(FLOAT, ( @Time % 3600 )) ) / 3600 ) * 60, 0)) + 'm'
        RETURN @Time_Varchar
    END
GO

And

CREATE FUNCTION [dbo].[udf_WeekEndDate] ( @Date DATETIME )
RETURNS DATETIME
AS 
    BEGIN
        RETURN DATEADD(DAY, 7, CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(day, -1 - ( DATEPART(dw, @Date) + @@DATEFIRST - 2 ) % 7, @Date), 103) + ' 23:59:59', 103))
    END
GO

These UDF's have been working fine for months, if not years, the stored procedure in question has ALWAYS ran in under 6 seconds - but as soon as the UDF's above are included on the SELECT statement of the table variable the procedure takes MINUTES!

As soon as we comment out the usage of these functions it returns to executing in 3-6 seconds.

These functions are only being used on the SELECT from @TableVar statement (few hundred rows) - so i dont see why the query optimiser if struggling here?!?!

If we run the following - the UDF's and procedure start to work normally again:

DBCC FreeProcCache
DBCC DropCleanbuffers

So i can only assume the query optimiser is doing something stupid in the background - but it's not clear on the execution plan as to what it is.

I have tried recreating the proc with RECOMPILE - this didnt help, only FreeProcCache & DropCleanbuffers helped.

Any ideas what the root cause of this? we dont really want to go re-writing all the UDFs as table valued function (which i understand is a work around for this problem) as this is a live production database which has been working fine for years.

EDIT 1: Blam requested detail of the actual query that hangs:

    IF @ModeInt = 2 
        SELECT  [DET_NUMBERA] ,
                [Name] ,
                [Branch] ,
                COUNT(*) OVER ( PARTITION BY [DET_NUMBERA] ) AS RowsForThisEmployee ,
                CONVERT(VARCHAR(10), dbo.udf_WeekEndDate([Date]), 103) AS [WeekEnding] ,
                [Date] ,
                [Start Time] ,
                [End Time] ,
                [Duration] ,
                dbo.format_timeV2([Duration] * 60) AS [DurationF] ,
                [EntryCount] ,
                [EntryColour] ,
                [DurationColour] ,
                [DurationComment]
        FROM    @CompletedData
        WHERE   [EntryCount] = 0
        ORDER BY [DET_NUMBERA] ,
                [Date] ,
                [Start Time]

    IF @ModeInt = 3 
        SELECT  [DET_NUMBERA] ,
                [Name] ,
                [Branch] ,
                COUNT(*) OVER ( PARTITION BY [DET_NUMBERA] ) AS RowsForThisEmployee ,
                CONVERT(VARCHAR(10), dbo.udf_WeekEndDate([Date]), 103) AS [WeekEnding] ,
                [Date] ,
                [Start Time] ,
                [End Time] ,
                [Duration] ,
                dbo.format_timeV2([Duration] * 60) AS [DurationF] ,
                [EntryCount] ,
                [EntryColour] ,
                [DurationColour] ,
                [DurationComment]
        FROM    @CompletedData
        WHERE   [EntryCount] > 1
        ORDER BY [DET_NUMBERA] ,
                [Date] ,
                [Start Time]

The temp table doesnt have an index (didnt think it was needed as it only ever contains 100 - 200 rows:

    DECLARE @CompletedData TABLE
        (
          [DET_NUMBERA] VARCHAR(7) ,
          [Name] VARCHAR(255) ,
          [Branch] VARCHAR(3) ,
          [Date] DATE ,
          [Start Time] TIME(0) ,
          [End Time] TIME(0) ,
          [Duration] INT ,
          [EntryCount] INT ,
          [EntryColour] VARCHAR(15) ,
          [DurationColour] VARCHAR(15) ,
          [DurationComment] VARCHAR(65)
        )

The CTE populates the table in 3 seconds, its only the above SELETS that make use of UDF's that hang, if i remove the UDF's from the SELECT it works instantly.

Answer 1

3 things I would do.

1. Use #temporary tables instead of @table variables. Both get materialized in tempdb, but using #temporary tables gives you greater flexibility and better information to the Query Optimizer.

2. Perform the COUNT in a classic GROUP BY query prior rather than using it as a windowing function. These are known to work terribly in SQL Server for certain conditions. Feel free to compare the plans generated.

3. Drop the usage of the functions. The expressions are very simple and can be directly included in the query

e.g.

SELECT  a.[DET_NUMBERA] ,
        a.[Name] ,
        a.[Branch] ,
        G.RowsForThisEmployee ,
        CONVERT(CHAR(10), a.[Date] +6 - (DATEPART(dw, a.[Date]) + @@DATEFIRST -2) % 7, 103) [WeekEnding],
        a.[Date] ,
        a.[Start Time] ,
        a.[End Time] ,
        a.[Duration] ,
        RIGHT(a.[Duration]/60, 10) + 'h ' + RIGHT(a.[Duration]%60,10) + 'm' [DurationF], 
        a.[EntryCount] ,
        a.[EntryColour] ,
        a.[DurationColour] ,
        a.[DurationComment]
FROM    #CompletedData a
JOIN    (SELECT   [DET_NUMBERA], COUNT(*) RowsForThisEmployee
         FROM     #CompletedData
         WHERE    a.[EntryCount] = 0
         GROUP BY [DET_NUMBERA]) G ON a.[DET_NUMBERA] = G.[DET_NUMBERA]
WHERE    a.[EntryCount] = 0
ORDER BY a.[DET_NUMBERA] ,
         a.[Date] ,
         a.[Start Time];