Sql-server – UDF performance suddenly degraded

sql serversql-server-2008

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.

Best Answer

3 things I would do.

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.
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.
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];

Related Solutions

Sql-server – Inserting and updating and selecting at thousands of times per second

Change the updates to use WITH (ROWLOCK). That will reduce the locks from page level to row level. You can try the same on the inserts as well.

Sql-server – How to avoid using variables in WHERE clause

Parameter sniffing is your friend almost all of the time and you should write your queries so that it can be used. Parameter sniffing helps building the plan for you using the parameter values available when the query is compiled. The dark side of parameter sniffing is when the values used when compiling the query is not optimal for the queries to come.

The query in a stored procedure is compiled when the stored procedure is executed, not when the query is executed so the values that SQL Server has to deal with here...

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

is a known value for @endDate and an unknown value for @startDate. That will leave SQL Server to guessing on 30% of the rows returned for the filter on @startDate combined with whatever the statistics tells it for @endDate. If you have a big table with a lot of rows that could give you a scan operation where you would benefit most from a seek.

Your wrapper procedure solution makes sure that SQL Server sees the values when DateRangeProc is compiled so it can use known values for both @endDate and @startDate.

Both your dynamic queries leads to the same thing, the values are known at compile-time.

The one with a default null value is a bit special. The values known to SQL Server at compile-time is a known value for @endDate and null for @startDate. Using a null in a between will give you 0 rows but SQL Server always guess at 1 in those cases. That might be a good thing in this case but if you call the stored procedure with a large date interval where a scan would have been the best choice it may end up doing a bunch of seeks.

I left "Use the DATEADD() function directly" to the end of this answer because it is the one I would use and there is something strange with it as well.

First off, SQL Server does not call the function multiple times when it is used in the where clause. DATEADD is considered runtime constant.

And I would think that DATEADD is evaluated when the query is compiled so that you would get a good estimate on the number of rows returned. But it is not so in this case.
SQL Server estimates based on the value in the parameter regardless of what you do with DATEADD (tested on SQL Server 2012) so in your case the estimate will be the number of rows that is registered on @endDate. Why it does that I don't know but it has to do with the use of the datatype DATE. Shift to DATETIME in the stored procedure and the table and the estimate will be accurate, meaning that DATEADD is considered at compile time for DATETIME not for DATE.

So to summarize this rather lengthy answer I would recommend the wrapper procedure solution. It will always allow SQL Server to use the values provided when compiling the the query without the hassle of using dynamic SQL.

PS:

In comments you got two suggestions.

OPTION (OPTIMIZE FOR UNKNOWN) will give you an estimate of 9% of rows returned and OPTION (RECOMPILE) will make SQL Server see the parameter values since the query is recompiled every time.

Best Answer

Related Solutions

Sql-server – Inserting and updating and selecting at thousands of times per second

Sql-server – How to avoid using variables in WHERE clause

Related Question