Início / dba / Perguntas / 20183
Accepted
Paul Williams
Asked: 2012-06-30 13:34:52 +0800 CST

Desempenho lento Inserindo poucas linhas em uma tabela enorme

  • 772

Temos um processo que coleta dados das lojas e atualiza uma tabela de estoque de toda a empresa. Esta tabela possui linhas para cada loja por data e por item. Em clientes com muitas lojas, essa tabela pode ficar muito grande, da ordem de 500 milhões de linhas.

Esse processo de atualização de inventário geralmente é executado várias vezes ao dia conforme as lojas inserem dados. Essas execuções atualizam dados de apenas algumas lojas. No entanto, os clientes também podem executar isso para atualizar, digamos, todas as lojas nos últimos 30 dias. Nesse caso, o processo cria 10 threads e atualiza o estoque de cada loja em um thread separado.

O cliente está reclamando que o processo está demorando. Eu tracei o perfil do processo e descobri que uma consulta que INSERTs nesta tabela está consumindo muito mais tempo do que eu esperava. Às vezes, esse INSERT é concluído em 30 segundos.

Quando eu executo um comando SQL INSERT ad-hoc nesta tabela (limitada por BEGIN TRAN e ROLLBACK), o SQL ad-hoc é concluído na ordem de milissegundos.

A consulta de desempenho lento está abaixo. A ideia é INSERIR os registros que não existem e depois ATUALIZÁ-los à medida que calculamos vários bits de dados. Uma etapa anterior do processo identificou os itens que precisam ser atualizados, fez alguns cálculos e inseriu os resultados na tabela tempdb Update_Item_Work. Esse processo está sendo executado em 10 threads separados e cada thread tem seu próprio GUID em Update_Item_Work.

INSERT INTO Inventory
(
    Inv_Site_Key,
    Inv_Item_Key,
    Inv_Date,
    Inv_BusEnt_ID,
    Inv_End_WtAvg_Cost
)
SELECT DISTINCT
    UpdItemWrk_Site_Key,
    UpdItemWrk_Item_Key,
    UpdItemWrk_Date,
    UpdItemWrk_BusEnt_ID,
    (CASE UpdItemWrk_Set_WtAvg_Cost WHEN 1 THEN UpdItemWrk_WtAvg_Cost ELSE 0 END)
FROM tempdb..Update_Item_Work (NOLOCK)
WHERE UpdItemWrk_GUID = @GUID
AND NOT EXISTS
    -- Only insert for site/item/date combinations that don't exist
    (SELECT *
    FROM Inventory (NOLOCK)
    WHERE Inv_Site_Key = UpdItemWrk_Site_Key
    AND Inv_Item_Key = UpdItemWrk_Item_Key
    AND Inv_Date = UpdItemWrk_Date)

A tabela Estoque tem 42 colunas, a maioria das quais acompanha quantidades e contagens para vários ajustes de estoque. sys.dm_db_index_physical_stats diz que cada linha tem cerca de 242 bytes, então espero que cerca de 33 linhas caibam em uma única página de 8 KB.

A tabela é agrupada na restrição exclusiva (Inv_Site_Key, Inv_Item_Key, Inv_Date). Todas as chaves são DECIMAL(15,0) e a data é SMALLDATETIME. Há uma chave primária IDENTITY (não agrupada) e 4 outros índices. Todos os índices e a restrição clusterizada são definidos com um explícito (FILLFACTOR = 90, PAD_INDEX = ON).

Procurei no arquivo de log para contar as divisões de página. Eu medi cerca de 1.027 divisões no índice clusterizado e 1.724 divisões em outro índice, mas não registrei em que intervalo elas ocorreram. Uma hora e meia depois, medi 7.035 divisões de página no índice agrupado.

O plano de consulta que capturei no criador de perfil é assim:

