Partilhar via

  • Artigo

Otimizando consultas que acessam colunas de data e hora correlacionadas

O opção de SET do banco de dados DATE_CORRELATION_OPTIMIZATION melhora o desempenho de consultas que executam uma junção por igualdade entre duas tabelas correlacionadas cujas colunas são data ou datetime e que especificam uma restrição de data no predicado de consulta.

Tabelas cujos valores de coluna data ou datetime são correlacionados e que podem se beneficiar da habilitação de DATE_CORRELATION_OPTIMIZATION, são normalmente parte de uma relação um para muitos e são principalmente usadas para suporte a decisão, relatório ou propósitos de armazenagem de dados.

Por exemplo, no banco de dados de exemplo AdventureWorks, a coluna OrderDate da tabela Purchasing.PurchaseOrderHeader e a coluna DueDate da tabela Purchasing.PurchaseOrderDetail são correlacionadas. Os valores de data de PurchaseOrderDetail.DueDate tendem a seguir logo após esses de PurchaseOrderHeader.OrderDate.

Quando a opção do banco de dados DATE_CORRELATION_OPTIMIZATION é definida como ON, o SQL Server mantém estatísticas de correlação entre duas tabelas quaisquer no banco de dados que tenham colunas de data ou datetime e que são unidas por uma restrição de chave estrangeira de uma coluna. Por padrão, essa opção está definida como OFF.

O SQL Server usa essas estatísticas de correlação junto com a restrição de data especificada no predicado de consulta para inferir que as restrições adicionais podem ser acrescentadas à consulta sem alterar o conjunto de resultados. O otimizador de consulta usa essas condições inferidas quando escolhe um plano de consulta. O resultado pode ser um plano de consulta mais rápido, porque as restrições acrescentadas permitem ao SQL Server ler menos dados quando está processando a consulta. O desempenho também é aprimorado quando ambas as tabelas têm índices clusterizados definidos, e as suas colunas de data ou datetime para as quais são mantidas estatísticas de correlação são a primeira ou única chave do índice clusterizado.

Por exemplo, imagine que você prepare o banco de dados do AdventureWorkspara manter informações de correlação para Purchasing.PurchaseOrderDetail e Purchasing.PurchaseOrderHeader executando o seguinte script 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);
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 AdventureWorks
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

Agora, imagine que você executa a seguinte consulta:

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

Os valores de PurchaseOrderDetail.DueDate retornados por essa consulta geralmente devem ficar dentro de um certo período de dias, como 14 dias, nos valores de PurchaseOrderHeader.OrderDate. Por causa disso, o SQL Server poderia inferir que a consulta prévia pode ser expressada melhor usando uma consulta comparável a esta:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS 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;

A forma exata da condição acrescentada, especificada na segunda cláusula AND, depende da consulta original e os valores dos dados em seu banco de dados. Depois de acrescentar uma condição implícita, o otimizador a usa para construir um plano de execução. Neste exemplo, há um índice clusterizado em PurchaseOrderDetail.DueDate de forma que o índice pode ser usado para recuperar as linhas que satisfazem d.DueDate BETWEEN CAST ('20020101' AS datetime) + 14 AND CAST ('20020201' AS datetime) + 14. Se houver vários de anos de dados em Purchasing.PurchaseOrderDetail, essa consulta poderá causar uma diminuição significativa (várias vezes) no tempo de execução comparado à consulta original.

Antes de executar um plano de consulta com uma condição que é inferida por causa da habilitação de DATE_CORRELATION_OPTIMIZATION, o SQL Server verifica se a consulta produzirá a resposta correta, baseado nos conteúdos atuais do banco de dados.

Requisitos para usar a opção de banco de dados DATE_CORRELATION_OPTIMIZATION

Todas as condições seguintes devem ser satisfeitas nas duas tabelas para que se beneficiem da habilitação da opção do banco de dados DATE_CORRELATION_OPTIMIZATION:

  • As opções do banco de dados SET devem ser definidas do seguinte modo. ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL e QUOTED IDENTIFIER devem ser SET como ON. NUMERIC_ROUNDABORT deve ser SET como OFF.

  • Deve haver uma relação de chave estrangeira de coluna única entre as tabelas.

  • As tabelas devem ter colunas de datetime que são definidas como NOT NULL .

  • Pelo menos uma das colunas datetime deve ser a coluna chave de um índice clusterizado (se a chave de índice for composta, deve ser a primeira chave) ou deve ser a coluna de partição, se for uma tabela particionada.

  • Ambas as tabelas devem pertencer ao mesmo usuário.

