Início / dba / Perguntas / 1584
Accepted
bernd_k
Asked: 2011-03-06 08:59:41 +0800 CST

'Evite criar um índice clusterizado com base em uma chave de incremento' é um mito dos dias do SQL Server 2000?

  • 772

Nossos bancos de dados consistem em muitas tabelas, a maioria delas usando uma chave substituta inteira como chave primária. Cerca de metade dessas chaves primárias estão em colunas de identidade.

O desenvolvimento do banco de dados começou nos dias do SQL Server 6.0.

Uma das regras seguidas desde o início foi, conforme você encontra nestas dicas de otimização de índice :

Evite criar um índice clusterizado com base em uma chave de incremento.
Por exemplo, se uma tabela tiver uma chave primária substituta inteira declarada como IDENTITY e o índice clusterizado tiver sido criado nessa coluna, toda vez que os dados forem inseridos nessa tabela, as linhas serão adicionadas ao final da tabela. Quando muitas linhas forem adicionadas, pode ocorrer um "ponto de acesso". Um "ponto de acesso" ocorre quando muitas consultas tentam ler ou gravar dados na mesma área ao mesmo tempo. Um "ponto de acesso" resulta em gargalo de E/S.
Observação. Por padrão, o SQL Server cria um índice clusterizado para a restrição de chave primária. Portanto, nesse caso, você deve especificar explicitamente a palavra-chave NONCLUSTERED para indicar que um índice não clusterizado foi criado para a restrição de chave primária.

Agora, usando SQL Server 2005 e SQL Server 2008, tenho a forte impressão de que as circunstâncias mudaram. Enquanto isso, essas colunas de chave primária são as primeiras candidatas perfeitas para o índice clusterizado da tabela.

sql-server sql-server-2005

5 respostas

  1. Best Answer

    O mito remonta a antes do SQL Server 6.5, que adicionou bloqueio de nível de linha . E sugerido aqui por Kalen Delaney .

    Tinha a ver com "pontos de acesso" do uso da página de dados e o fato de que uma página inteira de 2k (SQL Server 7 e superior usa 8k páginas) foi bloqueada, em vez de uma linha inserida

    Artigo autorizado encontrado por Kimberly L. Tripp

    "O debate sobre o índice agrupado continua..."

    Hotspots eram algo que tentamos muito evitar ANTES do SQL Server 7.0 por causa do bloqueio no nível da página (e é aqui que o termo hot spot se tornou um termo negativo). Na verdade, não precisa ser um termo negativo. No entanto, como o mecanismo de armazenamento foi reprojetado/reprojetado (no SQL Server 7.0) e agora inclui bloqueio de nível de linha verdadeiro, essa motivação (para evitar pontos de acesso) não existe mais.

    O link na resposta de lucky7_2000 parece dizer que os pontos de acesso podem existir e causam problemas. No entanto, o artigo usa um índice clusterizado não exclusivo no TranTime. Isso requer que um unificador seja adicionado. O que significa que o índice não aumenta monotonicamente (e é muito amplo). O link nessa resposta não contradiz esta resposta ou meus links.

    Em um nível pessoal, acordei em bancos de dados onde inseri dezenas de milhares de linhas por segundo em uma tabela que possui uma coluna IDENTITY bigint como o PK agrupado.

    • 33

  2. Para resumir, nas versões modernas do SQL Server, uma chave agrupada em uma coluna de identidade é a opção preferida atualmente.

    Eu disse preferido, não obrigatório. Para aplicativos normais que compõem 98% dos bancos de dados do mundo, uma chave agrupada em uma coluna de identidade funciona muito bem.

    • 21

  3. Kimberly Tripp tem uma postagem de blog fantástica sobre esse tópico. Eu poderia parafrasear, mas confie em mim, eu não faria justiça. Dê uma lida.

    OK, então o critério final que procuro em uma chave de agrupamento é: um padrão cada vez maior!
    Se a chave de agrupamento for sempre crescente, as novas linhas terão um local específico onde podem ser colocadas. Se esse local estiver no final da tabela, a nova linha precisará de espaço alocado para ela, mas não precisará abrir espaço no meio da tabela. Se uma linha for inserida em um local que não tem espaço, então o espaço precisa ser feito (por exemplo, você insere com base no sobrenome, então, como as linhas vêm no espaço, será necessário criar onde esse nome deve ser colocado). Se for necessário abrir espaço, ele será feito pelo SQL Server fazendo algo chamado divisão. As divisões no SQL Server são divisões 50/50 – simplesmente – 50% dos dados permanecem e 50% dos dados são movidos. Isso mantém o índice logicamente intacto (o nível mais baixo de um índice – chamado de nível folha – é uma lista duplamente vinculada), mas não fisicamente intacto. Quando um índice tem muitas divisões, diz-se que o índice está fragmentado. Bons exemplos de um índice que está sempre aumentando são as colunas IDENTITY (e elas também são naturalmente únicas, estáticas e naturalmente estreitas) ou algo que segue o máximo possível dessas coisas - como uma coluna de data e hora (ou já que NÃO é muito provável que seja único por si só datetime, identity).

    Enquanto estiver lá, confira algumas de suas outras postagens sobre o tópico de chaves de agrupamento. Há uma boa riqueza de conhecimento a ser obtida em seu site.

    • 9

  4. Confira esta postagem:

    O aumento monotônico de chaves de índice clusterizado pode causar contenção de LATCH por Amit Banerjee da Microsoft.

    A criação de um índice clusterizado com base em uma chave de incremento pode criar pontos de acesso ruins para o desempenho.

    • 4

  5. 'Evite criar um índice clusterizado com base em uma chave de incremento' é um mito dos dias do SQL Server 2000?

    Ainda é uma consideração relevante, talvez mais agora do que nunca, à medida que a contagem de núcleos aumenta. Seria muito forte dizer que você deve sempre evitar essa prática.

    O SQL Server 2019 introduziu a OPTIMIZE_FOR_SEQUENTIAL_KEYopção de índice para ajudar a atenuar a contenção de trava de página e o comportamento de comboio de trava que pode ocorrer. Não é uma solução completa.

    Raramente há uma única consideração que domina todas as outras. Se você escolher uma chave de índice não sequencial, talvez seja necessário aceitar as divisões de página e diminuir a densidade média de dados como compensação pela escalabilidade potencialmente aumentada.

    Pam Lahoud da Microsoft escreveu um excelente artigo explicando porque OPTIMIZE_FOR_SEQUENTIAL_KEYé necessário e como funciona, incluído nas referências abaixo:

    • 1

relate perguntas