Suponha que uma tabela contenha apenas números inteiros e tenha um índice columnstore agrupado.

CREATE TABLE cci_account
(  
    AccountKey int NOT NULL,  
    UnitSold int,
    INDEX ColStore CLUSTERED COLUMNSTORE
);

então parece que somar uma coluna agrupada por outra é sempre rápido em tal tabela, por exemplo

SELECT
    AccountKey,
    SUM(UnitSold)
FROM
    cci_account
GROUP BY
    AccountKey;

Por que isso é tão rápido? Meu entendimento da arquitetura columnstore é que os segmentos que contêm AccountKeynão têm ideia de qual UnitSoldvalor está em suas linhas. Como, então, o SQL Server consegue agrupar tão rapidamente por AccountKey? Não consigo imaginar nenhum algoritmo que consiga isso.

Contexto

Tenho experimentado particionamento de tabelas e encontrei muitas surpresas. Atualmente, estou lendo o máximo de documentação que posso, mas há uma quantidade chocante dela . Acho que estou no segundo de seis white papers. Não vi essa pergunta mencionada em nenhum lugar da documentação.

O Problema

Usando DROP_EXISTING = ON, parece que algumas tabelas podem ser particionadas no local. Por exemplo, posso fazê-lo funcionar com um índice clusterizado exclusivo. No entanto, não posso fazer o mesmo com uma chave primária. Este guia concorda com minha conclusão e diz que particionar uma tabela com uma chave primária no local requer a remoção da chave primária primeiro. É como se houvesse uma restrição de sintaxe em vez de uma de funcionalidade.

Minha pergunta é esta: Quando uma tabela pode ser particionada sem descartar nada antes do particionamento? Em outras palavras, quando o particionamento pode ser feito no local?

Não estou perguntando especificamente sobre o que pode ser feito com DROP_EXISTINGem particular. Esse foi apenas um método que encontrei.

Suponha o SQL Server 2022. Não me importa se a operação é online ou não. Tenho uma pergunta diferente para algo bem parecido.

Código de demonstração

Em grande parte roubado de Paul White

CREATE PARTITION FUNCTION PF (integer) 
AS RANGE RIGHT
FOR VALUES 
(
    10000, 20000, 30000, 40000, 50000
);
GO
CREATE PARTITION SCHEME PS 
AS PARTITION PF 
ALL TO ([PRIMARY]);
GO
CREATE TABLE dbo.T1
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
    
    CONSTRAINT PK_T1
    PRIMARY KEY CLUSTERED (c1, c2, c3)
);

CREATE TABLE dbo.T2
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
    
    INDEX UIX_T2 UNIQUE CLUSTERED (c1, c2, c3)
);
GO

CREATE TABLE dbo.T3
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL,
    
    INDEX UIX_T3 UNIQUE NONCLUSTERED (c1, c2, c3)
);
GO


/* These work */
CREATE CLUSTERED INDEX UIX_T2 ON dbo.T2 (c1, c2, c3)
WITH (DROP_EXISTING = ON) ON PS(C1) 
GO

CREATE NONCLUSTERED INDEX UIX_T3 ON dbo.T3 (c1, c2, c3)
WITH (DROP_EXISTING = ON) ON PS(C1) 
GO

/* But I could never get the primary key to! */

Acima, você pode particionar índices exclusivos no lugar. No entanto, nada que eu tenha pensado em tentar funciona para chaves primárias. Tenho quase certeza de que isso REBUILDtambém pode fazer isso.

Em inglês simples, "Processamento de consulta paralela em tabelas e índices particionados" e "Paralelismo de tabela particionada" parecem a mesma coisa. No entanto, no SQL Server 2022, a Standard Edition tem "Paralelismo de tabela particionada" , mas não Processamento de consulta paralela em tabelas e índices particionados . Como os dois recursos diferem?

Fiz um esforço razoável para pesquisar na documentação, mas ainda não li todos os white papers. Não consegui aprender o que é cada recurso.

Esta questão é a mais bem avaliada neste site sobre heaps. Ela pergunta sobre o caso de uso de heaps. No entanto, acredito que heaps muito grandes (por exemplo, milhares de gigabytes) são um caso especial que merece sua própria pergunta.

