Partilhar via

  • Artigo

Otimizando consultas que acessam colunas de data e hora correlacionadas

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

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

Por exemplo, no banco de dados de exemplo AdventureWorks2008R2, 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 de banco de dados DATE_CORRELATION_OPTIMIZATION está definida como ON, o SQL Server mantém estatísticas de correlação entre duas tabelas quaisquer no banco de dados que possui colunas date ou datetime e são vinculadas por uma restrição de FOREIGN KEY 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 da consulta para inferir que as restrições adicionais podem ser adicionadas à 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 sua coluna date ou datetime para a qual são mantidas estatísticas de correlação é a primeira ou única chave do índice clusterizado.

Por exemplo, suponha que o banco de dados AdventureWorks2008R2 seja preparado para manter informações de correlação para Purchasing.PurchaseOrderDetail e Purchasing.PurchaseOrderHeader executando o seguinte script 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

Agora, imagine que você executa a seguinte consulta:

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

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/06' AND '2/1/06'
AND d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' 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 possa ser usado para recuperar as linhas que satisfazem d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' 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 datetime 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, ela deverá ser a primeira chave) ou, a coluna de partição, se a tabela for 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 dever 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 FOREIGN KEY na exibição de catálogo sys.objects na qual a correspondência de datetime é baseada. <constraint_object_id> é uma representação decimal de 8 dígitos de objectid da restrição FOREIGN KEY.

ObservaçãoObservação

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

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

DateCorrelationOptimization="true"

Por exemplo, um nó <Predicate> influenciado por estatísticas de correlação tem a seguinte aparência:

<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 está 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ê executa o seguinte:

  • Você cria restrições de FOREIGN KEY por meio de CREATE TABLE ou ALTER TABLE que satisfaça 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 FOREIGN KEY.

  • Você altera a nulidade ou o tipo de dados de uma coluna para torná-la elegível para correlação que corresponda à coluna datetime de outra tabela.

Não se deve fazer correspondência direta a 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, das funções de banco de dados fixas db_owner e db_ddladmin e para 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 sejam úteis são canceladas.

  • Quando você descarta uma restrição FOREIGN KEY usando DROP TABLE ou ALTER TABLE, qualquer estatística de correlação associada à restrição é descartada.

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

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

  • 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 descartadas. O SQL Server pode criar novas estatísticas de correlação baseadas no índice clusterizado recentemente criado, se elegível.

  • Quando você descarta um índice clusterizado cuja chave de índice principal é uma coluna datetime, qualquer estatística de correlação associada será descartada se outra coluna datetime existir na mesma tabela na qual 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. Nos dois cenários, o Orientador de Otimização do Mecanismo de Banco de Dados pode não recomendar a seleção de certos índices, como índices clusterizados em colunas datetime, que poderiam ser benéficos quando DATE_CORRELATION_OPTIMIZATION estiver habilitado.

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 da exibição do catálogo sys.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álogo sys.views.