Partitioning – Como saber se o partition elimination está realmente funcionando no SQL Server 2005?

Olá,

Post rápido sobre partitioning.. mais precisamente sobre como avaliar se sua query está ou não, utilizando apenas a partições necessárias, ou seja, se a eliminação de partições (“partition elimination”) está sendo devidamente acionada pelo SQL Server.

Observação: testes realizados no SQL Server 2005.

Montando o cenário para testes:

/* cenário */
IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N’PS1′)
DROP PARTITION SCHEME [PS1]
GO

IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N’PF1′)
DROP PARTITION FUNCTION [PF1]
GO

CREATE PARTITION FUNCTION PF1 (int) AS RANGE FOR VALUES (100, 200, 300, 400);
GO

CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]);
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’T1′) AND type in (N’U'))
DROP TABLE T1
GO

CREATE TABLE T1 (a INT, b INT) ON PS1 (a);
GO

DECLARE @i INT; SET @i=1;
WHILE (@i <= 50)
BEGIN;
 INSERT INTO T1 VALUES (20 * @i, @i);
 SET @i = @i + 1;
END;
GO

/* verificando a configuração das partições */
SELECT $partition.PF1(a) [Partition Number], a, b from t1

/* verificando o conteúdo da tabela particionada */
SELECT * FROM t1; –// total of 50 rows

/* DMVs úteis para verificações diversas */
select OBJECT_NAME(object_id), * from sys.partitions;
select * from sys.partition_schemes;
select * from sys.partition_functions;
select * from sys.partition_parameters;
select * from sys.partition_range_values;

 Ao verificar a configuração da partição PF1 através da query abaixo, é possível verificar todas as partições (ou intervalos) resultados da criação da tabela T1 nos ranges 100, 200, 300 e 400:

/* verificando a configuração das partições */
SELECT $partition.PF1(a) [Partition Number], a, b from t1

Partition Number a b
1 20 1
1 40 2
1 60 3
1 80 4
1 100 5
2 120 6
2 140 7
2 160 8
2 180 9
2 200 10
3 220 11
…
5 840 42
5 860 43
5 880 44
5 900 45
5 920 46
5 940 47
5 960 48
5 980 49
5 1000 50

Analisando o resultado, podemos ver que foram criadas 5 partições (0 a 100, 100 a 200, 200 a 300, 300 a 400 e 400 a infinito).

Mas vamos começar os testes. Habilite as informações de profile: 

 SET STATISTICS PROFILE ON;

Para começar, vamos executar uma query que traga TODOS os registros, para ver como SQL Server se comporta:

SELECT * FROM t1;

Rows Executes StmtText
50 1 SELECT * FROM t1;
50 1   |–Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PARTITION ID:([PtnIds1004]))
5 1        |–Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5))))
50 5        |–Table Scan(OBJECT:([TRANS_Trigger].[dbo].[T1])) 

Veja que o SQL Server 2005 utiliza um “Nested Loop” para gerar os resultados. Note também no “Constant Scan” nos valores 1, 2, 3, 4 e 5 (nossas partições!) e um “Table Scan” para cada um dos valores no “Constant Scan”. Ou seja, um “Table Scan” por partição foi executado, para que todas as linhas fossem retornadas (exatas 50 linhas foram lidas). Observação: no SQL Server 2008, nem o Nested Loop nem o Constant Scan são mais utilizados, logo os resultados serão diferentes.

Agora vamos tentar utilizar uma partição específica:

SELECT * FROM t1 WHERE a = 300;

Rows Executes StmtText
1 1 SELECT * FROM [t1] WHERE [a]=@1
1 1   |–Table Scan(OBJECT:([TRANS_Trigger].[dbo].[T1]), WHERE:([TRANS_Trigger].[dbo].[T1].[a]=(300)) PARTITION ID:((3)))

Veja que apenas um “Table Scan” é gerado (não existe “Constant Scan”), e note que o SQL Server vai procurar direto na terceira partição (PARTITION ID:((3))), no range de 200 a 300. Neste momento o partition elimination já está funcionando!

Agora vamos executar outra query, que precise utilizar mais de uma partição (porém não todas elas!):

SELECT * FROM t1 WHERE a > 300 and a < 600;

Rows Executes StmtText
14 1 SELECT * FROM [t1] WHERE [a]>@1 AND [a]<@2
14 1   |–Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006]) PARTITION ID:([PtnIds1006]))
2 1        |–Constant Scan(VALUES:(((4)),((5))))
14 2        |–Table Scan(OBJECT:([TRANS_Trigger].[dbo].[T1]), WHERE:([TRANS_Trigger].[dbo].[T1].[a]>(300) AND [TRANS_Trigger].[dbo].[T1].[a]<(600)) PARTITION ID:([PtnIds1006])) 

Veja que no “Constant Scan”, apenas os valores 4 e 5 aparecem, ou seja, só estas duas partições foram acionadas. Apenas 14 linhas foram lidas.

E como saber que não está funcionando? Por exemplo, ao utilizar OR na cláusula WHERE, ao invés de apenas AND:

SELECT * FROM t1 WHERE a > 300 OR a < 350;

Rows Executes StmtText
50 1 SELECT * FROM t1 WHERE a > 300 OR a < 600;
50 1   |–Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1004]) PARTITION ID:([PtnIds1004]))
5 1        |–Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5))))
50 5        |–Table Scan(OBJECT:([TRANS_Trigger].[dbo].[T1]), WHERE:([TRANS_Trigger].[dbo].[T1].[a] IS NOT NULL) PARTITION ID:([PtnIds1004]))

Veja que em tese, apenas uma partição deveria ter sido utilizada (a com range entre 300 e 400), porém o SQL Server não entende o OR e acaba lendo TODAS as partições. No SQL Server 2008 isto ainda não havia sido corrigido. Farei uns testes no SQL 2012 para ver se isto não acontece mais e posto aqui os resultados.

Um abraço,
Raul Santos Neto