All perguntas(dba)

De acordo com sys.dm_os_memory_clerks, o uso total de RAM do meu servidor foi de 30 GB. Usando essas informações, defini max server memory (MB)para 31.000. Fiquei chocado ao ver sys.dm_os_memory_clerkso relatório de que o uso total de RAM do meu servidor era de 20 GB. Isso é duplamente confuso, visto que a documentação diz que max server memory (MB)é apenas para o buffer pool

A opção max server memory limita apenas o tamanho do buffer pool do SQL Server. A opção de memória máxima do servidor não limita uma área de memória restante não reservada que o SQL Server deixa para alocações de outros componentes, como procedimentos armazenados estendidos, objetos COM, DLLs e EXEs não compartilhados.

É sys.dm_os_memory_clerksuma má fonte de informações para essas decisões ou preciso apenas esperar que meu buffer pool se atualize?

Por favor, desculpe-me se não estou fazendo muito sentido aqui. Sou muito novo em replicação e venho pesquisando há algum tempo sobre replicação transacional para implementar em nosso cenário.

Estou tentando considerar todas as opções antes de configurar a replicação transacional no meu servidor 2008R2 para o servidor de distribuição 2012 para o servidor Assinante 2012. Todos os três servidores estão no modo de recuperação COMPLETO. O backup do log é feito a cada 15 minutos e o backup completo é feito diariamente, todos os dias, em todos os três bancos de dados do servidor usados ​​na replicação. Queremos usar o assinante como servidor de relatórios. Publisher é nosso principal banco de dados OLTP

Que considerações especiais devo ter em mente antes de implementar a replicação transacional no meu ambiente de produção.

Quais consultas devo usar após configurar esta replicação para monitorar o uso do espaço de log, o uso de IO e o uso da CPU no meu editor? Com que frequência devo usar essas consultas - devo executá-las regularmente por meio de um trabalho de agente sql de hora em hora/diariamente?

E se o banco de dados do editor apresentar problemas de desempenho após a configuração da replicação? Se eu tiver que remover a replicação transacional - isso afetaria meu editor de alguma forma, como os T-logs seriam afetados ou eles não seriam afetados e seriam copiados normalmente (como era antes da replicação) ou quebraria o cadeia t-log do editor se a replicação for removida?

Há bons conselhos gerais sobre como configurar o Grau Máximo de Paralelismo (MAXDOP). No entanto, estou em uma circunstância especial. Existem duas instâncias na minha caixa. Se eu tiver 16 núcleos, como devo dividir o MAXDOP entre eles? Que métricas revelariam que fiz uma escolha errada?

Estou no SQL Server 2019 e o conselho padrão para MAXDOP mudou em 2016, portanto, informações anteriores a junho de 2016 (a data de lançamento do SQL Server 2016) podem não ser aplicáveis.

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

Estou projetando uma tabela Postgres para armazenar grandes quantidades de dados de séries temporais e estou tentando descobrir a melhor maneira de estruturar as colunas. Eu olhei respostas como esta , mas como já tem quase 10 anos, queria ver se há alguma coisa nova que eu deveria estar ciente.

Os dados da série temporal vêm de muitas fontes (é a isso que src_idos exemplos se referem). Cada fonte terá um ponto de dados por minuto e cada ponto de dados terá muitas medições diferentes. As medições representam coisas como temperatura, umidade, etc. para aquele minuto específico. Eu os abstraí para serem apenas "medida A", "medida B" e assim por diante para os exemplos. Existem atualmente 20 tipos de medição que precisam ser suportados, e mais serão adicionados no futuro.

A quantidade de dados está na casa dos bilhões de linhas. A grande maioria das gravações adicionará novas linhas para o minuto atual. As consultas de leitura típicas serão para uma fonte, janela de tempo e tipo de medição específicos. Também estou planejando particionar qualquer tabela que eu escolher, talvez em partições de um mês.

Opção 1) Mesa plana

Eu poderia implementar uma mesa plana simples. Uma desvantagem é que, à medida que adiciono mais tipos de medidas ao longo do tempo, terei que atualizar a tabela com novas colunas. Também está começando com 23 colunas, o que parece que estou seguindo o caminho errado.

TABLE data_points (id, src_id, timestamp          , measurement_a, measurement_b, ...)
                  (1 , 1     , 2024-01-01 00:00:00, 100          , 6.8          , ...)
                  (2 , 2     , 2024-01-01 00:00:00, 55           , 0.1          , ...)

