Действительно ли этот запрос эквивалентен 2008 SQL Server, ОПТИМИЗИРУЮТ ДЛЯ НЕИЗВЕСТНОГО?
Я поддерживаю хранимые процедуры для SQL Server 2005, и мне жаль, что я не мог использовать новую функцию в 2008, которая позволяет подсказку запроса: "ОПТИМИЗИРУЙТЕ ДЛЯ НЕИЗВЕСТНОГО"
Кажется, как будто следующий запрос (записанный для SQL Server 2005) оценивает то же количество строк (т.е. селективность), как будто ОПЦИЯ (ОПТИМИЗИРУЮТ ДЛЯ НЕИЗВЕСТНОГО) была указана:
CREATE PROCEDURE SwartTest(@productid INT)
AS
DECLARE @newproductid INT
SET @newproductid = @productid
SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @newproductid
Этот запрос избегает сниффинга параметра путем объявления и установки новой переменной. Это - действительно обходное решение SQL Server 2005 года для функции OPTIMIZE-UNKNOWN? Или я пропускаю что-то? (Авторитетные ссылки, ответы или результаты испытаний ценятся).
Подробнее: Был указан быстрый тест на SQL Server, 2008 говорит мне, что количество предполагаемых строк в этом запросе является на самом деле тем же, как будто ОПТИМИЗИРУЮТ ДЛЯ НЕИЗВЕСТНОГО. Действительно ли это - то же поведение на SQL Server 2005? Я думаю, что не забываю слышать, после того как это без большего количества информации, Механизм Оптимизации SQL Server должен предположить селективность параметра (обычно в 10% для предикатов неравенства). Я все еще ищу категорическую информацию о поведении SQL 2005 все же. Я не совсем уверен, что информация существует хотя...
Больше Информации 2: Чтобы быть ясным, этот вопрос просит сравнение НЕИЗВЕСТНОЙ подсказки запроса и метода маскирования параметра, который я описываю.
Это - технический вопрос, не проблема, решая вопрос. Я рассмотрел много других возможностей и обосновался на этом. Таким образом, единственная цель этого вопроса состояла в том, чтобы помочь мне завоевать некоторое доверие, что эти два метода эквивалентны.
4 ответа
Хорошо, я поэкспериментировал. Я напишу здесь результаты, но сначала я хочу сказать, что, основываясь на том, что я видел и знаю, я уверен, что с использованием временных параметров в 2005 и 2008 годах точно эквивалентно к использованию ОПТИМИЗАЦИИ ДЛЯ НЕИЗВЕСТНОГО 2008 г. . По крайней мере, в контексте хранимых процедур.
Вот что я нашел. В описанной выше процедуре я использую базу данных AdventureWorks. (Но я использую аналогичные методы и получаю аналогичные результаты для любой другой базы данных). Я запустил:
dbcc show_statistics ('Sales.SalesOrderDetail', IX_SalesOrderDetail_ProductID)
И я вижу статистику с 200 шагами на гистограмме. Глядя на его гистограмму, я вижу, что существует 66 различных строк диапазона (то есть 66 различных значений, которые не были включены в статистику как значения равенства). Добавьте 200 строк равенства (из каждого шага), и я получу оценку 266 различных значений для ProductId в Sales.SalesOrderDetail.
Имея в таблице 121317 строк, я могу оценить, что каждый ProductId содержит в среднем 456 строк. И когда я смотрю на план запроса для моей тестовой процедуры (в формате xml), я вижу что-то вроде:
...
<QueryPlan DegreeOfParallelism="1" >
<RelOp NodeId="0"
PhysicalOp="Index Seek"
LogicalOp="Index Seek"
EstimateRows="456.079"
TableCardinality="121317" />
...
<ParameterList>
<ColumnReference
Column="@newproductid"
ParameterRuntimeValue="(999)" />
</ParameterList>
</QueryPlan>
...
Итак, я знаю, откуда берется значение EstimateRows (с точностью до трех десятичных знаков), и замечаю, что атрибут ParameterCompiledValue отсутствует в план запроса. Именно так выглядит план при использовании OPTIMIZE FOR UNKNOWN от 2008 г.
Недавно я использовал это решение несколько раз, чтобы избежать перехвата параметров в SQL 2005, и мне кажется, что он делает то же, что и ОПТИМИЗАЦИЯ ДЛЯ НЕИЗВЕСТНОСТИ в SQL 2008. Исправлено множество проблем, которые у нас были с некоторыми из наших больших хранимых процедур, которые иногда просто зависали при передаче определенных параметров.
Я использовал эту технику маскировки параметров по крайней мере последний год из-за странных проблем с производительностью, и она работала хорошо, но ОЧЕНЬ раздражает необходимость постоянно это делать.
Я ВСЕГДА использовал WITH RECOMPILE.
У меня нет контролируемых тестов, потому что я не могу выборочно включать и выключать использование каждого параметра автоматически в системе, но я подозреваю, что маскировка параметров поможет только ЕСЛИ параметр используется. У меня есть несколько сложных SP, где параметр используется не в каждом операторе, и я предполагаю, что WITH RECOMPILE все же был необходим, потому что некоторые "временные" рабочие таблицы не заполняются (или даже индексируются одинаково, если я пытаюсь настроить) одинаково при каждом запуске, и некоторые последующие операторы не полагаются на параметр, когда рабочие таблицы уже заполнены соответствующим образом. Я разбил некоторые процессы на несколько SP именно для того, чтобы работа по заполнению рабочей таблицы в одном SP могла быть правильно проанализирована и выполнена против WITH RECOMPILE в следующем SP.
Интересный вопрос.
Есть хорошая статья в блоге SQL Programming and API Development Team здесь, в которой перечислены обходные решения до SQL 2008, а именно:
- использовать подсказку RECOMPILE, чтобы запрос перекомпилировался каждый раз
- непараметризировать запрос
- дать определенные значения в подсказке OPTIMIZE FOR
- заставить использовать определенный индекс
- использовать руководство по плану
Что приводит меня к этой статье, где упоминается ваш обходной путь использования локальных параметров и как он генерирует план выполнения на основе статистики. Насколько этот процесс похож на новую подсказку OPTIMIZER FOR UNKNOWN, я не знаю. Мне кажется, что это разумное обходное решение.