Microsoft TechDayshttp:// Алексей Халяко Program Manager Microsoft Corporation

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

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

Microsoft TechDayshttp:// Функциональность SQL 2008 R2, нацеленная на работу с хранилищами Star Queries Resource Governor Примеры проектов Telecom 1 – вертикальное масштабирование Непрерывное поступление данных, компрессия данных Telecom 2 – горизонтальное масштабирование Загрузка данных в реальном масштабе времени, Distributed Views Специальные темы Минимальное журналирование в Btrees Read Committed Snapshot Isolation

Microsoft TechDayshttp:// SQL Server 2005 сделал решения 10+TB DW возможными Секционирование Улучшенная поддержка NUMA архитектуры SSIS Большая часть хранилищ полагается на традиционный batch ориентированный подход «Ночной» режим загрузки данных и поддержки системы Запросы «только на чтение» в рабочее время Исключительные требования зависят от креативности разработчиков

Microsoft TechDayshttp:// Постоянный, почти в режиме реального времени поток данных на загрузку Доступность 7 x 24 для запросов Запросы к таблицам во время загрузки данных Больше одновременных и требовательных к ресурсам запросов Недорогие серверы, которые обеспечивают вертикальное масштабирование Решения горизонтального масштабирования не менее привлекательны, когда возможны

Microsoft TechDayshttp:// 256 ядер Сжатие данных Resource Governor Минимальное журналирование вставки Расширения Star Query Улучшения Read Committed Snapshot Isolation SSIS расширения Доработанный параллелизм обработки запросов Улучшения в области Dimension Join Parallelism

Microsoft TechDayshttp:// Microsoft Confidential DATE_SK Partitioning Key Clustered Index Key ITEM_SK Nonclustered Index Key STORE_SK Nonclustered Index Key PROMO_SK Nonclustered Index Key CUST_SK Nonclustered Index Key

Microsoft TechDayshttp:// Секционирование таблиц фактов Секционирование по ключу Date Кластерный индекс по ключу Date Выровненный по ограничениям ключей измерений не кластерный индекс Оптимизации Star Query Сканирование фактов с использованием фильтров Bitmap Снижает стоимость запроса по CPU и время исполнения В первую очередь объединение измерений, если это возможно Ограничивает сканирование по диапазону на таблице фактов «Уничтожение» секций

Microsoft TechDayshttp:// Reading query plans for SQL2008 Star Queries See a SQL2008 Star Query plan using Bitmap Filters Observe partition elimination Determine which partitions were touched See a Cartesian join of dimensions in action

Microsoft TechDayshttp:// Один запрос к хранилищу данных может полцчить до 25% памяти 3 таких запроса будут блокировать остальные «большие» запросы Ожидающие ресурсов запросы будут видны как «Memory Grants Pending» в Perfmon или как Resource Semaphore wait type в dm_exec_requests Resource Governor может помочь снизить «аппетиты» запросов Увеличивается число одновременно исполняемых запросов Наблюдайте в TempDB hash spills – стоит I/O Предупреждения Sort и Hash Warnings в Profiler или планах запросов Полезен в системах с большим количеством процессоров и объемом памяти

Microsoft TechDayshttp:// Некоторые типы загрузок выполняются эффективней с ограничением параллелизма Большое количество одновременно исполняемых больших запросов Пример: процессинг куба SSAS может сгенерировать много одновременно выполняемых запросов Назначить 1 OLAP секцию на ядро Если эти запросы «распараллелить», то потоки могу «забуксовать» Решение – запускать запросы с MAXDOP 1 13

