Início / dba / Perguntas / 17238
Accepted
MiMo
Asked: 2012-05-01 03:45:05 +0800 CST

Inserção lenta no SQL Server 2008

  • 772

O banco de dados de nossos produtos contém uma tabela de trilha de auditoria:

CREATE TABLE gn_AuditTable(
  gn_ObjectId int NULL,
  gn_Action smallint NULL,
  gn_Time datetime NULL,
  gn_UserId int NULL,
  gn_Login varbinary(16) NULL,
  gn_ExtraObjectId int NULL,
  gn_ExtraInfo int NULL
) ON [PRIMARY]

Adicionamos linhas a esta tabela com uma insertinstrução simples - sem procedimentos armazenados:

insert into gn_AuditTable (
  gn_ObjectId,gn_Time,gn_UserId,gn_Login,gn_Action,gn_ExtraObjectId,gn_ExtraInfo
) values (
  ?,?,?,?,?,?,?
)

passando os valores das colunas como parâmetros para a consulta; também não há gatilhos.

O problema é que às vezes essa inserção é super lenta - levando em alguns casos mais de 30 segundos e esgotando o tempo.

Temos várias instalações do mesmo produto e estamos enfrentando o problema em algumas delas, mas não em todas.

A tabela de auditoria pode ser bastante grande - até alguns milhões de linhas. A inserção muito lenta acontece com bancos de dados que possuem tabelas grandes (naturalmente) - mas temos outros bancos de dados com número semelhante de linhas que funcionam bem, e mesmo os bancos de dados com o problema o apresentam de forma intermitente, na maioria dos casos a inserção é razoavelmente rápida ( subsegundo).

A tabela tem 6 índices não clusterizados e não exclusivos:

  • gn_Login;

  • gn_ObjectId;

  • gn_Actione gn_ExtraInfo;

  • gn_ExtraObjectId;

  • gn_Time;

  • gn_UserId.

Ter todos esses índices pode causar o problema, mas, novamente, temos os mesmos índices em todos os lugares e o problema apenas em algum lugar/às vezes.

Tivemos o tempo limite de inserção pelo menos uma vez em um banco de dados de teste com apenas um usuário, portanto, o problema não parece estar relacionado à carga geral, mas à inserção e à própria tabela.

Costumávamos ter um índice clusterizado em gn_ObjectId, mas nos livramos dele como uma tentativa de resolver esse problema. gn_ObjectIdnão é sequencial e, portanto, pensamos que é uma escolha ruim para um índice clusterizado, forçando o SQL Server a reordenar as linhas nas inserções. Provavelmente gn_Time- que está sempre aumentando - seria uma escolha melhor.

Não há chave primária porque não há coluna ou combinação de colunas que seja 'naturalmente' única - teríamos que adicionar uma coluna de id e parece apenas uma sobrecarga.

Alguém tem alguma sugestão sobre o que poderíamos olhar para diagnosticar o problema? O que é uma insertação que pode levar tanto tempo - de forma intermitente?

sql-server-2008 performance

3 respostas

  1. Por que nenhum índice clusterizado? Por que nenhuma chave primária?

    Este é provavelmente o seu problema: você não tem nenhuma ordem para a tabela (no sentido de, digamos, uma coluna IDENTIDADE) que você está inserindo em um heap

    Ver

    Editar, após atualização.

    Ter um índice clusterizado não exclusivo requer um unificador (links DBA.se) por entrada gn_ObjectId duplicada. Esta não é uma boa chave de agrupamento, que é melhor quando é

    • numérico
    • aumentando monotonicamente
    • estreito

    Eu também suponho que você não tenha tentado uma coluna de IDENTIDADE então...? Experimente, comente..

    Edite 2, como @JNK diz, uma coluna de ID não é uma sobrecarga nessa situação por causa de como suas gravações serão gerenciadas no disco

    • 11

  2. Seu banco de dados é grande o suficiente? Tem certeza de que seu INSERT não aciona o crescimento automático? Se suas implantações não tiverem a Inicialização instantânea de arquivo habilitada, esse é exatamente o comportamento esperado quando o crescimento de um arquivo de banco de dados é acionado: bloqueio aleatório de gravações durante o crescimento e a inicialização do arquivo. Você também pode estar experimentando o crescimento do arquivo de log (pelo menos isso pode ser rapidamente identificado observando o contador de desempenho do Log Growths ), mas para que ocorram crescimentos de log, você precisa ter um modelo de recuperação não simples e uma estratégia de backup basicamente ausente ( ou seja, truncamento de log não ocorre).

    Quanto ao design da sua tabela: as séries temporais geralmente são agrupadas pelo valor do tempo, porque a maioria das consultas abrange intervalos de tempo (por exemplo, 'selecionar todos os eventos nos últimos 3 dias'), de modo que tornaria gn_Timeseu provável candidato chave agrupado não exclusivo .

    • 6
  3. Best Answer

    Encontramos o problema:

    • existe um procedimento de manutenção que remove dados do banco de dados, incluindo linhas de gn_AuditTable;

    • o delete gn_AuditTable . .está em uma transação junto com outras declarações (ruim)

    • pode acontecer que muitas linhas sejam excluídas de uma só vez - nesse caso, o SQL Server bloqueia a tabela inteira - e, em seguida insert gn_AuditTable, espera a conclusão da transação

    Descobrimos isso porque pudemos dar uma olhada nos bloqueios quando tivemos um tempo limite e notamos o bloqueio da mesa. Estamos removendo delete gn_AuditTable . . .a transação e possivelmente dividindo-a em lotes menores, para não acionar um bloqueio de tabela.

    Estamos implementando um índice clusterizado gn_Timee a inicialização instantânea de arquivo - ambos são benéficos independentemente deste problema específico - obrigado!

    • 1

relate perguntas