Opção 2) Pares de valores-chave

Isso reduz o número de colunas para um valor definido, portanto não terei que atualizar a tabela com novas colunas à medida que novas medidas forem adicionadas. No entanto, haverá muito mais linhas (20x para começar, já que estou começando com 20 tipos de medição).

TABLE data_points (id, src_id, timestamp          , meas_type, meas_value)
                  (1 , 1     , 2024-01-01 00:00:00, A        , 100       )
                  (2 , 1     , 2024-01-01 00:00:00, B        , 6.8       )
                  ... 

                  (3 , 2     , 2024-01-01 00:00:00, A        , 55        )
                  (4 , 2     , 2024-01-01 00:00:00, B        , 0.1       )
                  ... 

Opção 3) Duas tabelas

Eu poderia ter uma tabela armazenando o src_id e o carimbo de data/hora, e a outra tabela armazenando os dados de medição. Isso é semelhante aos pares de valores-chave, apenas divididos em duas tabelas para que não precise repetir tanto as colunas src_ide .timestamp

Isso pode tornar o particionamento um pouco mais complicado. Além disso, todas as leituras teriam que fazer uma junção, e eu me preocupo mais com o desempenho do que com o tamanho geral do banco de dados, então talvez não valha a pena a sobrecarga?

TABLE data_point_times (id, src_id, timestamp          )
                       (1 , 1     , 2024-01-01 00:00:00)
                       (2 , 1     , 2024-01-01 00:00:00)
                  
TABLE data_point_values (id, data_point_time_id, meas_type, meas_value)
                        (1 , 1                 , A        , 100       )
                        (2 , 1                 , B        , 6.8       )
                        ...
                        
                        (3 , 2                 , A        , 55       )
                        (4 , 2                 , B        , 0.1       )
                        ...

Opção 4) jsonb

Eu poderia obter "o melhor dos dois mundos" usando jsonb; um número estático de colunas com menos linhas. Mas talvez isso tenha desvantagens das quais não conheço?

TABLE data_points (id, src_id, timestamp          , data                                 )
                  (1 , 1     , 2024-01-01 00:00:00, {"measurement_a": 100, "measurement_b": 6.8, ... })
                  (2 , 2     , 2024-01-01 00:00:00, {"measurement_a": 55 , "measurement_b": 0.1, ... })
                 

Qualquer ajuda é muito apreciada!

Não consigo GRANTaceitar current_database()como argumento. Todos eles falham com o mesmo erro:

GRANT ALL PRIVILEGES ON DATABASE current_database() to justintestin;
GRANT ALL PRIVILEGES ON DATABASE (current_database()) to justintestin;
GRANT ALL PRIVILEGES ON DATABASE (Select current_database()) to justintestin;
GRANT ALL PRIVILEGES ON DATABASE format('%I', current_database()) to justintestin;

Erro SQL [42601]: ERRO: erro de sintaxe em ou próximo a "("

Atualmente, eu uso EXECUTE:

Do 
$$ 
Begin
    Execute Format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', current_database(), 'justintestin');
End;
$$;

mas o detalhista sabe que deve haver uma maneira mais simples. Executando na imagem oficial do Docker postgres:16:

PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) em x86_64-pc-linux-gnu, compilado por gcc (Debian 12.2.0-14) 12.2.0, 64 bits"*).

A concessão de procedimentos armazenados de execução (todos) permite implicitamente quaisquer privilégios críticos? Por exemplo, privilégio para controlar a segurança, etc., executando os processos armazenados relevantes?

A maneira de conceder a execução de todos os procedimentos armazenados é:

GRANT EXECUTE TO [user]

Temos um aplicativo SaaS multilocatário. Estávamos usando um banco de dados compartilhado para todos os nossos locatários com TenantId como chave estrangeira em todas as tabelas. Tudo estava funcionando bem, até que nosso negócio exigia arquitetura de banco de dados (ou esquema) por locatário.

Atualizamos nosso back-end (ASP.NET Core 8) para lidar com essa arquitetura e migramos ambiciosamente todos os nossos locatários para um banco de dados separado em um cluster. Temos cerca de 1.000 bancos de dados no cluster e desde então nossa vida de migração tem sido muito difícil. Onde, com a mesma quantidade de dados e carga, até mesmo r7g.xlinstâncias de 32 GB e 4 CPUs pareciam superprovisionadas, agora até r7g.4xlinstâncias AWS de 16 CPUs e 128 GB às vezes parecem subprovisionadas.