Considere o seguinte quando você definir a opção do banco de dados DATE_CORRELATION_OPTIMIZATION como ON:

  • O SQL Server mantém informações de correlação em formato de estatísticas. Estas estatísticas são atualizadas através do SQL Server durante operações de INSERT, UPDATE e DELETE nas tabelas aplicáveis o que pode afetar o desempenho destas operações. Você não deveria habilitar DATE_CORRELATION_OPTIMIZATION em ambientes de banco de dados com atualização intensiva.

  • Se qualquer uma das colunas datetime para as quais são mantidas estatísticas de correlação não for a primeira ou chave única de um índice clusterizado, considere criar um índice clusterizado nela. Fazendo isto geralmente conduz a um desempenho melhor nos tipos de consultas que usam estatísticas de correlação. Se um índice clusterizado já existir nas colunas de chave primária, você poderá modificar uma tabela de forma que o índice clusterizado e a chave primária usem conjuntos de coluna diferentes.

  • A habilitação de DATE_CORRELATION_OPTIMIZATION não proporciona benefício nas situações seguintes:

    • Não há pares de tabelas que satisfaçam os critérios previamente declarados para manter estatísticas de correlação.

    • Há pares de tabelas que satisfazem os critérios para manter estatísticas de correlação, mas consultas que unem estas tabelas não especificam uma restrição de data em seus predicados.

Para definir a opção de banco de dados DATE_CORRELATION_OPTIMIZATION

Trabalhando com estatísticas de correlação

Para todos os pares elegíveis de tabelas correspondentes são criadas automaticamente estatísticas de correlação, no formato de exibições indexadas quando você definir a opção de banco de dados DATE_CORRELATION_OPTIMIZATION como ON. Quando o otimizador de consulta do SQL Server puder se beneficiar da correlação entre pares de colunas datetime, ele usa essas estatísticas de correlação em seu plano de consulta. Estatísticas de correlação também são incluídas na lógica de instruções INSERT, UPDATE e DELETE quando elas são afetadas. Os nomes de estatísticas de correlação têm o seguinte formato:

_MPStats_Sys_<constraint_object_id>_<GUID>_<FK_constraint_name>

<>FK_constraint_name é o nome da restrição de chave estrangeira na exibição do catálogo sys.objects na qual a correspondência datetime é baseada. <constraint_object_id> é uma representação hexadecimal de 8 dígitos de objectid da restrição de chave estrangeira.

ObservaçãoObservação

O SQL Server encurtará parte do nome de estatísticas de correlação FK_constraint_ se o nome exceder o limite de comprimento de identificador.

Ao executar uma consulta que usa SET SHOWPLAN XML, qualquer nó de filtro derivado de estatísticas de correlação inclui o atributo a seguir:

DateCorrelationOptimization="true"

Por exemplo, um nó <Predicate> influenciado por aspectos de estatísticas de correlação fica assim:

<Predicate DateCorrelationOptimization="true">

Esse atributo é incluído em qualquer nó de filtro gerado totalmente de estatísticas de correlação, ou pela combinação de um predicado influenciado por estatísticas de correlação com algum outro predicado.

Geralmente, quando a opção de banco de dados DATE_CORRELATION_OPTIMIZATION é definida como ON, o SQL Server cria estatísticas de correlação para todos os pares elegíveis de colunas datetime. O SQL Server cria estatísticas de correlação adicionais quando você executar o seguinte:

  • Você cria restrições de chave estrangeira para CREATE TABLE ou ALTER TABLE que satisfazem os requisitos para otimização da correlação datetime.

  • Você cria um índice clusterizado em uma coluna datetime e essa coluna é elegível para correlação que corresponde à coluna datetime de outra tabela.

    ObservaçãoObservação

    Nenhuma estatística de correlação é criada quando índices clusterizados são criados usando a opção ONLINE =ON. Entretanto, depois que a construção de índice estiver confirmada, as estatísticas de correlação que dependem do índice podem ser criadas como o resultado de um evento em outra transação, como a criação de uma restrição de chave estrangeira.

  • Você altera o a possibilidade de nulidade ou tipo de dados de uma coluna para deixá-la elegível para a correlação correspondente a coluna datetime de outra tabela.

Você não deveria referir-se diretamente às estatísticas de correlação em aplicativos, porque o SQL Server pode decidir cancelá-las a qualquer momento. Você poderá decidir cancelar as estatísticas de correlação individuais se determinar que o custo para mantê-las afeta o desempenho. O padrão para permissões DROP em estatísticas de correlação é definido para os membros de funções de servidor fixas sysadmin, em funções de banco de dados fixas db_owner e db_ddladmin, e o proprietário do par de tabelas nas quais as estatísticas de correlação estão definidas. Essas permissões não são transferíveis.

Estatísticas de correlação são canceladas nas seguintes situações:

  • Quando você definir a opção do banco de dados DATE_CORRELATION_OPTIMIZATION como OFF, qualquer estatística de correlação criada pelo SQL Server é cancelada.

  • Estatísticas de correlação que exigem armazenamento excessivo para serem mantidas ou que não se espera que sejam úteis são canceladas.

  • Quando você cancelar uma restrição de chave estrangeira usando DROP TABLE ou ALTER TABLE, qualquer estatística de correlação associada é cancelada com essa restrição.

  • Quando uma operação fizer com que as tabelas, que estão participando em uma correlação correspondente ,não sejam possuídas pelo mesmo usuário, as estatísticas de correlação correspondentes são canceladas.

  • Quando você executa uma instrução ALTER TABLE.SWITCH e a tabela de fonte ou tabela de destino têm estatísticas de correlação definidas nela, essas estatísticas de correlação são canceladas.

  • Quando você cria um índice clusterizado em uma coluna datetime e são criadas estatísticas de correlação em uma coluna datetime diferente da mesma tabela, as estatísticas de correlação são canceladas. O SQL Server pode criar novas estatísticas de correlação baseadas no índice clusterizado recentemente criado, se elegível.

  • Quando você cancelar um índice clusterizado cuja chave de índice principal é uma coluna datetime, qualquer estatística de correlação associada será cancelada se outra coluna datetime existir na mesma tabela, nas quais as novas estatísticas de correlação podem ser criadas.

  • Quando você executar ALTER TABLE para alterar o tipo de dados ou de nulidade de uma coluna que participa de estatísticas de correlação, essas estatísticas são canceladas.

Estatísticas de correlação são criadas ou canceladas como parte da mesma transação que as criou ou cancelou. Essa transação não é online nem assíncrona.

Quando você usar o Orientador de Otimização do Mecanismo de Banco de Dados em um cenário de ajuste simples, baseado em um servidor para ajustar o servidor de produção diretamente, ele considera os custos e as vantagens das estatísticas de correlação. Porém, quando você usar o Orientador de Otimização do Mecanismo de Banco de Dados em um cenário de servidor da produção de teste, ele não considera as estatísticas de correlação como objetos de sistema internos. Portanto, as estatísticas de correlação não são usadas na otimização de consulta pelo Orientador de Otimização do Mecanismo de Banco de Dados durante sua análise de ajuste de índice. Em um cenário de produção teste, você pode querer ignorar qualquer recomendação que o Orientador de Otimização do Mecanismo de Banco de Dados faz sobre as exibições indexadas que mantêm estatísticas de correlação, porque o Orientador de Otimização sabe seus custos mas não os seus benefícios. Em ambos cenários, o Orientador de Otimização do Mecanismo de Banco de Dados pode não recomendar seleção de certos índices tais como índices clusterizados em colunas datetime que poderiam ser benéficas quando a DATE_CORRELATION_OPTIMIZATION é habilitada.

Consultando metadados sobre estatísticas de correlação

Para exibir a configuração da opção de banco de dados DATE_CORRELATION_OPTIMIZATION, selecione a coluna is_date_correlation_on exibições do catálogosys.databases .

Para determinar se uma exibição é baseada em estatísticas de correlação, selecione a coluna is_date_correlation_view da exibição do catálogosys.views.