Улучшенные возможности обработки запросов для секционированных таблиц и индексов

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

Примечание
Секционированные таблицы и индексы поддерживаются только в выпусках SQL Server Enterprise Edition, Developer Edition и Evaluation Edition.

Новая операция поиска, учитывающая секционирование

В SQL Server 2008 внутреннее представление секционированной таблицы изменено таким образом, что таблица представляется обработчику запросов как индекс по нескольким столбцам с PartitionID в качестве начального столбца. PartitionID представляет собой скрытый внутренний вычисляемый столбец для представления идентификатора секции, содержащей определенную строку. Например, предположим, что таблица T, определенная как T(a, b, c), секционирована по столбцу a и содержит кластеризованный индекс по столбцу b. В SQL Server 2008 эта секционированная таблица обрабатывается внутри как несекционированная таблица со схемой T(PartitionID, a, b, c) и кластеризованным индексом по составному ключу (PartitionID, b). Это позволяет оптимизатору запросов выполнять операции поиска, основанные на PartitionID, по любой секционированной таблице или индексу.

Устранение секций теперь осуществляется в этой операции поиска.

Кроме того, оптимизатор запросов расширен таким образом, что операция поиска или просмотра с одним условием может быть выполнена по PartitionID (в качестве логического начального столбца) и, возможно, по другим столбцам ключа индекса, а затем может быть выполнен поиск второго уровня с другим условием по одному дополнительному столбцу или более для каждого уникального значения, удовлетворяющего операции поиска первого уровня. Операция, называемая просмотр с пропуском, позволяет оптимизатору запросов выполнять операцию поиска или просмотра на основе одного условия для определения секций, к которым осуществляется доступ, и операцию поиска индекса второго уровня с помощью этого оператора для выборки строк из этих секций, удовлетворяющих другому условию. Например, рассмотрим следующий запрос.

SELECT * FROM T WHERE a < 10 and b = 2;

В данном примере, предположим, таблица T, определенная как T(a, b, c), секционирована по столбцу a и содержит кластеризованный индекс по столбцу b. Границы секции для столбца T определены следующей функцией секционирования:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Для разрешения запроса обработчик запросов выполняет операцию поиска первого уровня для нахождения каждой секции, содержащей строки, удовлетворяющие условию T.a < 10. Это позволяет выявить секции, к которым необходимо получить доступ. В каждой выявленной секции обработчик выполняет поиск второго уровня по кластеризованному индексу по столбцу b для нахождения строк, удовлетворяющих условию T.b = 2 и T.a < 10.

На следующем рисунке изображено логическое представление операции просмотра с пропуском. На нем изображена таблица T с данными в столбцах a и b. Секции пронумерованы от 1 до 4, а границы секций показаны вертикальными штриховыми линиями. Операция поиска первого уровня для секций (на иллюстрации не показана) определила, что секции 1, 2 и 3 удовлетворяют условию поиска, предполагаемого секционированием, определенным для таблицы и предиката по столбцу a. то есть T.a < 10. Путь, пройденный частью операции просмотра с пропуском, поиском второго уровня, изображен изогнутой линией. Фактически операция просмотра с пропуском выполняет поиск строк, удовлетворяющих условию b = 2 в каждой их этих секций. Общие затраты на выполнение операции просмотра с пропуском соответствуют трем отдельным поискам по индексу.

Отображение сведений о секционировании в планах выполнения запросов

Планы выполнения запросов в секционированных таблицах и индексах могут быть исследованы с помощью инструкций SET языка Transact-SQL, SET SHOWPLAN_XML или SET STATISTICS XML, либо с помощью графического представления плана выполнения в среде SQL Server Management Studio. Например, план выполнения времени компиляции можно отобразить, щелкнув Показать предполагаемый план выполнения на панели инструментов редактора запросов, а план времени выполнения — щелкнув Включить действительный план выполнения.

С помощью этих средств можно получить следующую информацию:

• операции, такие как проверка, поиск, вставка, обновление, слияние и удаление, которые осуществляют доступ к таблицам и индексам;

• секции, к которым запрос получает доступ — например, в планах времени выполнения приведено общее число секций, к которым получен доступ, и диапазоны смежных секций, к которым получен доступ;

• когда операция просмотра с пропуском используется в операции поиска или просмотра для получения данных из одной секции или более.

Дополнительные сведения об отображении планов выполнения см. в разделе Инструкции по планам выполнения.

Улучшенные возможности информации о секции

SQL Server 2008 содержит расширенные сведения о секционировании как для планов времени компиляции, так и для планов времени выполнения. Планы выполнения теперь содержат следующую информацию.

• Дополнительный атрибут Partitioned указывает, что оператор, например поиска, проверки, вставки, обновления, слияния или удаления, выполняется в отношении секционированной таблицы.

