I have a SQL Server 2005 table-function that normally runs in about .3
seconds.  About every two weeks it starts taking about 48 seconds.  To fix it
all I have to do is to resave the procedure (i.e. in Server Management
Studio, right-click on the function, select Modify, and then click on
Execute).  It then runs in .3 seconds again.

Any ideas what might be going on?

Here is the function, although I can't imagine it makes a difference what it
is:

ALTER FUNCTION [dbo].[Respondent_Stats] (@sitecode VARCHAR(30))
RETURNS @retRespondents TABLE
(
    [key] INT,
    total_logins INT,   
    total_viewed INT,   
    total_profile INT,   
    total_aprofile INT,   
    total_essay INT,   
    total_bymail INT,   
    total_byemail INT
)
AS
BEGIN

    DECLARE @settings AS XML
    SELECT @settings=publishing FROM Classes WHERE sitecode=@sitecode

    DECLARE @from DATETIME
    DECLARE @to DATETIME

    SELECT  @from =        @settings.value('(//respondents/from)[1]','DATETIME')
    SELECT  @to
=        DATEADD(d,1,@settings.value('(//respondents/to)[1]','DATETIME'))

    INSERT @retRespondents
    SELECT [key],
        SUM(login) AS total_logins,   
        SUM(viewed) AS total_viewed,   
        SUM(profile) AS total_profile,   
        SUM(aprofile) AS total_aprofile,   
        SUM(essay) AS total_essay,   
        SUM(bymail) AS total_bymail,   
        SUM(byemail) AS total_byemail
    FROM
    (
    SELECT [key],
        CASE WHEN logcode='LOGIN' THEN 1 ELSE 0 END as login,
        CASE WHEN logcode='VIEWED' THEN 1 ELSE 0 END as viewed,
        CASE WHEN logcode='PROFILE' AND [key]=loggedby THEN 1 ELSE 0 END as profile,
        CASE WHEN logcode='PROFILE' AND [key]<>loggedby THEN 1 ELSE 0 END as
aprofile,
        CASE WHEN logcode='ESSAY' THEN 1 ELSE 0 END as essay,
        0 AS bymail,
        0 AS byemail
        FROM Log
        WHERE sitecode=@sitecode
        AND [key] IN (SELECT [key] FROM Classmates WHERE sitecode=@sitecode)
        AND logtime BETWEEN @from AND @to
    UNION ALL
    SELECT [key],
        0 as login,
        0 as viewed,
        0 as profile,
        0 as aprofile,
        0 as essay,
        CASE WHEN
profile.value('(/Profile/Yearbook/submissionby)[1]','VARCHAR(30)')='mail'
THEN 1 ELSE 0 END as bymail,
        CASE WHEN
profile.value('(/Profile/Yearbook/submissionby)[1]','VARCHAR(30)')='email'
THEN 1 ELSE 0 END as byemail
        FROM Classmates
        WHERE sitecode=@sitecode
        AND profile.value('(/Profile/Yearbook/submissionby)[1]','VARCHAR(30)')<>''
    ) as a
    GROUP BY [key]

    RETURN
END;

Sounds like every couple of weeks, the query execution plan 'goes bad'.

Is there some regularly scheduled data maintenance process that runs every
couple of weeks that has the potential to drastically alter the composition
of the Log or Classmates tables?

Show quoteHide quote

No.  Nothing close to that.  It also happen unreugularly.  The strange thing
is it keeps working, just much slower.

Show quoteHide quote

Marking (Not just Finding) Duplicate Values ......
Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string