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

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

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

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.

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

Conceitos