Aperfeiçoamentos de processamento de consultas em tabelas e índices particionados

O SQL Server 2008 melhora o desempenho do processamento de consultas em tabelas particionadas para muitos planos paralelos, altera a maneira como os planos paralelos e seriais são representados e aperfeiçoa as informações de particionamento fornecidas nos planos de execução de tempo de compilação e tempo de execução. Este tópico descreve esses aperfeiçoamentos, fornece orientação sobre como interpretar os planos de execução de consultas de tabelas e índices particionados e fornece as práticas recomendadas para aperfeiçoar o desempenho de consultas em objetos particionados.

Observação
Há suporte para tabelas e índices particionados apenas nas edições Enterprise, Developer e Evaluation do SQL Server.

Nova operação de busca que reconhece a partição

No SQL Server 2008, a representação interna de uma tabela particionada é alterada para que a tabela pareça ser um índice multicolunas para o processador de consulta, tendo PartitionID como a coluna principal. PartitionID é uma coluna computada oculta usada internamente para representar a ID da partição que contém uma linha específica. Por exemplo, suponha que a tabela T, definida como T(a, b, c), seja particionada na coluna a e tenha um índice clusterizado na coluna b. No SQL Server 2008, essa tabela particionada é tratada internamente como uma tabela não particionada com o esquema T(PartitionID, a, b, c) e um índice clusterizado na chave composta (PartitionID, b). Isso permite que o otimizador de consulta execute operações de busca baseadas em PartitionID em qualquer tabela ou índice particionado.

Agora a eliminação de partição está concluída na operação de busca.

Além disso, o otimizador de consulta é estendido para que uma operação de busca ou de exame com uma condição possa ser realizada em PartitionID (como a coluna lógica principal) e possivelmente em outras colunas de chave de índice e, depois, uma busca de segundo nível, com uma condição diferente, possa ser realizada em uma ou mais colunas adicionais, para cada valor diferente que atenda à qualificação para a operação de busca de primeiro nível. Ou seja, essa operação, chamada de busca seletiva, permite que o otimizador de consulta realize uma operação de busca ou de exame baseada em uma condição para determinar as partições a serem acessadas e uma operação de busca de segundo nível naquele operador para retornar linhas dessas partições que atendam a uma condição diferente. Por exemplo, considere a consulta abaixo.

SELECT * FROM T WHERE a < 10 and b = 2;

Para esse exemplo, suponha que a tabela T, definida como T(a, b, c), é particionada na coluna a e tem um índice clusterizado na coluna b. Os limites de partição da tabela T estão definidos pela seguinte função de partição:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Para solucionar a consulta, o processador de consulta realiza uma operação de busca de primeiro nível para encontrar todas as partições que contenham linhas que atendam à condição T.a < 10. Isso identifica as partições a serem acessadas. Em cada partição identificada, o processador realiza uma busca de segundo nível no índice clusterizado na coluna b para encontrar as linhas que atendem à condição T.b = 2 e T.a < 10.

A ilustração a seguir é uma representação lógica da operação de busca seletiva. Ela mostra a tabela T com dados nas colunas a e b. As partições são numeradas de 1 a 4 com os limites de partição mostrados por linhas verticais tracejadas. Uma operação de busca de primeiro nível nas partições (não mostrada na ilustração) determinou que as partições 1, 2 e 3 atendem à condição de busca implícita pelo particionamento definido para a tabela e o predicado na coluna a. Ou seja, T.a < 10. O caminho atravessado pela parte da busca de segundo nível da operação de busca seletiva é ilustrado pela linha curva. Essencialmente, a operação de busca seletiva procura, em cada uma destas partições, por linhas que atendam à condição b = 2. O custo total da operação de busca seletiva é igual ao de três buscas de índice separadas.

Exibindo informações sobre particionamento em planos de execução de consultas

Os planos de execução de consultas em tabelas e índices particionados podem ser examinados usando SET SHOWPLAN_XML ou SET STATISTICS XML das instruções SET do Transact-SQL ou usando a saída do plano de execução gráfica no SQL Server Management Studio. Por exemplo, é possível exibir o plano de execução de tempo de compilação clicando no botão Exibir Plano de Execução Estimado na barra de ferramentas do Editor de Consultas e exibir o plano de tempo de execução, clicando no botão Incluir Plano de Execução Real.

Usando essas ferramentas, você pode averiguar as seguintes informações:

• As operações como exames, buscas, inserções, atualizações, mesclagens e exclusões que acessam tabelas ou índices particionados.

• As partições acessadas pela consulta. Por exemplo, a contagem total de partições acessadas e os intervalos de partições contíguas que são acessadas estão disponíveis nos planos de execução de tempo de execução.

• Quando a operação de busca seletiva é usada em uma operação de busca ou de exame para recuperar dados de uma ou mais partições.

Para obter mais informações sobre planos de execução, consulte Tópicos de instruções de plano de execução.

Aprimoramentos das informações sobre partições

O SQL Server 2008 fornece informações aperfeiçoadas de particionamento para planos de execução de tempo de compilação e tempo de execução. Agora, os planos de execução fornecem as seguintes informações:

