Consulta de particionamento

Ok, então aqui está um exemplo rápido demonstrando por que - no caso em que a maioria de suas operações (consultas de relatórios, operações de arquivamento, trocas de partição, etc.) identificará intervalos de linhas por data - é melhor agrupar na coluna de particionamento. Vamos ter um esquema e função simples de partição baseada em data:

CREATE PARTITION FUNCTION DateRange (DATE)
AS RANGE RIGHT FOR VALUES ('20150101');
GO

CREATE PARTITION SCHEME DateRangeScheme
AS PARTITION DateRange ALL TO ([PRIMARY]);
GO

Em seguida, duas tabelas - uma com uma PK agrupada em ID, Data e um índice não agrupado em Data, e outra com uma PK não agrupada em ID, Data e um índice agrupado em Data.

CREATE TABLE dbo.PKClustered
(
  ID INT, 
  dt DATE, 
  filler CHAR(4000)
   CONSTRAINT df_filler_c DEFAULT '' NOT NULL, 
  CONSTRAINT pk_clust PRIMARY KEY CLUSTERED (ID,dt)
);
CREATE INDEX dt ON dbo.PKClustered(dt) ON DateRangeScheme(dt);

CREATE TABLE dbo.PKNonClustered
(
  ID INT, 
  dt DATE, 
  filler CHAR(4000)
   CONSTRAINT df_filler_nc DEFAULT '' NOT NULL, 
  CONSTRAINT pk_nonclust PRIMARY KEY NONCLUSTERED (ID,dt)
);
CREATE CLUSTERED INDEX dt ON dbo.PKNonClustered(dt) ON DateRangeScheme(dt);

Agora preencha-os com alguns dados:

INSERT dbo.PKClustered(ID, dt) SELECT TOP (100) Number, '20141231'
  FROM master.dbo.spt_values WHERE [type] = N'P' ORDER BY Number;

INSERT dbo.PKClustered(ID, dt) SELECT TOP (50) Number, '20150101'
  FROM master.dbo.spt_values WHERE [type] = N'P' ORDER BY Number DESC;

INSERT dbo.PKNonClustered(ID, dt) SELECT ID, dt FROM dbo.PKClustered;

Portanto, devemos ter 100 linhas na partição 1 e 50 linhas na partição 2, certo? sys.partitionsconfirma:

SELECT [table] = o.name, [index] = i.name, 
  p.partition_number, p.[rows]
FROM sys.tables AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN sys.partitions AS p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name LIKE N'PK%Clustered'
ORDER BY o.name, i.name;

Resultados:

Observe que em ambos os casos os dados no PK são todos armazenados em uma única partição. Como isso afeta as consultas? Bem, considere estes quatro, que provavelmente são típicos (além do SELECT *, usado apenas para concisão):

SELECT * FROM dbo.PKClustered WHERE dt >= '20150101';
SELECT * FROM dbo.PKNonClustered WHERE dt >= '20150101';

DELETE dbo.PKClustered WHERE dt >= '20140101' AND dt < '20150101';
DELETE dbo.PKNonClustered WHERE dt >= '20140101' AND dt < '20150101';

Aqui estão alguns resultados do SQL Sentry Plan Explorer :*

Custos estimados e métricas reais de tempo de execução:

O SELECT *contra o PK não clusterizado executou uma busca de índice clusterizado eficiente, acessando apenas uma única partição:

Quando o PK está em cluster, ele decide executar uma varredura de índice em cluster, o que significa que não pode eliminar partições, levando a mais leituras e, portanto, a um custo de E/S mais alto. Interessante notar, também, que a varredura não é ordenada.

Coisas semelhantes acontecem com o delete. A parte mais cara da operação de exclusão em ambos os casos é a exclusão do índice clusterizado; ter o benefício da eliminação de partições torna o PK não clusterizado muito mais desejável para suportar esta operação (mesmo que, no final das contas, as leituras necessárias e superiores sejam aproximadamente as mesmas).

Com o PK clusterizado, as linhas de origem são encontradas com uma busca (que você pode esperar ser mais eficiente), mas, novamente, a maior parte do trabalho é executada pela exclusão subsequente, portanto, pelo menos neste tamanho, não tem muito impacto em tudo:

Agora, em volumes muito mais altos, essa varredura inicial pode acabar inclinando a balança na outra direção, então você terá que testar.

É claro que, nesse limite inferior, isso tem um impacto negativo nas consultas de linha única em que você identifica por ID, pois normalmente identifica a linha por uma busca de índice e, em seguida, precisa fazer uma pesquisa, em vez de uma busca de índice clusterizado único. Vamos considerar essas duas consultas (novamente, em relação a SELECT *, faça o que eu digo, não o que eu faço):

SELECT * FROM dbo.PKClustered WHERE ID = 2045;
SELECT * FROM dbo.PKNonClustered WHERE ID = 2045;

Resultados do Plan Explorer:

O primeiro é simples, precisa apenas de uma busca de índice clusterizado (e, portanto, sem pesquisas):

Mas, como mencionado, o segundo decide uma busca não particionada contra o PK, mas uma pesquisa de chave particionada . Nesse caso, isso acaba sendo mais caro, mas nem sempre, e nem sempre pode ser a escolha do otimizador.

O mesmo tipo de coisa pode acontecer com certas consultas de junção, dependendo de quantas linhas e como a junção é construída.

E, novamente, as escolhas do otimizador aqui geralmente dependem do volume. Enfim: depende . Minha escolha com as informações que você forneceu seria agrupar na chave de particionamento e usar um PK não agrupado. E eu evitaria fortemente usar um GUID para esse ID em ambos os casos - embora essa distribuição possa ser boa para inserções se você estiver tentando inserir 8 bilhões de linhas por segundo, isso não ajudará em nada mais que você esteja fazendo.

Outra opção é usar um único PK combinado em Date primeiro, depois ID:

CREATE TABLE dbo.PKCombined
(
  ID INT, 
  dt DATE, 
  filler CHAR(4000)
   CONSTRAINT df_filler_comb DEFAULT '' NOT NULL, 
  CONSTRAINT pk_comb PRIMARY KEY CLUSTERED (dt,ID) ON DateRangeScheme(dt)
);

Isso obviamente resulta em menos linhas sendo armazenadas em menos páginas (nenhum índice não clusterizado para manter, por exemplo):

SELECT [table] = o.name, 
  [rows] = SUM(row_count), 
  [pages] = SUM(used_page_count),
  [size_in_kb] = 8.192*SUM(used_page_count)
FROM sys.tables AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_partition_stats AS p
ON i.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name LIKE N'PK%'
GROUP BY o.name
ORDER BY o.name;

Resultados:

Mas como isso afeta essas outras consultas? O SELECT *é idêntico ao da SELECT *versão PK sem cluster; uma busca simples de índice clusterizado. O DELETE, no entanto, é um plano muito mais simples:

A busca de linha única, no entanto, acaba sendo muito mais cara:

Provavelmente, você pode combater isso com um índice de cobertura não clusterizado no ID, que converteria a varredura em uma busca (com uma pesquisa se o índice não for de cobertura), mas ainda não se beneficiaria da eliminação da partição.

* Isenção de responsabilidade: eu trabalho para o SQL Sentry.