As colunas calculadas podem causar problemas de desempenho difíceis de diagnosticar. Este artigo discute vários problemas e algumas maneiras de resolvê-los.
As colunas calculadas são uma maneira conveniente de incorporar cálculos nas definições da tabela. Mas eles podem causar problemas de desempenho, especialmente à medida que as expressões ficam mais complexas, os aplicativos se tornam mais exigentes e os volumes de dados continuam a crescer.
Uma coluna calculada é uma coluna virtual cujo valor é calculado com base nos valores de outras colunas da tabela. Por padrão, o valor calculado não é armazenado fisicamente, mas, em vez disso, o SQL Server o calcula em cada solicitação de coluna. Isso aumenta a carga no processador, mas reduz a quantidade de dados que precisam ser retidos quando a tabela muda.
Freqüentemente, as colunas computadas não persistentes consomem muita CPU, tornando as consultas mais lentas e congelando os aplicativos. Felizmente, o SQL Server oferece várias maneiras de melhorar o desempenho das colunas computadas. Você pode criar colunas computadas persistentes, indexá-las ou fazer ambos.
Para a demonstração, criei quatro tabelas semelhantes e preenchi-as com dados idênticos do banco de dados de demonstração WideWorldImporters. Cada tabela tem a mesma coluna computada, mas duas tabelas têm persistente e duas têm um índice. O resultado são as seguintes opções:
- A tabela
Orders1é uma coluna computada não salva. - A tabela
Orders2é uma coluna calculada persistente. - A tabela
Orders3é uma coluna computada não persistente com um índice. - A tabela
Orders4é uma coluna computada persistente com um índice.
A expressão calculada é bastante simples e o conjunto de dados é muito pequeno. No entanto, deve ser suficiente demonstrar os princípios de colunas computadas persistentes e indexadas e como isso ajuda a resolver problemas de desempenho.
Coluna computada não salva
Talvez na sua situação você queira colunas computadas não persistentes para evitar o armazenamento de dados, a criação de índices ou para uso com uma coluna não determinística. Por exemplo, o SQL Server tratará uma UDF escalar como não determinística se WITH SCHEMABINDING estiver faltando na definição da função. Se você tentar criar uma coluna persistente calculada usando esta função, obterá um erro informando que a coluna persistente não pode ser criada.
No entanto, deve-se observar que as funções personalizadas podem criar seus próprios problemas de desempenho. Se a tabela contiver uma coluna calculada com uma função, o Query Engine não usará simultaneidade (a menos que você esteja usando o SQL Server 2019). Mesmo em uma situação em que a coluna calculada não é especificada na consulta. Para um grande conjunto de dados, isso pode ter um grande impacto no desempenho. As funções também podem desacelerar a execução de UPDATEs e afetar como o otimizador calcula o custo de uma consulta em uma coluna computada. Isso não significa que você nunca deve usar funções em uma coluna computada, mas definitivamente deve ser tratado com cuidado.
Quer você use funções ou não, a criação de uma coluna computada não persistente é bastante simples. Próxima instrução
CREATE TABLEdefine uma tabela Orders1que inclui uma coluna calculada Cost.
USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Orders1;
GO
CREATE TABLE Orders1(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit));
INSERT INTO Orders1 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;Para definir uma coluna calculada, especifique seu nome seguido pela palavra-chave e expressão AS. No nosso exemplo, nós estamos multiplicando
Quantitypor Pricee subtraindo Profit. Depois de criar a tabela, nós a preenchemos com INSERT usando dados da Sales.InvoiceLinestabela do banco de dados WideWorldImporters. Em seguida, executamos SELECT.
SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;Esta consulta deve retornar 22.973 linhas, ou todas as linhas que você possui no banco de dados WideWorldImporters. O plano de execução para esta consulta é mostrado na Figura 1.
Figura 1. O plano de execução para a consulta na tabela Orders1
A primeira coisa a observar é a Clustered Index Scan, que não é uma maneira eficiente de obter os dados. Mas este não é o único problema. Vamos dar uma olhada no número de leituras lógicas (leituras lógicas reais) nas propriedades do Clustered Index Scan (consulte a figura 2).
Figura 2. Leituras lógicas para consultar a tabela Pedidos1
O número de leituras lógicas (neste caso 1108) é o número de páginas que foram lidas do cache de dados. O objetivo é tentar reduzir ao máximo esse número. Portanto, é útil lembrar e comparar com outras opções.
O número de leituras lógicas também pode ser obtido executando a instrução
SET STATISTICS IO ONantes de executar o SELECT. Para visualizar a CPU e o tempo total - SET STATISTICS TIME ONou visualizar as propriedades da instrução SELECT no plano de execução da consulta.
Outro ponto digno de nota é que existem duas instruções Compute Scalar no plano de execução. O primeiro (o da direita) é o cálculo do valor da coluna calculada para cada linha retornada. Como os valores das colunas são calculados instantaneamente, você não pode evitar esta etapa com colunas calculadas não persistentes, a menos que crie um índice nessa coluna.
Em alguns casos, uma coluna computada não persistente fornece o desempenho necessário sem armazená-lo ou usar um índice. Isso não apenas economiza espaço de armazenamento, mas também evita a sobrecarga associada à atualização de valores calculados em uma tabela ou índice. No entanto, na maioria das vezes, uma coluna computada não persistente leva a problemas de desempenho e, então, você deve começar a procurar uma alternativa.
Coluna calculada persistente
Uma técnica frequentemente usada para resolver problemas de desempenho é definir uma coluna computada como persistente. Com essa abordagem, a expressão é calculada antecipadamente e o resultado é armazenado junto com o restante dos dados da tabela.
Para que uma coluna seja persistente, ela deve ser determinística, ou seja, a expressão deve sempre retornar o mesmo resultado para a mesma entrada. Por exemplo, você não pode usar a função GETDATE em uma expressão de coluna, porque o valor de retorno está sempre mudando.
Para criar uma coluna calculada persistente, você deve adicionar uma palavra-chave à definição da coluna
PERSISTED, conforme mostrado no exemplo a seguir.
DROP TABLE IF EXISTS Orders2;
GO
CREATE TABLE Orders2(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit) PERSISTED);
INSERT INTO Orders2 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;A tabela é
Orders2quase idêntica à tabela Orders1, exceto que a coluna Costcontém a palavra-chave PERSISTED. O SQL Server preenche automaticamente esta coluna conforme as linhas são adicionadas ou modificadas. Claro, isso significa que a mesa Orders2ocupará mais espaço do que a mesa Orders1. Isso pode ser verificado usando um procedimento armazenado sp_spaceused.
sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GOA Figura 3 mostra a saída desse procedimento armazenado. O tamanho dos dados na tabela
Orders1é 8.824 KB e na tabela Orders2- 12.936 KB. 4 112 KB a mais para armazenar os valores calculados.
Figura 3. Comparação do tamanho das tabelas Pedidos1 e Pedidos2
Embora esses exemplos sejam baseados em um conjunto de dados relativamente pequeno, você pode ver como a quantidade de dados armazenados pode crescer rapidamente. No entanto, isso pode ser uma compensação se o desempenho melhorar.
Para ver a diferença no desempenho, faça o seguinte SELECT.
SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;Este é o mesmo SELECT que usei para a tabela Pedidos1 (exceto para a mudança de nome). A Figura 4 mostra o plano de execução.
Figura 4. Plano de execução para uma consulta na tabela Orders 2.
Isso também começa com o Clustered Index Scan. Mas, desta vez, há apenas uma instrução Compute Scalar porque as colunas computadas não precisam mais ser computadas em tempo de execução. Em geral, quanto menos etapas, melhor. Embora nem sempre seja esse o caso.
A segunda consulta gerou 1593 leituras lógicas, o que representa 485 a mais do que 1108 leituras para a primeira tabela. Apesar disso, ele é executado mais rápido que o primeiro. Embora apenas cerca de 100 ms, e às vezes muito menos. O tempo do processador também diminuiu, mas também não muito. Provavelmente, a diferença seria muito maior em volumes maiores e cálculos mais complexos.
Índice em coluna computada não persistente
Outra técnica comumente usada para melhorar o desempenho de uma coluna computada é a indexação. Para poder criar um índice, a coluna deve ser determinística e precisa, o que significa que a expressão não pode usar os tipos float e real (se a coluna não for persistente). Existem também restrições em outros tipos de dados, bem como nos parâmetros SET. Para obter uma lista completa de restrições, consulte a documentação do SQL Server, Índices em Colunas Computadas .
Você pode verificar se uma coluna computada não persistente é adequada para indexação por meio de suas propriedades. Vamos usar a função para visualizar as propriedades
COLUMNPROPERTY. As propriedades IsDeterministic, IsIndexable e IsPrecise são importantes para nós.
DECLARE @id int = OBJECT_ID('dbo.Orders1')
SELECT
COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic',
COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable',
COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';A instrução SELECT deve retornar 1 para cada propriedade para que a coluna calculada possa ser indexada (consulte a Figura 5).
Figura 5. Verificando se o índice
pode ser criado Após a verificação, você pode criar um índice não clusterizado. Em vez de modificar a tabela,
Orders1criei uma terceira tabela ( Orders3) e incluí o índice na definição da tabela.
DROP TABLE IF EXISTS Orders3;
GO
CREATE TABLE Orders3(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit),
INDEX ix_cost3 NONCLUSTERED (Cost, ItemID));
INSERT INTO Orders3 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;Eu criei um índice de cobertura não agrupado que inclui colunas de
ItemIDe Costpara uma consulta SELECT. Depois de criar e preencher a tabela e o índice, você pode executar a seguinte instrução SELECT semelhante aos exemplos anteriores.
SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;A Figura 6 mostra o plano de execução para esta consulta, que agora usa o índice não clusterizado ix_cost3 (Index Seek) em vez de executar uma varredura de índice clusterizado.
Figura 6. Plano de execução para uma consulta na tabela Orders3
Se você observar as propriedades da instrução Index Seek, descobrirá que a consulta agora executa apenas 92 leituras lógicas e, nas propriedades da instrução SELECT, verá que a CPU e o tempo total diminuíram. A diferença não é significativa, mas, novamente, este é um pequeno conjunto de dados.
Também deve ser observado que há apenas uma instrução Compute Scalar no plano de execução, não duas como na primeira consulta. Como a coluna calculada está indexada, os valores já foram calculados. Isso elimina a necessidade de calcular valores em tempo de execução, mesmo se a coluna não tiver sido definida para ser persistente.
Índice na coluna armazenada
Você também pode criar um índice na coluna computada que está salvando. Embora isso resulte no armazenamento de dados adicionais e dados de índice, pode ser útil em alguns casos. Por exemplo, você pode criar um índice em uma coluna computada persistente, mesmo se ela usar tipos de dados flutuantes ou reais. Essa abordagem também pode ser útil ao trabalhar com funções CLR e quando você não pode verificar se as funções são determinísticas.
A declaração a seguir
CREATE TABLEcria uma tabela Orders4. A definição da tabela inclui uma coluna persistente Coste um índice de cobertura não clusterizado ix_cost4.
DROP TABLE IF EXISTS Orders4;
GO
CREATE TABLE Orders4(
LineID int IDENTITY PRIMARY KEY,
ItemID int NOT NULL,
Quantity int NOT NULL,
Price decimal(18, 2) NOT NULL,
Profit decimal(18, 2) NOT NULL,
Cost AS (Quantity * Price - Profit) PERSISTED,
INDEX ix_cost4 NONCLUSTERED (Cost, ItemID));
INSERT INTO Orders4 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;Depois que a tabela e o índice forem criados e preenchidos, execute SELECT.
SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;A Figura 7 mostra o plano de execução. Como no exemplo anterior, a consulta começa com uma pesquisa de índice não agrupado (Index Seek).
Figura 7. Plano de execução para uma consulta na tabela Orders4
Esta consulta também executa apenas 92 leituras lógicas como a anterior, o que resulta em aproximadamente o mesmo desempenho. A principal diferença entre as duas colunas calculadas e entre colunas indexadas e não indexadas é a quantidade de espaço usado. Vamos verificar isso executando o procedimento armazenado
sp_spaceused.
sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO
sp_spaceused 'Orders3';
GO
sp_spaceused 'Orders4';
GOOs resultados são mostrados na Figura 8. Como esperado, as colunas computadas armazenadas têm mais dados e as colunas indexadas têm mais índices.
Figura 8. Comparação de utilização de espaço para todas as quatro tabelas
Provavelmente, você não precisará indexar as colunas computadas armazenadas sem um bom motivo. Assim como com outras questões relacionadas ao banco de dados, sua escolha deve ser baseada em sua situação particular: suas consultas e a natureza de seus dados.
Trabalho com colunas calculadas no SQL Server
A coluna computada não é uma coluna de tabela regular e deve ser tratada com cuidado para evitar degradação do desempenho. A maioria dos problemas de desempenho pode ser resolvida através do armazenamento ou indexação da coluna, mas ambas as abordagens precisam considerar o espaço extra em disco e como os dados mudam. Quando os dados mudam, os valores da coluna calculada devem ser atualizados na tabela ou índice, ou ambos, se você indexou a coluna calculada persistente. Você só pode decidir qual opção é melhor para o seu caso específico. E, provavelmente, você terá que usar todas as opções.
Consulte Mais informação