• Um atributo opcional Partitioned que indica que um operador, como busca, exame, inserção, atualização, mesclagem ou exclusão, é executado em uma tabela particionada.

• Um novo elemento SeekPredicateNew com um subelemento SeekKeys que inclui PartitionID como a coluna de chave de índice à esquerda e as condições de filtro que especificam buscas de intervalo em PartitionID. A presença de dois subelementos SeekKeys indica que uma operação de busca seletiva no PartitionID é usada.

• Informações resumidas que fornecem uma contagem total das partições acessadas. Essas informações só estão disponíveis em planos de tempo de execução.

Para demonstrar como essas informações são exibidas tanto na saída do plano de execução gráfica, quanto na saída do Plano de Execução XML, considere a seguinte consulta na tabela particionada fact_sales. Esta consulta atualiza dados em duas partições.

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

A ilustração a seguir mostra as propriedades do operador Clustered Index Seek no plano de execução de tempo de compilação para essa consulta. Para exibir a definição da tabela fact_sales e a definição de partição, consulte "Exemplo" neste tópico.

Atributo particionado

Quando um operador como um Index Seek é executado em uma tabela ou índice particionado, o atributo Partitioned é exibido no plano de tempo de compilação e de tempo de execução e é definido como True (1). O atributo não é exibido quando é definido como False (0).

O atributo Partitioned pode ser exibido nos seguintes operadores físicos e lógicos:

• Table Scan

• Index Scan

• Index Seek

• Insert

• Update

• Delete

• Merge

Conforme mostrado na ilustração anterior, esse atributo é exibido nas propriedades do operador no qual ele é definido. Na saída Plano de Execução XML, esse atributo é exibido como Partitioned="1" no nó RelOp do operador no qual é definido.

Novo predicado de busca

Na saída Plano de Execução XML, o elemento SeekPredicateNew aparece no operador no qual está definido. Ele pode conter até duas ocorrências de subelemento SeekKeys. O primeiro item SeekKeys especifica a operação de busca de primeiro nível no nível da ID da partição do índice lógico. Ou seja, essa busca determina as partições que devem ser acessadas para atender as condições da consulta. O segundo item SeekKeys especifica a parte de busca de segundo nível da operação de busca seletiva que ocorre em cada partição identificada na busca de primeiro nível.

Informações resumidas sobre partições

Nos planos de execução de tempo de execução, as informações resumidas sobre partições fornecem uma contagem das partições acessadas e da identidade das partições reais acessadas. É possível usar essas informações para verificar se as partições corretas são acessadas na consulta e se todas as outras partições são eliminadas do exame.

As seguintes informações são fornecidas: Contagem Real de Partições e Partições Acessadas.

Contagem Real de Partições é o número total de partições acessadas pela consulta.

Partições Acessadas, na saída Plano de Execução XML, são as informações resumidas sobre as partições que aparecem no novo elemento RuntimePartitionSummary no nó RelOp do operador no qual ele é definido. O exemplo a seguir demonstra o conteúdo do elemento RuntimePartitionSummary, indicando que é acessado o total de duas partições (partições 2 e 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Exibindo informações sobre partições usando outros métodos de Plano de Execução

Os métodos de Plano de Execução SHOWPLAN_ALL, SHOWPLAN_TEXT e STATISTICS PROFILE não reportam as informações sobre partições descritas neste tópico, com a seguinte exceção: Como parte do predicado SEEK, as partições a serem acessadas são identificadas por um predicado de intervalo na coluna computada representando a ID da partição. O exemplo a seguir mostra o predicado SEEK para um operador Clustered Index Seek. As partições 2 e 3 são acessadas e o operador de busca filtra as linhas que atendem à condição date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))

                ORDERED FORWARD)

Interpretando planos de execução para heaps particionados

Em SQL Server 2008, um heap particionado é tratado como um índice lógico na ID da partição. A eliminação de partição em um heap particionado é representada em um plano de execução como um operador Table Scan com um predicado SEEK em ID de partição. O exemplo a seguir mostra as informações fornecidas sobre o Plano de Execução:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpretando planos de execução para junções colocadas

Uma colocação de junção pode ocorrer quando duas tabelas são particionadas usando a mesma função de particionamento ou função equivalente e as colunas de particionamento de ambos os lados da junção são especificadas na condição de junção da consulta. O otimizador de consulta pode gerar um plano em que as partições de cada uma das tabelas que tenham IDs de partição iguais sejam unidas separadamente. As junções colocadas podem ser mais rápidas que as junções não colocadas porque podem exigir menos memória e tempo de processamento. O otimizador escolhe um plano não colocado ou colocado com base em estimativas de custo.

Em um plano colocado, a junção Loops Aninhados lê uma ou mais partições de índice e tabela unidas a partir da parte interna. Os números dos operadores Constant Scan representam os números de partições.

Quando planos paralelos para junções colocadas são gerados para tabelas ou índices particionados, um operador Parallelism é exibido entre os operadores de junção Constant Scan e Nested Loops. Nesse caso, cada um dos vários threads da parte externa da junção lê e trabalha em uma partição diferente.

A ilustração a seguir demonstra um plano de consulta paralelo para uma junção colocada.