Dos 1.000 bancos de dados, mais da metade não terá mais de 100 MB de dados. Poucos podem ter dados em poucos GB.

Como estávamos usando AWS DMS (sem servidor), a replicação também estava inativa há algum tempo. Então vieram os avisos no vácuo depois de algum tempo:

WARNING: oldest xmin is far in the past

Quando verificamos todos os motivos possíveis, descobrimos que os slots de replicação estão retendo o xmin. Aqui está o que a seguinte consulta retorna:

SELECT c.relnamespace::regnamespace as schema_name, c.relname as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') ORDER BY 4;

Resultado:

O valor está aumentando, tentamos excluir os slots de replicação duas vezes, e cada vez isso causou um tempo de inatividade e o cluster não conseguiu se recuperar e tivemos que recorrer à criação de um novo cluster a partir do backup. Então, no momento em que excluímos os slots de replicação inativos, isso acontece:

As consultas de inserção/atualização que, de outra forma, funcionam bem, de repente, logo após a exclusão dos slots de replicação, começam a mostrar bloqueios LW.

Esta é a aparência agora (slots não excluídos):

Passamos a acreditar que é um erro ter mais de 300 bancos de dados por cluster. Portanto, usaremos vários clusters com cerca de 300 bancos de dados por cluster.

Mas o que devemos fazer agora e por que a exclusão dos slots de replicação aciona esses bloqueios?

Desculpas pela pergunta de iniciante.

Tenho alguns bancos de dados AWS Postgres que receberão alguns milhares de gravações por segundo no dia de um evento.

Também preciso fazer consultas de leitura no banco de dados. Obviamente, isso seria melhor com índices, mas adicionar esses índices retardará muito as gravações.

Gostaria de configurar a replicação dos bancos de dados Postgres originais na AWS para permitir leituras de desempenho sem afetar gravações de desempenho.

Sei que não posso fazer replicação física porque não poderei adicionar índices. No entanto, fazer replicação lógica e adicionar índices nas réplicas é uma boa abordagem?

Ou existe uma maneira melhor de abordar isso?

Eu estava solucionando um problema de desempenho e preciso de um SP para parar de ser recompilado devido às estatísticas de atualização automática.

Não quero desabilitar a atualização automática de estatísticas em todo o banco de dados, então pensei em desativá-la em todas as tabelas envolvidas usando sp_autostatsproc. Mas mesmo depois de desabilitar as estatísticas de atualização automática em todas as tabelas envolvidas, ela ainda está sendo recompilada com o motivo "Estatísticas alteradas".

Verifiquei a última vez que as estatísticas foram atualizadas e também usei o evento estendido auto_stats para rastrear se as estatísticas estavam sendo atualizadas, mas não está.

REPRODUÇÃO

USE [test]
GO
CREATE TABLE [dbo].[test](
    [a] [int] IDENTITY(1,1) NOT NULL,   [b] [int] NULL, [c] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [a] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx] ON [dbo].[test]
(
    [c] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE proc [dbo].[sp1] @a int
as
select * from  test  where c=@a  order by b
GO
sp_autostats 'test','off'
go

--creating lots of changes.
--Session 1
set nocount on 
while 1=1
begin
delete top(1) from test
end

--Session 2
set nocount on 
while 1=1
insert into test select 1, FLOOR(RAND() * (10 - 1 + 1)) + 1;

--powershell 3.  Calling the SP in a loop
while ($true) {
    
        $sqlQuery = "EXEC test.dbo.sp1 @a=100;"
        Invoke-Sqlcmd -ServerInstance 'sql1\s14' -Database 'test' -Query $sqlQuery -Encrypt Optional
        Start-Sleep -Milliseconds 500
}

--Monitor sql_statement_recompile extended event.  Should see a recompile event in a couple minutes with "Statistics changed" as recompile_cause
CREATE EVENT SESSION [test] ON SERVER 
ADD EVENT sqlserver.sql_statement_recompile(SET collect_object_name=(1),collect_statement=(1)
    ACTION(sqlserver.server_principal_name,sqlserver.sql_text))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Suspeito que possa ter algo a ver com estatísticas na mesa de trabalho criada para esse tipo. se comentarmos a cláusula order by no SP o problema desaparece. Chamar periodicamente sp_recompiletambém parece redefinir o que quer que esteja rastreando e acionando a recompilação.

Alguma solução alternativa para isso e alguém mais enfrentou esse problema?