Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 О вреде хинтов  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Некоторое время назад я стартовал топик О пользе хинтов.

На днях работал с упомянутыми там запросами и обнаружил интересный баг.
В топике рассматривался такой запрос:
SELECT RecordDateTrim AS DateBegin, COUNT(*) AS RowNumber
FROM OLTP.SkuByDayCategories WITH(NOEXPAND, INDEX([IX_SkuByDayCategories_RecordDateTrim]), FORCESCAN)
GROUP BY RecordDateTrim
, где OLTP.SkuByDayCategories - индексированное представление.
При работе с большими объёмами эти хинты уменьшали стоимость запроса в 10 раз.

Понадобилось выбрать данные за февраль, исключая отчётный день.
DECLARE @UpdateDate datetime;
SELECT @UpdateDate = '2015-02-11 03:21:16.357';

SELECT RecordDateTrim AS DateBegin, COUNT(*) AS RowNumber
    FROM OLTP.SkuByDayCategories WITH(NOEXPAND, INDEX([IX_SkuByDayCategories_RecordDateTrim]), FORCESCAN)
    WHERE RecordDateTrim >= '2015-02-01' AND RecordDateTrim < CAST(@UpdateDate AS date)
    GROUP BY RecordDateTrim;

Результат нулевой.
DateBeginRowNumber

Более того, индекс вообще не сканируется.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Убираю хинт FORCESCAN.
SELECT RecordDateTrim AS DateBegin, COUNT(*) AS RowNumber
    FROM OLTP.SkuByDayCategories WITH(NOEXPAND, INDEX([IX_SkuByDayCategories_RecordDateTrim]))
    WHERE RecordDateTrim >= '2015-02-01' AND RecordDateTrim < CAST(@UpdateDate AS date)
    GROUP BY RecordDateTrim;

Получаю искомые данные.
DateBeginRowNumber
2015-02-013027796
2015-02-021457700
2015-02-031258466
2015-02-04316967
2015-02-0555842
2015-02-0651383
2015-02-0749618
2015-02-0848589
2015-02-09156147
2015-02-1087924

Убираю CAST из WHERE - и тоже получаю искомый результат.
SELECT RecordDateTrim AS DateBegin, COUNT(*) AS RowNumber
    FROM OLTP.SkuByDayCategories WITH(NOEXPAND, INDEX([IX_SkuByDayCategories_RecordDateTrim]), FORCESCAN)
    WHERE RecordDateTrim >= '2015-02-01' AND RecordDateTrim < DATEADD(DAY, -1, @UpdateDate)
    GROUP BY RecordDateTrim;

DateBeginRowNumber
2015-02-013027796
2015-02-021457700
2015-02-031258466
2015-02-04316967
2015-02-0555842
2015-02-0651383
2015-02-0749618
2015-02-0848589
2015-02-09156147
2015-02-1087924

Планы выполнения всех 3 запросов прилагаются.
Есть идеи по поводу такого интересного поведения?

К сообщению приложен файл (Plans.xml - 107Kb) cкачать
16 фев 15, 11:52    [17270290]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Есть идеи по поводу такого интересного поведения?

дык, баг, по всей видимости, какие тут особо идеи. делайте репро и заявляйте на connect.
16 фев 15, 12:20    [17270542]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
daw
> Есть идеи по поводу такого интересного поведения?

дык, баг, по всей видимости, какие тут особо идеи. делайте репро и заявляйте на connect.


Может вам лучше посмотреть в сторону обновления или даже целенаправленного создания статистик под данных запрос. Вся неоптимальность планов от плохих или необновлённых статистик. В частности, если таблица большая, то автоматичское обновление статистик может сработать не так быстро как хотелось бы. Например, при резком вбросе куска данных с какими-то новыми значениями.
16 фев 15, 15:21    [17271963]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
a_voronin
Вся неоптимальность планов от плохих или необновлённых статистик.
Речь идёт не об оптимальности планов, а о возвращении неверных результатов. Добавление хинта FORCESCAN меняет результат запроса. Хотя скажу по секрету, оптимизатор этот хинт игнорирует.
16 фев 15, 16:17    [17272522]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Jovanny
a_voronin
Вся неоптимальность планов от плохих или необновлённых статистик.
Речь идёт не об оптимальности планов, а о возвращении неверных результатов. Добавление хинта FORCESCAN меняет результат запроса. Хотя скажу по секрету, оптимизатор этот хинт игнорирует.


Под каким уровнем изоляции это работает?
16 фев 15, 16:21    [17272549]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Дефолтным - READ COMMITTED.
16 фев 15, 16:34    [17272653]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Jovanny
Дефолтным - READ COMMITTED.


Посмотрите, может есть конфликт между транзакциями.