Estratégias de execução de consulta paralela para objetos particionados

O processador de consulta usa uma estratégia de execução paralela para consultas selecionadas a partir de objetos particionados. Como parte da estratégia de execução, o processador de consulta determina as partições de tabela necessárias para a consulta e a proporção de threads para alocar em cada partição. Na maioria dos casos, o processador de consulta aloca um número igual ou aproximado de threads para cada partição e executa a consulta paralelamente por meio das partições. Os parágrafos a seguir explicam a alocação de thread mais detalhadamente.

Se o número de threads é menor que o número de partições, o processador de consulta atribui cada thread a uma partição diferente, inicialmente deixando uma ou mais partições sem um thread atribuído. Quando um thread termina a execução em uma partição, o processador de consulta o atribui para a próxima partição até que tenha sido atribuído um único thread para cada partição. Esse é o único caso em que o processador de consulta realoca threads a outras partições.

Se o número de threads é igual ao número de partições, o processador de consulta atribui um thread para cada partição. Quando um thread é concluído, não é realocado a outra partição.

Se o número de threads é maior que o número de partições, o processador de consulta aloca um número igual de threads para cada partição. Se o número de threads não é um múltiplo exato do número de partições, o processador de consulta aloca um thread adicional a algumas partições, de forma a usar todos os threads disponíveis. Observe que se houver apenas uma partição, todos os threads serão atribuídos àquela partição. No diagrama a seguir, há quatro partições e 14 threads. Cada partição tem 3 threads atribuídos e duas partições têm um thread adicional para um total de 14 threads atribuídos. Quando um thread é concluído, não é reatribuído a outra partição.

Embora o exemplo anterior sugira um modo objetivo para alocar threads, a estratégia real é mais complexa e serve para outras variáveis que ocorrem durante a execução da consulta. Por exemplo, se a tabela estiver particionada e tiver um índice clusterizado na coluna A e uma consulta tiver a cláusula de predicado WHERE A IN (13, 17, 25),, o processador de consulta alocará um ou mais threads a cada um destes três valores de busca (A=13, A=17 e A=25) em vez de cada partição de tabela. Só será necessário executar a consulta nas partições que tiverem esses valores. Se todos esses predicados de busca estiverem na mesma partição de tabela, todos os threads serão atribuídos para a mesma partição de tabela.

Vejamos outro exemplo: suponha que a tabela tem quatro partições na coluna A com pontos de limite (10, 20, 30), um índice na coluna B e a consulta tem uma cláusula de predicado WHERE B IN (50, 100, 150). Como as partições de tabela são baseadas nos valores de A, os valores de B podem ocorrer em qualquer uma das partições de tabela. Dessa forma, o processador de consulta buscará cada um dos três valores de B (50, 100, 150) em cada uma das quatro partições de tabela. O processador de consulta atribuirá threads proporcionalmente para que possa executar cada um desses 12 exames de consulta em paralelo.

As partições de tabela baseadas na coluna A	Buscas para coluna B em cada partição de tabela
Partição de tabela 1: A < 10	B=50, B=100, B=150
Partição de tabela 2: A >= 10 AND A < 20	B=50, B=100, B=150
Partição de tabela 3: A >= 20 AND A < 30	B=50, B=100, B=150
Partição de tabela 4: A >= 30	B=50, B=100, B=150

Práticas recomendadas

Para melhorar o desempenho das consultas que acessam uma grande quantidade de dados de grandes tabelas e índices particionados, recomendamos as seguintes práticas:

• Distribuir cada partição em muitos discos.

• Quando possível, usar um servidor com memória principal suficiente para ajustar as partições acessadas com frequência ou todas as partições na memória para reduzir o custo de E/S.

• Se os dados que você consultar não se ajustarem na memória, compacte as tabelas e os índices. Isso reduzirá o custo de E/S.

• Usar um servidor com processadores rápidos e o máximo possível de núcleos de processador, para se beneficiar da capacidade de processamento de consultas paralelas.

• Verificar se o servidor tem largura de banda suficiente do controlador de E/S.

• Criar um índice clusterizado em todas as tabelas particionadas grandes para beneficiar-se de otimizações de exames da árvore B.

• Seguir as práticas recomendadas no documento "Loading Bulk Data into a Partitioned Table," quando estiver carregando dados em massa em tabelas particionadas.

Exemplo

O exemplo a seguir cria um banco de dados de teste que contém uma única tabela com sete partições. Use as ferramentas descritas anteriormente quando estiver executando as consultas descritas neste exemplo para exibir informações sobre particionamento para planos de tempo de compilação e de tempo de execução.

Observação
Este exemplo insere mais de 1 milhão de linhas na tabela. A execução deste exemplo pode demorar vários minutos, dependendo de seu hardware. Antes de executar este exemplo, verifique se há mais de 1.5 GB de espaço em disco disponível.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

Consulte também

Referência

Exibindo planos de execução gráfica (SQL Server Management Studio)

DBCC TRACEON (Transact-SQL)

Conceitos

Tabelas e índices particionados

Exibindo planos de execução usando Opções SET de plano de execução (Transact-SQL)