Авторы:: Стюарт Озер (Stuart Ozer) при участии Према Мехры (Prem Mehra) и Кевина Кокса (Kevin Cox)

Построение крупномасштабного реляционного хранилища данных представляет собой сложную задачу. В этойтой статье описан ряд методов проектирования, которые могут помочь в создании эффективного реляционного хранилища данных большого объема с помощью SQL Server. В большинстве крупномасштабных хранилищ данных применяется секционирование таблиц и индексов, и поэтому многие из приводимых рекомендаций относятся к секционированию. Большинство этих советов основаны на опыте построения крупных хранилищ данных на базе SQL Server 2005.

Рекомендуется секционировать большие таблицы фактов

• Рекомендуется секционировать таблицы фактов размером 50–100 ГБ и более.
• Секционирование может упростить управление данными и часто приводит к повышению производительности.
• Быстрое, более детальное  сопровождение индексовБолее гибкие варианты резервного копирования и восстановления.
• Ускоренная загрузка и удаление данных.
• Ускорение работы запросов, ограниченных одной секцией.
• Обычно таблицу фактов секционируют по ключу даты.
• Позволяет применять метод скользящего окна.
• Позволяет применять методику «устранения» секций.

Постройте кластеризованный индекс на ключе даты в таблице фактов

• Это поддерживает использование эффективных запросов для заполнения кубов или получения среза исторических данных.
• Если данные загружаются  пакетами в отведенное для заливки данных время, то для кластеризованного индекса на таблице фактов нужно использовать параметры ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCKS = OFF. Это поможет ускорить операции просмотра таблиц во время запроса и избежать избыточной активности блокировок во время масштабных операций обновления.
• Постройте некластеризованные индексы для каждого внешнего ключа. Это помогает точечным запросам извлекать строки на основе избирательного предиката измерения. Используйте файловые группы для административных задач, в том числе резервного копирования и восстановления, частичного обеспечения доступности базы данных и т. п.

Будьте внимательны с выбором фрагмента секционирования

• Большинство клиентов используют для секционирования месяц, квартал или год.
• Для эффективного удаления необходимо удалять по одной полной секции за один раз.
• Загрузка происходит быстрее, если за один раз загружается полная секция.
• Привлекательным вариантом может оказаться создание ежедневных секций для ежедневной нагрузки.
• Однако следует помнить, что таблица может содержать не более 1000 секций.
• Выбор степени секционирования влияет на параллелизм запросов.
• Для SQL Server 2005:
• Для запросов, затрагивающих одну секцию, параллелизм может достигать значения MAXDOP (максимальной степени параллелизма).
• В запросах, затрагивающих несколько секций, используется один поток на секцию, вплоть до значения MAXDOP.
• Для SQL Server 2008:
• Параллельные потоки количеством до MAXDOP пропорционально распределяются пропорционально при сканировании секций; на одну секцию может приходиться несколько потоков даже при необходимости сканирования нескольких секций.
• Если необходимо добиться степени параллелизма, соответствующей MAXDOP, рекомендуется избегать таких схем секционирования, при которых запросы часто затрагивают лишь 2 или 3 секции (предполагается, что MAXDOP равно или больше 4). 

Правильно проектируйте таблицы измерения

• Используйте целочисленные суррогатные ключи для всех измерений, кроме измерения даты. Для суррогатных ключей измерения используйте минимально возможные целые числа. Это поможет сохранить малый размер таблицы фактов.
• Используйте для ключа даты осмысленное целочисленное значение, получаемое на основе типа данных DATETIME (например, 20060215).
• Не используйте суррогатный ключ для измерения даты.
• Удобно писать запросы, в которых предложение WHERE относится к этому столбцу, что обеспечит устранение секций в таблице фактов.
• Постройте кластеризованный индекс на суррогатном ключе для каждой таблицы измерения и некластеризованный индекс на бизнес-ключе (потенциально объединенный с эффективной датой строки) для поддержки поиска по суррогатному ключу во время загрузки.
• Постройте некластеризованные индексы для других столбцов измерения, в которых часто выполняется поиск.
• Избегайте секционирования таблиц измерений.
• Избегайте принудительного установления связей по внешним ключам между таблицами фактов и измерений — это ускорит загрузку данных. Можно создать ограничения внешнего ключа с параметром NOCHECK для документирования связей, но не следует обеспечивать строгое выполнение таких ограничений. Проверять целостность данных можно с помощью преобразований Transform Lookup или же можно проверять целостность данных в источнике данных.

Пишите эффективные запросы для устранения секций

• Всегда, когда это возможно, используйте в предикате запроса (условие WHERE) ключ секционирования (ключ измерения даты) таблицы фактов.

Используйте для сопровождения данных метод скользящего окна

