Различия в плане запросов SQL Server

Question

У меня возникают проблемы с пониманием поведения оценочных планов запросов для моего оператора в SQL Server при переходе от параметризованного запроса к непараметрическому запросу.

У меня есть следующий запрос:

DECLARE @p0 UniqueIdentifier = '1fc66e37-6eaf-4032-b374-e7b60fbd25ea'
SELECT [t5].[value2] AS [Date], [t5].[value] AS [New]
FROM (
    SELECT COUNT(*) AS [value], [t4].[value] AS [value2]
    FROM (
        SELECT CONVERT(DATE, [t3].[ServerTime]) AS [value]
        FROM (
            SELECT [t0].[CookieID]
            FROM [dbo].[Usage] AS [t0]
            WHERE ([t0].[CookieID] IS NOT NULL) AND ([t0].[ProductID] = @p0)
            GROUP BY [t0].[CookieID]
            ) AS [t1]
        OUTER APPLY (
            SELECT TOP (1) [t2].[ServerTime]
            FROM [dbo].[Usage] AS [t2]
            WHERE ((([t1].[CookieID] IS NULL) AND ([t2].[CookieID] IS NULL)) 
            OR (([t1].[CookieID] IS NOT NULL) AND ([t2].[CookieID] IS NOT NULL) 
            AND ([t1].[CookieID] = [t2].[CookieID]))) 
            AND ([t2].[CookieID] IS NOT NULL)          
            AND ([t2].[ProductID] = @p0)
            ORDER BY [t2].[ServerTime]
            ) AS [t3]
        ) AS [t4]
    GROUP BY [t4].[value]
    ) AS [t5]
ORDER BY [t5].[value2]

Этот запрос генерируется выражением Linq2SQL и извлекается из LINQPad. Это дает хороший план запроса (насколько я могу судить) и выполняется за 10 секунд в базе данных. Однако если я заменю два использования параметров точным значением, то есть заменим две части '= @p0' на '= '1fc66e37-6eaf-4032-b374-e7b60fbd25ea' ', я получу другой примерный план запроса и запрос теперь выполняется намного дольше (более 60 секунд, до сих пор не дошел).

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

Моя настоящая проблема в том, что я могу жить с 10-секундным временем выполнения (по крайней мере, какое-то время), но я не могу жить с 60-секундным временем выполнения. Мой запрос будет (как указано выше) произведен Linq2SQL, поэтому он выполняется в базе данных как

exec sp_executesql N'
        ...
        WHERE ([t0].[CookieID] IS NOT NULL) AND ([t0].[ProductID] = @p0)
        ...
        AND ([t2].[ProductID] = @p0)
        ...
       ',N'@p0 uniqueidentifier',@p0='1FC66E37-6EAF-4032-B374-E7B60FBD25EA'

что приводит к тому же плохому времени выполнения (что я думаю вдвойне странно, так как кажется, что используются параметризованные запросы.

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

РЕДАКТИРОВАТЬ: я загрузил планы выполнения для не параметризованного и параметризованного запроса, а также план выполнения для параметризованного запроса (как предложено Хайнцем) с другим GUID здесь

Надеюсь, это поможет вам помочь мне:)

Answer 1

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

Похоже, у вас есть два индекса:

IX_NonCluster_Config (ProductID, ServerTime)
IX_NonCluster_ProductID_CookieID_With_ServerTime (ProductID, CookieID) INCLUDE (ServerTime)

Первый индекс не охватывает CookieID но заказан на ServerTime и, следовательно, более эффективен для менее избирательных ProductIDх (то есть те, которые у вас много)

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

В среднем вы ProductID мощность такова, что SQL Server ожидает, что второй метод будет эффективным, то есть тот, который он использует, когда вы используете параметризованные запросы или явно предоставляете выборочный GUID"S.

Тем не менее, ваш оригинал GUID считается менее избирательным, поэтому используется первый метод.

К сожалению, первый метод требует дополнительной фильтрации CookieID вот почему это менее эффективно на самом деле.

Answer 2

Если вы укажете явное значение, SQL Server может использовать статистику этого поля, чтобы принять "лучшее" решение плана запроса. К сожалению (как я недавно испытал), если информация, содержащаяся в статистике, вводит в заблуждение, иногда SQL Server просто делает неправильный выбор.

Если вы хотите углубиться в эту проблему, я рекомендую вам проверить, что произойдет, если вы используете другие GUID: если он использует другой план запросов для разных конкретных GUID, это указывает на то, что используются статистические данные. В этом случае вы можете посмотреть на sp_updatestats и связанные команды.

РЕДАКТИРОВАТЬ: Посмотрите на DBCC SHOW_STATISTICS: "Медленный" и "быстрый" GUID, вероятно, находятся в разных сегментах в гистограмме. У меня была похожая проблема, которую я решил, добавив INDEX табличная подсказка к SQL, которая "ведет" SQL Server к поиску "правильного" плана запроса. По сути, я посмотрел, какие индексы используются во время "быстрого" запроса, и жестко запрограммировал их в SQL. Это далеко не оптимальное или элегантное решение, но лучшего я пока не нашел...

Answer 3

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

Я видел, как это происходит с использованием запросов, которые имеют smalldatetime в предложении where для столбца, который использует datetime.

Answer 4

Трудно сказать, не глядя на планы выполнения, однако, если бы я собирался угадать причину, я бы сказал, что это комбинация перехвата параметров и плохой статистики - В случае, когда вы жестко кодируете GUID в запросе, оптимизатор запросов пытается оптимизировать запрос для этого значения параметра. Я полагаю, что то же самое происходит с параметризованным / подготовленным запросом (это называется анализом параметров - план выполнения оптимизируется для параметров, используемых при первом выполнении подготовленного оператора), однако этого определенно не происходит, когда вы объявляете параметр и использовать его в запросе.

Как я уже сказал, SQL-сервер пытается оптимизировать план выполнения для этого значения, и поэтому обычно вы должны увидеть лучшие результаты. Здесь кажется, что та информация, на которой он основывает свои решения, неверна / вводит в заблуждение, и вам лучше (по некоторым причинам), когда он оптимизирует запрос для значения общего параметра.

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