À medida que o tamanho do seu índice clusterizado cresce, também aumenta a penalidade do índice clusterizado , ou seja, o número de leituras lógicas necessárias para obter as chaves restantes dos seus índices não clusterizados. O mesmo não é verdade para um heap. Acredito que os heaps são injustamente descartados porque muitas pessoas aprenderam todo o design de seu banco de dados em data warehouses (onde varreduras de chave primária e, portanto, índices clusterizados, são geralmente uma boa ideia) ou nunca trabalharam em um banco de dados grande o suficiente para sentir o custo de ter uma tabela realmente grande.

Isso me faz perguntar: heaps muito grandes e bem indexados têm casos de uso práticos que não são encontrados em tabelas clusterizadas muito grandes e bem indexadas?

Com medo de tornar esta questão muito ampla, deixe as seguintes restrições se aplicarem. Suponha:

• Um heap de 1 TB
• Uma caixa da edição padrão
• Um ambiente OLTP ( não um data warehouse).

Cumulativamente, isso significa que você está limitado a 128 GB de RAM de pool de buffer e não fará varreduras grandes regularmente na chave primária. Ler todo o heap do disco será miserável, então quaisquer respostas sobre usar a tabela como uma tabela de preparação ou somente backup não devem se aplicar.

Configurar

CREATE TABLE persons
(
  person_id int not null,
  name TEXT 
);

INSERT INTO persons VALUES
(1, 'Adam'),
(2, 'Paul'),
(3, 'Tye'),
(4, 'Sarah');

CREATE TABLE json_to_parse
(
  person_id int not null,
  block json
);

INSERT INTO json_to_parse VALUES
  (1, '{"size": "small", "love": "x"}'),
  (2, '{"size": "medium", "love": "xx"}'),
  (3, '{"size": "big", "love": "xxx"}');

O erro

Isso funciona sem problemas

SELECT
  *
FROM
  json_to_parse
CROSS JOIN LATERAL
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

mas isso não

SELECT
  *
FROM
  json_to_parse,
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

e recebo o erro "referência inválida para a entrada da cláusula FROM para a tabela "json_to_parse""

Por que esse segundo erro de consulta? Os documentos deixam bem claro que LATERALé opcional para funções com valor de tabela

As funções de tabela que aparecem em FROMtambém podem ser precedidas pela palavra-chave LATERAL, mas para funções a palavra-chave é opcional; os argumentos da função podem conter referências a colunas fornecidas pelos FROMitens precedentes em qualquer caso.

dbfiddle

Tenho lido sobre os índices columnstore do Microsoft SQL Server recentemente e fiquei preocupado que minha experiência não corresponda à do setor. Os índices columnstore são projetados para tornar os agregados fáceis. É dito com muita frequência que, por esse motivo, eles são ideais para tabelas de fatos.

Isso me deixa desconfortável. Na minha experiência, tabelas de fatos não são agregadas . A lista de colunas para cada tabela de fatos que já fiz tem o formato "chave primária seguida por várias colunas de dimensão, seguidas por cada bit de informação que armazenamos sobre a chave primária". Elas então são despejadas em alguma ferramenta de BI como PowerBI ou ThoughtSpot, para que pessoas não técnicas possam filtrar com base nas dimensões e descobrir o que quiserem saber. Elas podem agregar de tempos em tempos, mas esse certamente não é o uso principal da tabela. Espero que essas tabelas tenham bem mais de 100 colunas.

O design da tabela e o padrão de acesso que estou descrevendo são o que se espera de uma tabela de fatos? Ou estou descrevendo um tipo de tabela totalmente diferente, cujo nome desconheço?

Estou em um servidor com grandes problemas de bloqueio. Não deadlocks, apenas bloqueios simples. Sabemos que é o gargalo principal do servidor. No entanto, eles não sabem quais são os líderes de bloco, então não sei quais consultas ajustar. Eu poderia instalar mais monitoramento, mas notei que eles têm o Query Store habilitado. Existe alguma informação no Query Store que pode ser usada para encontrar líderes de bloco?

Pelo que entendi, o SQL dinâmico faz parte de um lote. No entanto, quando listei para sqlserver.sql_batch_completedo seguinte

CREATE EVENT SESSION [CaptureBatch] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)
    WHERE ([sqlserver].[database_id]>(4))) 
ADD TARGET package0.event_file(SET filename=N'CaptureBatch',max_rollover_files=(0))
WITH (STARTUP_STATE=ON);

