Параллельная загрузка данных с использованием секционирования

1. Параллельная загрузка данных с использованием секционирования ИванКосяков Архитектор (SQL-BI), MTC Moscow Лидер Russian Business Intelligence User Group (PASS Chapter)

2. Благодарности • Microsoft Services– за демонстрационный стенд • Hewlett Packard – за оборудование для стенда • Kevin Cox – за слайды с анимацией • MTC Moscow – за предоставленную инфраструктуру

3. Введение Секционирование Управление секциями Параллельная загрузка (SSIS) Блокировки при секционировании Распараллеливание запросов к секционированным таблицам (движок) FastTrack DW Заключение Содержание

4. Введение

5. Производительность SQL Server 2008 64 + Scalable shared databases Scalable shared databases for Analysis Services Workload prioritization Distributed partitioned views NUMA TPC benchmarks Tuning and optimization tools Data dependent routing Peer-to-peer replication Improved BI performance Multi-instance architecture Query notifications Service Broker Enterprise health monitoring Hot-add hardware 64-bit technologies NUMA Support

6. Производительность реляционной базы

7. Секцианирование

8. Секционирование и параллельная загрузка • Быстрое и эффективное управление и доступ к наборам данных • Упрощение и ускорения настройки файловых групп • Ускорение загрузки и управления данными • Использование всех процессоров для ускорения операций

9. Секционированные таблицы и индексы • Разбиты на объекты хранения по значению колонки (диапазоны значений) • Для запросов считаются одним объектом • Обрабатываются движком хранения как различные объекты (B-Trees) • До1000 секций на объект

10. Преимущества секционированных таблиц • Управляемость • Быстрое удаление и загрузка данных • Управление индексами на уровне секций • Уменьшение фрагментации индексов (для секций по дате) • Резервирование и восстановление исторических данных по частям • Альтернативное хранение для исторических данных • Быстрые запросы к большим таблицам • Эффективные операции «Join» • Меньше дерево индекса и сканирование таблицы при запросе к секции

11. Главные термины секционирования • Объекты: • Функция секционирования • Схема секционирования • Операции: • SPLIT (разделение секций) • MERGE (объединение секций) • SWITCH (переключение секций)

12. Функция секционированияPartitioning function • Привязывает области данных к целочисленным значениям • Определяется заданием граничных точек (boundary points) • N граничных точек определяют N+1 секций CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); Граничная точка 1 2 3 4 Номер секции 1 2 3 4 5

13. Схема секционированияPartitioning scheme • Связывает место хранения – группу файлов(filegroup)с каждой секцией(partition) через функцию секционирования (partitioning function) • Не требуется использовать различных группы файлов для различных секций CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg); Лучшая практика: Распределите все группы файлов в схеме секционирования между всеми дисковыми шпинделями

14. Partition Function Partition Scheme Table or Index 1 many many Секционированные таблицы и индексы • Ключ секционирования (Partitioning Key) – отдельная колонка • Создаются на базе схем секционирования(Partition Schemes)вместо групп файлов (Filegroups) • Секции прозрачны для запросов • Различные таблицы и индексы могут использовать одни и те же функции и схемы секционирования

15. Создание секционированной таблицы и индекса CREATE TABLE Order_History ( Order_ID bigint, Order_Date datetime, Customer_ID bigint … ) ON Annual_Scheme_1(Order_Date) CREATE INDEX Order_Cust_Idx ON Order_History(Order_ID) ON Annual_Scheme_1(Order_Date)

16. Aligned Index One-to-one partition correspondence Секции таблиц Секции индексов Note: You cannot SWITCH if indexes are not aligned. Normally best practise to align (this is also the default when creating an index)

17. Объекты секционирования Секционированный индекс Строится для Секционированная таблица Состоит из Функция секционирования Секция Определяет Строится над Определяет Схема секционирования Группа файлов

18. Параллельная загрузка (SSIS)

