Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
Все доброго дня.
Поставила меня в ступор следующая ситуация.

Имеем таблицу с кластерным уникальным индексам по полям Period+ID
Period - datetime2, ID - binary(16). Колонок > 2 (с десяток).
Таблица и кластерный индекс одинаково секционированы (одной функцией, по кварталам) по полю Period.
в таблице под 40 млн записей.

Имеем запрос вида
SELECT TOP 45 * FROM [dbo].[Table] t
  WHERE t._Period >= {ts '2018-08-01 00:00:00'}
  order by t._Period, t._ID


На совместимостях 110, 120 - работает прекрасно (CIseek).
При переключении на совместимость 130 - CIscan.
"Хм", подумал я... и пошел забавляться с флагами трассировки, статистикой, перестроением индекса и т.д.
Самое странное, что это даже не новый оптимизатор, т.к. на 120 - все ок (хотя как знать - новый активно развивают).

Оказывается, скуль (130) невзлюбил формат даты!
Любые другие варианты работают отлично
WHERE T._Period >= convert(datetime2, {ts '2018-08-01 00:00:00'})
WHERE T._Period >= '2018-08-01'
и т.п. - всегда CIseek

Может кто-нибудь объяснить В ЧЕМ ФИШКА?
Что случилось, если на более ранних совместимостях все прекрасно работало? Какие-то изменения может были правилах применения форматов дат?

PS: СУБД - mssql 2016 (enterprise) sp1 cu8

Сообщение было отредактировано: 20 авг 18, 00:50
20 авг 18, 00:30    [21647472]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Кто не делает явного преобразования литералов/констант в нужный тип, тот страдает.
Вы же смотрели, что там в Predicate написано, когда CIscan?

Сообщение было отредактировано: 20 авг 18, 01:07
20 авг 18, 00:53    [21647480]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
Гавриленко Сергей Алексеевич,
Содержимым запроса, текстом, параметрами управляет приложение. Я тут никак не повлияю.
Какой предикат в скане? Их там нет.
Поясните свои слова.
Преобразования над самим столбцомп (периодом) ведь не выполняются.
Попробовал воспроизвести на других данных без секционирования- пока не получается получить скан.
20 авг 18, 08:28    [21647554]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
nvv
Какой предикат в скане? Их там нет.
Их там есть.
Свойства итератора смотрите. Должны увидеть примерно это - https://littlekendra.com/2016/03/10/the-case-of-datetime2-and-partition-elimination/
20 авг 18, 08:57    [21647581]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
invm, виноват... ИхТамЕсть
......Period >= '2018-08-06 00:00:00.000'
При том что при "поиске по индексу" поиск по предикату выглядит так:
......Period >= Скалярный оператор('2018-08-06 00:00:00.0000000')

Значит ли это, что в первом случае СУБД не выполнила неявное преобразование значения и выполняется сравнение разных типов?

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

Какова вероятная причина такого поведения СУБД? Ошибка движка? До совместимости 130 этого косяка не было.
20 авг 18, 09:50    [21647670]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
nvv,

в 130 поменялось преобpазование
сравните для 120 и 130
DECLARE @datetime datetime = '2018-04-29 10:30:30.553';
DECLARE @datetime2 datetime2 = @datetime;

SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime'