Если он не берёт FORCESCAN, то какую операцию он делает. Может он пытается наложить блокировку на всю таблицу или читает игнорируя блокировки.
16 фев 15, 17:10    [17272909]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Никаких блокировок нет, это проверено.
И везде используется Seek вместо Scan. Я там прикреплял планы запросов, посмотрите.

Добавлю ещё - этот индекс секционирован и проблемный запрос возвращает записи с RecordDateTrim < '2015-01-01'
Более того убираю из запроса CAST - и запрос работает корректно.

Краткое резюме: "Баг при одновременном использовании FORCESCAN и CAST(... AS date) в индексированных представлениях с некластерным индексом секционированным по полю типа date, по которому производится фильтрация".

Нашёл ещё один вариант.
DECLARE @UpdateDate datetime;
SELECT @UpdateDate = '2015-02-11 03:21:16.357';

SELECT RecordDateTrim AS DateBegin, COUNT(*) AS RowNumber
    FROM OLTP.SkuByDayCategories WITH(NOEXPAND, INDEX([IX_SkuByDayCategories_RecordDateTrim]), FORCESCAN)
    WHERE RecordDateTrim >= '2015-02-01' AND RecordDateTrim < CAST(@UpdateDate AS date)
    GROUP BY RecordDateTrim;

SELECT RecordDateTrim AS DateBegin, COUNT(*) AS RowNumber
    FROM OLTP.SkuByDayCategories WITH(NOEXPAND, INDEX([IX_SkuByDayCategories_RecordDateTrim]), FORCESCAN)
    WHERE RecordDateTrim >= '2015-02-01' AND RecordDateTrim < CAST('2015-02-11 03:21:16.357' AS date)
    GROUP BY RecordDateTrim;

Первый запрос не возвращает записей, а второй возвращает искомые результаты.
И второй запрос использует Scan, а первый хинт игнорирует, пытаясь использовать Seek.

К сообщению приложен файл (Plans2.sqlplan - 74Kb) cкачать
16 фев 15, 17:30    [17273057]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Jovanny,

Это баг.
Чуть позже сделаю репро с объяснением.
16 фев 15, 18:07    [17273235]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
SomewhereSomehow,

Спасибо! Ждём.
16 фев 15, 18:30    [17273317]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Я объясню сначала общую картину, а потом вернусь к вашему случаю.
Мне кажется, так будет понятнее и вам и всем, кому интересно.

Коротко говоря - это баг.
Для его воспоизведения, не нужно ничего особенного.

Баг
Ниже следующий скрипт создает функцию секционировария, из 4-х диапазонов:
Range 1 – (…,0]
Range 2 – (0,10]
Range 3 – (10,100]
Range 4 – (100,…)

Затем создается схема и таблица секционированная по первичному кластерному ключу по этой схеме.
В таблице 4 строки: 1, 10, 50, 100.

Далее, делаем два запроса, с предикатом - выбрать строки < 100, но в одном из них указываем подсказку - FORCESCAN.

Репро
use tempdb;
go
create partition function pf(int) as range for values (0, 10, 100)
create partition scheme ps as partition pf all to ([primary]);
go
if object_id('t') is not null drop table t;
create table t (a int primary key) on ps(a);
insert t(a) values (1),(10),(50),(100);
go
select a,b=(select 1) from t where a < 100;
select a,b=(select 1) from t with(forcescan) where a < 100;
go

Результаты
Картинка с другого сайта.
Очевидно, что второй запрос вернул результат не с той спецификацией, которую мы заказывали. Потерялось значение 50, которое тоже меньше 100.

Исследование
Прежде чем погрузится вглубь, немного вспомним, как организована работа с секционированными таблицами с точки зрения оптимизатора начиная с сиквел 2008: Craig Freedman - Partitioned Tables in SQL Server 2008
Кому лень читать, вкратце: в 2005-м работа с секционированными таблицами осуществлялась "по особому", т.е. нужно было строить для этого "особые" планы. Это неудобно. С 2008-го, колонка секционирования логически добавляется в качестве лидирующего ключа в индексе (физически - разные структуры, но оптимизатор этого не видит). Т.е. создаете на секционированной таблице секционированный индекс - index(a), оптимизатор видит его как index(PartitionID, a).

