All perguntas(dba)

De acordo com 17.6.2.1 Índices agrupados e secundários ,

Índices diferentes do índice agrupado são conhecidos como índices secundários

Isso significa que existem apenas 2 tipos de índices: agrupados e secundários.

No entanto, 15.1.20.9 Índices secundários e colunas geradas diz

O InnoDB suporta índices secundários em colunas virtuais geradas. Outros tipos de índice não são suportados.

A forma plural "outros tipos de índice " implica que há pelo menos 3 tipos de índice (secundário + algo + algo).

Qual está correto?

Se o último estiver correto, quais são os outros tipos de índices especificamente além do índice secundário?

Então, eu gostaria de entender um conceito do MSSQL que é um pouco interessante para mim, mas não encontrei nenhuma resposta online até agora.

Tenho uma consulta que roda em uma tabela com mais de 100 mil linhas. Digamos que tenho algumas linhas que foram criadas em '2024-04-14', e não há linhas mais antigas do que isso.

A consulta procura entradas com este filtro:

CreatedAt < '2024-04-15'

A consulta funciona bem e retornará as linhas criadas em '2024-04-14'.

Mas se eu fizer isso:

CreatedAt < '2024-04-14'

A consulta será executada até retornar um tempo limite ou demorará muito para retornar um conjunto de resultados vazio, que é o resultado esperado, já que não temos linhas anteriores a '2024-04-15'.

Você pode me dizer qual conceito de SQL ou MSSQL está envolvido aqui e como posso evitar isso? O problema é que essa consulta é usada para recuperar dados para arquivamento, esses dados serão excluídos, tudo acontece em lotes. Temos um período de retenção de 150 dias, então, usando essas informações, calculamos uma data que usamos no filtro. Depois que o trabalho de arquivamento for executado algumas vezes e arquivar todos os dados encontrados, a consulta parará de encontrar dados mais antigos do que a data especificada, mas continuará a ser executada pelo resto do dia.

ATUALIZAÇÃO 1

Então voltei com mais detalhes.

A mesa:

CREATE TABLE [dbo].[Notifications](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [AlertId] [int] NOT NULL,
  [ChannelId] [int] NOT NULL,
  [Status] [int] NOT NULL,
  [Identifier] [nvarchar](1024) NOT NULL,
  [CreatedAt] [datetimeoffset](7) NOT NULL,
  [CanBeRetried] [bit] NULL,
  [RetryCount] [int] NOT NULL,
  ...
CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
 OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Notifications_CreatedAt] ON [dbo].[Notifications]
(
   [CreatedAt] ASC
)

CREATE NONCLUSTERED INDEX [IX_Notifications_AlertId_ChannelId_CreatedAt_Include_Identifier_Status_CanBeRetried_RetryCount] ON [dbo].[Notifications]
(
   [AlertId] ASC,
   [ChannelId] ASC,
   [CreatedAt] ASC
)
INCLUDE([Identifier],[Status],[CanBeRetried],[RetryCount]) 

CREATE NONCLUSTERED INDEX [IX_Notifications_AlertId_ChannelId_Identifier] ON [dbo].[Notifications]
(
   [AlertId] ASC,
   [ChannelId] ASC,
   [Identifier] ASC
)

CREATE NONCLUSTERED INDEX [IX_Notifications_CreatedAt_Status_Include_AlertId] ON [dbo].[Notifications]
(
   [CreatedAt] ASC,
   [Status] ASC
)
INCLUDE([AlertId]) 

Agora, os cenários + planos de execução. Para mais contexto, estou selecionando notificações que desejo arquivar, notificações são produzidas por um Alerta e desejo excluir do processo de arquivamento notificações que são produzidas por certos Alertas.

1. Criado em < '2024-04-14 09:00:00' E [n].[AlertId] NÃO EM ( '76', '126', '127', '128', '129')

https://www.brentozar.com/pastetheplan/?id=ByV4zrlaA

Este leva cerca de 11 minutos para processar. Não há notificações presentes além das excluídas, mais antigas do que essa data. Então isso produzirá um conjunto de resultados vazio. O otimizador escolheu uma varredura de índice clusterizado.

2. Criado em < '2024-04-15 09:00:00' E [n].[AlertId] NÃO EM ( '76', '126', '127', '128', '129')

https://www.brentozar.com/pastetheplan/?id=SJldhHxa0

Este leva cerca de 2 segundos para processar. Há notificações presentes além das excluídas, mais antigas que essa data. O otimizador escolheu, novamente, o mesmo Clustered Index Scan.