Rows         Executes     StmtText                                                                                                                                             
----         --------     --------                                                                                                                                             
490          1            Sequence                                                                                                                                             
0            1              |--Index Update
0            1              |    |--Collapse
0            1              |         |--Sort
0            1              |              |--Filter
996          1              |                   |--Table Spool                                                                                                                 
996          1              |                        |--Split                                                                                                                  
498          1              |                             |--Assert
0            0              |                                  |--Compute Scalar
498          1              |                                       |--Clustered Index Update(UK_Inventory)
498          1              |                                            |--Compute Scalar
0            0              |                                                 |--Compute Scalar
0            0              |                                                      |--Compute Scalar
498          1              |                                                           |--Compute Scalar
498          1              |                                                                |--Top
498          1              |                                                                     |--Nested Loops
498          1              |                                                                          |--Stream Aggregate
0            0              |                                                                          |    |--Compute Scalar
498          1              |                                                                          |         |--Clustered Index Seek(tempdb..Update_Item_Work)
498          498            |                                                                          |--Clustered Index Seek(Inventory)
0            1              |--Index Update(UX_Inv_Exceptions_Date_Site_Item)
0            1              |    |--Collapse
0            1              |         |--Sort
0            1              |              |--Filter
996          1              |                   |--Table Spool
490          1              |--Index Update(UX_Inv_Date_Site_Item)
490          1                   |--Collapse
980          1                        |--Sort
980          1                             |--Filter
996          1                                  |--Table Spool

Olhando para as consultas versus vários dmvs, vejo que a consulta está aguardando em PAGEIOLATCH_EX por uma duração de 0 em uma página nesta tabela de inventário. Não vejo nenhuma espera ou bloqueio em bloqueios.

Esta máquina tem cerca de 32 GB de memória. Ele está executando o SQL Server 2005 Standard Edition, embora eles estejam atualizando em breve para o 2008 R2 Enterprise Edition. Não tenho números sobre o tamanho da tabela de inventário em termos de uso de disco, mas posso obter isso, se necessário. É uma das maiores mesas deste sistema.

Executei uma consulta em sys.dm_io_virtual_file_stats e vi que a média de esperas de gravação em tempdb era superior a 1,1 segundos . O banco de dados no qual essa tabela está armazenada tem esperas médias de gravação de aproximadamente 350 ms. Mas eles só reiniciam o servidor a cada 6 meses, então não tenho ideia se essa informação é relevante. tempdb está distribuído em 4 arquivos diferentes Eles têm 3 arquivos diferentes para o banco de dados que contém a tabela Inventário.

Por que essa consulta demoraria tanto para INSERT algumas linhas quando executada com muitos threads diferentes quando um único INSERT é muito rápido?

-- ATUALIZAR --

Aqui estão os números de latência por unidade, incluindo bytes lidos. Como você pode ver, o desempenho do tempdb é questionável. A tabela Inventory está em PDICompany_252_01.mdf, PDICompany_252_01_Second.ndf ou PDICompany_252_01_Third.ndf.

ReadLatencyWriteLatencyLatencyAvgBPerRead AvgBPerWriteAvgBPerTransferDriveDB                     physical_name
         42        1112    623       62171       67654          65147R:   tempdb                 R:\Microsoft SQL Server\Tempdb\tempdev1.mdf
         38        1101    615       62122       67626          65109S:   tempdb                 S:\Microsoft SQL Server\Tempdb\tempdev2.ndf
         38        1101    615       62136       67639          65123T:   tempdb                 T:\Microsoft SQL Server\Tempdb\tempdev3.ndf
         38        1101    615       62140       67629          65119U:   tempdb                 U:\Microsoft SQL Server\Tempdb\tempdev4.ndf
         25         341     71       92767       53288          87009X:   PDICompany             X:\Program Files\PDI\Enterprise\Databases\PDICompany_Third.ndf
         26         339     71       90902       52507          85345X:   PDICompany             X:\Program Files\PDI\Enterprise\Databases\PDICompany_Second.ndf
         10         231     90       98544       60191          84618W:   PDICompany_FRx         W:\Program Files\PDI\Enterprise\Databases\PDICompany_FRx.mdf
         61         137     68        9120        9181           9125W:   model                  W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\modeldev.mdf
         36         113     97        9376        5663           6419V:   model                  V:\Microsoft SQL Server\Logs\modellog.ldf
         22          99     34       92233       52112          86304W:   PDICompany             W:\Program Files\PDI\Enterprise\Databases\PDICompany.mdf
          9          20     10       25188        9120          23538W:   master                 W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\master.mdf
         20          18     19       53419       10759          40850W:   msdb                   W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\MSDBData.mdf
         23          18     19      947956       58304         110123V:   PDICompany_FRx         V:\Program Files\PDI\Enterprise\Databases\PDICompany_FRx_1.ldf
         20          17     17      828123       55295         104730V:   PDICompany             V:\Program Files\PDI\Enterprise\Databases\PDICompany.ldf
          5          13     13       12308        4868           5129V:   master                 V:\Microsoft SQL Server\Logs\mastlog.ldf
         11          13     13       22233        7598           8513V:   PDIMaster              V:\Program Files\PDI\Enterprise\Databases\PDIMaster.ldf
         14          11     13       13846        9540          12598W:   PDIMaster              W:\Program Files\PDI\Enterprise\Databases\PDIMaster.mdf
         13          11     11       22350        1107           1110V:   msdb                   V:\Microsoft SQL Server\Logs\MSDBLog.ldf
         17           9      9      745437       11821          23249V:   PDIFoundation          V:\Program Files\PDI\Enterprise\Databases\PDIFoundation.ldf
         34           8     31       29490       33725          30031W:   PDIFoundation          W:\Program Files\PDI\Enterprise\Databases\PDIFoundation.mdf
          5           8      8       61560       61236          61237V:   tempdb                 V:\Microsoft SQL Server\Logs\templog.ldf
         13           6     11        8370       35087          16785W:   SAHost_Company01       W:\Program Files\PDI\Enterprise\Databases\SAHostCompany.mdf
          2           6      5       56235       33667          38911W:   SAHost_Company01       W:\Program Files\PDI\Enterprise\Databases\SAHost_Company_01_log.LDF