• Поддерживайте движущееся временное окно для оперативного доступа к таблицам фактов. Загружайте самые новые данные и выгружайте самые старые.
• Всегда сохраняйте пустые секции с обоих концов диапазона секций, чтобы гарантировать, что разбиение секций (перед загрузкой новых данных) и слияние секций (после выгрузки старых данных) не повлекут перемещения данных.
• Не следует выполнять разбиение или слияние заполненных секций. Разбиение или слияние заполненных секций может оказаться крайне неэффективным, поскольку в таком случае объем операций с журналом может возрасти в 4 раза и, кроме того, это приведет к интенсивной работе механизма блокировок.
• Создавайте промежуточную таблицу загрузки в той же файловой группе, где располагается загружаемая секция.
• Создавайте промежуточную таблицу выгрузки в той же файловой группе, где располагается удаляемая секция.
• Быстрее всего загружать сразу полную новую секцию, но это возможно только в случае, когда размер секции совпадает с периодом загрузки данных (например, используется одна секция для каждого дня и данные загружаются один раз в день).
• Если размер секции не совпадает с периодом загрузки данных, следует проводить добавочную загрузку только самой последней секции.
• Различные варианты загрузки массовых данных в секционированную таблицу обсуждаются в техническом документе http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx.
• Всегда выгружайте секции по одной.

• Во время начальной загрузки данных используйте модели восстановления SIMPLE или BULK LOGGED.
• Создайте секционированную таблицу фактов с кластеризованным индексом.
• Создайте неиндексированные промежуточные таблицы для каждой из секций и разделите файлы исходных данных, предназначенные для заполнения каждой из секций.
• Заполняйте промежуточные таблицы параллельно.
• Используйте несколько инструкций BULK INSERT, программу BCP или задачи служб SSIS.
• Если подсистема ввода-вывода не является  «узким местом», создайте столько параллельно выполняющихся сценариев загрузки, сколько в системе установлено процессоров. Если пропускная способность ввода-вывода ограничена, используйте одновременно меньшее число скриптов.
• Установите нулевой размер пакета для загрузки.
• Установите нулевой размер фиксации для загрузки.
• Используйте TABLOCK.
• Если источником данных служат расположенные на одном сервере «плоские» файлы, используйте инструкцию BULK INSERT. Если данные принудительно отправляются с удаленных компьютеров, используйте программу BCP или службы SSIS.
• Постройте кластеризованный индекс для каждой промежуточной таблицы, а затем создайте соответствующие ограничения CHECK.
• Переключите все секции в секционированную таблицу с помощью инструкции SWITCH.
• Постройте для секционированной таблицы некластеризованные индексы.
• На 64-процессорном сервере 1 ТБ данных можно загрузить менее чем за с хранилищем SAN с пропускной способностью 14 ГБ/с (для неиндексированной таблицы). Дополнительные сведения см. в записи блога SQLCAT http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx.

Эффективно удаляйте старые данные

• Когда возможно, используйте переключение секций.
• Если нужно удалить несколько миллионов строк из несекционированных индексированных таблиц:
• Не используйте конструкцию DELETE FROM ...WHERE ...
• При этом возникнут значительные проблемы, связанные с блокировками и ведением журналов.
• В случае отмены удаления откат займет длительное время.
• Как правило, более быстрым будет:
• Вставить записи в неиндексированную таблицу с помощью инструкции INSERT.
• Создать индекс (индексы) для таблицы.
• Переименовать новую таблицу, чтобы она заменила исходную.
• В качестве альтернативного способа можно организовать удаление небольшими порциями, циклически выполняя инструкцию
  
  
  DELETE TOP (1000) ... ;
  
  COMMIT

• В качестве еще одной альтернативы можно пометить строки как удаленные, а само удаление отложить и выполнить в период пониженной нагрузки.

Управляйте статистикой вручную

• Статистика по секционированным таблицам ведется для таблицы в целом.
• После загрузки новых данных обновляйте вручную статистику по крупным таблицам фактов.
• После повторного построения индекса для секции обновляйте статистику вручную.
• Если статистика обновляется регулярно, после периодической загрузки данных, то автоматическое создание статистики (параметр AUTOSTATS) для этой таблицы можно отключить.
• Это важно для оптимизации запросов, при которых может потребоваться чтение лишь самых новых данных.
• Также повысить производительность можно, обновляя статистику по таблицам измерений небольшого размера после добавочной загрузки данных. Чтобы получить более точные планы запросов, используйте параметр FULLSCAN для обновления статистики по таблицам измерения.

Выбирайте эффективные стратегии резервного копирования

• Для очень больших баз данных резервное копирование базы данных целиком может занимать значительное время.
• Например, резервное копирование базы данных размером 2 ТБ на массив RAID-5 из 10 дисков по сети SAN может занять 2 часа (при скорости 275 МБ/с).
• Отличным вариантом резервного копирования может быть создание моментальных снимков в SAN.
• Сократите объем данных, подлежащих регулярному резервному копированию.
• Файловые группы для секций с историческими данными можно пометить как READ ONLY.
• Создавайте резервную копию файловой группы лишь один раз при ее переводе в состояние «только для чтения».
• Проводите регулярное резервное копирование только для файловых групп, доступных и для чтения, и для записи.
• Учтите, что инструкции RESTORE для восстановления файловых групп, доступных только для чтения, не могут выполняться параллельно.