3. Criado em < '2023-09-13 11:00:00' E [n].[AlertId] NÃO EM ( '76', '126', '127', '128', '129')

https://www.brentozar.com/pastetheplan/?id=SJkp2Hl6R

Este leva 1 segundo. Não há nenhuma notificação, mais antiga que essa data. Este usa um Index Seek no índice IX_Notifications_CreatedAt.

Também encontrei isso em uma postagem: https://stackoverflow.com/questions/12846283/why-is-sql-server-not-using-index-for-very-similar-datetime-query

À medida que o intervalo cresce (e, portanto, o número de pesquisas necessárias), ele estima que será mais rápido apenas escanear todo o índice agrupado (de cobertura) evitando as pesquisas. Possivelmente incorretamente no seu caso. O ponto em que ele muda de um plano para o outro é conhecido como ponto de inflexão.

Essa resposta me deixaria satisfeito se o desempenho fosse ruim nos cenários 1 e 2, mas só ficou lento no cenário 1.

Tivemos um incidente estranho ontem à noite em nosso grupo AG do SQL Server 2014. Temos uma configuração de grupo de dois nós no modo assíncrono. O relógio no secundário estava atrasado em cerca de 30 segundos e se ajustou via NTP. Isso fez com que o grupo AG falhasse e se restabelecesse. Tudo isso faz sentido para mim.

O que não faz sentido é que quando o grupo AG falhou, todos os trabalhos ativos do agente SQL em execução no primário tiveram suas sessões eliminadas. Por que isso aconteceu? Não temos failover automático configurado e os trabalhos não estão realizando nenhuma consulta no nó secundário.

Considere a seguinte figura disponível no MySQL Workbench: Design Visual de Banco de Dados

Pergunta :

• Qual é o conceito usado para definir tabelas relacionadas entre si em uma espécie de pacote?

Quero saber esse conceito/termo para aprender sua respectiva sintaxe. Assim, se for usada outra GUI, como o DBeaver, quero ver a mesma estrutura. Claro que estou assumindo que as cores são definidas aleatoriamente pela GUI

Além disso: Essa sintaxe é portátil para outros provedores de BD? PostgreSQL, H2, etc.

Observe que conheço a CREATE TABLE ...sintaxe, mas como definir cada um em um "pacote" personalizado?

Tenho uma consulta em que retiro a parte do domínio dos endereços de e-mail de uma coluna e depois conto quantos usuários de qual domínio eu tenho.

Como obtenho a contagem total (todos os usuários em todos os domínios) na mesma consulta?

select count(regexp_replace( email_id , '^[^@]+@','')) as count, regexp_replace( email_id , '^[^@]+@','') as domain from logins 
  where last_login between '2024-08-06' and '2024-09-06'  
  group by regexp_replace( email_id , '^[^@]+@','') 
  order by count;

Tenho uma Instância Gerenciada de SQL no Azure com alguns bloqueios/deadlocks acontecendo. Esse BD estava no local e tinha todos os scripts instalados, então eu os desinstalei e então instalei os scripts específicos do Azure (Install-Azure.sql), baixados do site de Brent Ozar.

Todos os scripts parecem funcionar bem, exceto sp_blitzlock. O SpBlitz de fato confirma "94 deadlocks médios por dia. Para encontrá-los, execute sp_BlitzLock."