GO

ALTER EVENT SESSION [CaptureBatch] ON SERVER 
STATE = START;

Não consigo ver nenhuma chamada para EXECessa chamada também EXEC. Por exemplo, a linha final aqui não aparece no log do meu Evento Estendido.

CREATE PROCEDURE [TestThis]
AS
BEGIN
    SELECT 'Test';
END

GO

EXEC (N'EXEC TestThis');

Por que isso? Esses tipos EXECnão são considerados um lote ou estou esquecendo de algo?

Não consigo encontrar nenhuma documentação detalhada sobre esse evento, então me perdoe se esqueci de alguma.

Suponha que você tenha uma tabela que seja:

• Lido com tanta frequência que quase sempre fica na RAM.
• Muito pequeno.
• Quase nunca mudou.

Há alguma desvantagem em mover tal tabela para OLTP na memória? Meus pensamentos são que se ela estiver sempre na memória de qualquer maneira, então você deve considerar usar a arquitetura projetada para viver na memória.

O Read Committed Snapshot Isolation (RCSI) é bem compreendido. A principal maneira pela qual ele pode causar gargalos de desempenho é se as cadeias de versão ficarem muito longas. Existe algum tipo de espera que indique esse problema específico? Ou ele é mostrado apenas por outros tipos de espera (e se sim, quais?).

Frequentemente acho a saída sp_spaceusedenganosa. O que parece ser o tamanho de todos os dados em um banco de dados é, na verdade, o tamanho combinado do arquivo de dados e do arquivo de log . Descobri recentemente sys.database_filese achei que é superior em todos os sentidos. É muito melhor do que sp_spacedusedisso, estou planejando beber até esquecer esse procedimento.

Isso me dá uma pergunta: Ao verificar o tamanho de um banco de dados, há algum recurso ou valor de retorno oferecido por sp_spacedusedque não pode ser obtido de sys.database_files? Eu verifiquei a documentação e tenho quase certeza de que não há.

Hoje, estou verificando cada tabela em um banco de dados enorme e escrevendo um resumo do que cada uma é. Isso significa que estou clicando com o botão direito e pressionando "Selecionar as 1000 principais linhas" muitas vezes. Existe uma maneira mais rápida? Eu preferiria uma guia por tabela, então truques de SQL provavelmente estão fora de questão. O que eu quero fazer é aumentar a velocidade com que estou navegando no SSMS.

Pro SQL Server Relational Database Design and Implementation: Best Practices for Scalability and Performance é uma fonte muito confiável de boas ideias de design de banco de dados. Como alternativa ao Entity-Attribute-Value, ele sugere usar SQL dinâmico para permitir que seus usuários adicionem novas colunas esparsas a uma tabela.

Para mim, essa ideia fede. Adicionar novas colunas requer um bloqueio de modificação de esquema. Esse é um bloqueio muito sério e eu preferiria que meus usuários não tivessem a capacidade de obtê-lo.

Existe alguma propriedade de colunas esparsas que faz com que permitir que os usuários assumam um bloqueio tão sério não seja tão terrível quanto seria para outros tipos de coluna?

Ao executar sob READ COMMITTED(o tipo pessimista, não RCSI), acabei de ver um UPDATEdeadlock de bloqueio IX de 's com um SELECTbloqueio compartilhado de 's no PAGEnível -. Eles SELECTexecutaram com um plano de execução paralelo. Nenhum deles usou explicitamente uma transação. Entendo que UPDATEs implicitamente criam transações. Isso sugere que SELECTs paralelos também criam transações implícitas? Isso não é verdade para s não paralelos SELECT?

Estou achando isso um tanto confuso, porque nenhum dos exemplos de deadlocks nos livros didáticos mostra duas consultas sem transação gerando deadlocks.

Eu uso compactação de backup em todos os lugares. Recentemente, descobri que meus backups estavam ficando um pouco grandes demais. Decidi consertar isso compactando os índices no banco de dados (normalmente PAGEcompactação). O banco de dados ficou muito menor. Mas, para minha surpresa, os backups completos não ficaram menores!

Esse é um comportamento esperado?

Claramente tenho um equívoco, mas nenhuma quantidade de documentação de consultoria resolveu isso. Considere o seguinte argumento:

