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

  • Статья

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

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

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

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

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

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

USE AdventureWorks
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)

-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID

-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate)

--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks
   SET DATE_CORRELATION_OPTIMIZATION ON

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

SELECT *
FROM Purchasing.PurchaseOrderHeader h,
Purchasing.PurchaseOrderDetail d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '20020101' AND '20020201'

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

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

Точная форма нового условия во втором предложении AND зависит от первоначального запроса и значений в базе данных. Оптимизатор использует добавленное условие для построения плана выполнения. Например, по столбцу PurchaseOrderDetail.DueDate построен кластерный индекс, который используется для извлечения строк, удовлетворяющих условию d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' 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

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

При включении параметра базы данных 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> — это восьмиразрядное представление идентификатора objectid ограничения внешнего ключа.

ms177416.note(ru-ru,SQL.90).gifПримечание.
SQL Server сокращает часть FK_constraint_ имени статистики корреляции, если имя превышает ограничение на длину идентификаторов.

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

DateCorrelationOptimization="true"

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

<Predicate DateCorrelationOptimization="true">

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

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

  • При создании ограничения внешнего ключа при помощи инструкций CREATE TABLE или ALTER TABLE, удовлетворяющего требованиям к оптимизации корреляции столбцов datetime.
  • При создании кластерного индекса по столбцу datetime, который коррелирует со столбцом datetime в другой таблице.
    ms177416.note(ru-ru,SQL.90).gif
    Примечание. Статистика корреляции не создается, если кластерный индекс создается с параметром 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 (Transact-SQL).

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

См. также

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

Параметры SET

Справка и поддержка

Получение помощи по SQL Server 2005