Назад к запросу.
Посмотрим, в самом первом плане, в самом правом операторе Clustered Index Seek, который выдает корректные результаты, свойство Seek Predicates.
Картинка с другого сайта.
Можно увидеть два ключа поиска (не два предиката поиска), второй - это тот что мы определили в запросе. Первый, оптимизатор добавил автоматически. Он делает это, т.к. у него есть такой прием оптимизации как исключение секций (Partition Elimination).
Эта оптимизация, позволяет исключать секции, к которым не будет доступа. Например, если у нас предикат а < 100, и таблица секционирована по а, то зачем нам читать секции, которые содержат значения больше или равные 100? Оптимизатор их исключает и для этого формирует диапазон таких секций. Если бы мы спросили (как у ТС) по переменной (значение которой, в отличие от константы, не известно, пока запрос не начал выполняться), то для этого есть динамическое исключение секций, т.е. в план вставляется специальная функция RangePartitionNew - которая на этапе выполнения определяет какие секции будем читать, а какие нет.
Более подробно на этом не буду останавливаться, кому интересно:
Craig Freedman – Partitioning
Paul White - Why Doesn’t Partition Elimination Work?

Важно: Баг проявляется и при динамическом и при статическом исключении секций.

Вернемся к запросу и плану. Видно, что в данном случае, сервер применил статическое исключение секций при помощи добавления предиката:
Start: PtnId1000 >= Scalar Operator((1)); End: PtnId1000 <= Scalar Operator((3))
Который значит, что оптимизатор будет рассматривать секции с 1 по 3-ю.
И мы можем в это убедиться, посмотрев на свойство действительного плана "Actual Partition Accessed"
Картинка с другого сайта.

Теперь второй план:
Картинка с другого сайта.

Что мы видим (сверху вниз, по порядку):
- Residual predicate (предикат указанный в условии запроса)
- Статическое исключение секций, но со сложным ключем (заметьте, что ключ один, состоит из двух столбцов, которые имеют разделитель ";" в свойстве End. И мы ищем в диапазоне от PtnId1000>=Scalar Operator(1), до PtnId1000; [tempdb].[dbo].[t].a <= Scalar Operator((3)-(1)); Scalar Operator((100))
- И самое главное, обратите внимание на последний яркий красный квадрат, где вычисляется диапазон секций он: (3)-(1)!!. Т.е <= (3)-(1), т.е. <=(2).

И на самом деле в свойствах плана:
Картинка с другого сайта.

Если сделать простой запрос, чтобы посмотреть, какой секции принадлежит каждое значение:
select a, PtnId = $partition.pf(a) from t

Картинка с другого сайта.
То видно, что значение 50 принадлежит третей секции, которую сервер не собирается читать!

Объяснение

Чуть-чуть теории.
Сервер строит план запроса поэтапно, весь процесс я расписывал пару лет назад в своем блоге:
Оптимизатор (ч.1): Введение, Optimization: Simplification
Оптимизатор (ч.2): Optimization: Trivial Plan Optimization
Оптимизатор (ч.3): Optimization: Full Optimization: Search 0
Оптимизатор (ч.4): Optimization: Full Optimization: Search 1
Кому лень читать, кратко:
Начиная с разбора текста запроса в дерево логических операторов, сервер заканчивает оптимизацию деревом физических операторов (оба дерева объекты определенных классов С++ внутри сиквела). Но дерево физических операторов - еще не план
+ знаменитая картинка с моих докладов по внутренностям оптимизатора

Картинка с другого сайта.
Дерево логических операторов - красненькое, план синенький.

При построении запроса происходит процесс, который внутри сиквела называется "Copy Out" - т.е. копирование из одного дерева в другое. Но на самом деле происходит не просто копирование, а еще и некоторые преобразования.
Одно из них - склеивание вместе операции фильтра и сканирования "Filter + Scan" = "Scan with residual predicate".

Когда мы используем подсказку FORCESCAN, мы форсируем, вместо поиска, сканирование с фильтром - "Filter + Scan", которое затем может быть "схлопнуто" в один оператор. Опять же пару лет назад, я нашел флаг, который отменят эту операцию склеивания, давайте выполним запросы с этим флагом оба с подсказкой forcescan:
select a,b=(select 1) from t with(forcescan) where a < 100;
select a,b=(select 1) from t with(forcescan) where a < 100 option(querytraceon 9130);

Картинка с другого сайта.
Результаты:
Картинка с другого сайта.
Из результатов видно, что когда, после оптимизации, на этапе создания плана из дерева физических операторов происходит "схлопывание" филтра и сканирования - мы видим поиск (несмотря на forcescan) и неправильные результаты, если этого не делать - то правильные результаты и два оператора - сканирование и фильтрация.

На данном этапе понятно, что дело не в оптимизации (поиск плана), а в том, как строится сам план из уже найденного дерева.

А происходит вот что.

Оптимизатор, пытается использовать последний шанс, чтобы "пропихнуть" предикат на самый ранний этап (чтобы отбросить ненужные строки еще на этапе сканирования). И вот тут кроется ошибка.
При форсированном скане, он формирует два предиката: Seek и Residual - и при построении диапазона для исключения секций считает их оба выполняя арифметические действия над номерами секций.

Давайте сделаем такой запрос:
select a,b=(select 1) from t where a < 100 and a > 10;
select a,b=(select 1) from t with(forcescan) where a < 100 and a > 10;


Результат:
Картинка с другого сайта.

Второй запрос - вообще ничего не вернул.

Теперь планы.
Первый:
Картинка с другого сайта.
Как вы видите, он сопоставил начало поиска (первую секцию) с искомым значением "a > 10" и посчитал, что для этого условия, он должен начать с секции 1(первая) + 2(секция для значения 10) = 3.
Окончание в этом плане - также правильное - секция 3, т.к. нет residual предиката, т.е. не с чем комбинировать, нет никаких математических действий.

Во втором случае:
Картинка с другого сайта.

Условие исключение секций:
Начало PtnID >=2+1, но конец PtnID <= 3-1 - т.к. неверно почитано.
Итого - PtnID <= 3-1 and PtnID >=2+1, i.e – PtnID <= 2 and PtnID >=3 - Очевидно, что в таком диапазоне - строк нет.

Решение
Решение тут - пусть МС исправят баг =)
Баги с некорректными результатами они обычно исправляют быстро, так что ждите в скором времени, но чтобы привлечь внимание к проблеме, активно голосуем: https://connect.microsoft.com/SQLServer/feedback/details/1134053/incorrect-results-with-forcescan-and-partitioned-table