как это влияет на предикат индекса я не знаю. Но правильный ответ как всегда: преобразовуйте явно.
Ну и есть масса ссылок на баг, но коннект убит и искать лень :)
20 авг 18, 09:55    [21647680]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
TaPaK, посмотрел, увидел.
Что-то в таком духе я и ожидал ((

Последний вопрос: MS это никак не исправляла? Так оно все и висит до последних версий?
20 авг 18, 10:28    [21647759]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
nvv
TaPaK, посмотрел, увидел.
Что-то в таком духе я и ожидал ((

Последний вопрос: MS это никак не исправляла? Так оно все и висит до последних версий?

что он должен исправить?
20 авг 18, 10:30    [21647762]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
nvv
Последний вопрос: MS это никак не исправляла? Так оно все и висит до последних версий?
Зачем исправлять то, что было сделано специально? Сидите на 120 CL, для того CL и придумали.

Сообщение было отредактировано: 20 авг 18, 10:31
20 авг 18, 10:31    [21647763]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
nvv,
автор
Содержимым запроса, текстом, параметрами управляет приложение. Я тут никак не повлияю.

влияйте, если в базе datetime2 то и приложение должно отдавать DateTime2, всё остальное это ошибка
20 авг 18, 10:37    [21647777]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
автор
Зачем исправлять то, что было сделано специально? Сидите на 120 CL, для того CL и придумали.

Т.е. в 130 они специально отказались от преобразования? Это где-то документировано?
20 авг 18, 10:49    [21647800]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
Гавриленко Сергей Алексеевич
nvv
Последний вопрос: MS это никак не исправляла? Так оно все и висит до последних версий?
Зачем исправлять то, что было сделано специально? Сидите на 120 CL, для того CL и придумали.
А не знаете, зачем это было сделано?

Семантически ведь запрос не меняется, при изменении типа константы - "получить записи с значением поля больше заданного".

Тем более причина должна быть веской, потому что последствия - потеря совместимости с предыдущими версиями - достаточно тяжёлые.
20 авг 18, 10:52    [21647802]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
TaPaK
nvv,
автор
Содержимым запроса, текстом, параметрами управляет приложение. Я тут никак не повлияю.

влияйте, если в базе datetime2 то и приложение должно отдавать DateTime2, всё остальное это ошибка
Приложение то чужое, поменять они его не могут.
Понятно, что приложение совместимо с определённой версией сиквела, и с новой работать не будет, если совместимость версий производитель не поддерживает.
Так что решение перевести приложение на другую версию, конечно, несколько поспешное. Придётся откатывать назад.
20 авг 18, 10:54    [21647807]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
alexeyvg
TaPaK
nvv,
пропущено...

влияйте, если в базе datetime2 то и приложение должно отдавать DateTime2, всё остальное это ошибка
Приложение то чужое, поменять они его не могут.
Понятно, что приложение совместимо с определённой версией сиквела, и с новой работать не будет, если совместимость версий производитель не поддерживает.
Так что решение перевести приложение на другую версию, конечно, несколько поспешное. Придётся откатывать назад.

ну я так подозреваю что вводить тип datetime2 было не решение сопровождателей, а значить это ошибка не приведение в соотвествие клиента и сервера.

автор
Т.е. в 130 они специально отказались от преобразования? Это где-то документировано?

где сказано что нет преобразования?
20 авг 18, 10:58    [21647813]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
alexeyvg, приложение совместимо с 2016.
Но тот функционал, на котором это проявилось - это интеграция с внешними базами, т.е. не часто используется и сделан по принципу "чтобы всем было хорошо". Поэтому тут даже не угадаешь и особо не протестируешь.
Конечно же установил 120. С этим нет проблем.

Единственное, что интересовало - причина.
20 авг 18, 11:04    [21647822]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
FYI
http://www.dbdelta.com/sql-server-2016-and-azure-sql-database-v12-breaking-change/

ну и вывод тот же
автор
These datetime behavior changes have the benefit of improved accuracy and performance of datetime conversion/comparison. Affected applications can use a pre-SQL Server 2016 database compatibility level until they can be remediated.

20 авг 18, 11:22    [21647859]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
Владислав Колосов
Member

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

обычная ошибка новобранцев... Несовпадение типа данных аргумента функции секционирования и сравниваемого значения приводит к просмотру всех секций.

Оно и в нативном 110 так работает.
20 авг 18, 14:38    [21648235]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
Владислав Колосов, ну передать вендору ПО, что они новобранцы, я не смогу. До царя далеко. Но в поддержку обращусь )

Я кстати попробовал с не секционированной таблицей такой тест проделать.
В плане появляется вычисление скалярного значения, но уже работает быстро.

Прикрепил планы. 1-3 на секционированной таблице, 4-6 на не секционированной.

К сообщению приложен файл (Plan6.sqlplan - 123Kb) cкачать
20 авг 18, 18:24    [21648551]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Scan (Top Order) из-за формата даты в условии  [new]
nvv
Member

Откуда:
Сообщений: 54
Оно и в нативном 110 так работает.

Нет. Написал же еще в шапке, что до <130 было все отлично. Прикрепил подтверждение (как и в предыдущем посте: 4-6 без секций)

К сообщению приложен файл (Plan6_.sqlplan - 113Kb) cкачать
20 авг 18, 18:33    [21648562]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить