9:30 Приветственный кофе 10:00 Ключевой доклад 11:45 Аппаратные решения HP для платформы Microsoft BI Аппаратные решения HP для платформы Microsoft BI Microsoft Excel как OLAP клиент. Преодоление ограничений 12:45Обед 13:30 Методика построения хранилищ данных на FastTrack DW и PDW Анализ «что-если» в Excel 2010 и OLAP write-back в планировании продаж 14:45 Реляционное моделирование для больших хранилищ данных Прогнозирование навигации на сайте (Data Mining) 16:30Кофе-брейк 17:00 Анализ вторичных продаж на базе SQL Server 2008 R2 SQL-клиника Некоторые сценарии практического использования DAX в PowerPivot 18:00 Закрытие конференции 18:15Кофе-брейк 18:30 Лабораторные работы

SQLCAT: Использование архитектуры FastTrack при построении хранилищ данных на SQL Server Алексей Халяко Microsoft SQL Server Customer Advisory Team

SQL Server Customer Advisory Team (SQLCAT) Работает с самыми большими и сложными проектами во всем мире. MySpace million параллельных пользователей в пиковое время, 8 миллиардов friendов, 34 миллиарда s, 1 PetaByte хранилище, масштабирование с использованием технологий SSB и SOA Bwin – Самая популярная в Европе игровая платформа – 30k db транзакций в секунду, девиз: Failure is not an option; 100 TB общий размер хранилища Канал для передачи требований клиентов и ISV к функциональности в продуктовую группу. Обмен опытом работы с SQL Server с SQL Server community SQLCAT.com

SQL Server Design Win Program Нацелена на работу с самыми инновационными и сложными проектами 10+ TB DW, 3k/tran/s OLTP, больше 500GB+ кубы, миграции с конкурентных платформ, сложные имплементации, консолидации серверов (1000+) Инвестиции в самые масштабные проекты в мире, на которые в дальнейшем смогут ориентироваться другие клиенты Предоставляем техническую экспертизу группы Provide SQLCAT и опыт работы в проектах Осуществляем пересмотр архитектуры, фокусируясь на производительности, надёжности, масштабируемости и доступности Предоставляем лабораторию в Редмонде и возможность прямого общения с разработчиками.

Темы Предпосылки Сбалансированная архитектура, как подход к построению DW Оптимизация хранилищ, загрузки и поддержка Примеры внедрений Принципы работы PDW

Реляционные хранилища на SQL Server сегодня Тысячи имплементаций > 1 TB Множество имплементаций > 5 TB Разные подходы Связь с SQL Sever BI продуктами Импульс! Сильный поток нововведений Resource Governor, Compression, Star Query, … Следующий революционный шаг - Parallel Data Warehouse (PDW)

Некоторые SQL хранилища сегодня большой SAN Самый большой 64-ядерный сервер! Что не так на этой картинке?

Не сбалансированная система Сервер может потреблять 16GB/Sec IO операций, SAN может «выдать» только 2 GB/Sec Даже если SAN выделен только под SQL Data Warehouse, что обычно не так Множество дисков для Random IOPS НО Ограниченное количество контролеров ограниченная пропускная способность IO Система «привязана» к IO, запросы выполняются медленно И это при существенных инвестициях в сервер и хранилище

DW против OLTP База данных Фокус на аналитических операциях: стратегия Оптимизирована для массивных вставок данных, сложные и тяжело предсказуемые запросы Несколько одновременных пользователей Хранилище Фокус на операциях чтения Оптимизация для scan операций, а не seek Оптимизация хранилища с фокусом на disk scan rate (MB/s) База данных Разработана для операционных задач : тактика Оптимизация для транзакций: вызоводной строки Тысячи одновременных пользователей Хранилище Фокус на скорости выполнения транзакций Оптимизация для seek, а не scan операций Хранилище оптимизированно для I/O опреаций (IOPs)

Что такое FastTrack Data Warehouse? Метод построения эффективной по затратам, сбалансированной системы для загрузки, типично для хранилищ данных Эталонные «железные» конфигурации разработаны с поставщиками оборудования Рекомендации размещения, загрузки и управления данными Используется только для реляционных хранилищ – не для SSAS, IS, RS

Темы Предпосылки Сбалансированная архитектура, как подход к построению DW Примеры референсных архитектур на базе FastTrack Оптимизация хранилищ, загрузки и поддержка Примеры внедрений Выводы

Потенциальные узкие места в системе FC HBA FC HBA A A B B FC HBA FC HBA A A B B FC SWITCH STORAGE CONTROLLER STORAGE CONTROLLER A A B B A A B B CACHE SERVER CACHE SQL SERVER WINDOWS CPU CORES CPU Feed Rate HBA Port Rate Switch Port Rate SP Port Rate A A B B DISK LUN DISK LUN SQL Server Read Ahead Rate SQL Server Read Ahead Rate LUN Read Rate Disk Feed Rate

Сбалансированная архитектура КомпонентСбалансирован для … CPUМаксимальное потребление данных из кэша для определенных наборов запросов (на следующих слайдах) Controller (Service Processor) Пропускной канал для поставки данных ядрам CPU (базируется на наборе запросов) HBAАгрегирует потоки данных, поставляемые контроллером SwitchВыровнен с пропускной способностью HBA и оптимизирован для последовательных IO DisksАгрегирует потоки данных с контроллера /емкость хранилища

Cбалансированная система Построить систему, состоящую из сервера и хранилища, в которых пропускная способность IO может достаточно загрузить SQL Relational DW Избегайте разделения хранилища с другими серверами Избегайте избыточного инвестирования в диски Обращайте внимание на производительность scan операций, а не IOPS Располагайте данные так, чтобы максимально использовать сканирование диапазонов. Минимизировать фрагментацию данных

Характеристики нагрузок хранилищ данных Интенсивные сканирования Hash Joins Агрегации SELECTL_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY, AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER FROMLINEITEM GROUPBYL_RETURNFLAG, L_LINESTATUS ORDERBYL_RETURNFLAG, L_LINESTATUS

Сбалансированная система -CPU Определить объем потребления данных на ядро процессора для набора запросов Пример: Предположим TPCH запрос 2 – типичная загрузка для системы Выполнить запрос на тестовом сервере с данными полностью загруженными в кэш Запрос выполняется параллельно с MAXDOP 4 Убедиться в загрузке 100% CPU на 4 ядрах Засечь время выполнения и определить количество прочитанных #страниц (Set Statistics IO on; Set Statistics Time on) Расчет нагрузки на ядро = (# Logical Reads* 8K)/(CPU Time)

Можно сделать еще красивей В принципе, запросы, которые выполняют достаточно сложные вычисления, форматирование, объединения измерений – потребляют больше CPU Сложные запросы будут «медленней» потреблять мощность ядер, чем простые :Измерить потребление данных на ядро для разных запросов и вычислить «средний вес» Стандартный подход при расчете вычислительной емкости системы

Или давайте это сделаем мы… Мы протестировали набор TPCH запросов, которые соответствуют «типовой» загрузке для Data Warehouse Сделали вывод, что SQL Sever 2008 на нынешней x64 ядерной платформе потребляет ~200 MB/Sec на ядро в среднем для такого типа загрузки Использовали эти выводы как базу для опубликованной «эталонной» архитектуры Однако, Ваша нагрузка может отличаться! Для точного выбора архитектуры и объемов используйте свои измерения

Примеры загрузки CPU Пример 1: Характеристики запроса: Сканирование одного кластерного индекса, hash match, агрегации по 8 столбцам Statistics IO: logical reads , physical reads 0, Readahead reads 0 Statistics Time: CPU time = ms Нагрузка на ядро: ( * 8K) / (144690) = 185 MB/s per core Пример 2: Характеристики запроса: 3 объединения таблиц, одна агрегация, множественные hash joins, агрегация по одному столбцу Statistics IO: logical reads (total all tables) , physical reads 0, Readahead reads 0 Statistics Time: CPU time = ms Нагрузка на ядро: ( * 8K) / (121167) = 137 MB/s per core Quad Core AMD Opteron 2384 (Shanghai)

Fast Track калькулятор Определив типы запросов к системе используйте калькулятор:

Сбалансированная система - хранилище Количество ядер CPU и пропускная способность загрузки помогут определить количество контроллеров и «корзин» для представления суммарной нагрузки # конроллеров определит минимальное количество дисков для предоставления пропускной способности сканирования Определить требуемую ёмкость/диск исходя из ожидаемого объема дискового пространства Оставить достаточно пространства для TempDB или особенно большим таблицам в системе (для административных задач)

Сбалансированная система - IO Используем для начала 2x четырех ядерных сервера как «кубик» Убедиться, что скорость потребления данных на ядро может быть предоставлена всеми компонентами в IO стека Максимальная теоретическая пропускная способность IO стека оптимизированного для 8 ядерной Fast Track архитектуры ( предполагая 200 MB/s на ядро) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core)

Сбалансированная система – хранилище (2) Теоретические максимумы - всегда только теоретические Тесты для получения реальных параметров могут быть необходимы Наблюдаемая пропускная способность на 8 ядерной системе Fast Track при выполнении SQLIO CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core)

Сбалансированная система – масштабирование Server Fiber Switch HBA Storage Enclosure Storage Processor RAID-1 CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) HBA

Темы Предпосылки Сбалансированная архитектура, как подход к построению DW Примеры референсных архитектур на базе FastTrack Оптимизация хранилищ, загрузки и поддержка Примеры внедрений Выводы

Оптимизация схемы хранилища для интенсивных сканирований Конфигурация LUNов базируется на RAID10 Предоставляет оптимальный уровень доступа к дискам «Размазывание» данных по дискам происходит на уровне файлов SQL Server (разбиение на файлы в файловой группе) Наблюдаемая производительность одной RAID пары >= 130 MB/s

Влияние схемы хранилища на SQL Server Создать один файл данных на LUN для каждой файловой группы Файловая группа TempDB размещается на тех же LUN, что и другие базы Лог размещается на отдельных дисках в каждой «полки» Разбиение на stripes используя SQL Лог может размещаться на тех же дисках, что и файлы загрузки и резервирования

Влияние схемы хранилища на SQL Server LUN16 LUN 2LUN 3 Local Drive 1 Log LUN 1 Permanent DB Log LUN 1 TempD B TempDB.mdf (25GB)TempDB_02.ndf (25GB)TempDB_03ndf (25GB)TempDB_16.ndf (25GB) Permanent FG Permanent_1.ndf Permanant_DB Stage Databa se Stage FG Stage_1.ndfStage_2.ndfStage_3.ndf Stage_16.ndf Stage DB Log Permanent_2.ndf Permanent_3.ndf Permanent_16.ndf

Секционирование таблиц

Обзор фрагментации Логическая фрагментация Величина, показывающая степень несоответствия порядка размещения физических страниц логическому ключу(по всем файлам) sys.dm_db_index_physical_stats: Logical_Fragmentation 1:32 B-tree Page 1:31 1:36 1:53 1:35 1:34 1:33 1:54 Логический порядок индекса 1:38 1:37 1:60 1:80 1:42 1:41 1:40 1:39 Фрагменация Листовые страницы

Обзор фрагментации Фрагментация экстентов Величина, показывающая на сколько размещение экстентов является упорядоченным (по всем файлам) sys.dm_db_index_physical_stats: Avg_Fragment_Size_in_Pages, Fragment_Count Idx 1 Idx 2 Экстенты внутри файла данных Idx 2 Idx 1

Как оптимизировать сканирование SQL Server выполняет большое количество асинхронных read-ahead запросов выполняя сканирование Пытается выполнить столько операций I/O, чтобы поддерживать CPUзанятым Размер I/O зависит от «продолжительности» фрагмента в файле данных Размер I/O может быть в диапазоне от 8K до 512K Средний размер read-ahead запроса может быть выяснен с помощью avg_fragment_size_in_pages в составе sys.dm_index_physical_stats Значения >= 64 страниц означает, что размер I/Os близок к 512K

Read-Ahead операции в действии Кластерный индекс: упорядоченный ключ 1.Каждый следующий запрошенный диапазон страниц определяется при поиске в B-дереве следующего диапазона ключей 2.Страницы в диапазоне отсортированы 3.I/O запрос выполняется для каждого непрерывающегося диапазона страниц (до 64 страниц в запросе) Heap: порядок размещения Сканируются GAM страницы, чтобы определить следующий диапазон страниц 1.I/O запрос выполняется для каждого непрерывающегося диапазона страниц (до 64 страниц в запросе)

Read-Ahead операции в действии B 1:32 B 1:32 A 1:31 A 1:31 B 1:38 B 1:38 D 1:37 D 1:37 C 1:36 C 1:36 B 1:35 B 1:35 B 1:34 B 1:34 B 1:33 B 1:33 Физический порядок страниц B-tree Page C 1:40 C 1:40 B 1:39 B 1:39 A 1:46 A 1:46 A 1:45 A 1:45 A 1:44 A 1:44 A 1:43 A 1:43 A 1:42 A 1:42 D 1:41 D 1:41 Определение следующего диапазона страниц для запроса, основываясь на упорядоченном ключе (пример: ключи A-B) Группирование в физическом порядке B 1:32 B 1:32 A 1:31 A 1:31 B 1:38 B 1:38 B 1:35 B 1:35 B 1:34 B 1:34 B 1:33 B 1:33 B 1:39 B 1:39 A 1:46 A 1:46 A 1:45 A 1:45 A 1:44 A 1:44 A 1:43 A 1:43 A 1:42 A 1:42 3. Выполнение I/O запросов для каждого непрерывного куска Disk

Приемы для увеличения эффективности сканирования –E параметр запуска Минимизировать использование некластерных индексов на таблице фактов Использовать техники загрузки данных, позволяющих избегать фрагментацию Загрузка в порядке сортировки кластерного индекса (допустим, по дате)если это возможно Создавать индекс всегда с MAXDOP 1, SORT_IN_TEMPDB Изолировать «активные» таблицы в другие файловые группы Изолировать стейджинговые таблицы в отдельные файловые группы или базы Периодические административные операции

«Обычный» тип загрузки приводит к фрагментации Bulk Insert в кластерный индекс со «средним» размером пакета Каждый пакет отсортирован независимо Пересекающиеся пакеты приводят к «расщеплениям» страниц 1:32 1:31 1:35 1:34 1:33 1:36 1:38 1:37 1:40 1:39 1:32 1:31 1:35 1:34 1:33 Порядок сортировки по ключу

Альтернативные пути загрузки Использование heap Полезно, если запрос сканирует всю секцию или…использование batchsize = 0 Допустимо. Если параллелизм при загрузке не требуется или…загрузка в два приема 1.Загрузка в стейджинговую таблицу (heap) 2.INSERT-SELECT из стейджинговой таблицы в целевую с CI В результате таблица не фрагментирована В шаге 1 можно использовать параллелизм, что критично при загрузке больших объемов данных

Двух шаговая загрузка – варианты Вариант A: высокий параллелизм при загрузке архивных данных Обычно в секционированную таблицу Использование временных таблиц (heap), секционированных по тому же принципу, что и целевая таблица Использование множественных потоков при загрузке во временную таблицу с «умеренным» размером пакетов batchsize (SSIS, Bulk Insert, и т.д.) INSERT-SELECT в раздельные секции целевой таблицы (параллелизм) Использование ALTER TABLE SET ( LOCK_ESCALATION = AUTO) Внимание: если памяти не хватает, то TempDB будет перегружена операциями сортировки sorting

Двух шаговая загрузка – варианты Вариант B: избегаем нагрузку на TempDB во время загрузки данных Использовать стейджинговые таблицы, которые используют индексы, аналогичные целевой таблице Загрузка в стейджиговые таблицы с «умеренным» размером пакетов: batchsize (< 1M rows) Финальный INSERT-SELECT в целевую таблицу будет сортированным! Однако мы платим журналированием вставки в стейджинговую таблицу Внимание: ограниченный параллелизм при «накладке» диапазонов вставки

Другие рекомендации по избеганию фрагментации НЕ использовать Autogrow для файловых групп Заранее назначать размер файловой группе, исходя из ожиданий использования базы Если нужно, то производить операцию «вручную» добавляя сразу большие «куски» «Активные таблицы» - в отдельную файловую группу Таблицы, которые часто перестраиваются, или куда данные добавляются маленькими порциями Если архивные данные загружаются параллельно, можно подумать о разделении файловых групп для разделения секций, к ним привязанных и избежать фрагментации экстентов

Иногда фрагментации не избежать Если «дозаливки» пересекаются с уже существующими диапазонами данных в кластерном индексе – расщеплений страниц не избежать Периодические административные действия могут помочь уменьшит/избежать фрагментации Секционирование по историческому ключу (date key) может помочь уменьшить объем административных задач

Администрирование Использовать ALTER INDEX … REBUILD … … WITH (MAXDOP = 1, SORT_IN_TEMPDB) Один поток -- избегаем создания фрагментации экстентов Можно ограничиться перестроением «актуальной» секцииAvoid ALTER INDEX … REORGANIZE Страницы будут упорядочены на физическом уровне, однако может отразиться серьезной фрагментацией экстентов

Управление «долгосрочной» фрагментацией Иногда проще «начать с начала» : Создать новую файловую группу, чтобы перенести данные. Удалить старую группу Создать пустую копию таблицы в новой файловой группе С совпадающими ключами секционирования и кластеризации INSERT-SELECT из старой таблицы в новую Создать вторичные индексы Удалить оригинальную таблицу и переименовать новую Все шаги могут выполняться онлайн

Стратегии индексирования Если большинство операций (запросов) характеризуются сканированием диапазонов – нужен ли нам кластерный индекс? Ключ секционирования не обязательно должен быть кластерным Меньше проблем при параллельной заливке данных Какая нагрузка возлагается на некластерные индексы? Возможные блокировки при заливке данных Неактуальная статистика может привести к неэффективным CI или RID Lookup Необходим «облегченный! Вариант индексирования

Секционирование для доступности to INSERT / UPDATE MSCFactCDR (View) MSCFactCDR (View) SELECT... FROM MSCFactCDR ALTER VIEW + SWITCH ALTER VIEW + SWITCH Исторические и актуальные данные находятся в разных таблицах, в разных файловых группах

Пример 1: Страховая компания-- массивная загрузка за ограниченное время Задача: Загрузить и дополнить данные объемом в 50 GB за менее, чем 1 час Выполнимо только при высоком параллелизме загрузки Используется секционирование таблицы Секционирование по ключу customer Кластерный индекс по дате! # секций = # ядер Параллельная загрузка во временные таблицы Разделение файловых групп (группа – секция) не допускают пересечения загрузок

Архитектура Primary Storage 8 Drives (4 RAID1 Pairs) Logs 2 Drives (1 RAID1 Pair) Spares 2 Drives MSA2000 DAE

Результат Существующее решениеSQL Server Fast Track DW Сравнение Loading – Subject Area 1 5:10:21 total time51:31 total time SQL Server 6x faster Loading – Subject Area 2 4:36:08 total time1:50.01 total time SQL Server 2.5x faster Время запроса– Subject Area 1 3:03 avg query time (using 9 benchmark queries) 0:15 avg query time (using 9 benchmark queries) SQL Server 12x faster Время запроса – Subject Area 2 56:44 avg query time (using 4 benchmark queries) 8:09 avg query time (using 4 benchmark queries) SQL Server 7x faster Цена за TB (8TB) – Cal : $22K / TB Цена за TB (16TB) – Cal: $13K / TB

Пример 2: Телеком– изначальная загрузка данных Загрузка 400 GB в «новый» кластерный индекс на 8-ядерном сервере в течении 7часов Целевая таблица- 8 секций поделенных по историческим диапазонам 3-шаговая загрузка, использующая секционирование Load, Index, Switch Все шаги используют параллелизм Минимальное журналирование

Европейский Телеком Описание Реляционная часть разработана на : HP DL785 G6 с 8 x 6 ядрами AMD 196GB RAM EMC SAN с 12 x EMC AX4, где каждый 20 x 450 GB дисков. Общая ёмкость примерно 38 TB без сжатия и 76 TB при консервативной оценке сжатия в 50% Windows Server 2008 R2 Enterprise Edition SQL Server 2008 R2 Enterprise Edition

Производительность Оценка производительности была произведена с помощью: SQLIO SQL Server обрабатывал актуальные данные Результаты: SQLIO показал общую пропускную способность системы в 9,6GB/sec, что является теоретическим максимумом. SQL Server производил сканирование таблиц со скоростью в 8,8 до 9.0GB/sec. SQLIO показал комбинированную скорость записи в 4,7 до 5.1 GB/sec SQL Server произвел запись 1 TB за менее, чем 20 минут при использовании параллельных пакетов SSIS. SQL Server показал скорость создания резервной копии в более, чем 3 GB/sec.

Почти FastTrack Многие клиенты следуют рекомендациям FastTrack без точного следования описанной архитектуре: НЕ использовать разделяемое хранилище данных Инвестировать в большее количество «полок» и HBA для обеспечения соответствующей пропускной способности Повысить эффективность операций сканирования используя техники загрузки данных

