Início / dba / Perguntas / 47279
Accepted
AK_
Asked: 2013-07-31 05:55:08 +0800 CST

SQL Server Ajustando uma tabela para inserções massivas

  • 772

Estou tentando resolver um problema de desempenho e preciso de conselhos.

Eu tenho uma tabela, que cerca de duas vezes por dia tem um grande aumento de inserções chegando, e exatamente ao mesmo tempo todo mundo está tentando ler nela. Então eu tenho bloqueios e tudo se torna dolorosamente lento.

Meus requisitos de desempenho são os seguintes:

  1. muitos escritores, de diferentes fontes, e eu prefiro que cada escrita seja atômica.
  2. não me importo com a ordem das gravações, o número 7 pode vir antes do número 3.
  3. as gravações devem terminar o mais rápido possível, quero que os escritores esperem o mínimo de tempo possível.
  4. os dados nunca são atualizados , apenas inseridos.
  5. Eu tenho muitos muitos leitores.
  6. os leitores querem ver o instantâneo mais atualizado possível no momento da leitura.
  7. Posso viver com os leitores vendo dados atrasados ​​(10 a 20 segundos)
  8. Eu preciso ocasionalmente fazer consultas complexas e grandes sobre esses dados, então preciso de vários índices nele.

Como você abordaria isso e como implementaria isso com o SQL Server?

Estou pensando em dividir em 2 tabelas... uma para as inserções, e outra para leitura, e um trabalhador movimentando os dados.

as consultas grandes rodarão somente na segunda, os leitores que precisam de dados atualizados, podem rodar na segunda, e completam o que precisam da primeira.

sql-server sql-server-2008

2 respostas

  1. Best Answer

    Uma solução que usei foi encadear gravações em várias tabelas de preparação e, em seguida, coletar os dados em lotes. Colocamos as tabelas de preparação em seus próprios grupos de arquivos para tentar segregar a E/S o máximo possível, embora tenhamos acabado com duas tabelas em cada LUN disponível, em vez de cada tabela obter sua própria E/S dedicada.

    Como você disse que a ordem das gravações não importa, isso pode ser viável, mas não sei se 20 segundos serão suficientes para fazer isso funcionar e ainda não encontrar muitos bloqueios.

    Então, digamos que temos uma tabela base como esta, que é de onde todos leem (e atualmente escrevem):

    CREATE TABLE dbo.StockData
(
  StockID INT,
  ...other info...
);

    Então temos tabelas de preparação como esta, onde vamos escrever:

    CREATE TABLE staging.StockData0
(
  StockID INT,
  ...other info...
);

CREATE TABLE staging.StockData1
(
  StockID INT,
  ...other info...
);

... up to StockData9

    *Não se preocupe em imitar todos os índices não agrupados, restrições etc. nessas tabelas de preparação,

    Em seguida, supondo que você tenha inserções singleton por meio de um procedimento armazenado, altere o procedimento armazenado para usar SQL dinâmico e insira em uma das 10 tabelas com base no StockIDvalor:

    DECLARE @sql NVARCHAR(MAX) = N'INSERT staging.StockData'
  + CONVERT(CHAR(1), @StockID % 10 + '(StockID, cols...)
    SELECT @StockID, @params...;';

EXEC sp_executesql @sql, N'@StockID INT, @params...', @StockID, @params...;

    Em seguida, tenha um trabalho em segundo plano que execute uma operação de metadados muito rápida, movendo essas tabelas para um esquema fictício - isso minimiza a quantidade de tempo gasto bloqueando a capacidade dos usuários de escrever enquanto você move esses dados para a tabela real (que agora você pode controle muito melhor do que gravações esporádicas de todo o lugar). Portanto, primeiro crie uma segunda cópia das tabelas em um esquema diferente:

    CREATE SCHEMA shadow  AUTHORIZATION dbo;
CREATE SCHEMA holding AUTHORIZATION dbo;
GO

CREATE TABLE shadow.StockData0
(
  StockID INT,
  ...other info...
);

-- repeat for 1-9

    Agora o trabalho faria isso:

    TRUNCATE TABLE shadow.StockData0;

BEGIN TRANSACTION;

  ALTER SCHEMA holding TRANSFER staging.StockData0;
  ALTER SCHEMA staging TRANSFER shadow.StockData0;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER holding.StockData0;

-- repeat for 1-9 (I actually used dynamic SQL in a loop
-- so I wouldn't have to repeat myself ten times...

    Eu escrevi sobre esta parte aqui e aqui .

    O trabalho continuaria inserindo lotes na tabela principal, com um atraso entre eles, para permitir que algumas leituras acontecessem.

    BEGIN TRANSACTION;

  INSERT dbo.StockData(StockID, ...cols...)
    SELECT StockID, ...cols...
    FROM holding.StockData0;

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:01';

-- repeat for 1-9

    Isso foi há vários anos, e algo que não me ocorreu na época (o que me lembro agora também, depois de ver o outro link postado por wBob) é que usar TABLOCKdentro dessas transações pode ajudar a acelerar as inserções (embora você vai querer testar com base no volume, nível de isolamento, etc).

    Também pode ser útil (mesmo com todo esse trabalho) habilitar o isolamento de instantâneo confirmado de leitura e/ou se você estiver no Enterprise Edition considerando o particionamento (e alinhar as tabelas de preparação a essas partições).

    • 5

  2. A equipe do SQL CAT postou um artigo sobre um tópico semelhante:

    Carregando dados em massa em uma tabela com consultas simultâneas

    • 2

relate perguntas