Início / dba / Perguntas / 340202
Accepted
Arokh
Asked: 2024-06-13 18:32:19 +0800 CST

Alto uso de memória para SqlBulkCopy

  • 772

Comecei a usar o QueryStore para monitorar meu App e uma das coisas que notei é o alto uso inesperado de memória para o que considerei uma operação simples:

insira a descrição da imagem aqui

O que significaria que a instrução usa quase 600 MB de RAM cada vez que é executada?
O RowCount para cada execução nesse período é muito menor que 100. A instrução em si é executada a cada 5 segundos.

A tabela é particionada e baseada em um índice ColumnStore e não possui outros índices ou chave primária/identidade e possui cerca de 750 mil linhas:

CREATE TABLE [DataLink].[LogEntry](
  [AppInstanceId] [bigint] NOT NULL,
  [LoggedOnUtc] [datetime2](7) NOT NULL,
  [CategoryName] [nvarchar](256) NOT NULL,
  [EventCode] [int] NOT NULL,
  [EventName] [nvarchar](256) NULL,
  [LogLevel] [int] NOT NULL,
  [ScopeJson] [nvarchar](max) NULL,
  [StateJson] [nvarchar](max) NULL,
  [ExceptionJson] [nvarchar](max) NULL,
  [Message] [nvarchar](max) NULL
) ON [PSCH_Logging_DataLink_LogEntry_Daily7Of9]([LoggedOnUtc])

CREATE CLUSTERED COLUMNSTORE INDEX [CIX_LogEntry] 
ON [DataLink].[LogEntry] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) 
ON [PSCH_Logging_DataLink_LogEntry_Daily7Of9]([LoggedOnUtc])

O Código que aciona as inserções:

using var conn = connInfo.Main.GetConnection(DatabaseLoginType.User);
await conn.OpenAsync(ct).CAf();
using var sqlBulkCopy = new SqlBulkCopy((SqlConnection)conn, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers, null);

foreach(var toWriteItemGroup in toWriteItems.GroupBy(x => x.SchemaName)) {
...

    dataReader.Init(toWriteItemGroup, tableInfo.ColumnMappings.Length);

    sqlBulkCopy.DestinationTableName = $"{schemaName}.LogEntry";
    sqlBulkCopy.ColumnMappings.Clear();
    for(int i = 0; i < tableInfo.ColumnMappings.Length; i++) sqlBulkCopy.ColumnMappings.Add(i, tableInfo.ColumnMappings[i]);
    await sqlBulkCopy.WriteToServerAsync(dataReader, ct).CAf();
...
}

Alguma ideia de por que o uso de memória é tão alto e o que posso fazer para corrigir isso?

Edit4
Fiz alguns testes alterando e compilando manualmente o Microsoft.Data.SqlClient. As alterações que fiz incluíram a adição de ROWS_PER_BATCH e/ou KILOBYTES_PER_BATCH às opções with da instrução "insert bulk". Nenhuma das opções alterou a quantidade de memória usada, mas a primeira alterou a estimativa de contagem de linhas: https://www.brentozar.com/pastetheplan/?id=HkKjc9HIC Não parece que "inserir volume" possa ser otimizado para contagens baixas de linhas.

Edit3
Aqui está um pequeno exemplo com o qual posso reproduzir o problema.
Ele contém um script "Script.sql" que precisa ser executado primeiro para configurar a tabela e adicionar alguns dados. Depois disso execute o programa com "dotnet run" (ou use um IDE).
Como não consigo fazer upload de arquivos aqui, fiz o upload para o github Gist: https://gist.github.com/DvdKhl/d042ed05e3237136265295cb39ecb4f4

O roteiro irá:

  • Crie uma visualização que mostre PartitionInfo da tabela
  • Crie (ou recrie) a tabela e sua configuração
  • Insira 700 mil linhas (100 mil por partição)
  • Reconstrua o índice
  • Produza as informações da partição
  • Comentada é
    • Sessão de Evento (Evento Estendido) para capturar o plano de consulta
    • Outra declaração de inserção
    • Limpar

