Arokh's questions

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:

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

Uso de disco por partição

para isolar tabelas internas do acesso a bancos de dados de terceiros, criamos esquemas especificamente para esse caso de uso. Logins/usuários de banco de dados de terceiros só têm acesso a esses esquemas "públicos".
O problema é que o usuário tinha mais permissões do que deveria e muitas consultas de terceiros acessam as tabelas internas.
Como não posso remover as permissões abruptamente, gostaria de fazer isso aos poucos. Identificar consultas problemáticas e alterá-las de acordo.

Para isso tentei utilizar os recursos de Auditoria de Banco de Dados do Sql Server, mas parece que estou faltando informações para o que preciso:
Digamos que tenho a tabela [Internal].[Machine]e a View [Public].[Machine]que para simplificar é apenas select * from [Internal].[Machine]. O usuário viewersó tem permissão para selecionar [Public].[Machine].

Adicionar uma "especificação de auditoria de banco de dados" para (Type=SELECT, ObjectClass=SCHEMA, ObjectName=Internal, PrincipalName=viewer) produz uma entrada para select * from [Internal].[Machine]e select * from [Public].[Machine].
Meu objetivo é ter apenas uma entrada para select * from [Internal].[Machine], ou seja, quando o objeto for acessado diretamente pela instrução de consulta. E até onde vi, as linhas produzidas não contêm as informações necessárias para diferenciar as duas.

Isso é de alguma forma possível com o recurso Auditoria/Eventos Estendidos do Sql Server (ou algo completamente diferente)?

Estou tentando mudar uma partição de tabela para uma tabela temporária com a seguinte consulta:

create partition function [PFCT_T](datetime2) as range right for values ()
create partition scheme [PSCH_T] as partition [PFCT_T] all to ([primary])
    
create table T(A1 datetime2 not null, A2 int not null) on [PSCH_T](A1)
create clustered columnstore index [CIX_T] on dbo.T
    
insert into T(A1, A2) values ('2024-01-01 00:00:00', 1)
    
select top 0 * into #T from T
create clustered columnstore index [CIX_#T] on #T
    
alter table T switch partition 1 to #T
--Results in:
--Msg 608, Level 16, State 1, Line 15
--No catalog entry found for partition ID 5620492390548766720 in database 23. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.

Não é possível mudar uma partição para uma tabela temporária (funciona com tabelas normais) ou perdi alguma coisa?