msdevcon.ru#msdevcon

COLUMNSTORE ИНДЕКСЫ И ОПТИМИЗАТОР ЗАПРОСОВ В SQL SERVER Дмитрий Пилюгин TNS Russia

Основная идея Column Store C1C2C3C4 C1 C2 C3 C4 Row Store Column Store Page 1 Page 2 Page 3 Page 4 Page 1 Page 2 Page 3 Page 4 Хранение данных сгруппированных по столбцам, а не по строкам

Демо Columnstore Intro

Построение Columnstore Шаг 1. Разделить таблицу на группы строк Row Group 1 ~ 1 Million Row Group 2 ~ 1 Million Segment Шаг 2. Разделить группу строк на столбцы Шаг 3. Переупорядочить, сжать, сохранить в LOB

Column Store дизайн, чтение только нужных столбцов Сжатие Исключение сегментов Режим обработки Batch Основные «ускорители» Columnstore

Чтение только необходимых столбцов Column Store дизайн select distinct c2, c3 from cstable; c1 c2c3 c4c5 c6

Происходит автоматически, нет способов повлиять Семейство алгоритмов xVelosity/VERTIPAQ Run Length Encoding Dictionary Encoding … Каждый сегмент может быть сжать по-своему Разные алгоритмы для разных данных и распределений Степень сжатия зависит от самих данных «средняя температура по больнице» сжатие примерно в раза выше чем традиционное сжатие Сжатие

Идея Dictionary Encoding Петров Иванов Петров Сидоров Иванов LastName : Петров 2: Иванов 3: Сидоров Dictionary

Идея Run Length Encoding Петров Иванов Петров Сидоров Иванов LastName Иван Петр FirstName Петров Иванов Петров Сидоров Иванов LastName Иван: 3 Петр: 4 FirstName Петров: 1 Иванов: 1 Петров: 2 Сидоров: 2 Иванов: 1 LastName Иван: 3 Петр: 4 FirstName Иванов Петров Сидоров LastName Иван Петр Иван Петр FirstName 1 3 Иванов: 2 Петров: 3 Сидоров: 2 LastName Иван: 1 Петр: 1 Иван: 2 Петр: 3 FirstName Определение наилучшего порядка сортировки с целю получения максимального сжатия и обратная реконструкция строки – ключевой момент (VertiPaq)

Возможность исключать из чтения сегменты, которые заведомо не содержат нужные данные Исключение сегментов select c1 from cstable where c1 between 1000 and 2000 *TF 646, событие xEvent column_store_segment_eliminate c1c2 Segment 1 Segment 2 Segment 3 MinIDMaxID Segment Segment Segment

Демо Segment Elimination

Нельзя изменять данные Не может быть кластерным Не может быть уникальным Не может быть фильтрованным Нельзя создавать несколько CS индексов Нельзя создавать на представлении и индексированном представлении Не все типы данных Некоторые ограничения Columnstore *один из способов посмотреть ограничения: select * from sys.messages where language_id = 1033 and text like '%columnstore%

Что такое Метод выполнения запроса основанный на том, что итераторы работают не со строками, а с пакетами. Где пакетом является объект, состоящий приблизительно из 1000 строк, в котором, каждая колонка внутренне представлена в виде вектора Ограничения Только для CS индексов Только для параллельных планов Не для всех итераторов Где посмотреть Estimated Execution Mode Actual Execution Mode Режим Batch

Объект Batch Векторы колонок Qualifying rows vector Batch Приблизительно 1000 строк Колонка – вектор элементов фиксированного размера Вектор qualifying rows указывает, какие строки были логически удалены из пакета

Сокращение числа инструкций при передаче от итератора к итератору Push vs. Pull модель получения строк Операции над пакетами Project Filter Index Scan Project Filter Index Scan select * from t1 where a = 1 PullPush 1000x

Размер Batch максимально задействует L2 cache Dictionary Encoding позволяет сравнивать 64 битные идентификаторы вместо строк Снижение промахов в кэше за счет уменьшения числа инструкций Столбцы которые не могут содержать null, желательно объявлять как not null Оптимизация CPU L2 cache, Registers CPU L1 L3 Память Диск L2 Регистры

Новая модель выполнения и уменьшение числа инструкций за счет: Оптимизированная обработка с учетом размера регистра (64 bit) Векторная обработка, операции над сжатыми данными Уменьшение количества вызовов методов при передаче данных от итератора к итератору Выполнение Push vs. Pull Оптимизирован для работы с CPU L2 cache (задача избежать промахов в кэше - cache misses) Особенности режима Batch

Демо Batch Intro

Обработка запроса Parse/Bind Pre optimize Optimize Parse (разбор в дерево логических операторов) Bind (связывание) Constant Fold (сворачивание констант) Expand View, Computed Cols (развернуть представления) Prepare for Auto Param (подготовка к автопараметризации) Parse (разбор в дерево логических операторов) Bind (связывание) Constant Fold (сворачивание констант) Expand View, Computed Cols (развернуть представления) Prepare for Auto Param (подготовка к автопараметризации) NNF Convert (преобразование NNF) Project Remove (удаление лишних проекций) Simplify (упрощение – выявление противоречий и лишних условий, проталкивание предикатов, раскрытие подзапросов) Join Collapse (сворачивание соединений) Derive Cardinality (вычисление кардинальности) Heuristic Join Reorder (эвристические перестановки соединений) Project Normalization (нормализация проекций) NNF Convert (преобразование NNF) Project Remove (удаление лишних проекций) Simplify (упрощение – выявление противоречий и лишних условий, проталкивание предикатов, раскрытие подзапросов) Join Collapse (сворачивание соединений) Derive Cardinality (вычисление кардинальности) Heuristic Join Reorder (эвристические перестановки соединений) Project Normalization (нормализация проекций) Trivial Plan Full Search 0 (Transaction Processing) Search 1 (Quick Plan) Search 2 (Full) Применение правил преобразования Оценка и выбор стоимости Генерация плана с финальными правками Trivial Plan Full Search 0 (Transaction Processing) Search 1 (Quick Plan) Search 2 (Full) Применение правил преобразования Оценка и выбор стоимости Генерация плана с финальными правками Query Log Op Tree Simplified Log Op Tree Plan

Что такое Алгоритмы преобразования дерева логических операторов, реализованные в виде классов и их методов Применяются для генерирования альтернативных операторов плана или для перевода логических операторов в физические Во многом определяют, какой план будет сгенерирован для запроса Где посмотреть sys.dm_exec_query_transformation_stats Правила преобразования select * from t1 where 1 = 2; + SelectOnEmpty select * from t2 join t1 on t1.a = t2.b; + JoinCommmute

Что такое Алгоритмы преобразования дерева логических операторов, реализованные в виде классов и их методов Применяются для генерирования альтернативных операторов плана или для перевода логических операторов в физические Во многом определяют, какой план будет сгенерирован для запроса Где посмотреть sys.dm_exec_query_transformation_stats Правила преобразования Batch Rules BatchExecutionOnProject BatchExecutionOnSelect BatchGbAggToHS JoinToBatchJoin GbAggToBatchGbAgg ExpandNAryJoinToBatchSnowflake BHTBToBHTB(Batch Hash Table Build) …

Новые операторы плана Batch Hash Table Build Columnstore Index Scan Не является новым оператором Только сканирование, нет поиска Hash таблица в памяти Создает bitmap фильтры *Hints Игнорирование CS индекса ignore_nonclustered_columnstore_index Форсировать CS индекс так же, как и любой другой with(index(...))

Традиционная статистика по индексу CS не создается Может использоваться row store статистика Плохие оценки, плохи всегда Статистика и оценки Row Store Статистика Оценка числа строк выбираемых по предикату Columnstore Index

Batch возможен, если: Есть хотя бы один CSI Возможно параллельное выполнение Хватает памяти Итераторы, которые поддерживают Batch: Inner Hash Join Local Aggregation Project Filter Scan Условия Batch

Демо Columnstore Query Performance

Особенности и возможности: Проталкивание простых предикатов (не только CS) вниз на уровень сканирования, исключение сегментов Local/Global агрегация (не только CS) Использование bitmap фильтров во время скана (не только CS). За построение bitmap отвечает оператор Batch Hash Table Build (типы данных в соединении имеют значения) Операторы Exchange для обеспечения отката к Row Mode Обращать внимание: Spill в hash join, sort Estimated Batch mode может «свалиться» в Actual Row Mode во время выполнения Строковые предикаты и соединение по строкам Compute Scalar после CS Index Scan Помнить: В итоге все запросы переключаются на построчный режим, таков интерфейс верхнего компонента, задача – выполнить как можно больше операций в режиме Batch Если медленно, можно попробовать переписать запрос, для того, чтобы сервер обрабатывал в режиме Batch наибольшее число строк Особенности запросов с CS

COLUMNSTORE ИНДЕКСЫ И ОПТИМИЗАТОР ЗАПРОСОВ В SQL SERVER Дмитрий

© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Columnar Storage in SQL Server 2012 (Per-Ake Larson, Eric N. Hanson, Susan L. Price) TechNet - SQL Server Columnstore Performance Tuning TechNet - SQL Server Columnstore Index FAQ Column-Oriented Database Systems (VLDB 2009 Tutorial) Dejan Sarka Data Warehouse Deck Lubor Kollar - Parallel Query Processing in SQL Server Conor Cunningham - DW Design with the Product Team Conor Cunningham - Inside SQL Server Column Store Index Alexei Khalyako - Rolap with Column Store Deep Dive Susan Price - Turbocharge your DW queries with column store indexes Ресурсы

Если данные имеют нестандартное распределение В Row mode из-за перераспределения данных между потоками по хэш функции, можно загрузить один поток, второй оставив свободным В Batch mode данные выталкиваются снизу первому свободному потоку, искажения нет Искажения Exchange Parallel Page Supplier Thread 1 Thread 2 Repartition Streams Hash Hash Table Thread 1 Thread