O Programa irá:

  • Abra uma conexão em “localhost” para o banco de dados “main”
  • Crie um DataReader fictício (alterar contagem para alterar a contagem de linhas inseridas)
  • Configure o SqlBulkCopy como acima
  • Chame WriteToServerAsync para inserir as linhas

Isso resulta no seguinte plano de consulta: https://www.brentozar.com/pastetheplan/?id=B1v_8bGLC

Edit2
Como sugerido por Denis Rubashkin, configurei BatchSize e uma dica de pedido:

sqlBulkCopy.BatchSize = toWriteItemGroup.Count();
sqlBulkCopy.ColumnOrderHints.Add("LoggedOnUtc", SortOrder.Ascending);

BatchSize parece não mudar nada (a estimativa permanece a mesma).
Parece que ROWS_PER_BATCH não é usado, embora sqlBulkCopy.BatchSize esteja definido no código, o que pode ser o principal problema.

Quando a dica Order é adicionada, a consulta não aparece no QueryStore.
O uso de eventos estendidos para obter o plano de consulta mostra um aviso de "Concessão excessiva". Então não tenho certeza se isso ajudou.

A opção KILOBYTES_PER_BATCH parece interessante, mas não parece que posso configurá-la no código C#.

WithOrderHint / WithoutOrderHint (tabela diferente, mas exatamente o mesmo problema)

Editar:
Plano de consulta: https://www.brentozar.com/pastetheplan/?id=SJGpBktH0

Propriedades da Tabela - Armazenamento
insira a descrição da imagem aqui

Uso de disco por partição
insira a descrição da imagem aqui

sql-server

3 respostas

  1. Best Answer

    eu fiz um teste

    Eu criei três tabelas

    • Columnstore clusterizado
    • Índice clusterizado
    • Pilha

    E executei uma bcpferramenta em três deles enquanto procurava no Query Store

    Reprodução

    CREATE DATABASE BulkCopy
GO
USE BulkCopy
go
ALTER DATABASE [BulkCopy] SET QUERY_STORE CLEAR

DROP TABLE IF EXISTS dbo.OrderItemCCX
CREATE TABLE dbo.OrderItemCCX
(
    Id int IDENTITY (1,1) NOT NULL
    , OrderId int NOT NULL
    , ProductName varchar(50) NOT NULL
    , Qty int NOT NULL

)
GO
    CREATE CLUSTERED COLUMNSTORE INDEX [CCX_OrderItemCCX]
ON dbo.OrderItemCCX WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) 
GO

DROP TABLE IF EXISTS dbo.OrderItemHeap
go
CREATE TABLE dbo.OrderItemHeap
(
    Id int IDENTITY (1,1) NOT NULL
    , OrderId int NOT NULL
    , ProductName varchar(50) NOT NULL
    , Qty int NOT NULL

)

go
DROP TABLE IF EXISTS dbo.OrderItemCX
go
CREATE TABLE dbo.OrderItemCX
(
    Id int IDENTITY (1,1) NOT NULL
    , OrderId int NOT NULL
    , ProductName varchar(50) NOT NULL
    , Qty int NOT NULL
    , CONSTRAINT PK_OrderItem PRIMARY KEY CLUSTERED (Id)
)
GO

    Eu armazenei isso como um arquivo csv no meu disco localD:\OrderItems.csv

    ,4,abcd,42
,4,asdasd,42
,5,asdasdasd,42
,5,dhdrh,42
,5,dasd,42
,6,fdhg,42
,4,abcd,42
,4,asdasd,42
,5,asdasdasd,42
,5,dhdrh,42
,5,dasd,42
,6,fdhg,42
,4,abcd,42
,4,asdasd,42
,5,asdasdasd,42
,5,dhdrh,42
,5,dasd,42
,6,fdhg,42
,4,abcd,42
,4,asdasd,42

    E executei o bcp em um loop de 5 segundos com o PowerShell (altere a tabela de destino para uma das três acima)

    while ($true) {
    # Run the bcp command
    & bcp dbo.OrderItemCCX in "D:\OrderItems.csv" -S localhost -d BulkCopy -T -c -t ','
    
    Start-Sleep -Seconds 5
}

    Posteriormente também adicionei a tabela LogEntry (embora não particionada) com alguns dados gerados pelo chatGPT

    DROP TABLE IF EXISTS dbo.LogEntry