Уточнения:
К куче - это не относится. Для быстрого фикса на кластерном индексе можно with(index(0)).
Residual Predicate - сфь по себе не является бедствием, ошибка в том, как он комбинируется с другими.

Еще написал статью в свой англоязычный блог, кому интересно: Partitioned clustered index and FORCESCAN bug

ТС
Jovanny
Теперь, отвечаю персонально Вам.

Во первых спасибо за интересный кейс и грамотно оформленный запрос: планы, действительные, в нужном формате (а не картинкой, как тут делают иногда =) или просто текст запроса, грамотно описан эксперимент - одно удовольствие.

По вашему эксперименту:

- пост 1
1) Когда есть FORCESCAN и секционированная таблица, думаю понятно - тут я все выше объяснил, как сумел.
2) Если убираете хинт FORCESCAN - то не будет сгенерирован дополнительный residual predicate, который комбинируется с конечным, и на этапе переписывания дерева в план, не будет лишнего предиката - по этому нет ошибки.
3) Если убираете cast из where - то у вас расхождения в типа параметров, столбец date, а функция возвращает datetime, в таком случае - не работает механизм исключения секций (уже выше давал ссылку на Пола, но еще раз - Why Doesn’t Partition Elimination Work?

- пост 2
4) то же что и п.1
5) У вас тут тоже преобразование типов (хотя в плане его нет), и тут оптимизатор как вы видите показывает Index Scan, не Seek как в ошибочном варианте.
Если в моем репро выше сделать:
select a,b=(select 1) from t with(forcescan) where a > 10 and a < 100;
select a,b=(select 1) from t with(forcescan) where a > 10 and a < convert(bigint,100);

То первый вернет неправильный результат, а второй - правильный. Оба имеют и seek и residual предикат - только они разные из-за преобразования типов (пусть даже мы преобразовали int в bigint). Эти случаи обрабатываются отдельно, я можно докопаться как в данном случае конкретно - но игра не стоит свеч. Если любопытно, вот Пол пишет на примере хэш-соединения Join Performance, Implicit Conversions, and Residuals, при вычислении диапазона, преобразования типов (даже если не видны в плане - можете поискать, во втором плане выше - and a < convert(bigint,100); - там нигде нет слова - convert, кроме текста запроса) тоже имеют значение. Это связано с Type System Expression Services и его взаимодействием в QP - они там делают особую магию =).

П.С.
Я бы ваши темы переименовал, в первой как раз банальный кейс, где хины косвенно влияли на план, а тут - такой баг шикарный нашли, польза всем сиквелистам будет от его устранения =)
17 фев 15, 02:37    [17274446]     Ответить | Цитировать Сообщить модератору
 Re: О вреде хинтов  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
SomewhereSomehow,

Огромное спасибо! Получил массу удовольствий, читая Ваш ответ. Понял некоторые ранее непонятные вещи.
SomewhereSomehow
Я бы ваши темы переименовал, в первой как раз банальный кейс, где хины косвенно влияли на план, а тут - такой баг шикарный нашли, польза всем сиквелистам будет от его устранения =)

Переименуйте, как считате нужным.)
Ещё раз спасибо!
17 фев 15, 10:48    [17275140]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить