Ответ 1
При использовании OPTION (RECOMPILE) обязательно посмотрите план пост-исполнения ( "фактический" ), а не предварительный ( "оцененный" ). Некоторые оптимизации применяются только при выполнении:
DECLARE @ForeignKeyCol int = 20;
SELECT ForeignKeyCol, ForeignKeyRank
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol
OPTION (RECOMPILE);
План предварительного исполнения:
План после исполнения:
Протестировано на SQL Server 2012 build 11.0.3339 и SQL Server 2008 R2 build 10.50.4270
Фон и ограничения
Когда в SQL Server 2005 были добавлены функции оконной перестройки, оптимизатор не имел возможности выталкивать выборку из этих новых прогнозов последовательности. Чтобы устранить некоторые распространенные сценарии, в которых это вызвало проблемы с производительностью, SQL Server 2008 добавил новое правило упрощения, SelOnSeqPrj, которое позволяет выбирать подходящие варианты выбора, где значение является константой. Эта константа может быть литералом в тексте запроса или оцененным значением параметра, полученного с помощью OPTION (RECOMPILE). Нет особой проблемы с NULLs, хотя для этого запроса может потребоваться ANSI_NULLS OFF. Насколько я знаю, применение упрощения к постоянным значениям является ограничением реализации; нет особых причин, по которым он не может быть расширен для работы с переменными. Я помню, что правило SelOnSeqPrj рассматривало наиболее часто встречающиеся проблемы с производительностью.
Параметрирование
Правило SelOnSeqPrj не применяется, когда запрос успешно автопараметризирован. Не существует надежного способа определить, была ли автоматическая параметризация запроса в SSMS, это только указывает на попытку автопарама. Чтобы быть ясным, присутствие держателей мест, таких как [@0], показывает только, что была предпринята попытка автоматической параметризации. Надежный способ определить, был ли подготовлен план для повторного использования, заключается в проверке кеша плана, где "параметризованный дескриптор плана" обеспечивает связь между специальными и подготовленными планами.
Например, в SSMS автоматически настраивается следующий запрос:
SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;
Но кеш плана показывает иначе:
WITH XMLNAMESPACES
(
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
parameterized_plan_handle =
deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedPlanHandle', 'nvarchar(64)'),
parameterized_text =
deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedText', 'nvarchar(max)'),
decp.cacheobjtype,
decp.objtype,
decp.plan_handle
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
WHERE
dest.[text] LIKE N'%ViewOnBaseTable%'
AND dest.[text] NOT LIKE N'%dm_exec_cached_plans%';
Если параметр базы данных принудительной параметризации включен, мы получаем параметризованный результат, в котором оптимизация не применяется:
ALTER DATABASE Sandpit SET PARAMETERIZATION FORCED;
DBCC FREEPROCCACHE;
SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;
В запросе кеша плана теперь отображается параметризованный кешированный план, связанный параметризованным дескриптором плана:
Обход
По возможности, мое предпочтение заключается в том, чтобы переписать представление как встроенную функцию таблицы, где предполагаемая позиция выбора может быть сделана более явной (если необходимо):
CREATE FUNCTION dbo.ParameterizedViewOnBaseTable
(@ForeignKeyCol integer)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
bt.PrimaryKeyCol,
bt.ForeignKeyCol,
ForeignKeyRank = DENSE_RANK() OVER (
PARTITION BY bt.ForeignKeyCol
ORDER BY bt.PrimaryKeyCol),
bt.DataCol
FROM dbo.BaseTable AS bt
WHERE
bt.ForeignKeyCol = @ForeignKeyCol;
Запрос будет выглядеть следующим образом:
DECLARE @ForeignKeyCol integer = 20;
SELECT pvobt.*
FROM dbo.ParameterizedViewOnBaseTable(@ForeignKeyCol) AS pvobt;
С планом выполнения:
