29.05.2015 08:23
ajvol 2 5455 Источник
«24 Hours of PASS» — это ежегодная онлайн-конференция о MS SQL Server, проводимая по эгидой профессиональной ассоциации PASS, и длящаяся 24 часа. Вот прям буквально 24 часа: докладчики из разных частей света сменяют друг-друга в марафоне вебинаров (конечно же, это отсылка к 24 часам Ле-Мана).

Усилиями Андрея Коршиков, уже несколько лет проводится русскоязычная версия «24 часа PASS». Последняя состоялась в середине марта, и если вы ещё не успели посмотреть все 24 часа видео (кстати, вот плей-лист на YouTube), то именно для вас я и сделал этот обзор.


  • SQL Server 2014 In-Memory OLTP — Сергей Олонцев
  • Размер имеет значение: 10 способов уменьшить размер БД — Дмитрий Короткевич
  • Внутри оптимизатора запросов: Соединения — Дмитрий Пилюгин
  • Оптимизация SSAS-кубов — Евгений Полоничко
  • Тяп-ляп и в продакшн! — Алексей Ковалёв
  • Оффлайн-разработка баз данных и тестирование с SSDT — Андрей Завадский
  • 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. Блог

Зачем смотреть. Про новый крутой In-Memory движок, вы наверное уже слышали. В докладе же много говорится о встречи радужных ожиданий с реальностью.



Классический движок — семейный универсал, большой багажник, кондиционер, детское кресло можно поставить… много комфорта. 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. Это соединение таблиц производится оптимизатором в запросе следующего вида:
  • операции работы с множествами, например, 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 и др.
  • Логические операторы преобразуются в физические, в процессе применения «реализующих» правил
  • Физическая оптимизация «Исследующие правила»: коммутативность соединений, группировка до соединения, сопоставление индексированных представлений, 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

Зачем смотреть. Вы уже работаете с OLAP и хотите посмотреть как с этим зверем управляются другие разработчики.



Заметки по докладу
Основные механизмы мониторинга:
  • старый добрый SQL Profiler (группа событий QueryProccesing)
  • Extended Events (приходящая на замену Profiler технология)
  • DMV — системные представления, к которым можно делать запросы


На практике полезны:
  • $SYSTEM.DISCOVER_OBJECT_ACTIVITY — статистика использования объектов куба
  • $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
  • $SYSTEM.DISCOVER_SESSIONS — можно найти самого прожорливого клиента
  • $SYSTEM.DISCOVER_LOCK


В кубе можно оптимизировать:
  • Партиции — делим данные по временным периодам
  • Агрегаты
  • Настройка параметров куба
  • Настройка измерений: отношения и иерархии
  • настройка MDX-запросов (перенос расчётов в ETL, отдельный вычисляемый элемент)


Советы:
  • использование NonEmpty(), в том числе для оптимизации NON EMPTY
  • AttributeHierarchyEnabled = False
  • замена LastNonEmpty на LastChild
  • скрипт для прогрева кеша после процессинга
  • используйте DAX Studio


Оффлайн-разработка баз данных и модульное тестирование с SSDT


слайды — видео видео

Андрей Завадский — SQL, ASP.NET и Sharepoint разработчик из Краснодара

Зачем смотреть. Вы сроднились с Management Studio, но хотите взглянуть как люди используют для SQL-разработки большую Visual Studio.



Заметки по докладу
  • разделение понятий «сохранить изменения в проекте» и «применить их на сервере»
  • скрипт для вставки данных
  • инструменты сравнения схемы и данных в проекте и на сервере
  • фокус на том, как код должен выглядеть, а не на скриптах преобразований
  • готовим DACPAC-файл и отдаём его админу (data-tier application)
  • интересный интерфейс создания таблиц — посмотрите 36 минуту видео
  • Visual Studio удобна, когда приходится держать рядом код SQL и C#
  • есть статический анализатор кода, который будет предупреждать, например, что «SELECT *» — неудачная конструкция
  • развёртывание на подключенной базе или развёртывание на отсоединённой базе
  • post deployment script
  • модульные тесты, например, могут проверить структуру возвращаемого датасета, число его строк
  • негативные тесты, проверяющие, что должна появиться именно такая ошибка


BIML — лучший друг для SSIS разработчика


слайды — видео видео

Андрей Коршиков. BI-разработчик, активист PASS, которую он представляет в Восточной Европе, организатор Global Russian Virtual Chapter, обладатель редкой награды PASSion Award.

Зачем смотреть. Вы разрабатываете SSIS-пакеты, хотите вывести разработку на новый уровень, не боитесь нестандартных технологий и не брезгуете генераторами кода.



Заметки по докладу
  • стандартный SSIS-пакет генерируется из BIML-файла
  • работа с BIML происходит через правку XML-файла, но это очень человечный XML, совсем не похож DTSX
  • есть подсказки и автодополнение при редактировании
  • вставки C# кода (как когда-то встраивали PHP в HTML)
  • например, можно сделать цикл по таблицам и колонкам, не описывая каждую отдельно
  • удобно генерировать пакеты для однотипных задач
  • повторное использование кода
  • кто рискнёт использовать это в продакшене?


Azure Data Factory — облачный ETL


