UDF性能突然下降

Question

Server 2008 R2

我知道这是一个常见的问题(例如：http://connect.microsoft.com/SQLServer/feedback/details/524983/user-defined-function-performance-is-unacceptable) --但我只是刚刚知道。

我们有一个活的生产数据库，已经运行了几个月，特别是一个存储过程，其中包含一个大的CTE，它选择到一个表变量中。然后，根据过程中的一个参数，以各种方式(列序列、order by子句等)从表变量中选择数据。

这个临时表只有几百行从CTE插入-源数据是巨大的，但结果行的数目总是很低)

表变量上使用的选择饱和使用2种非常简单的UDF：

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

和

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

这些UDF已经运行了几个月，如果不是几年的话，所讨论的存储过程总是在6秒内运行-但是一旦UDF上面的UDF包含在表变量的SELECT语句中，这个过程就需要几分钟！

一旦我们注释掉这些函数的使用情况，它就会在3-6秒内返回执行。

这些函数只用于SELECT from @TableVar语句(几百行)，所以我不明白为什么查询优化器会在这里挣扎？！

如果我们运行以下代码--UDF和过程将再次正常工作：

DBCC FreeProcCache
DBCC DropCleanbuffers

因此，我只能假设查询优化器在后台做了一些愚蠢的事情--但执行计划中并不清楚它是什么。

我试过用RECOMPILE重新创建proc --这没有帮助，只有FreeProcCache & DropCleanbuffers提供了帮助。

知道这是什么根本原因吗？我们真的不想把所有的UDF重写为表值函数(据我所知，这是解决这个问题的一项工作)，因为这是一个多年来一直运行良好的实时生产数据库。

编辑1: Blam请求挂起的实际查询的详细信息：

    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]

temp表没有索引(不认为它是必需的，因为它只包含100 - 200行：

    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)
        )

CTE在3秒内填充表，只有上面的SELETS使用挂起的UDF，如果我将UDF从SELECT中移除，它就会立即工作。

Answer 1

三件我会做的事。

1. 使用#临时表而不是@table变量。这两种方法都是在tempdb中实现的，但是使用#临时表可以为查询优化器提供更大的灵活性和更好的信息。
2. 先按查询执行经典组中的计数，而不是将其用作窗口函数。众所周知，在某些情况下，这些操作在Server中非常有效。可以自由地比较所产生的计划。
3. 放弃函数的使用。表达式非常简单，可以直接包含在查询中。

例如：

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