CREATE TABLE dbo.LogEntry
(
    AppInstanceId bigint        NOT NULL
  , LoggedOnUtc   datetime2(7)  NOT NULL
  , CategoryName  nvarchar(256) NOT NULL
  , EventCode     int           NOT NULL
  , EventName     nvarchar(256) NULL
  , LogLevel      int           NOT NULL
  , ScopeJson     nvarchar(MAX) NULL
  , StateJson     nvarchar(MAX) NULL
  , ExceptionJson nvarchar(MAX) NULL
  , Message       nvarchar(MAX) NULL
)

CREATE CLUSTERED COLUMNSTORE INDEX [CIX_LogEntry] 
ON dbo.LogEntry WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) 

    1,2024-06-01 12:00:00.0000000,Application,1001,Startup,1,scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app,init-state-init-state-init-state-init-state-init-state-init-state-init-state-init-state-init-state-init,,Application started successfully.
2,2024-06-01 12:05:00.0000000,Application,1002,Shutdown,1,scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app,shutdown-state-shutdown-state-shutdown-state-shutdown-state-shutdown-state-shutdown-state-shutdown,,Application shutdown initiated.
3,2024-06-01 12:10:00.0000000,Security,2001,Login,2,scope-user-scope-user-scope-user-scope-user-scope-user-scope-user-scope-user-scope-user-scope-user,login-state-login-state-login-state-login-state-login-state-login-state-login-state-login-state-login,,User login successful.
4,2024-06-01 12:15:00.0000000,Security,2002,Logout,2,scope-user-scope-user-scope-user-scope-user-scope-user-scope-user-scope-user-scope-user-scope-user,logout-state-logout-state-logout-state-logout-state-logout-state-logout-state-logout-state-logout-state-logout,,User logout successful.
5,2024-06-01 12:20:00.0000000,Database,3001,Connection,3,scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db,connected-state-connected-state-connected-state-connected-state-connected-state-connected-state-connected-state-connected,,Database connection established.
6,2024-06-01 12:25:00.0000000,Database,3002,Disconnection,3,scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db-scope-db,disconnected-state-disconnected-state-disconnected-state-disconnected-state-disconnected-state-disconnected-state-disconnected,,Database connection closed.
7,2024-06-01 12:30:00.0000000,Error,4001,Exception,4,scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app,error-state-error-state-error-state-error-state-error-state-error-state-error-state-error-state-error-state-error-state,error-NullReferenceException-error-NullReferenceException-error-NullReferenceException-error-NullReferenceException,An unexpected error occurred.
8,2024-06-01 12:35:00.0000000,Performance,5001,HighCPU,2,scope-system-scope-system-scope-system-scope-system-scope-system-scope-system-scope-system-scope-system-scope-system,warning-state-warning-state-warning-state-warning-state-warning-state-warning-state-warning-state-warning-state-warning,,High CPU usage detected.
9,2024-06-01 12:40:00.0000000,Performance,5002,HighMemory,2,scope-system-scope-system-scope-system-scope-system-scope-system-scope-system-scope-system-scope-system-scope-system,warning-state-warning-state-warning-state-warning-state-warning-state-warning-state-warning-state-warning-state-warning,,High memory usage detected.
10,2024-06-01 12:45:00.0000000,Application,1003,Update,1,scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app-scope-app,update-state-update-state-update-state-update-state-update-state-update-state-update-state-update-state-update,,Application update completed.

    Resultados

    Estes são os resultados do Query Store (não se importe com as duas linhas por horário de início, isso ocorre porque o intervalo de tempo ainda não está fechado)

    SELECT 
    qsq.query_id
    , qsp.plan_id
    , LEFT(qsqt.query_sql_text, 30) AS QueryText
    , CONVERT(VARCHAR(8), qsrsi.start_time, 108) AS start_time
    , qsrs.count_executions
    , qsrs.avg_rowcount
    , qsrs.avg_query_max_used_memory   /* KiB */
    , qsrs.last_query_max_used_memory  /* KiB */
    , qsrs.min_query_max_used_memory   /* KiB */
    , qsrs.max_query_max_used_memory   /* KiB */
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt
    ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qsrs
    ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_runtime_stats_interval AS qsrsi
    ON qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