1. Geralmente nos preocupamos em unir heaps, porque classificá-los costuma ser mais caro do que ter um índice agrupado.
2. Junções hash não requerem classificações.
3. Do exposto acima, concluímos que você não deve se preocupar em unir heaps se você sempre puder obter (ou forçar) uma junção hash.

O desdém popular por montes sugere que o que foi dito acima está errado, mas onde está errado?

Suponha que meu servidor tenha um trabalho que às vezes reconstrói índices com base em fatores que não controlo (por exemplo, fragmentação). Suponha também que tenho certeza de que um dia ele reconstruirá um índice que estou interessado em alterar. Existe alguma maneira de instruir o SQL Server que eu quero alterar o WITHíndice de um índice em sua próxima reconstrução, sem reconstruí-lo agora?

Por exemplo, suponha que ele PK_HugeTableesteja definido para preencher o fator 25. Na próxima reconstrução, quero defini-lo como 100. Se eu não quiser reconstruir o índice agora, o que devo fazer?

ALTER INDEX [...] SETfunciona para algumas opções, mas não para o fator de preenchimento.

Tenho uma chave primária clusterizada descompactada. Ela consome 63,6 GB na parte principal do índice clusterizado com 17,9 GB LOB. O único índice não clusterizado da tabela é 57,3 GB também com 17,9 GB LOB. Desejo reconstruir o índice clusterizado offline sem que o banco de dados cresça. Executar sp_spaceusedsem argumentos relata 66 GB em sua coluna "espaço não alocado".

O que eu desejo fazer é possível? Ou reconstruir o índice compactado forçará uma reconstrução do índice não clusterizado, ocupando assim pelo menos 100 GB?

Apesar do meu melhor esforço, não encontrei uma resposta nem na documentação nem no Stack Overflow. Meus experimentos com um pequeno banco de dados que eu tinha à mão sugerem que reconstruir o índice clusterizado não força uma reconstrução dos índices não clusterizados.

Um antigo artigo da Microsoft diz para considerar o uso de ROWcompactação por padrão se você tiver muito espaço na CPU (ênfase minha).

Se a compactação de linha resultar em economia de espaço e o sistema puder acomodar um aumento de 10% no uso da CPU, todos os dados deverão ser compactados por linha

Hoje, temos ainda mais espaço de CPU do que tínhamos quando o artigo foi escrito. Homens sábios disseram que deveríamos considerar usar PAGEcompressão em todos os lugares, a menos que tenhamos uma razão convincente para não fazê-lo.

Tudo isso é um bom conselho e muitas vezes vejo sp_estimate_data_compression_savingsque concordam. No entanto, o que deve ser feito com tabelas minúsculas que são acessadas com frequência? Por exemplo, tenho algumas tabelas de dimensão extremamente pequenas. 100 linhas no máximo e pouquíssimas colunas. Como são tão pequenas, o benefício de economia de espaço de qualquer compactação é mínimo. Qual é considerada a melhor prática para aplicar ROWou PAGEcompactar tabelas tão minúsculas em caixas que têm uma quantidade enorme de espaço livre na CPU?

Para os propósitos desta questão, ignore columnstore. Estamos falando apenas de índices rowstore da velha escola em tabelas baseadas em disco.

Suponha que eu tenha uma tabela com muitas colunas com as quais não me importo, mas duas com as quais me importo: Primarye Secondary. Há uma chave primária agrupada em Primary.

CREATE TABLE [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary] INT PRIMARY KEY CLUSTERED,
    [Secondary] NVARCHAR(500) NOT NULL,
    [Evil1] NVARCHAR(MAX),
    [Evil2] NVARCHAR(MAX),
    [Evil3] NVARCHAR(MAX),
    [Evil4] NVARCHAR(MAX),
    [Evil5] NVARCHAR(MAX)
);

Como não me importo com nenhuma das outras colunas, decido que quero este índice:

CREATE NONCLUSTERED INDEX [IX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

Dado que a lista de chaves deste índice contém a chave primária, não há dúvidas de que a lista de chaves é única. Assim, estou tentado a torná-la UNIQUE.

CREATE UNIQUE NONCLUSTERED INDEX [UIX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

Mas isso tem algum benefício? E o que dizer do mesmo caso exato, mas com o Secondarycomo a chave de índice principal seguido por Primary?