• Новый элемент SeekPredicateNew с подэлементом SeekKeys, содержащим PartitionID в качестве начального столбца ключа индекса и условия фильтра, определяющие операции поиска по диапазону в PartitionID. Наличие двух подэлементов SeekKeys указывает на то, что в отношении PartitionID используется операция просмотра с пропуском.

• Сводные данные об общем числе секций, к которым получен доступ. Эта информация доступна только в планах времени выполнения.

Для демонстрации отображения этой информации как в графическом плане выполнения, так и в отчете инструкции XML Showplan рассмотрим следующий запрос по таблице секционирования fact_sales. Этот запрос обновляет данные в двух секциях.

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

На следующем рисунке показаны свойства оператора Clustered Index Seek в плане выполнения времени компиляции для этого запроса. Определение таблицы fact_sales и определение секции см. в подразделе «Пример» в этом разделе.

Атрибут Partitioned

Когда оператор, такой как Index Seek, выполняется по секционированной таблице или индексу, в планах времени компиляции и времени выполнения появляется атрибут Partitioned со значением True (1). Этот атрибут не отображается, если его значение установлено как False (0).

Атрибут Partitioned может встречаться в следующих физических и логических операторах:

• Table Scan

• Index Scan

• Index Seek

• Insert

• Update

• Delete

• Merge

Как показано на предыдущей иллюстрации, этот атрибут отображается в свойствах оператора, в котором он определен. В отчете инструкции XML Showplan этот атрибут появляется как Partitioned="1" в узле RelOp оператора, в котором он определен.

Предикат New Seek

В отчете инструкции XML Showplan элемент SeekPredicateNew появляется в операторе, в котором он определен. Он может содержать до двух экземпляров вложенного элемента SeekKeys. Первый элемент SeekKeys определяет операцию поиска первого уровня на уровне идентификатора секции логического индекса. То есть эта операция поиска определяет секции, к которым должен быть осуществлен доступ для удовлетворения условий запроса. Второй элемент SeekKeys определяет часть операции просмотра с пропуском, поиск второго уровня, который производится в каждой секции, определенной поиском первого уровня.

Сводные данные по секциям

В планах времени выполнения сводка по секциям содержит данные о числе секций, к которым осуществлен доступ, и фактический перечень секций, к которым осуществлен доступ. С помощью этих данных можно проверить, к правильным ли секциям обращается запрос и исключены ли из рассмотрения остальные секции.

Предоставляется следующая информация: Actual Partition Count и Partitions Accessed.

Actual Partition Count — это общее число секций, к которым запрос получает доступ.

Partitions Accessed в отчете инструкции XML Showplan — это сводные данные по секциям, которые появляются в новом элементе RuntimePartitionSummary в узле RelOp оператора, в котором он определен. В следующем примере показано содержимое элемента RuntimePartitionSummary, указывающее, что всего получен доступ к двум секциям (секции 2 и 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Отображение сведений о секционировании с помощью других методов Showplan

Методы Showplan SHOWPLAN_ALL, SHOWPLAN_TEXT и STATISTICS PROFILE не формируют сведения о секционировании, описанные в этом разделе, за следующим исключением. Как часть предиката SEEK, секции, к которым необходимо получить доступ, обозначаются предикатом по диапазону в вычисляемом столбце, представляющем идентификатор секций. . В следующем примере показан предикат SEEK для оператора Clustered Index Seek. К секциям 2 и 3 происходит обращение, и оператор поиска производит фильтрацию по строкам, удовлетворяющим условию date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902)

                ORDERED FORWARD)

Интерпретация планов выполнения для секционированной кучи

В SQL Server 2008 секционированная куча обрабатывается как логический индекс по идентификатору секции. Устранение секций на секционированной куче представлено в плане выполнения в виде оператора Table Scan с предикатом SEEK по идентификатору секции. Следующий пример показывает данные инструкции Showplan:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Интерпретация планов выполнения для выровненных соединений

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

В выровненных планах соединение Nested Loops считывает одну или более секций для соединяемых таблиц или индексов с внутренней стороны. Цифры в операторах Constant Scan представляют номера секций.

Если для секционированных таблиц или индексов формируются параллельные планы для выровненных соединений, то между операторами соединения Constant Scan и Nested Loops появляется оператор Parallelism. В этом случае каждый из нескольких потоков на внешней стороне соединения считывает и работает на разных секциях.

Следующий рисунок демонстрирует план параллельных запросов для выровненных соединений.

Стратегия выполнения параллельных запросов для секционированных объектов

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

