Статья
04/01/2012

Оптимизация запросов, которые обращаются к коррелируемым столбцам типа datetime

Параметр DATE_CORRELATION_OPTIMIZATION инструкции SET улучшает производительность запросов, выполняющих эквивалентное соединение двух таблиц, у которых столбцы типа date или datetime коррелируют друг с другом, и содержащих ограничение на дату в предикате запроса.

Таблицы с коррелируемыми столбцами типа date или datetime, которые могут получить преимущество при включении параметра DATE_CORRELATION_OPTIMIZATION, обычно участвуют в связи «один ко многим» и применяются главным образом для поддержки принятия решений, отчетов или для целей хранения данных.

Например, в образце базы данных База данных AdventureWorks2008R2 столбец OrderDate таблицы Purchasing.PurchaseOrderHeader и столбец DueDate таблицы Purchasing.PurchaseOrderDetail коррелируют друг с другом. Значения даты в столбце PurchaseOrderDetail.DueDate слегка превышают значения в таблице PurchaseOrderHeader.OrderDate.

Если параметр базы данных DATE_CORRELATION_OPTIMIZATION установлен в значение ON, SQL Server собирает статистику корреляции между двумя таблицами базы данных, содержащими столбцы типа date или datetime и связанными ограничением внешнего ключа из одного столбца. По умолчанию этот параметр выключен.

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

Предположим, что база данных База данных AdventureWorks2008R2 была подготовлена к сбору сведений о корреляции таблиц Purchasing.PurchaseOrderDetail и Purchasing.PurchaseOrderHeader путем выполнения следующего скрипта Transact-SQL:

USE AdventureWorks2008R2;
GO
-- Create a unique index to take the place of the existing 
-- primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX
IX_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID 
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,PurchaseOrderDetailID);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks2008R2
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

Допустим теперь, что был выполнен следующий запрос:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
INNER JOIN Purchasing.PurchaseOrderDetail AS d
    ON h.PurchaseOrderID = d.PurchaseOrderID
WHERE h.OrderDate BETWEEN '20060101' AND '20060201';

Значения столбца PurchaseOrderDetail.DueDate, которые возвращает этот запрос, как правило, превышают значения в столбце PurchaseOrderHeader.OrderDate на величину, принадлежащую некоторому интервалу, например 14 дней. Благодаря этому, SQL Server может предположить, что предыдущий запрос можно выполнить быстрее, используя запрос, подобный следующему:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/06' AND '2/1/06'
AND d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14;

Точная форма нового условия во втором предложении AND зависит от первоначального запроса и значений в базе данных. Оптимизатор использует добавленное условие для построения плана выполнения. Например, по столбцу PurchaseOrderDetail.DueDate построен кластеризованный индекс, который используется для получения строк, удовлетворяющих условию d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14. Если столбец Purchasing.PurchaseOrderDetail содержит данные за несколько лет, производительность запроса может значительно ухудшиться (в несколько раз) по сравнению с первоначальным запросом.

Перед выполнением плана запроса с условием, выведенным после включения параметра DATE_CORRELATION_OPTIMIZATION, SQL Server проверяет, не изменятся ли результаты запроса, опираясь на текущее содержимое базы данных.

Требования к использованию параметра базы данных DATE_CORRELATION_OPTIMIZATION

Чтобы извлечь выгоду от включения параметра базы данных DATE_CORRELATION_OPTIMIZATION, обе таблицы должны удовлетворять следующим условиям:

Параметры базы данных инструкции SET должны быть установлены следующим образом: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL и QUOTED IDENTIFIER должны быть установлены (SET) в состояние ON. Параметр NUMERIC_ROUNDABORT должен быть установлен (SET) в состояние OFF.
Таблицы должны быть связаны по внешнему ключу из одного столбца.
Обе таблицы должны содержать столбцы типа datetime, объявленные как NOT NULL.
По крайней мере один из столбцов datetime должен быть ключевым столбцом кластеризованного индекса (первым столбцом, если ключ индекса является составным) или столбцом секционирования, если таблица разбита на секции.
Обе таблицы должны принадлежать одному пользователю.

При включении параметра базы данных DATE_CORRELATION_OPTIMIZATION учтите следующее:

SQL Server хранит сведения о корреляции в форме статистики. SQL Server обновляет статистику при выполнении операций INSERT, UPDATE и DELETE в соответствующих таблицах, это может привести к ухудшению производительности этих операций. Параметр DATE_CORRELATION_OPTIMIZATION не следует включать в базе данных, где часто выполняются операции обновления.
Если любой из столбцов типа datetime, для которых собирается статистика корреляции, не является первым или единственным столбцом кластеризованного индекса, создайте его. Создание кластеризованного индекса, как правило, улучшает производительность запросов, использующих статистику корреляции. Если кластеризованный индекс уже существует по столбцам первичного ключа, можно изменить таблицу так, чтобы кластерный индекс и первичный ключ использовали различные наборы столбцов.
Включение параметра DATE_CORRELATION_OPTIMIZATION не улучшает производительность в следующих ситуациях:
- Нет такой пары таблиц, которые удовлетворяют описанным условиям для сбора статистики.
- Пары таблиц, удовлетворяющие условиям для сбора статистики, существуют, но в запросах, соединяющих эти таблицы, не указано ограничение на дату в предикатах.

Чтобы включить параметр базы данных DATE_CORRELATION_OPTIMIZATION