19. Проектирование параллельной загрузки • Разделение данных на кускиодинакового размера • Секционированный источник или отдельные файлы • Секционирование приемника – команда SWITCH • Исключение конфликтов • Независимые объекты (локировки) • Минимум общих ресурсов (ввод-вывод, процессоры, память) • Эффективный план запуска • Диаграмма Ганта

20. SSIS – настройки параллелизма • Свойство пакета:MaxConcurrentExecutables • По умолчанию: -1 • Свойство Data Flow Task: EngineThreads • По умолчанию: 5 Источник: ttp://blogs.msdn.com/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx

21. Демонстрация.Параллельная загрузка ИванКосяков Архитектор (SQL-BI), MTC Moscow Лидер Russian Business Intelligence UG (PASS Chapter) Демонстрационный сценарий подготовлен специалистами

22. Управление секциями

23. Управление секциями • SPLIT • MERGE ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) } [ ; ] • SWITCH ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;

24. Switch – Table to Partition Table A: Alter Table BSWITCH TO APARTITION 2 [EMPTY] Partition# 6 1 2 3 4 5 2004 & Earlier 2005 Data 2006 Data 2007 Data 2008 Data 2009 & Later FilegroupDATA_2005 CHECK CONSTRAINT:B.Date_Key >= ‘20050101’ and B.Date_Key < ‘20060101’ Table B:

25. 2008 Staging Table Loading Most Recent Data [EMPTY] [EMPTY] [EMPTY] • Create staging table in same filegroup as target partition (2005) • Split most recent partition,adding boundary pointfor following period • Bulk load and index staging table • Switch data into next-to-last partition Partition # 6 1 2 3 4 5 2004 & Earlier 2005 Data 2006 Data 2007 Data 2008 & Later 2009 & Later 2008 Data

26. [EMPTY] 1 2 3 4 5 2005 & Earlier Unloading Oldest Data [EMPTY] [EMPTY] • Create Unload Table in same filegroup as partition to remove (2005) • Switch data out of second partition • Merge first partition, removing the boundary point for the unload period • Archive or Truncate the unload table Partition # 2004 & Earlier 2006 Data 2007 Data 2008 Data 2009 & Later 2005 Data 2005 Unload Table

27. Dropping and merging in the middle (left range case) – not optimal [EMPTY] [EMPTY] • Create Unload Table in same filegroup as partition to remove (2006) • Switch data out of third partition • Merge second and third partition, removing the boundary point for the unload period • Data will move from 2005 range to origin 2006 range  long run time, high Tlog consumption 2005 & 2006 Data Range 2004 & Earlier 2006 Data 2007 Data 2008 Data 2009 & Later 2005 Data 2006 Unload Table

28. Dropping and merging in the middle – better way [EMPTY] [EMPTY] • Create Unload Tables in same filegroup as partitions to remove 2005 and 2006 partitions • Switch data out of second and third partition • Merge second and third partition, removing the boundary point for the unload period • Move 2005 data back into partitioned table again 2004 & Earlier 2005 & 2006 Data Range 2006 Data 2007 Data 2008 Data 2009 & Later 2005 Data 2005 Unload Table 2006 Unload Table

29. Демонстрация.Управление секциями ИванКосяков Архитектор (SQL-BI), MTC Moscow Лидер Russian Business Intelligence UG (PASS Chapter) Демонстрационный сценарий подготовлен специалистами

30. Блокировки при секционировании

31. Конкурентные запросы

32. Совместимость блокировок

33. Анализ блокировокsys.dm_tran_locks

34. Демонстрация.Блокировка секций ИванКосяков Архитектор (SQL-BI), MTC Moscow Лидер Russian Business Intelligence UG (PASS Chapter) Демонстрационный сценарий подготовлен специалистами

35. Распараллеливание запросов к секционированным таблицам

36. Распараллеливание запросов • Уровень параллелизма для экземпляра SQL Server – ‘max degree of parallelism’ • Минимальная граница для возможности параллелизма – ‘cost threshold for parallelism’ • Можно установить для • Обычных запросов • Команд управления индексами • Уровень параллелизма (во время запуска): • Имеющаяся память • Имеющиеся процессорные ресурсы