WHERE qsqt.query_sql_text LIKE 'insert bulk%'
ORDER BY qsrsi.start_time, qsq.query_id

    insira a descrição da imagem aqui

    Aprendizado

    Como podemos ver, apenas as inserções em massa no columnstore têm grande uso de memória. LogEntrytambém tem um uso maior do que OrderItemCCX- que pode ser baseado no tamanho dos dados ou nos segmentos de armazenamento de colunas (mais colunas).

    De qualquer forma, inserir 20 linhas por lote não me parece um trabalho para cópia em massa .

    Recomendo a leitura dos índices Columnstore - orientação sobre carregamento de dados e talvez o uso de uma abordagem de tabela intermediária.

    • 3

  2. O operador Sort é o único operador que precisa de memória no plano de consulta anexado que posso ver. O que parece estranho para mim é que o uso zero de memória foi declarado dentro do plano:

    <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />

    Talvez o servidor precise de alguma memória para trabalhar com dados binários do seu aplicativo ou isso seja apenas algum recurso dos planos de consultas de "inserção em massa". De qualquer forma, acho que essa grande concessão de memória se deve à estimativa errada (10.000) no operador Remote Scan.

    Você pode tentar adicionar dicas ROWS_PER_BATCH = rows_per_batchpara melhorar a estimativa e/ou adicionar dicas ORDER LoggedOnUtc ASCpara evitar o operador Sort no plano de consulta.

    Dê uma olhada na sintaxe somente da ferramenta externa

    Espero que isto ajude.

    • 1

  3. A pastilha em massa é otimizada para pastilhas em massa. Com um destino columnstore clusterizado, isso significa, em particular, que a concessão de memória é dimensionada para permitir a produção de grupos de linhas compactados, o que pode consumir muita memória.

    Você pode pretender inserir apenas um pequeno número de linhas e pode convencer o otimizador a gerar um plano otimizado para um pequeno número de linhas, MAS a concessão de memória de inserção em massa ainda será grande porque um grande número de linhas pode ser encontrado em tempo de execução .

    Outra maneira de ver isso é: se você fosse inserir apenas um pequeno número de linhas, não usaria insert em massa . É uma inferência razoável a ser feita pelo SQL Server.

    Gambiarra

    Dito isso, é inegavelmente conveniente usar SqlBulkCopya partir do código. Se você deseja manter seu arranjo existente praticamente intacto, mas conseguir inserções lentas, uma solução alternativa é usar um gatilho.

    Agora, você não pode criar um gatilho diretamente em uma tabela columnstore clusterizada, mas pode criar um em uma exibição de uma tabela columnstore clusterizada.

    Tornar o gatilho um INSTEAD OFgatilho de inserção nos permite converter a inserção em massa em uma inserção lenta .

    Exemplo

    CREATE VIEW dbo.LogEntryInsertAdapter
WITH SCHEMABINDING AS
SELECT 
    AppInstanceId,
    LoggedOnUtc,
    CategoryName,
    EventCode,
    EventName,
    LogLevel,
    ScopeJson,
    StateJson,
    ExceptionJson,
    [Message] 
FROM dbo.LogEntry;
GO
CREATE TRIGGER LogEntryInsertAdapter_IOI
ON dbo.LogEntryInsertAdapter 
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
INSERT dbo.LogEntry SELECT * FROM Inserted;
GO

    A única alteração de código necessária é:

    sqlBulkCopy.DestinationTableName = $"dbo.LogEntryInsertAdapter";

    Seu código de demonstração resulta em uma inserção sem concessão de memória:

    ver inserção

    Estritamente, isso é uma inserção em uma tabela temporária oculta. A inserção real realizada pelo gatilho é:

    inserção de mesa

    Isso também tem uma concessão de memória zero.

    Para aquelas ocasiões em que você tem um grande número de linhas e deseja realizar uma inserção em massa , direcione o nome da tabela original em vez da visualização (ou omita SqlBulkCopyOptions.FireTriggers).

    • 1

relate perguntas