sql-server performance

2 respostas

  1. Best Answer

    Parece que suas divisões de página de índice clusterizado serão dolorosas porque o índice clusterizado contém os dados reais e isso precisará de novas páginas a serem alocadas e os dados movidos para elas. É provável que isso cause bloqueio de página e, portanto, bloqueio.

    Lembre-se também de que sua chave de índice clusterizado tem 21 bytes e isso precisará ser armazenado em todos os seus índices secundários como um marcador.

    Você já pensou em tornar sua coluna de identidade de chave primária seu índice clusterizado, isso não apenas reduzirá o tamanho de seus outros índices, mas também significará que você reduzirá o número de divisões de página em seu índice clusterizado. Vale a pena tentar se você tiver estômago para reconstruir seus índices.

    • 4

  2. Com a abordagem multithread, sou cauteloso com a inserção em uma tabela da qual você deve primeiro verificar a existência anterior de uma chave. Isso meio que me diz que há um problema de simultaneidade naquele índice PK para aquela tabela, não importa quantos threads existam. Pelo mesmo motivo, não gosto da dica NOLOCK na tabela de inventário porque parece que ocorreria um erro se threads diferentes pudessem escrever a mesma chave (o esquema de particionamento remove essa possibilidade?). Estou curioso sobre o tamanho da aceleração na introdução inicial de vários threads, porque deve ter funcionado bem em algum momento.

    Algo a tentar é fazer com que a consulta seja lida mais como uma operação em massa e converter o "onde não existe" em um "anti-junção". (finalmente, o otimizador pode optar por ignorar esse esforço). Como mencionado acima, eu removeria a dica NOLOCK na tabela de destino, a menos que talvez o particionamento tenha garantido nenhuma colisão de chaves entre os threads.

     INSERT INTO i (...)
 SELECT DISTINCT ...             
   FROM tempdb..Update_Item_Work t (NOLOCK) -- nolock okay on read table
   left join Inventory i -- use without NOLOCK because PK is written inter-thread
     on i.Inv_Site_Key = t.UpdItemWrk_Site_Key
    and i.Inv_Item_Key = t.UpdItemWrk_Item_Key
    and i.Inv_Date = t.UpdItemWrk_Date
  where i.Inv_Site_Key is null   -- where not exist in inventory
    and UpdItemWrk_GUID = @GUID  -- for this thread

    Cronometrando essa execução como base, você pode executar novamente com a dica de mesclagem ("junção esquerda" --> "junção de mesclagem esquerda") como outra possibilidade. Você provavelmente deve ter um índice na tabela temporária (UpdItemWrk_Site_Key,UpdItemWrk_Item_Key,UpdItemWrk_Date) para a dica de mesclagem.

    Não sei se as versões não expressas mais recentes do SQL Server 2008/2012 seriam capazes de paralelizar automaticamente mesclagens maiores deste formulário, permitindo remover o particionamento baseado em GUID.

    Para encorajar a junção a ocorrer apenas nos itens distintos em vez de em todos os itens, as cláusulas "selecionar distinto ... de ..." podem ser convertidas em "selecionar * de (selecionar distinto ... de ...)" antes continuando com a união. Isso só pode fazer uma diferença perceptível se o distinto estiver filtrando muitas linhas. Novamente, o otimizador pode ignorar esse esforço.

    • 1

relate perguntas