Microsoft TechDayshttp:// ALTER WORKLOAD GROUP [default] WITH (group_max_requests=0, importance=Medium, request_max_cpu_time_sec=0, request_max_memory_grant_percent=10, request_memory_grant_timeout_sec=0, max_dop=8) ALTER WORKLOAD GROUP [CubeProcess] WITH (group_max_requests=0, importance=Medium, request_max_cpu_time_sec=0, request_max_memory_grant_percent=10, request_memory_grant_timeout_sec=0, max_dop=1) Ограничивает запросы 10% доступной памяти Первый шаг: определяем CubeProcess группу, которая основана на Application Name (определяется в UDM) Второй шаг: Ограничиваем запросы использованием одного потока

Microsoft TechDayshttp:// Функциональность SQL 2008 R2, нацеленная на работу с хранилищами Star Queries Resource Governor Примеры проектов Telecom 1 – вертикальное масштабирование Непрерывное поступление данных, компрессия данных Telecom 2 – горизонтальное масштабирование Загрузка данных в реальном масштабе времени, Distributed Views Специальные темы Минимальное журналирование в Btrees Read Committed Snapshot Isolation

Microsoft TechDayshttp:// Сценарий: Провайдер мобильных услуг – 15+ TB 250 миллионов Call Detail Records (CDRs) загружаются ежедневно от 25M пользователей ~60GB/день для загрузки 1 из 20 схем «Звезда» в решении 150 дней история, растет до 2 лет Загрузка CDR трансформируется в SSIS и производит поиск данных для измерений 16-ядерный сервер на базе Itanium (128 GB RAM) и большой HDS SAN как платформа

Microsoft TechDayshttp:// Индикаторы производительности Использование, биллинг и доход базированные на типе телефона, плане, типе трафика (voice, data, sms, 3g, etc.) Задолженности других или другим операторам от междусетевого трафика Активность сети по региону/переключателю (switch) Активация предоплаченных устройств Сервис и контроль нарушений Исследование активностей по определенному номеру

Microsoft TechDayshttp://

Требования: CDR должны быть загружены в реальном масштабе времени Секция куба по текущему дню должна быть обновлена 4 раза в день для анализа Реляционные данные ищутся по исходящему номеру, набранному номеру или пакету загрузки. Требуются 3 некластерных индекса Отчеты дням до сегодняшнего должны выполняться быстро. Для нынешнего дня медленный отчет допустим Как минимум 3 дня исторических данных должны загружаться в течении 1 дня при активной системе

Microsoft TechDayshttp:// В начале каждого дня таблица «куча» создается для загрузки CDR этого дня Иначе параллельная загрузка будет невыполнимой Все более «старые» данные находятся в исторической таблице фактов, секционированной по дням Секционированная таблица содержит 3 некластерных индекса, выровненных по секциям : Dialed Number, Originating Number, и Batch Для заметки: никаких кластерных индексов в данном решении В конце дня строятся индексы на таблице, таблица переключается в секционированную UPDATE STATISTICS выполняется вручную, по ключу даты секционированной таблицы

Microsoft TechDayshttp:// CDR Today Partitioned Table (indexed) Heap no index CDR_All (Union View) Flat Files Pipeline Raw Files Old Data (5%) CDR Partitioned Table (indexed) Raw Files (prior days late arriving data) CDR Slice Switched Out Slice (non-indexed) Key lookup Refresh dimensions Bulk loads facts Typical Flow If high- volume backlog 21 Current Data (95%)

Microsoft TechDayshttp:// 4 одинаковых, параллельно исполняемых SSIS пакета Запускаются, когда приходят данные Загрузка CDR файлов в «кучу», поиск записей для поддержки измерений Настроено для оптимизации использования CPU

Microsoft TechDayshttp:// Каждый SSIS загрузчик использует SQL Server Destination для пополнения дневной таблицы-«куча» БЕЗ TABLOCK Получается, что процесс полностью логгируется, однако мы получаем возможность заливать данные параллельно несколькими процессами Дополнительный кэш для новых данных таблиц измерений Отключить Lock Escalation В 2008: Alter Table Set (Lock_Escalation = DISABLE) В 2005: Статья /05/17/Lock-escalation.aspx -- Trace Flag 1211http://blogs.msdn.com/sqlserverstorageengine/archive/ Или же используйте значения commit size < 5000 Любые данные из предыдущих дней обрабатываются отдельным логическим потоком Bulk Load напрямую в индексированную секцию