Estou executando-o como administrador do sistema, e ambas as tabelas principais estão em branco. (Quando executei-o como minha conta EntraID, tive um problema de permissão: Msg 50000, Nível 11, Estado 1, Procedimento sp_blitzlock, Linha 335 [Linha de início do lote 0] Uma sessão com o nome system_health não existe ou não está ativa no momento.

Não recebo nenhum erro ao executá-lo como um administrador de sistema de instância. Os dados estão apenas em branco. É possível que não haja dados, mas duvido, dado o que observamos em outros lugares e o que sp_blitz confirmou.

Baixei o mais recente First Responder Kit e estou usando sp_BlitzLock versão 8.21 e sp_Blitz versão 1 de jul. de 2024 12:00AM. Esta é uma Instância Gerenciada de SQL no Azure, a versão da instância é 12.0.2000.8. Estou apenas executando sp_blitzlock; no SSMS para executá-lo.

Pode valer a pena ressaltar que recentemente atualizamos o nível de serviço de General Purpose para Business Critical. Isso melhorou o desempenho e o bloqueio no geral, mas estou pensando se isso acionou uma nova instância nos bastidores, razão pela qual nenhum dado de deadlocking aparece. (Embora por que isso apareceria no sp_blitz?)

Alguma ideia?

Perdoe-me se essa for uma pergunta fácil, mas tentei encontrar uma resposta, mas realmente não sei o que procurar (sou um desenvolvedor de software, com apenas alguma experiência limitada como DBA).

Temos servidores SQL de teste configurados com 3 instâncias cada:

TSTSQL01\Enterprise
TSTSQL01\Warehouse
TSTSQL01\Legacy

TSTSQL02\Enterprise
TSTSQL02\Warehouse
TSTSQL02\Legacy

TSTSQL03\Enterprise
...

Na produção não usamos instâncias, temos 3 servidores separados:

PRDSQL01 --"Enterprise" server
PRDSQL02 --"Warehouse" server
PRDSQL03 --"Legacy" server

Devido às limitações do nosso aplicativo COBOL legado, preciso de uma string de conexão que tenha EXATAMENTE 8 caracteres para corresponder ao comprimento das strings de conexão de produção. O que quero fazer é criar um alias para cada servidor sql de teste que permita isso, usando o exemplo tstsql01 acima, idealmente ficaria assim:

TSQL1ENT --maps to TSTSQL01\Enterprise
TSQL1WAR --maps to TSTSQL01\Warehouse
TSQL1LEG --maps to TSTSQL01\Legacy

Minha solução alternativa atual é usar o SQL Configuration Manager para adicionar um alias . Isso me permite conectar ao banco de dados naquele servidor, mas não em estações de trabalho locais. Nosso aplicativo COBOL legado executa 75% do código do servidor, então ele funciona para isso. Mas os 25% restantes do código são executados localmente em suas estações de trabalho. Isso significa que tenho que adicionar esse alias na máquina de cada testador (eu criei um script via powershell, mas os testadores não são administradores, então requer uma solicitação de help desk para cada pessoa para cada servidor de teste).

Agradecemos desde já a ajuda!

Se eu estiver consultando um banco de dados para selecionar todos os registros que têm um VARCHAR()campo igual a um parâmetro de consulta. Há alguma vantagem de velocidade em fazer uma pesquisa linear linha por linha de um "banco de dados" de arquivo simples? Se sim, como essa aceleração é realizada?

Suponha que eu tenha uma tabela com muitas colunas com as quais não me importo, mas duas com as quais me importo: Primarye Secondary. Há uma chave primária agrupada em Primary.

CREATE TABLE [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary] INT PRIMARY KEY CLUSTERED,
    [Secondary] NVARCHAR(500) NOT NULL,
    [Evil1] NVARCHAR(MAX),
    [Evil2] NVARCHAR(MAX),
    [Evil3] NVARCHAR(MAX),
    [Evil4] NVARCHAR(MAX),
    [Evil5] NVARCHAR(MAX)
);

Como não me importo com nenhuma das outras colunas, decido que quero este índice:

