TSQL IVF causing timeout in ASP.net application

Question

Hi and thanks for your attention.

First some background on the question:

I have an Inline Table Function which is used ubiquitously throughout an ASP.net application I have developed, running on IIS7. It's the basis for 30 or more stored procedures, all of while will normally process in a maximum of 200ms (more than fast enough). The application always uses the same connection string for all database connections. The function itself runs in a maximum of ~10ms.

Occasionally a call to a page which calls a stored procedure in which the function is used will result in a timeout after 15 seconds. This timeout applies to all users of the page, but other pages which also have stored procedures which use this function still perform normally while this is occurring, suggesting that it is a specific stored procedure having problems. However this has occurred on multiple pages, suggesting it is either multiple stored procedures or the function itself.

Running any (or all) of the stored procedures on the page from a management studio session with different user credentials is <200ms, even when a timeout is occurring for the web application.

Running an sp_recompile on the function will always "clear" the timeout, from any login credentials.

Because this is a critical portion of the application, sp_recompile is run as soon as possible, and little time is available for debugging. Also, I have never been able to recreate the timeout at will.

I've tried to do a lot of research on Inline Table Functions and haven't come across anything which suggests that this is a common problem with them and they should therefore be avoided.

The Question:

Is it possible that these timeouts are being caused by using the function, or are they guaranteed to be an issue with the stored procedures which rely upon it? To put it another way, is there likely to be any benefit in refactoring the stored procedures to use either a view or by encapsulating the appropriate logic inline?

My guess is that it is the stored procedures only and I'll likely solve this by adding optimize for unknown, option recompile where appropriate, or localising the parameters, but in all honesty I'd prefer to find a solution which applies to the underlying function such that I can apply a fix in a single location.

The function:

CREATE FUNCTION [dbo].[fn_ObjectIDs] (
    @DateFrom       AS DATETIME = NULL
    ,@DateTo        AS DATETIME = NULL
    ,@Region        AS INT = NULL
    ,@FamilyID      AS INT = NULL
    ,@ParentID      AS INT = NULL
    ,@ChildID       AS INT = NULL
) RETURNS TABLE AS
RETURN
    SELECT DISTINCT
        obj.ID       AS IDs
    FROM tblObjects obj WITH (NOLOCK)
    INNER JOIN tblFamily fam WITH (NOLOCK)
            ON obj.famID = fam.famID
        LEFT JOIN tblCountry cntry WITH (NOLOCK)
            ON (@Region IS NOT NULL) AND (fam.countryId = cntry.countryId)
        LEFT JOIN tblParent parent WITH (NOLOCK)
            ON (@ParentID IS NOT NULL) AND (obj.ID = parent.objectID)
        LEFT JOIN tblChild child WITH (NOLOCK)
            ON (@ChildID IS NOT NULL) AND (obj.ID = child.objectID)
    WHERE
        obj.Visible = 1
        AND obj.statusID IN (3,4,6,8)
        AND ((@DateFrom IS NULL) OR (obj.CreatedDate >= @DateFrom))
        AND ((@DateTo IS NULL) OR (obj.CreatedDate <= @DateTo))
        AND ((@Region IS NULL) OR (cntry.regionID = @Region))
        AND ((@FamilyID IS NULL) OR (obj.famID = @FamilyID))
        AND ((@ParentID IS NULL) OR (parent.parentID = @ParentID))
        AND ((@ChildID IS NULL) OR (child.childID = @ChildID))

Answer 1

Temporarily add some logging to verify what is actually causing the timeout problem. You can do this in your application and it the stored procedure itself. Have the procedure write the current timestamp and the parameters used to invoke the procedure to a logging tables when it's executed. Also add logging to your application. Then you'll be able to identify when specific timeouts occur if there are certain parameters causing the problem or if the problem is in the procedure at all.

In general user defined functions aren't a good idea, though my understanding is that a table inline function is better than some of the others. It adds a lot of overhead and the optimizer can't work properly with UDFs.