слайды — видео видео

Сергей Лунякин — лидер PASS Local Chapter в г. Львов

Зачем смотреть. Посмотреть интерфейсы Azure, познакомиться с новыми терминами (сам продукт ещё сыроват).



Заметки по докладу
  • многие настройки через JSON
  • есть неплохие туториалы и лабы от Microsoft
  • удобен для совместного использования с Azure Machine Learning
  • да и вообще, полезен, когда всё в Azure
  • можно установить коннектор для вашей локальной базы
  • последние полгода очень активно развивается
  • аналог Amazon Data Pipeline


Все что вы хотели узнать о Workspace memory


слайды — видео видео

Мария Закурдаева — основатель PASS Virtual Chapter «Global Hebrew»

Зачем смотреть. Вам хочется узнать как SQL-сервер использует оперативную память, как работают очереди к ресурсам, чем страшно слово «spill». Да, и ещё презентация очень красиво оформлена.



Заметки по докладу
  • итераторы, требующие памяти: Sort, Hash Match, Exchange
  • для сортировки требуется в 2 раза больше памяти, чем размер сортируемых данных
  • пример, как два очень похожих запроса занимают 5 Мб и 108 Мб
  • при выполнении запроса память не может быть дозапрошена
  • 2 проблемы: недооценка необходимой памяти и расточительное резервирование памяти
  • рекомендация использовать Resource Governor
  • размер varchar оценивается в половину его длины
  • мусорные одноразовые планы могут выесть большой кусок Buffer Pool


Deadlocks 3.0. Final Edition


слайды — видео видео

Денис Резник — MVP и, наверное, главный украинский организатор SQL-сообщества.

Зачем смотреть. После нескольких банальностей об уровнях блокировок, посмотреть разбор действительно сложных и даже невероятных случаев.



Заметки по докладу
  • если изменения затрагивают более 5000 строк, то эскалация блокировки до уровня всей таблицы
  • но можно отключить табличные блокировки
  • данные из version store не логируются
  • хороший пример deadlock между UPDATE и SELECT
  • в приложениях не забывать об обработке дедлоков
  • нагрузочное тестирование помогает выявить проблемы


Power BI Q&A


слайды — видео видео

Константин Хомяков — MVP, BI-разработчик из Австралии

Зачем смотреть. Запросы к данным на естественном языке — технология новая, ещё не очень популярная, но, говорят, на некоторых заказчиков производит впечатление.



Заметки по докладу
  • нужен Office 365 + Power BI
  • «сustomers by countries as chart»
  • после вывода результата, запрос можно уточнять в обычном интерфейсе
  • важны связи между таблицами, хорошие наименования (CustomerName vs strCustNm)
  • пока только на английском, но, вроде, делают и китайскую версию
  • говорит, что клиенты в восторге, но что-то не верится
  • задание синонимов
  • подключение к локальным серверам из облака
  • коннекторы к Salesforce, Google Analytics


Быстрый анализ производительности SQL Server за 1,5 часа


слайды — видео видео

Кирилл Панов

Зачем смотреть. Убедиться, что вы действительно в курсе всех перечисленных тем. Несколько сумбурный доклад обо всём понемногу.



Заметки по докладу


Внутреннее устройство страниц и экстентов SQL Server


слайды — видео видео

Алексей Князев — DWH-специалист, лидер SQL User Group в Екатеринбурге

Зачем смотреть. Если вам нравится разбираться в сути вещей. Очень подробное погружение в структуры хранения, битовые маски, таблицы смещения. Практическая ценность очень ограничена, так как нет возможности как-либо повлиять на описанные механизмы, хотя докладчик и приводит примеры из жизни.



Заметки по докладу
  • контрольная сумма рассчитывается только в момент записи на диск
  • хранение datetime как двух INT
  • Bulk Change Map — отслеживает изменения с неполным протоколированием для бекапа
  • Internals Viewer for SQL Server — плагин для визуализации распределения страниц в файле
  • Пример: что происходит при изменении колонки NULL на not NULL?
  • Пример: что будет, если поля обычного индекса частично повторяют поля кластерного индекса


Тяп-ляп и в продакшн!


слайды — видео видео

Алексей Ковалёв — харьковчанин, автор SQL Code Guard (must have плагин к SSMS).

Зачем смотреть. Если вы не ведёте контроля версий вашей БД и не знаете как подойти к этой задаче.



Заметки по докладу
  • делайте всё на скриптах, не используйте магию Reg gate
  • хранение и загрузка справочников в виде XML для гибкости
  • подстановка переменных, например, «create database [$(dbname)];» и потом запуск как «Sqlcmd -i final.sql -v dbname=MyDB»
  • старайтесь писать скрипты так, чтобы их можно было безболезненно запускать на разных исторических версиях вашей БД
  • бренчевание по разработчикам или по фичам


И ещё...


Не забывайте про англоязычный 24 hours PASS и Global Russian Virtual Chapter. Следите за анонсами в Facebook.

Комментарии (2)


  1. ALTF13
    01.06.2015 10:38
    #8442017

    Доклад Сергея Олонцева просто обязателен к просмотру.


  1. pihel
    04.06.2015 09:21
    #8447549

    Такую бы конференцию, да по Oracle.