CREATE NONCLUSTERED INDEX [IX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

Dado que a lista de chaves deste índice contém a chave primária, não há dúvidas de que a lista de chaves é única. Assim, estou tentado a torná-la UNIQUE.

CREATE UNIQUE NONCLUSTERED INDEX [UIX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

Mas isso tem algum benefício? E o que dizer do mesmo caso exato, mas com o Secondarycomo a chave de índice principal seguido por Primary?

Tenho um caso estranho de uma consulta que demora para ser executada, apesar de ter um bom índice de correspondência. O problema é que os valores das 2 primeiras colunas em um índice não clusterizado são NULL em todas as linhas. (Para contexto, ficou assim depois de arquivar dados antigos de processos de negócios antigos). A consulta tem cláusula WHERE nessas 2 colunas usando igualdade e valores não NULL, portanto, deve ser fácil para o SQL Server preparar um bom plano usando index seek+lookup, especialmente porque os dados que estão sendo procurados não existem, então as estimativas são 0 ou 1 linha.

Infelizmente, a consulta é muito sensível a alterações na tabela, nem mesmo à atualização automática de estatísticas ou distorção de dados. Suspeito que pode ser um bug no otimizador, onde ele não pode fazer uso de histogramas de estatísticas de etapa única, onde NULL é o único valor no intervalo. Não tenho certeza se explorei todas as opções, portanto, gostaria que alguém desse uma olhada. O servidor de produção é 2017, mas preparei esta reprodução em 2022, e o comportamento é idêntico nessas 2 versões.

A tabela em produção contém 35 milhões de linhas e cerca de 60 colunas e está sendo atualizada (principalmente inserção+atualização) em cerca de 3.000 linhas em 1 hora, portanto, não há muito tráfego de gravação, principalmente leituras.

Tem alguma coisa que eu esqueci? Ou é um bug real?

Agradeço antecipadamente.

use tempdb;
go

-- The simplest repro of the issue.

-- Just in case you wondered if it's enabled. It's not needed, but included for your reference.
alter database tempdb set auto_create_statistics on;
alter database tempdb set auto_update_statistics on;

drop table if exists t1;

create table t1 (
  ID int identity(1,1) not null
      primary key clustered,
  c2 int null,
  c3 varchar(16) null,
  c4 int null,
  c5 varchar(128) null,
  -- in prod there are ~50 more columns
);


-- The actual prod index has 4 columns with explicit ID PK at the end.
create index ix_test on t1 (c2, c3, c4, ID);

-- The prod table contains ~35 million rows. The issue occurs with much smaller
-- number of rows so I only use 1 million here to not waste your time playing with it.
--
-- It happens that in prod all rows have NULL values in c2 and c3.
insert into t1 (c4, c5)
select top (1000*1000) crypt_gen_random(2), 'a'
from sys.all_columns ac1, sys.all_columns ac2;

-- At this point the query uses the index (see the plan).
declare @0 int = 100;      -- value that may or may not exist
select ID, c2, c3, c4, c5  -- and all remaining columns in prod
from t1
where c2 = @0 and c3 = 'x' -- 'x' is a value that may or may not exist
order by c2, c3, c4, ID;   -- this is the exact order of the index
go

-- But with "option (recompile)" it does the full scan of the clustered index
-- and has awful estimates.
declare @0 int = 100;
select ID, c2, c3, c4, c5
from t1
where c2 = @0 and c3 = 'x'
order by c2, c3, c4, ID
option (recompile);
go

-- The same issue also occurs when not using parameters.
select ID, c2, c3, c4, c5
from t1
where c2 = 100 and c3 = 'x'
order by c2, c3, c4, ID
option (recompile);

-- Now let's update statistics to be as great as possible.
update statistics t1 with fullscan, maxdop=1;

-- After which it picks the right index and have great estimates with or
-- without "option (recompile)".
declare @0 int = 100;
select ID, c2, c3, c4, c5
from t1
where c2 = @0 and c3 = 'x'
order by c2, c3, c4, ID
option (recompile);

-- Insert only a tiny bit of more data. It does not trigger auto update of the
-- statistics.
insert into t1 (c4, c5)
select top (10) 1, crypt_gen_random(2)
from sys.all_columns ac1, sys.all_columns ac2;

-- And now it again does the full scan even when not using the hint nor the
-- @parameter.
select ID, c2, c3, c4, c5
from t1
where c2 = 100 and c3 = 'x'
order by c2, c3, c4, ID;
go

-- If the above gave you seek+lookup, this one will not:
declare @0 int = 100;
select ID, c2, c3, c4, c5
from t1
where c2 = @0 and c3 = 'x'
order by c2, c3, c4, ID
option (recompile);

-- When using the index hint it works fine, but has awful estimates.
select ID, c2, c3, c4, c5
from t1
where c2 = 100 and c3 = 'x'
order by c2, c3, c4, ID
option (table hint (t1, index (ix_test)));

-- I would expect it to just always use the index seek + lookup. The query
-- should always be fast. It's easy to determine that there's no data
-- satisfying the filter.

-- When you check the stats histogram, there's none.
declare @stats_id int = (
  select stats_id from sys.stats
  where [object_id] = object_id('t1') and name = 'ix_test');
select * from sys.dm_db_stats_histogram(object_id('t1'), @stats_id);

/* In SSMS you can see that there's only 1 step:
Statistics for INDEX 'ix_test'.
-------------------------------------------------------------------------------------------------------------------
   Name             Updated     Rows   Rows Sampled   Steps  Density  Average Key Length  String Index
-------------------------------------------------------------------------------------------------------------------
ix_test   Sep 9 2024 4:59PM  1000000        1000000       1        0                   8            NO  1000000  0

 All Density  Average Length         Columns
--------------------------------------------
           1               0              c2
           1               0          c2, c3
1.525879E-05               4      c2, c3, c4
       1E-06               8  c2, c3, c4, ID

Histogram Steps
RANGE_HI_KEY  RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
----------------------------------------------------------------------
                       0  1000000                    0               1

so the optimizer has the knowledge that all rows have NULL values, and
that there's nothing else in the table, it just is unable to use that
information in some cases.
*/

EDIT: Este é o plano de consulta ao usar OPTION (RECOMPILE):

e esse é o plano sem a dica. Eu perdi o fato de que ele foi parametrizado apesar de passar o valor literal na string: