«24 Hours of PASS» — это ежегодная онлайн-конференция о MS SQL Server, проводимая по эгидой профессиональной ассоциации PASS, и длящаяся 24 часа. Вот прям буквально 24 часа: докладчики из разных частей света сменяют друг-друга в марафоне вебинаров (конечно же, это отсылка к 24 часам Ле-Мана).
Усилиями Андрея Коршиков, уже несколько лет проводится русскоязычная версия «24 часа PASS». Последняя состоялась как раз недавно — 18-19 марта, и если вам лень смотреть все 24 часа видео (кстати, вот плей-лист), то именно для вас я и сделал этот обзор.
- SQL Server 2014 In-Memory OLTP (Сергей Олонцев)
- Оптимизация SSAS кубов (multidimension and tabular): возможно ли медленный куб сделать быстрым? (Евгений Полоничко)
- Внешние ключи — зло? (Евгений Хабаров)
- Тяп-ляп и в продакшн! (Алексей Ковалёв)
- Размер имеет значение: 10 способов уменьшить размер БД и улучшить производительность системы (Дмитрий Короткевич)
- Внутри Оптимизатора Запросов: Соединения (Дмитрий Пилюгин)
- Оффлайн-разработка баз данных и модульное тестирование с помощью SQL Server Data Tools (Андрей Завадский)
- Deadlocks 3.0. Final Edition (Денис Резник)
- BIML — лучший друг для SSIS-разработчика (Андрей Коршиков)
- Power BI Q&A (Константин Хомяков)
- Azure Data Factory — облачный ETL (Сергей Лунякин)
- Все что вы хотели узнать о Workspace memory (Мария Закурдаева)
- Быстрый анализ производительности SQL Server за 1,5 часа (Кирилл Панов)
- Внутреннее устройство страниц и экстентов SQL Server (Алексей Князев)
SQL Server 2014 In-Memory OLTP
слайды — видео часть 1, часть 2
Сергей Олонцев (Лаборатория Касперского) на данный момент, пожалуй, главный движитель московской SQL User Group, организатор нескольких сиквельных мероприятий в Москве, участник многих конференций, MVP и обладатель раритетного статуса SQL MCM.
Раньше в SQL Server было два основных механизма хранения данных: классическое построчное хранение и поколоночное хранение (ColumnStore). В MSSQL 2014 появилось хранение In-Memory и сопутствующие ему технологии. И это не просто RAM-диск, коренным образом поменялись структуры и алгоритмы хранение. В идеальной ситуации скорость обработки данных может ускорить почти в 100 раз. Но новая технология не смогла реализовать все возможности прежней, ей сопутствует куча ограничений. Главный слад презентации:
Классический движок — семейный универсал, большой багажник, кондиционер, детское кресло можно поставить… много комфорта. In-Memory — гоночный болид, способный выжать максимальную скорость, но число функций и удобств очень ограничено. В нашем случае, это: сумма полей не более 8060 байт, не более 512 Гб на базу, нет вычисляемых колонок, нельзя изменять структуру уже созданных таблиц, нет фильтрованных индексов и др.
Дальше тезисно:
* Неочевидный факт: если таблица объявлена как Memory_Optimized, это ещё не значит, что при выключении электричества данные будут потеряны, ведь они ещё пишутся и в лог-файл. Его можно отключить, и это ещё заметно прибавит скорость.
* Как устроено хранение данных: Bw-tree, однонаправленные списки. В докладе разобрана структура записей, показано что происходит при редактировании, как ведут себя индексы
* Многоверсионная модель — нет больше блокировок и латчей.
* Новые типы индексов «хешовые» и «range»
* Native compile — это сопутствующая технология, позволяющая компилировать запросы к InMem в машинный код. Раньше планы запросов тоже сохранялись в буфере и могли повторно использоваться, это позволяло не запускать заново оптимизатор. Но все планы все равно были интерпретируемыми. Теперь запросы могут быть сохранены в честном машинном коде. Это даёт огромный скачок производительности, но влечёт и чудовищные ограничения. Среди всех, назову только: нет CTE, нельзя использовать LEFT JOIN, не работает оператор CASE.
* Самый простой способ начать использовать InMem — это Memory_Optimized табличные типы. Это аналог временных таблиц и табличных переменных, но в отличие от них, действительно работающие в памяти.
* Другие сценарии, где будет полезно InMem: одновременная вставка из множества потоков, staging-таблицы для ETL, интенсивные операции чтения.
Размер имеет значение
Размер имеет значение: 10 способов уменьшить размер БД и улучшить производительность системы — скрипты и слайды — видео
Дмитрий Короткевич. Тоже MVP и MCM. Автор лучшей, по моему мнению, книги о MS SQL — «Pro SQL Server Internals» (на английском языке)
Доклад основан на одноимённом посте в блоге автора (рекомендую подписаться).
«Я люблю работать с большими базами данных, они очень интересны. Но только когда у меня почасовая оплата.»
Если данные меньше, то с ними быстрее и удобнее работать. Вот несколько спопобов сделать их поменьше.:
О чём рассказано:
* Установите параметр «Instant file Initialization». Он позволяет серверу не делать заполнение нулями при создании и увеличении файлов данных.
* Фрагментация внутренняя и внешняя
* Типы страниц данных: IN_ROW, ROW_OVERFLOW (если есть большая колонка, не помещающаяся на страницу вместе с другими данными строки), LOB (например, для VARCHAR(MAX))
* Компрессия работает только для страниц IN_ROW
* ROW-компрессию практически всегда имеет смысл включать. Если есть колонка INT и в ней хранится значение 0, то при row-компрессии это значение занимает 1 байт, а не 4.
* PAGE-компрессия — это zip-ование страниц памяти. Меняем ресурсы процессора на ресурсы диска (быстрее прочитать, но нужно ещё распаковать).
* LOB компрессия. Вообще-то такой нет. Но можно реализовать свои CLR-функции. Они несложны и реально работают.
* обычно стоит использовать datetime2 вместо datetime
* примеры замен избыточных индексов (их можно находить автоматически):
* IDX1(A, B) & IDX2(A) -> IDX2 можно удалять, он является частью первого индекса
* IDX3(A) INCLUDE(B) & IDX4(A) INCLUDE© -> IDX5(A) INCLUDE(B,C)
* ColumnStore-индекс можно рассматривать как особый вид компрессии. Порядок эффективности сжатия: исходная таблица 10 Гб, ROW-компрессия 7 Гб, PAGE-компрессия 2 Гб, COLUMNSTORE от 0,8 до 0,4 Гб
* освобождение места: CREATE INDEX WITH (DROP_EXISTING=ON) ON [NewFileGroup]
Дмитрий приводит рад полезных скриптов:
* Detecting Space Consumers
* Monitoring Splits
* LOBCompress
* Unused Indexes
* Redundant Indexes
Внутри Оптимизатора Запросов: Соединения
слайды — видео часть 1 и часть 2
Дмитрий Пилюгин (TNS Gallup Media). Ещё один MVP. Знаток недокументированных флагов трассировки и необычных хинтов. Известен своей способностью вгрызаться в тему, разбирая её до мельчайших деталей. Помню, меня очень впечатлила глубина его харьковского доклада о механизме кардинальности (оценки числа строк, возвращаемых после некоторой операции). Блоги: SomewhereSomehow.ru и QueryProcessor.com
С точки зрения сервера, пользовательские запросы — это рулетка:
О чём рассказывается (кратко пересказать невозможно, перечисляю только для того, чтобы дать представление о масштабе материала):
* Помимо общеизвестных INNER JOIN, LEFT OUTER JOIN и FULL JOIN, бывают и другие, например, LEFT ANTI SEMI JOIN. Это соединение таблиц производится оптимизатором в запросе следующего вида:
select * from t1 where not exists(select * from t2 where t1.a = t2.b);
* операции работы с множествами, например, EXCEPT — это тоже скрытое соединение таблиц
* PREDICATE — скалярный оператор. Например: CScaOp_AggFunc, CScaOp_Arithmetic, CScaOp_Assign, CScaOp_Collate…
* PROBE — это оператор при запросах вида SELECT CASE WHEN EXISTS(SELECT ..) THEN 10 ELSE 20 END…
* PASS THROUGH — это оператор при запросах вида SELECT CASE WHEN a=1 THEN (SELECT TOP(1)..) ELSE 0 END…
* Строится дерево логических операторов, это такие объекты, вроде:
* LogOp_Get – получить таблицу
* LogOp_Select – фильтр («выбрать из», where, on, having, …)
* LogOp_LeftSemiJoin, LogOp_RightSemiJoin – полу соединения
* К дереву применяются упрощающие/заменяющие правила оптимизации (simplification/substitution rules)
* Логическая оптимизация «Упрощающие правила»: исключение пустых множеств, исключение избыточности, проталкивание предикатов, раскрытие подзапросов, линеаризация соединений — всех их около 150, в докладе хорошие примеры
* Примеры: отбрасываются неиспользуемые джойны таблиц, LEFT JOIN преобразуется в INNER JOIN, если есть условие по нему в WHERE и др.
* Логические операторы преобразуются в физические, в процессе применения «реализующих» правил
LogOp_GbAgg – группировка → PhyOp_HashGbAgg, CPhyOp_StreamGbAgg
* Физическая оптимизация «Исследующие правила»: коммутативность соединений, группировка до соединения, сопоставление индексированных представлений, Full Outer -> Left Outer + Left Anti Semi Join и другие (всего 130 правил)
* Физическая оптимизация «Реализующие правила»: зависят от логического оператора, стоимости, hints (например, использовать LOOP JOIN или HASH JOIN)
* Работает эвристический алгоритм подбора порядка соединения таблиц, всего вариантов слишком много: для 10 таблиц даже методом Left Deep Tree будет 3 628 800 вариантов
В целом картина выглядит так:
Основные свойства физических соединений:
* Nested Loops Join. Хорош для: универсальный (неблокирующий)
* Nested Loops Apply (вызов функции в цикле). Хорош для: небольшого внешнего набора и индексированного внутреннего набора; быстрое получение небольшой порции данных (TOP, FAST N, EXISTS)
* Merge Join One-To-Many: Хорош для: средних и больших наборов имеющих индекс по ключу соединения и предикат равенства
* Merge Join Many-To-Many: тоже самое, но использует tempdb (поэтому важно, чтобы оптимизатор знал какие столбцы являются уникальными)
* Hash Match. Хорош для: неиндексированные средние и большие наборы; масштабируется при параллельном выполнении
И несколько практических ответов:
* Как быстрее подзапросом или «джойном»? — Не важно, сервер сведёт оба запроса к одному плану (если не используются сложные предикаты)
* Где писать условия в on или where? — Для INNER JOIN это неважно.
* Имеет ли значение порядок написания соединений в запросе? — Нет
* Что лучше, группировка после джойна или джойн сгруппированных значений? — Оптимизатор сам протолкнёт группировку до джойна.
Оптимизация SSAS кубов
Оптимизация SSAS кубов (multidimension and tabular): возможно ли медленный куб сделать быстрым?
слайды и скрипты — видео
Евгений Полоничко — DWH/BI архитектор, лидер SQL Server User Group Donetsk
И ещё
Да, кстати, помимо ежегодной «24 часа PASS», в рамках Global Russian Virtual Chapter проходят и ежемесячные доклады-вебинары по SQL. Следите за анонсами в Facebook и на sql.ru.
Автор: ajvol
