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

 Рассмотрите возможность секционирования крупных таблиц фактов

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

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

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

 Ответственно подходите к выбору гранулярности секционирования

• Большинство клиентов используют для секционирования месяц, квартал или год.
• Чтобы удаления секций были эффективными, необходимо удалять всю секцию за один раз.
• Загрузка целой секции за один раз выполняется быстрее.
• Привлекательным вариантом может оказаться создание ежедневных секций для данных загружаемых ежедневно.
• Следует, однако, помнить, что таблица может иметь не более 1 тыс. секций.
• Выбор фрагмента секционирования влияет на параллелизм запросов.
• Для запросов, затрагивающих одну секцию, степень параллелизма может достигать максимального значения (MAXDOP).
• В запросах, затрагивающих несколько секций, используется один поток на секцию вплоть до значения MAXDOP.
• Если необходимо добиться максимального параллелизма (параметр MAXDOP = 4 или выше), не следует секционировать данные так, чтобы частые запросы затрагивали только 2 или 3 секции.

 Правильно

• Используйте суррогатные ключи типа integer для всех измерений, кроме измерения даты. Для суррогатных ключей измерения используйте минимальный по размеру тип семейства integer. Это поможет сохранить малый размер таблицы фактов.
• Выберите для ключа даты осмысленное целочисленное значение, производное от типа данных DATETIME (например, 20060215).

• Для каждой таблицы измерения создайте кластеризованный индекс по бизнес-ключу (не по суррогатному ключу). Такой индекс будет полезным, так как:

• Создайте некластеризованный индекс первичного ключа по ключу измерения (суррогатному) каждой таблицы измерения.
• Создайте некластеризованные индексы по другим столбцам измерения, в которых часто выполняется поиск.
• Избегайте секционирования таблиц измерения.
• Избегайте создания связей «первичный ключ – внешний ключ» между таблицей фактов и таблицей измерения для ускорения загрузки. Ограничения по внешнему ключу можно создать с указателем NOCHECK — так вы сможете документировать связи; однако не включайте их принудительное применение. Обеспечьте целостность данных с помощью преобразований «Уточняющий запрос» или проводите проверку целостности в источнике данных.

 Пишите эффективные запросы для использования механизма исключения секций

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

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

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

 Загружайте исходные данные эффективным образом

• Во время загрузки исходных данных используйте модель восстановления SIMPLE или BULK LOGGED.
• Создайте секционированную таблицу фактов с кластеризованным индексом.
• Создайте неиндексированные промежуточные таблицы для каждой секции, а также отдельные файлы исходных данных для заполнения каждой секции.
• Заполняйте промежуточные таблицы параллельно.
• Используйте несколько инструкций BULK INSERT, программу BCP или задачи служб SSIS.
• Если в дисковой подсистеме ввода-вывода нет узких мест, для параллельной работы создайте столько сценариев загрузки, сколько процессоров имеется в системе. Если пропускная способность дискового ввода-вывода ограничена, запускайте меньшее число сценариев для параллельной работы.
• Установите размер пакета для загрузки в 0.
• Установите размер фиксирования данных для загрузки в 0.
• Используйте ключевое слово TABLOCK.
• Если источники представляют собой плоские файлы, расположенные на одном сервере, используйте инструкцию BULK INSERT. Если данные принудительно отправляются с удаленных компьютеров, используйте программу BCP или службы SSIS.
• Создайте кластеризованный индекс для каждой промежуточной таблицы, а затем создайте соответствующие ограничения CHECK. Не используйте параметр SORT_IN_TEMPDB.
• Переключите все секции в секционированную таблицу с помощью инструкции 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 для восстановления файловых групп, доступных только для чтения, не могут выполняться параллельно.