ALTER DATABASE (Transact-SQL)

Работа со статистикой корреляции

При включении параметра базы данных DATE_CORRELATION_OPTIMIZATION для каждой подходящей пары таблиц автоматически создается статистика корреляции в форме индексированных представлений. Когда оптимизатор запросов SQL Server может извлечь пользу из сведений о корреляции двух столбцов datetime, он использует статистику корреляции в плане запроса. Статистика корреляции также включается в логику работы инструкций INSERT, UPDATE и DELETE. Имя статистики корреляции имеет следующую форму:

MPStats_Sys<constraint_object_id><GUID><FK_constraint_name>

<FK_constraint_name> — это имя ограничения внешнего ключа в представлении каталога sys.objects, на котором основано соответствие столбцов типа datetime. <constraint_object_id> — это состоящее из 8 цифр представление идентификатора objectid ограничения внешнего ключа.

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

При выполнении запроса с параметром SET SHOWPLAN XML любой узел фильтра, полученный из статистики корреляции, содержит следующий атрибут:

DateCorrelationOptimization="true"

Например, узел <Predicate> под влиянием статистики корреляции примет следующий вид:

<Предикат DateCorrelationOptimization="true">

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

При включении параметра базы данных DATE_CORRELATION_OPTIMIZATION SQL Server создает статистику корреляции для всех подходящих пар столбцов datetime. Кроме того, SQL Server создает дополнительную статистику корреляции в следующих случаях.

При создании ограничения внешнего ключа при помощи инструкций CREATE TABLE или ALTER TABLE, удовлетворяющего требованиям к оптимизации корреляции столбцов datetime.

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

Примечание
Статистика корреляции не создается, если кластеризованный индекс создается с параметром ONLINE = ON. Тем не менее после подтверждения создания индекса статистика корреляции, которая зависит от индекса, может быть построена как результат события в другой транзакции, например в результате создания ограничения внешнего ключа.

Статистика корреляции не создается, если кластеризованный индекс создается с параметром ONLINE = ON. Тем не менее после подтверждения создания индекса статистика корреляции, которая зависит от индекса, может быть построена как результат события в другой транзакции, например в результате создания ограничения внешнего ключа.

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

На статистику корреляции не следует напрямую ссылаться в приложениях, поскольку SQL Server может в любое время удалить ее. Отдельную статистику корреляции можно удалить, если затраты на ее сбор снижают производительность. По умолчанию разрешения на удаление статистики корреляции предоставлены членам предопределенной роли сервера sysadmin, предопределенным ролям базы данных db_owner и db_ddladmin и владельцу этой пары таблиц, для которых создана статистика корреляции. Эти разрешения не предназначены для передачи.

Статистика корреляции удаляется в следующих ситуациях:

При выключении параметра DATE_CORRELATION_OPTIMIZATION вся статистика корреляции, созданная SQL Server, удаляется.
Если статистика корреляции занимает слишком много места или не улучшает производительность, она удаляется.
При удалении ограничения внешнего ключа инструкциями DROP TABLE или ALTER TABLE удаляется статистика корреляции, связанная с этим ограничением.
Если в результате некоторой операции владельцами таблиц, содержащих коррелируемые столбцы, становятся различные пользователи, соответствующая статистика корреляции удаляется.
При выполнении инструкции ALTER TABLE...SWITCH удаляется статистика корреляции, созданная для исходной или целевой таблицы.
При создании кластеризованного индекса по столбцу datetime таблицы, содержащей другой столбец datetime, для которого создана статистика корреляции, она удаляется. SQL Server может создать новую статистику корреляции на основе нового кластеризованного индекса, если он подходит для этих целей.
При удалении кластеризованного индекса, ключ индекса которого является столбцом типа datetime, удаляется любая связанная с ним статистика корреляции, если таблица содержит другой столбец datetime, для которого можно создать новую статистику корреляции.
При изменении типа данных столбца или возможности содержать значения NULL инструкцией ALTER TABLE удаляется любая статистика корреляции, связанная с этим столбцом.

Статистика корреляции создается в ходе той же транзакции, которая привела к ее созданию или удалению. Эта транзакция не является ни оперативной, ни асинхронной.

При простой настройке отдельного рабочего сервера помощник по настройке ядра СУБД учитывает затраты и преимущества статистики корреляции. Однако в тестовой среде помощник по настройке ядра СУБД не считает статистику корреляции внутренним объектом системы. Таким образом, статистика корреляции не используется при оптимизации запросов помощником по настройке ядра СУБД во время анализа индексов. В тестовой среде можно пропускать любые рекомендации, которые помощник по настройке ядра СУБД делает относительно индексированных представлений, содержащих статистику корреляции, поскольку он принимает во внимание затраты на ее сбор, но не учитывает преимущества. В обоих случаях помощник по настройке ядра СУБД может не порекомендовать выбор определенных индексов, например кластеризованных индексов по столбцам datetime, которые могут увеличить производительность при включении параметра DATE_CORRELATION_OPTIMIZATION.

Запрос метаданных о статистике корреляции

Значение параметра базы данных DATE_CORRELATION_OPTIMIZATION хранится в столбце is_date_correlation_on представления каталога sys.databases.

Чтобы определить, основано ли представление на статистике корреляций, выберите столбец is_date_correlation_view представления каталога sys.views.

См. также

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

Параметры SET

Другие ресурсы

Применение помощника по настройке ядра СУБД