TSQL IVF вызывает таймаут в приложении ASP.net

Question

Привет и спасибо за внимание.

Сначала немного предыстории по вопросу:

У меня есть встроенная табличная функция, которая используется повсеместно в разрабатываемом мной приложении ASP.net, работающем на IIS7. Это основа для 30 или более хранимых процедур, которые обычно обрабатываются максимум за 200 мс (более чем достаточно быстро). Приложение всегда использует одну и ту же строку подключения для всех подключений к базе данных. Сама функция работает максимум за 10 мс.

Иногда вызов страницы, которая вызывает хранимую процедуру, в которой используется функция, приводит к истечении времени ожидания через 15 секунд. Этот тайм-аут применяется ко всем пользователям страницы, но другие страницы, на которых также есть хранимые процедуры, использующие эту функцию, по-прежнему работают нормально, пока это происходит, что указывает на наличие специфической хранимой процедуры, имеющей проблемы. Однако это произошло на нескольких страницах, предполагая, что это либо несколько хранимых процедур, либо сама функция.

Выполнение любой (или всех) хранимых процедур на странице из сеанса студии управления с различными учетными данными пользователя составляет <200 мс, даже если для веб-приложения истекает время ожидания.

Запуск sp_recompile для функции всегда "очищает" тайм-аут от любых учетных данных для входа.

Поскольку это критическая часть приложения, sp_recompile запускается как можно скорее, и для отладки остается мало времени. Кроме того, я никогда не мог воссоздать тайм-аут по желанию.

Я пытался провести много исследований функций встроенных таблиц и не сталкивался с чем-либо, что наводит на мысль, что это общая проблема с ними, и поэтому их следует избегать.

Вопрос:

Возможно ли, что эти тайм-ауты вызваны использованием функции, или они гарантированно являются проблемой с хранимыми процедурами, которые полагаются на это? Другими словами, может ли быть какая-то польза от рефакторинга хранимых процедур для использования либо представления, либо инкапсуляции соответствующей встроенной логики?

Я предполагаю, что это только хранимые процедуры, и я, скорее всего, решу это, добавив оптимизацию для неизвестных, перекомпиляцию опций, где это необходимо, или локализацию параметров, но, честно говоря, я бы предпочел найти решение, которое применимо к базовому функция, так что я могу применить исправление в одном месте.

Функция:

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

Временно добавьте запись в журнал, чтобы проверить, что на самом деле вызывает проблему тайм-аута Вы можете сделать это в своем приложении и в самой хранимой процедуре. Попросите процедуру записать текущую метку времени и параметры, используемые для вызова процедуры в таблицах журналов при ее выполнении. Также добавьте регистрацию в ваше приложение. Тогда вы сможете определить, когда происходят определенные таймауты, если есть определенные параметры, вызывающие проблему, или проблема вообще в процедуре.

В общем, пользовательские функции не очень хорошая идея, хотя я понимаю, что встроенная функция таблицы лучше, чем некоторые другие. Это добавляет много накладных расходов, и оптимизатор не может работать должным образом с UDF.