Fast Track «подобная» система Table Scan Current Disk Queue Length = ~ 670 (достаточное время отклика, учитывая объем и глубину outstanding I/O) Disk Read Bytes / sec = ~ 4 GB/s Read-ahead pages/sec is почти на том же уровне, что и pages/sec. Avg.Disk Bytes/Read = ~ 500 KB Storage – MSA60 –5 x HP SAS P800 controllers with 512MB cache. –Каждый конроллер подключен MSA60 «полке» LUN Configuration –24 Data LUNs, One RAID1 Pair per LUN –1 Log LUN –50 spindles total

А если нагрузка включает много Random IO? Принципы FastTrack позволят получить приемлемую скорость для операций сканирования. Особенно учитывая количество контроллеров и HBAs Однако Возможно придется дополнительно инвестировать в большее количество дисков для обеспечения поддержки высокого уровня random IO в секунду 100+ дисков – не редкость

Parallel Data Warehouse Control Rack Data Rack Control Rack Data Rack/s

PDW Appliance Database Servers Control Nodes Active / Passive Landing Zone Backup Node Storage Nodes Spare Database Server Dual Fiber Channel Management Servers Compute Rack Compute Nodes Dual Infiniband Пример на HP архитектуре Compute Nodes: HP DL380 G6 HP MSA

PDW преимущества Appliance модель Система поставляется с преднастреоной аппаратной частью и установленным SQL Решение разработано и оптимизированно специально для нагрузок типичных для хранилищ данных Пропускные способности CPU и IO сбалансированы для «сканирующих» запросов Проще «стартовать» проект и начать с ним работать: Вся система поставляется как один заказной номер Установив. Можно сразу же создавать базы

PDW – в чем прелесть? Загрузка данных и запросы выполняются параллельно автоматически Все DML (Inserts, Updates) также параллельны по всем узам Масштабируемость и уменьшение скорости запросов добавлением стойки Меньше настроек, меньше сложностей для администраторов Не нужно задумываться о физическом расположении файлов базы данных и таблиц Настройки памяти, параллелизм, много других опций уже настроены оптимально

PDW Benefits – Massive Parallel Processing Control Rack Data Rack Query Запрос – обычный SQL запрос, который передается управляющему узлу ? ? ? ? ? ? ? ? ? ? Запрос копилируетс я в несколько выполняемых шагов Результат передан обратно клиенту

Базовые физические принципы построения баз в PDW Принцип «не делиться ни чем» 60 Time Dim Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Store Dim Store Dim ID Store Name Store Mgr Store Size Store Dim ID Store Name Store Mgr Store Size Product Dim Prod Dim ID Prod Category Prod Sub Cat Prod Desc Prod Dim ID Prod Category Prod Sub Cat Prod Desc Mktg Campaign Dim Mktg Campaign Dim Mktg Camp ID Camp Name Camp Mgr Camp Start Camp End Sales Facts Date Dim ID Store Dim ID Prod Dim ID Mktg Camp Id Qty Sold Dollars Sold

SF -1 Базовые физические принципы построения баз в PDW Distributed Tables 61 Sales Facts Date Dim ID Store Dim ID Prod Dim ID Mktg Camp Id Qty Sold Dollars Sold Большие таблицы фактов разбиваются с помощью Hash функции и распределяются по узлам SF -1 SF -2 SF -3 SF -4 Time Dim Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Store Dim Store Dim ID Store Name Store Mgr Store Size Store Dim ID Store Name Store Mgr Store Size Product Dim Prod Dim ID Prod Category Prod Sub Cat Prod Desc Prod Dim ID Prod Category Prod Sub Cat Prod Desc Mktg Campaign Dim Mktg Campaign Dim Mktg Camp ID Camp Name Camp Mgr Camp Start Camp End

Базовые физические принципы построения баз в PDW Replicated Tables 62 Time Dim Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Store Dim Store Dim ID Store Name Store Mgr Store Size Store Dim ID Store Name Store Mgr Store Size Product Dim Prod Dim ID Prod Category Prod Sub Cat Prod Desc Prod Dim ID Prod Category Prod Sub Cat Prod Desc Mktg Campaign Dim Mktg Campaign Dim Mktg Camp ID Camp Name Camp Mgr Camp Start Camp End TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD маленькие Dimension Tables реплицируется на каждый узел TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD Sales Facts Date Dim ID Store Dim ID Prod Dim ID Mktg Camp Id Qty Sold Dollars Sold SF -1 SF -2 SF -3 SF -4 Результат: Fact -Dimension Joins can be performed locally

Create Database with( AUTOGROW = ON | OFF DISTRIBUTION_SIZE = value_in_GB REPLICATION_SIZE = value_in_GB LOG_SIZE = value_in_GB Упрощенный DDL – создание базы

Упрощенный DDL – Create Table CREATE TABLE [ db_name. ] table_name [ ( { } ] [,...n ] ) [ AS SELECT ] [ WITH ( [,...n ] ) ] ::= column_name [ NULL | NOT NULL ] ::= { [ CLUSTER_ON column_name [,...n ] ] [ DISTRIBUTE_ON (column_name) ] | [ REPLICATE ] } [ PARTITION_ON column_name (RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [,...n ] ] ) ) ] }

Create Table as Select (CTAS) Создает еще одну копию таблицы Создает Distributed из Replicated или наоборот Создает Новую таблицу, Distributed по другой колонке Создает Новую таблицу с другим Clustered Index, или вообще без индекса Создает другую таблицу с другим критерием секционирования Minimal Logging Используется периодически для дефрагментации таблиц Создает новую таблицу с новой колонкой Создает новую таблицу с заменой типов даных.

Загрузка данных в PDW: два варианта

Что происходит внутри PDW Load File Bulk Insert Clustered and/or Partitioned Staging Table Clustered and/or Partitioned Staging Table Insert-Select Clustered and/or Partitioned Final Table Clustered and/or Partitioned Final Table Sort each BATCH in memory or TempDB Sort each BATCH in memory or TempDB Bulk Insert Phase Insert-Select Phase Staging Table Target Table

Control Rack Загрузка таблицы фактов в PDW (Distributed) 68 Control Node Active/Passive Control Node Active/Passive Landing Zone Database Server Nodes Storage Nodes Infiniband Load Source Load Source DMS Server PDWEngine Load Manager Export Manager DMS Manager DMS Manager DMS SQL Server SQL Server DMS Converter Sender Receiver Writer DMS Converter Sender Receiver Writer DMS Reads Load File and buffers records to send to Compute Nodes in a round-robin fashion Load Manager Creates Staging Tables Each row is hashed and sent to Sender Hashed row is sent to appropriate node receiver for loading Received row is pushed onto writer thread Row is bulk inserted into staging table Load Client on External SSIS Server Load Client DWLoader Load SSIS Load

Драйверы для SSIS (and Nexus Query Tool) X86 Servers ClientTools-x86 SSISSQLPDWDest-x86 X64 Servers ClientTools-x86 ClientTools-amd64 SSISSQLPDWDest-x86 SSISSQLPDWDest-amd64

Integration with PDW: Hub and Spoke PDW

Рекомендация Изучите «FastTrack Methodology and Reference Architectures for Data Warehouse» fasttrack.aspx fasttrack.aspx Дополнительные ресурсы: Data Loading Performance Guide SQLCAT Top 10 DW Best Practices

Ответы на вопросы

Спасибо за внимание!

9:30 Приветственный кофе 10:00 Ключевой доклад 11:45 Аппаратные решения HP для платформы Microsoft BI Аппаратные решения HP для платформы Microsoft BI Microsoft Excel как OLAP клиент. Преодоление ограничений 12:45Обед 13:30 Методика построения хранилищ данных на FastTrack DW и PDW Анализ «что-если» в Excel 2010 и OLAP write-back в планировании продаж 14:45 Реляционное моделирование для больших хранилищ данных Прогнозирование навигации на сайте (Data Mining) 16:30Кофе-брейк 17:00 Анализ вторичных продаж на базе SQL Server 2008 R2 SQL-клиника Некоторые сценарии практического использования DAX в PowerPivot 18:00 Закрытие конференции 18:15Кофе-брейк 18:30 Лабораторные работы

TPC-H QUERY 2 /* TPC_H Query 2 - Minimum Cost Supplier */ SELECT TOP 100 S_ACCTBAL, S_NAME, N_NAME, P_PARTKEY, P_MFGR, S_ADDRESS, S_PHONE, S_COMMENT FROM PART, SUPPLIER, PARTSUPP, NATION, REGION WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND P_SIZE = 15 AND P_TYPE LIKE '%BRASS' AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'EUROPE' AND PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST) FROM PARTSUPP, SUPPLIER, NATION, REGION WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'EUROPE') ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY

TPC-H Query 2 plan