Microsoft TechDayshttp:// Создано представление UNION VIEW, основанное на комбинации секционированной таблицы фактов ( индексированной) и «дневной» таблицы-куча Обе таблицы используют Check Constraint по диапазону даты, который они покрывают Запросы, заполняющие куб, используют UNION VIEW Индекс по нынешнему дню не нужен, так как для заполнения куба требуется сканирование всей таблицы Phone Number – специфические запросы используют секционированную таблицу для «архивных» данных ОЧЕНЬ быстро при использовании индекса В редких случаях, когда требуется опросить нынешний день, staging таблица может быть просканирована за пару минут Запросы накладывают ограничения по дате на таблице фактов напрямую Не на измерениях, так как Clustered Index таблицы фактов там не представлен

Microsoft TechDayshttp:// Тестировалась миграция данного решения на SQL2008 Использовались идентичные сценарии создания баз данных и SSIS пакеты В новой среде заработало абсолютно всё! При загрузке в 4 потока на 4х-процессорном AMD quad core + SQL2008, скорость загрузки 60% быстрее, чем на 16-ядерном Itanium + SQL2005 Никаких серьёзных ожиданий по IO ни на одной из платформ SSIS использует около половины CPU во время процесса загрузки

Microsoft TechDayshttp:// Данные CDR сжимаются на 55% используя сжатие уровня страниц Только 10% uиспользуя сжатие строк Тестовые загрузки с использованием сжатия не показали изменения пропускной способности, так как процессор не был перегружен Загрузка в «кучу» со сжатием требует: Отсутствие NonClustered индексов на таблице Загрузка BULK обязана включать TABLOCK опцию Клиент принял решение сжимать данные в конце дня TABLOCK не может использоваться, так как требуется параллельная загрузка Всего лишь 5 минут дополнительно Online операция

Microsoft TechDayshttp:// Статистика Обновленная статистка на секционированной таблице – ключ к «правильным» планам запроса. На автоматическое обновление полагаться нельзя Статистика на дневной секции не обновляется автоматически после подключения Пример обновления статистики после переключения выполнялся на заднем фоне ~ 30 минут по одному столбцу на 3 ТБ данных Сжатие TABLOCK необходим для bulk загрузки со сжатием данных в таблицу-«куча» Загрузка данных Параллельная загрузка данных не будет работать non-clustered индексами indexes Попытки использования индексов показали deadlockи между процессами загрузки Исторические срезы с различными индексами Если недавние данные должны быть проиндексированы иначе, чем архивная таблица - UNION VIEW работает прекрасно и в этом случае.

Microsoft TechDayshttp:// Полный день CDR-записей может быть очищен и полностью загружен менее, чем за 3 часа Данные нынешнего дня загружаются в куб менее, чем за < 1 час Точечные запросы по одному телефонному номеру за последние 3 месяца выдаются в течении нескольких секунд SQL 2008 – никаких размышлений не требуется Абсолютно беспроблемная миграция На 55% сэкономлено дискового пространства

Microsoft TechDayshttp:// Сценарий: Телекоммуникационная компания – 25 ТБ данных 700 миллионов Call Detail Records (CDRs) загружаются ежедневно Соответствует ~350GB/day Хранится 60 дней исторических данных Постоянная массивная загрузка, никаких схем измерений. Загрузка распределена по 5 серверам, каждый выделен под определенный регион Каждый сервер 4-core x64 Каждый сервер включен в active/passive кластерную пару EMC DMX Storage

Microsoft TechDayshttp:// Мониторинг активности сети Качество сети, доступность и нагрузка Сервис Запросы по счетам Исследования злоупотреблений Трафик к/от определенного номера Клиентские приложения Запросы компаний по телефонным звонкам Пропущенные звонки

Microsoft TechDayshttp:// Требования: Загрузка данных в реальном времени Данные должны быть доступны в системе через несколько секунд после прихода с коммутаторов Агрегирующий процесс заполняет суммарную таблицу каждые 5 минут на выделенном сервере Требуется кластерный индекс для сканирований по диапазонам времени CDR таблицы Реляционные запросы, базирующиеся на дынных коммутатора, набранного/звонящего номера за определенное время или детали счета (billing line-item) Требуется два non-clustered индекса Некоторые запросы не ограничены регионом – т.е. не ограничены одним сервером Пользовательские запросы выполняются только в рабочие часы, дынные же загружаются 7x24

Microsoft TechDayshttp:// CDR таблица секционирована, новая секция для каждых 3 часов данных Кластерный индекс и ключ секционирования по CDR DateTime Два выровненных некластерных индекса По детали счета (пример: Call ID) На основных, участвующих в запросе столбцах (Dialed Number, Originating Number, Switch,и т.д.) Достаточное количество пустых секций создаются в полночь для заполнения данными следующего дня Избегаем блокировок при SPLIT или SWITCH операциях

Microsoft TechDayshttp:// Плоские файлы с данными из регионов создаются каждые две секунды Пред-сортированы по дате и времени Сервис вызывает процесс массовой загрузки CDR файлов в каждый сервер, выделенный под регион Один BULK INSERT на сервер в один момент времени Так как присутствуют индексы, параллельная загрузка приведет к блокировкам или даже deadlock Эскалация блокировок избегается использованием небольшого BATCHSIZE (

Microsoft TechDayshttp:// Данные доступны для запросов из индексированных таблиц через 5 секунд после доставки с маршрутизатора Каждые 5 минут агрегирующий запрос анализирует данные Использует быстрое сканирование 5 минутного диапазона кластерного индекса Добавляет результаты в агрегирующую таблицу Все пользовательские запросы привязаны ко времени Все CDR привязаны к специфическому Call ID в диапазоне 24 часов NCI Seek + CI Lookup (~80 msec) Все звонки, исходящие с номера … за последние 3 часа NCI Scan + CI Lookup (~30 msec)

Microsoft TechDayshttp:// Запросы используют Distributed Partition View определенное на выделенном сервере ITOC 25 TB Storage Lab Global Query Union View } } App DB Region 1Region 2Region 3Region 4Region 5

Microsoft TechDayshttp:// Представление использует явно определенную константу для указание на Node ID каждого сервера Create View CDR_ALL as Select 1 as NodeID, * from Server_1.cdrDB.dbo.cdr UNION ALL Select 2 as NodeID, * from Server_2.cdrDB.dbo.cdr UNION ALL … Select 5 as NodeID, * from Server_5.cdrDB.dbo.cdr

Microsoft TechDayshttp:// Запросы Select * from CDR_ALL where … and NodeID = 2 Будут обращаться только к Node 2 Клиентское приложение «переводит» ограничения по географии/региону в ограничение по NodeID Запросы без NodeID выполняются на всех серверах параллельно и все условия, фильтры, агрегации выполняются на каждом сервере локально Логически это запросы к одной таблице Нужны дополнительные ухищрения для распределенных joins Следите за обновлениями на SQLCAT блоге по этой теме

Microsoft TechDayshttp://

39

Microsoft TechDayshttp:// Управление секциями Создание новых секций (ALTER TABLE SPLIT) приводило к блокировкам (deadlock) Исправлено в SQL2005 CU9 или последнем Service Pack; KB Переключение секций SWITCH блокируется выполняющимися тяжелыми запросами Опция ORDERED не предотвращает SORT при загрузке в секционированную таблицу Определяйте DPV и направляйте запросы к выделенному серверу Позволяет опрашивать все узлы параллельно Физическая схема Убедитесь, что лидирующие ключи NonClustered индексов селективны Не используйте секционирующий столбец (datetime) как либирующий ключ Онлайновые индексы Для перестроения индексов на одной секции в онлайновом режиме, отключите секцию во временную таблицу которая будет определена в представлении для выполнения запросов), и только тогда выполняйте перестроение ALTER INDEX REBUILD online

Microsoft TechDayshttp:// Поступающие в реальном времени данные – индексируются для быстрого поиска и доступны через 5 секунд после доставки с маршрутизаторов Параллельная загрузка – ключ к успеху В данном случае – один поток/сервер, однако параллельная загрузка в несколько серверов Масштабируема архитектура на доступных серверах При росте объема данных можно добавить больше серверов, каждый для определенного географического сегмента Эффективное решение для реляционных агрегаций, плюс быстрые результаты по детализированным drill- down запросам Клиентские Web-отчеты

Microsoft TechDayshttp:// Функциональность SQL 2008 R2, нацеленная на работу с хранилищами Star Queries Resource Governor Примеры проектов Telecom 1 – вертикальное масштабирование Непрерывное поступление данных, компрессия данных Telecom 2 – горизонтальное масштабирование Загрузка данных в реальном масштабе времени, Distributed Views Специальные темы Минимальное журналирование в Btrees Read Committed Snapshot Isolation

Microsoft TechDayshttp:// Объявление Trace Flag 610 База данных должна быть SIMPLE или BULK LOGGED модели восстановления Разрешает минимально журналирование для Btrees: При операциях BCP, Bulk Insert, SSIS, и INSERT … SELECT Строки, которые направляются в новые страницы не журналируются Только резервирование новых страниц журналируется Строки, которые вмещаются в существующие страницы журналируются полностью Применяемо для сценариев: Массивное наполнение данными (Batch data processing) Вставка в «хвост» существующей таблицы Создание новой напиленной таблицы в один шаг Прочитайте Data Loading Performance Guide на SQLCAT.COM 43

Microsoft TechDayshttp:// Представьте: данные загружаются постоянно в таблицу, в то же время выполняются тяжелые отчеты Обычно такие запросы будут блокировать процесс загрузки, или же загрузка - запросы Read Committed Snapshot Isolation избегает блокировок когда «чтение» и «запись» конкурируют Изначально задумано для избежание блокировок между конкурентными процессами «чтения» и «записи»/»обновлений в OLTP системах Работает также в Data Warehouses, когда необходимо загружать данные параллельно с «чтениями» Детали в статье

Microsoft TechDayshttp:// ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON; Распространяется на всю базу данных Не применяемо к только одной таблице или объекту Требует одного подсоединения к таблице в момент применения Ка проверить, что RCSI включен SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = Не путать с Snapshot Isolation State – другое использование версионности

Microsoft TechDayshttp:// Отчеты не требуют больше Shared Locks Нет необходимости беспокоиться о эскалации блокировок Использует TempDB для UPDATE и DELETE для сохранения старых версий Нет влияния на TempDB при операциях INSERТ RCSI позволит одновременные чтения при почти всех процессах массовой загрузки данных Исключение: загрузка в Heaps или пустые Btrees при использовании TABLOCK – конфликт с RCSI и Nolock RCSI добавляет 17 несжимаемых байтов при вставке каждой строки в таблицу. Может обойтись дорого на больших сжатых таблицах фактов

Microsoft TechDayshttp:// SQL Server 2005 и 2008 соответствуют требованиям к хранилищам данных Раздвигают рамки загрузки данных в реальном времени при постоянной доступности базы Масштабируется вертикально и горизонтально Размеры в 10 – 30 TB – не редкость; 100TB с некоторыми инвестициями в архитектуру Функциональность SQL 2008 расширяет ключевые возможности хранилищ данных Компрессия, Resource Governor, Минимальное Журналирование

Microsoft TechDayshttp://