В учебной базе данных EducationDatabase накапливаются данные по продажам; текущим (последним обрабатываемым) годом продаж является 2011. Проведем секционирование таблицы Bill. Атрибутом секционирования выберем дату продажи товара Date. Каждая секция будет содержать сведения о продажах за определенный месяц 2011 года, за исключением последней секции, которая будет содержать сведения о продажах в последнем месяце 2011 года и позже. Дополнительно одна секция будет содержать продажи, совершенные до 2011 года.
Порядок секционирования таблиц:
1. Для таблицы Bill в контекстном меню выберите раздел Storage -> Create Partition. Откроется окно мастера секционирования.
2. На первом шаге работы мастера выберите столбец таблицы, на основе которого будет производиться секционирование. Выберем столбец Date (рисунок 1).
Рисунок 1 – Выбор столбца секционирования таблицы
3. На шаге Select a Partition Function укажите имя функции секционирования (рисунок 2).
Рисунок 2 – Создание функции секционирования
4. На шаге Select a Partition Scheme укажите имя схемы секционирования (рисунок 3).
Рисунок 3 – Создание схемы секционирования
5. На шаге Map Partitions требуется определить границы секций и связать их с файловыми группами. Сначала необходимо определиться с видом интервалов секционирования. Если выбрана опция Left Boundary, будут формироваться интервалы вида (предыдущее значение, текущее значение]. Если выбрана опция Right Boundary, будут формироваться интервалы вида [предыдущее значение, текущее значение). Для каждой секции может быть назначена своя файловая группа, либо несколько секций (возможно все) будут связаны с одной файловой группой. Выберем файловую группу PRIMARY для всех секций. Следует учитывать, что значительного выигрыша в производительности и пропускной способности возможно достичь при размещении различных секций в разных файловых группах на различных физических дисках. При этом возможно обеспечить, для определенных запросов, параллельный доступ к массивам дисков. Правые границы диапазонов секций указываются в колонке Boundary, при этом необходимо указать границы каждой секции. Возможен автоматический расчет границ диапазонов с использованием кнопки Set Boundaries… В последнем случае СУБД автоматически определит минимальное и максимальное значение в колонке секционирования и предложит размер секции (по месяцам, по кварталам и т.п.). Пользователь должен самостоятельно определить требуемые верхнюю и нижнюю границы секционирования (нас интересует 2011 год) и определиться с размером секции (выберем по месяцам). Кнопка Estimate Storage используется для расчета характеристик секций: количество записей, необходимый объем памяти, занимаемый объем памяти. В итоге карта секционировании примет вид, представленный на рисунке 4.
|
|
Рисунок 4 – Карта секционирования
6. На шаге Select an Output Option выберите опцию Create Script, открыть скрипт в новом окне редактирования запросов Script to New Query Window (рисунок 5). Другими возможными опциями являются: выполнить немедленно Run Immediately, выполнить по расписанию Schedule.
Рисунок 5 – Опции выгрузки
7. На заключительном шаге Review Summary проверьте итоговую информацию о секционировании и нажмите кнопку Finish.
Выполните сформированный скрипт:
USE [EducationDatabase]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [billPartitionFun](datetime)
AS RANGE RIGHT FOR
|
|
VALUES (N'2011-01-01T00:00:00', N'2011-02-01T00:00:00',
N'2011-03-01T00:00:00', N'2011-04-01T00:00:00',
N'2011-05-01T00:00:00', N'2011-06-01T00:00:00',
N'2011-07-01T00:00:00', N'2011-08-01T00:00:00',
N'2011-09-01T00:00:00')
CREATE PARTITION SCHEME [billPartitionScheme] AS PARTITION [billPartitionFun] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE CLUSTERED INDEX [ClusteredIndex_on_billPartitionScheme_634648357407858521] ON [dbo].[Bill]
([Date]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [billPartitionScheme]([Date])
COMMIT TRANSACTION
Обратите внимание, что СУБД создает новый кластерный индекс по столбцу Date взамен кластерного индекса по первичному ключу-счетчику BillId, что предполагает реорганизацию данных в порядке сортировки по датам покупок и разделения отсортированных данных по секциям.
Рассмотрим, как изменились планы выполнения запросов на выборку данных из таблицы Bill.
Выполним запрос:
select * from bill
where date between '05.02.2011' and '20.02.2011'
В данном случае все результирующие записи располагаются в одной секции, связанной с месяцем «февраль 2011 г.». Поиск записей осуществляется в одной секции (рисунок 6) с использованием кластерного индекса по полю date (рисунок 7).
Рисунок 6 – Описание операции поиска по кластерному индексу
Рисунок 7 – План выполнения запроса
Выполним следующий запрос:
select * from bill
where date between '05.02.2011' and '20.03.2011'
Теперь результирующие записи располагаются в двух секциях, связанных с месяцами «февраль 2011 г.» и «март 2011 г.». План запроса изменился за счет появления дополнительной операции чтения внутренней таблицы констант с номерами секций (рисунок 8). Операция поиска по кластерному индексу выполняется два раз по числу секций, участвующих в запросе (рисунок 9). Новый план выполнения запроса представлен на рисунке 10.
|
|
Рисунок 8 – Операция сканирования внутренней таблицы констант
Рисунок 9 – Операция поиска по кластерному индексу
Рисунок 10 – План выполнения запроса
Представленные два примера наглядно демонстрируют преимущества секционирования данных – интерфейс доступа к данным не меняется (используется одинаковый синтаксис SQL-запросов), но оптимизатор запросов прозрачным для разработчика и приложения способом меняется способ доступа к данным с возможностью параллельного выполнения отдельных операторов (в последнем случае – поиск по кластерному индексу).
Содержание работы
1. Напишите 2 запроса соединения таблиц Bill и BillItem в первом случае с фильтрацией Bill.date between '05.02.2011' and '20.02.2011', во втором случае с фильтрацией Bill.date between '05.02.2011' and '20.03.2011'. Изучите планы выполнения запросов, объясните, почему они различны. Сохраните планы выполнения запросов.
2. Проведите секционирование таблицы BillItem по сценарию, описанному в данной лабораторной работе. В качестве ключа секционирования используйте BillItem.Date. Объясните, являются ли секционированные таблицы Bill и BillItem выровненными?
3. Выполните запросы из п.1 и изучите планы их выполнения. Объясните, что изменилось в планах выполнения запросов.
Литература
1. Секционированные таблицы и индексы SQL Server 2005, По материалам статьи Kimberly L. Tripp: SQL Server 2005. Partitioned Tables and Indexes, Перевод Алексея Сафонова, https://www.sql.ru/articles/mssql/2005/073102PartitionedTablesAndIndexes.shtml
2. Microsoft MSDN, https://msdn.microsoft.com/ru-ru/