37. Parallelism against Partitioned Tables – Default SQL Server 2005 • Scan against single partition (e.g. select * from my_partitioned_table where Year = ‘2005’ Parallelism for scan possible [EMPTY] [EMPTY] 6 1 2 3 4 5 2004 & Earlier 2005 Data 2006 Data 2007 Data 2009 & Later 2008 Data

38. Parallelism against Partitioned Tables – Default SQL Server 2005 [EMPTY] [EMPTY] • Scan against multiple partitions (e.g. select * from my_partitioned_table where Year between ‘2005’ and ‘2006 Single threaded scan on single partitions – Performance regression 6 1 2 3 4 5 2004 & Earlier 2005 Data 2006 Data 2007 Data 2009 & Later 2008 Data

39. Parallelism against Partitioned Tables–SQL Server 2008 [EMPTY] [EMPTY] • Scan against multiple partitions (e.g. select * from my_partitioned_table where Year between ‘2005’ and ‘2006 Multi threaded scan on single partitions again. • Algorithm: Assume n partitions to be scanned by k threads available • threads per partition = n/k 6 1 2 3 4 5 2004 & Earlier 2005 Data 2006 Data 2007 Data 2009 & Later 2008 Data n/k threads n/k threads

40. Parallelism against Partitioned Tables –SQL Server 2008 option using trace flag 2440 [EMPTY] [EMPTY] • Scan against multiple partitions (e.g. select * from my_partitioned_table where Year between ‘2005’ and ‘2006 Multi threaded scan on single partitions again. • Algorithm: Assume n partitions to be scanned by k threads available • threads per partition = k • Jump from partition to partition scanning them with k-threads 6 1 2 3 4 5 2004 & Earlier 2005 Data 2006 Data 2007 Data 2009 & Later 2008 Data k threads

41. FastTrack DW

42. Что такое SQL Server Fast TrackData Warehouse (FTDW)? Предложение помощи клиентам и партнерам ускорить внедрение хранилищ данных (ХД) 12 симметричных мультипроцессорных (SMP)рекомендованных (reference) архитектур (RA)

43. Фокус SQL Server FTDW Аналитические кубы Источники ХД,Витрины данных Промежуточнаяобласть ERP CRM Фокус рекомендованных архитектур HRMS Портал

44. Оптимизированы под транзакции Не сбалансированы Нет гарантии положительного результата масштабирования Возможные проблемы архитектур

45. Интенсивное сканирование Чтение с диска большого количества строк Не волатильные данные После записи данных они редко меняются Мало индексов Минимальное использование вторичных индексов Использование разделов Упрощает управление жизненным циклом данных и способствует уменьшению фрагментации Шаблоны нагрузки для сценариев ХД

46. Сбалансированы между всеми компонентами Подробно описаны Основаны на последовательном вводе/выводе и нагрузке, характерной для ХД Ниже стоимость владения (TCO) Масштабируемость и производительность Зачем нужны рекомендованные архитектуры?

47. Состав FTDW • Метод разработки оптимальных по цене, сбалансированных систем для задач ХД • Рекомендованные конфигурации аппаратного обеспечения, разработанные совместно с партнерами • Лучшие практики организации данных, загрузки и управления Только для реляционныхбаз. Не предназначены для • Analysis Services • Integration Services • Reporting Services

48. Область действия RA Поддерживающие системы Системы хранения аналитических данных Системы презентационного уровня Пакеты загрузки Integration Services Кубы Analysis Services Инструменты веб-анализа Путь данных Reporting Services Данные для отображения Выделенный SAN, Массив хранения Хранилище данных, Промежуточная область,Пакетная загрузка SharePoint Services Microsoft Office SharePoint Область действия рекомендованных архитектур (пунктиром) PerformancePoint Excel Services

49. Пример оценки пропускной способности

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