Если число потоков меньше числа секций, обработчик запросов присваивает по одному потоку для каждой отдельной секции, изначально оставляя несколько секций без присвоенных потоков. Когда поток завершает работу с секцией, обработчик запросов присваивает его следующей секции. Это продолжается до тех пор, пока у каждой секции не будет по одному потоку. Это единственный случай, когда обработчик запросов перераспределяет потоки к другим секциям.

Если число потоков равно числу секций, обработчик запросов присваивает каждой секции по одному потоку. После того как поток заканчивает работу, он не перераспределяется к другой секции.

Если число потоков больше числа секций, обработчик запросов присваивает каждой секции одинаковое число потоков. Если число потоков не кратно числу секций, обработчик запросов выделяет по одному дополнительному потоку для некоторых секций, чтобы были использованы все доступные потоки. Следует заметить, что если существует только одна секция, ей будут присвоены все потоки. На схеме, приведенной ниже, показаны четыре секции и 14 потоков. Каждой секции присвоено по 3 потока, у двух секций есть дополнительные потоки; всего присвоено 14 потоков. После того как поток заканчивает работу, он не перераспределяется к другой секции.

Хотя в приведенных выше примерах показывается достаточно прямолинейный способ распределения потоков, реальная стратегия более сложна; она учитывает другие факторы, которые возникают при выполнении запроса. Например, если таблица секционирована и имеет кластеризованный индекс на столбце А, а в запросе используется предложение предиката WHERE A IN (13, 17, 25), обработчик запросов присвоит один или несколько потоков каждому из трех искомых значений из значений поиска (A=13, A=17 и A=25) (а не каждой секции таблицы). Запрос необходимо выполнить только в секциях, содержащих эти значения; если все предикаты поиска будут расположены в одной секции таблицы, все потоки будут присвоены этой секции.

Другой пример: предположим, что таблица имеет четыре секции для столбца A с граничными точками (10, 20, 30), индекс на столбце B, а в запросе содержится предикат WHERE B IN (50, 100, 150). Поскольку секции таблицы основаны на значениях A, значения столбца B могут появляться во всех секциях таблицы. Поэтому обработчик запросов будет искать каждое из этих трех значений столбца B (50, 100, 150) в каждой из четырех секций таблицы. Обработчик запросов распределит потоки пропорционально, чтобы эти 12 просмотров запроса могли выполняться параллельно.

Секции таблицы основаны на столбце А	Операции поиска для столбца B в каждой секции таблицы
Секция таблицы 1: A < 10	B = 50, B = 100, B = 150
Секция таблицы 2: A >= 10 AND A < 20	B = 50, B = 100, B = 150
Секция таблицы 3: A >= 20 AND A < 30	B = 50, B = 100, B = 150
Секция таблицы 4: A >= 30	B = 50, B = 100, B = 150

Советы и рекомендации

Для увеличения производительности запросов, обращающихся к большому количеству данных из больших секционированных таблиц и индексов, предлагаются следующие рекомендации.

• Распределяйте каждую секцию по нескольким дискам.

• Чтобы снизить затраты на ввод-вывод, по возможности используйте сервер с достаточным объемом основной памяти, вмещающей секции, требующие частого доступа, или все секции.

• Если данные, по которым выполняется запрос, не помещаются в памяти, рекомендуется выполнить сжатие таблиц и индексов. Это позволит снизить затраты на ввод-вывод.

• Чтобы в полной мере реализовать возможности параллельной обработки запросов, используйте сервер с быстрыми процессорами и как можно большим числом процессорных ядер.

• Обеспечьте достаточную пропускную способность контроллера ввода-вывода для сервера.

• Чтобы в полной мере реализовать возможности оптимизированного просмотра сбалансированного дерева, создайте кластеризованный индекс по каждой большой секционированной таблице.

• При массовой загрузке данных в секционированные таблицы следуйте рекомендациям технического документа по массовой загрузке данных в секционированные таблицы.

Пример

В следующем примере показано создание тестовой базы данных, состоящей из одной таблицы с семью секциями. Чтобы при выполнении запросов в этом примере просматривать сведения о секционировании в планах времени компиляции и времени выполнения, следует пользоваться инструментами, описанными ранее.

Примечание
В данном примере в таблицу вставляется более 1 миллиона строк. В зависимости от имеющегося оборудования выполнение данного примера может занять несколько минут. Перед выполнением этого примера следует убедиться, что на диске 1,5 ГБ свободного места.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

См. также

Справочник

Графическое отображение планов выполнения (SQL Server Management Studio)

DBCC TRACEON (Transact-SQL)

Основные понятия

Секционированные таблицы и индексы

Отображение планов выполнения с помощью параметров Showplan инструкции SET (Transact-SQL)

Улучшенные возможности обработки запросов для секционированных таблиц и индексов