Sranda's questions

Estou executando o seguinte select:

SELECT tstats.object_id, tstats.database_id, tstats.sql_handle, 
DB_NAME(tstats.database_id) AS [db_name], OBJECT_NAME(tstats.object_id) AS      
trigger_name, tstats.execution_count, tstats.total_worker_time,     
tstats.total_logical_reads, tstats.total_logical_writes, OBJECT_NAME(trgs.parent_id) AS  
trigger_parent, getdate() AS [timestamp] FROM sys.dm_exec_trigger_stats tstats INNER 
JOIN  sys.triggers trgs ON tstats.object_id = trgs.object_id ORDER BY object_id asc

A versão do servidor SQL é o padrão 2019 (RTM-CU24).

O problema é que alguns dos gatilhos são retornados duas vezes (mesmo ID de objeto, contagem de execução diferente e outras estatísticas):

Estou tentando fazer uma amostra deste sys.dm_exec_trigger_statsDMV e fazer deltas por minuto para ver quais gatilhos estão prejudicando o desempenho do aplicativo - pensei que a combinação do nome do banco de dados, o nome pai do gatilho e o nome do gatilho (ou object_id) fossem exclusivos. Eles são retornados várias vezes com estatísticas de tempo de execução diferentes que não consigo entender. Dicas apreciadas.

Atualmente, tenho cluster do Windows 2012R2 de 1 nó (é um laboratório) com SQL server 2014 instalado (corrigido para o último patch baseado em sqlserverupdates.com). O objetivo é atualizar o cluster para algo mais novo e com suporte. Descobri que um nó com o sistema operacional mais jovem que posso ingressar no cluster é 2016 (provavelmente não é possível pular a versão do sistema operacional e a adesão a 2019 falhou de qualquer maneira). Isso é tolerável. Quando tentei instalar o SQL 2016,2019,2022, todos eles (veja a captura de tela) afirmam não ver o cluster, apenas o SQL 2014 sendo instalado no nó Win2016 o faz. Lendo fóruns durante todo o dia, estou convencido de que é possível ingressar no SQL (2016+) mais recente ao cluster existente com o SQL (2014) mais antigo sem nenhum problema.

Então, ao fazer failover para o SQL mais recente, estou ciente de que não posso voltar para o SQL mais antigo (o SQL atualiza os arquivos internamente), e tudo bem - o objetivo é convidar gradualmente os nós mais novos para agrupar e remover os nós antigos e sem suporte. Mas por que não consigo juntar o SQL2016+ ao cluster é um enigma para mim em primeiro lugar. Sugestões apreciadas.

Estou coçando a cabeça, relendo a ajuda da MSFT e ainda não consigo entender a diferença entre forwarded_fetch_count e forwarded_record_count em sys.dm_db_index_operative_stats e sys.dm_db_index_physical_stats. Deixe-me ilustrar meu problema de compreensão dos pontos de vista com o exemplo abaixo.

Eu executei as seguintes consultas:

;with heaps as ( 
select 
    DB_NAME(DB_ID()) dbname, object_name ( p.object_id ) objname, sum(row_count) row_count,
    DB_ID() database_id, p.object_id objectid
from 
    sys.dm_db_partition_stats p
    join sys.objects o on o.object_id = p.object_id 
WHERE 
    index_id = 0 and o.is_ms_shipped = 0 --and row_count > 0
    group by p.object_id ) 
select 
    h.*, 
    forwarded_fetch_count
from heaps h
    cross apply sys.dm_db_index_operational_stats(database_id, objectid, 0, null) ps
    WHERE forwarded_fetch_count > 0 ORDER BY forwarded_fetch_count DESC¨

E

SELECT page_count, OBJECT_NAME(ps.object_id)
,avg_record_size_in_bytes
,avg_page_space_used_in_percent
,forwarded_record_count 
FROM sys.dm_db_index_physical_stats(db_id('your_db_name'), NULL,NULL, NULL, 'DETAILED') AS ps
WHERE forwarded_record_count IS NOT NULL AND forwarded_record_count > 0
GO

Ambas as consultas retornaram listas diferentes de tabelas em um único banco de dados que estou ajustando. A primeira consulta retornou tabelas variando de 1.000 a 100.000 buscas encaminhadas por tabela, a segunda consulta retornou qualquer coisa entre 10 e 60.000 contagens de registros encaminhados para outro conjunto de tabelas.

Para consertar a fita adesiva, reconstruí as tabelas em questão. No entanto, no monitor de desempenho do Windows, ainda vejo muitos registros encaminhados/s (o gráfico geralmente chega a 100). Ao executar sp_blitzfirst @seconds = 30, sou alertado sobre muitas buscas encaminhadas/s. Uma vez que o alerta é geral (ou seja, não relacionado a nenhum banco de dados), outros 9 alertas da mesma instrução mencionam: "Forwarded Fetches/Sec High: TempDB Object". E de acordo com a contagem de buscas encaminhadas, há dez ou cem vezes mais buscas encaminhadas no TempDB do que fora do TempDB.

Por último, mas não menos importante, o ISV implementou fortemente gatilhos (que relaciono com as buscas encaminhadas do TempDB).

Minhas perguntas:

• Qual é a diferença entre as duas colunas em duas visualizações?
• Ao lidar com banco de dados lento devido a registros encaminhados em um heap, qual devo usar? (Eu sei que a ajuda da MSFT menciona forwarded_record_count em dm_db_index_physical_stats relacionado especificamente a heaps, ainda não deixando isso mais claro para mim).
• Posso identificar o que no TempDB está causando essas buscas encaminhadas?

Eu tenho um problema que se manifesta da seguinte maneira, meu conhecimento do TempDB não cobre isso (ainda):

• Uma consulta analítica, que roda no SSMS por cerca de 200ms, continua rodando no servidor SQL, quando iniciada a partir do aplicativo, por mais de 60 segundos - isso acontece apenas ocasionalmente, na maioria das vezes o problema não está presente
• A fila de consultas executáveis/suspensas pode crescer até dezenas de consultas do mesmo texto de consulta, sendo uma delas consultas SELECT um bloqueador de cabeça bloqueando outros SELECTs idênticos
• as esperas mais dominantes são SOS_SCHEDULER_YIELD e PAGELATCH_UP para as consultas específicas do mesmo texto e valores de parâmetro, quando as filas executáveis/suspensas começam a crescer significativamente em comparação com a linha de base
• quando o problema ocorre, as dezenas de consultas na fila têm os mesmos valores literais de parâmetros (timestamp de início de turno, id de funcionário e área de produção)
• as queries que estão sendo suspensas (durante o tempo do snapshot do nosso monitoramento - DBA Dash) têm a espera PAGELATCH_UP como a mais dominante e estão esperando a página GAM em tempdb
• a consulta não está vazando para o tempdb quando verifico o plano de execução no SSMS, usando os parâmetros da consulta que continua se acumulando nas filas executáveis/suspensas

Configuração do servidor, do banco de dados e do tráfego do banco de dados:

• 4 a 6 núcleos (o problema ocorreu independentemente em dois servidores diferentes com contagem de núcleo diferente)
• 4 - 6 arquivos TempDB de tamanho uniforme
• 40 GB de RAM atribuídos à instância
• no banco de dados específico, o RCSI + Snapshot está ativado (portanto, o TempDB está sendo atingido)
• nenhuma exclusão está ocorrendo nas duas tabelas nas quais a consulta é executada, apenas INSERTs e SELECTs - apenas 1 linha por vez é inserida
• SELECTs estão atingindo geralmente os registros mais novos (aqueles inseridos recentemente)
• o servidor está fazendo geralmente 400 - 700 solicitações em lote/s; Quando ocorre o problema, atinge um pico de até 1500, criando alta carga de E/S + CPU em comparação com a operação normal
• os dados na tabela têm distribuição mais ou menos uniforme, ou seja, nenhum funcionário em nenhum turno para determinada área de produção tem significativamente mais registros do que os outros
• a captura de tela em anexo é uma tabela que mostra o instantâneo do DBA Dash em um momento em que o problema se manifesta totalmente
• tempDB, apesar do snapshot RCSI+ não crescer muito, são alguns GBs em um drive de 60GB (status estável por vários meses e depois de alguns episódios do que descrevo aqui)
• todas as consultas SELECT na captura de tela têm espera PAGELATCH_UP, aproximadamente metade delas tem as seguintes informações adicionais:
  • tipo de recurso de espera: PAGE
  • recurso de espera: 2:3:2 (acho que o significado é: banco de dados tempdb, 3º arquivo em tempdb, 2ª página que estamos esperando)
  • arquivo de espera: PRIMÁRIO | temp2
  • tipo de página: GAM
  • Esperar para compilar: Falso

Além do esforço óbvio para chegar à causa raiz e corrigi-la permanentemente, minhas principais perguntas seriam:

• como é que as consultas SELECT podem acessar páginas GAM pesadamente em tempdb?
• se eu admitir que SELECT pode tocar na página GAM com frequência, o que pode impedir o progresso da consulta (bloqueador de cabeça) (ou seja, ler a página GAM e disponibilizá-la imediatamente para outras pessoas)?

Apontar para qualquer recurso / conselho de estudo relevante seria muito apreciado

plano de consulta aqui

EDIT: Para responder ao comentário de John, gostaria de acrescentar o seguinte:

• CTFP: 30
• MAXDOP: 4 (definido para ambos os servidores SQL, embora um tenha 6 núcleos, o outro tenha 4)
• existem 2 instâncias por SQL VM - esta instância em que ocorreu o problema, depois outra instância de "escritório", que coloca uma carga insignificante na VM do servidor SQL
• Tabelas

ManualPanelEntries : 6933089 registros, 1221 MB de dados, 2591 MB de índices - de fato, os índices consomem mais espaço do que os próprios dados

Pesos : 3108486 registros, 178 MB de dados, 170 MB de índices

• Todo o banco de dados: 125 GB

============

O que descobri depois de escrever a postagem é que também o bloqueador de cabeça (um dos muitos SELECTs paralelos idênticos) espera pelo GAM, segunda página de um arquivo tempdb específico. Como se algo mais (armazenamento de versão?) Bloqueasse uma página GAM específica, impedindo que outras consultas usando o TempDB continuassem.

O ambiente em questão consiste no domínio AD e 2 VMs AZ SQL. A versão do servidor SQL é SQL 2022 Standard - CU 2. Atualmente, existem três instâncias de banco de dados em cada AZ SQL VM e os bancos de dados nessas instâncias funcionam em AlwaysON AG (básico) para bancos de dados que não são do sistema.

Recentemente, cooperei com a Microsoft para aproveitar toda a hospedagem de servidores SQL no Azure e seus conselhos, para aproveitar totalmente os recursos do agente SQL IaaS do Azure é instalar a instância padrão, além de 3 instâncias nomeadas. O que eu fiz.

Agora, para essas 3 instâncias, a situação é a seguinte:

• As VMs do servidor SQL têm, digamos, endereços A, B em sua sub-rede
• Os ouvintes para cada instância e para cada banco de dados nessa instância têm endereços C,D,E,F....
• Os ouvintes em 2 instâncias são executados em portas TCP > 49152
• Os ouvintes da última instância são executados nas portas 1433, mas em endereços diferentes de A e B

Meu problema Depois de instalar a instância SQL padrão de acordo com o conselho da Microsoft, ao conectar-me ao ouvinte na porta 1433, de alguma forma acabei na instância padrão em vez da instância à qual pertence o ouvinte em execução no TCP 1433.

/* EDIT 01 */ Para responder às valiosas perguntas de Sean:

Para alcançar o ouvinte AG escutando em 1433 (mas em IP diferente do endereço do servidor), usei <Listener's DNS name> e acabei na instância padrão - ambas rodando em 1433, mas com IPs diferentes. Depois que a instância padrão foi desinstalada, tudo começou a funcionar novamente.

Todas as instâncias nomeadas escutam em portas dinâmicas, a instância padrão escuta em 1433. Em 1433, também ouvintes de uma instância nomeada escutam, mas cada um desses ouvintes tem um endereço IP individual. A configuração da rede está de acordo com a diretriz MSFT - balanceador de carga do Azure na frente das VMs AZ SQL para que os AGs funcionem corretamente. Não toquei no balanceador de carga para refletir a instância padrão escutando em 1433, porque essa instância padrão não tinha nenhum AG.

/* Fim da EDIÇÃO 01 */

Alguém tem uma ideia do porquê?

Eu gostaria de ajustar esta consulta - ela está lendo mais linhas do que eu gostaria. Adicionei um índice conforme sugerido pelo Database Tuning Advisor.

A consulta fica assim:

SET STATISTICS TIME, IO ON;

SELECT TOP 1000 project,
    Max(timestamp) timestamp
FROM   the_table
WHERE  deviceid = 581
GROUP  BY project

As estatísticas IO , Time são assim:

Table 'the_table'. Scan count 1, logical reads 2373, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 390 ms,  elapsed time = 829 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

O plano de execução pode ser encontrado aqui .

O dta_index que esta consulta procura consiste em projectnumbere timestamp. A tabela tem aproximadamente 4.500.000 linhas, consumindo aproximadamente 350 MB e cada uma deviceidtem aproximadamente a mesma quantidade de registros (ou seja, distribuição uniforme e cada dispositivo tem aproximadamente 700 mil registros).

Meu problema: embora eu esteja solicitando apenas as primeiras 1.000 linhas da tabela, não importa o índice que criei ou como tentei reescrever a consulta, sempre acabei lendo +/- 700K linhas (ou seja, todas as linhas para o dispositivo selecionado na WHEREcláusula). Eu ficaria grato se você pudesse deixar suas dicas sobre como ajustar esta consulta.

Eu não acho que haja uma maneira de restringir essa consulta mais rápido do que tocar as linhas de aproximadamente 700K para particular deviceidcom o primeiro operador de exec. plano.

Aqui está um exemplo reproduzível mínimo, incluindo a definição da tabela e a inserção de duas linhas de amostra:

CREATE TABLE [dbo].[the_table]
(
    [primary_key_col] [int] IDENTITY(1,1) NOT NULL,
    [Project] [int] NULL,
    [DeviceID] [int] NULL,
    [TimeStamp] [datetime] NULL,
    [Quantity] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [primary_key_col] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

SET IDENTITY_INSERT [dbo].[the_table] ON;
INSERT [dbo].[the_table] ([primary_key_col], [Project], [DeviceID], [TimeStamp], [Quantity]) VALUES (4825107, 1232, 587, CAST(N'2021-10-23T05:48:48.000' AS DateTime), 1);
INSERT [dbo].[the_table]  ([primary_key_col], [Project], [DeviceID], [TimeStamp], [Quantity])  VALUES (4825108, 6458, 581, CAST(N'2021-10-23T05:49:17.000' AS DateTime), 1);

Os índices não fazem parte da questão deliberadamente, não importa qual índice eu tente, sempre atingi 700 mil linhas para um deviceID específico.

Obrigado.

Estou usando esta consulta para examinar mais profundamente meus índices columnstore enquanto fotografo meus planos de execução (ou seja, eliminação de segmento etc.):

    SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    columns.name AS column_name,
    partitions.partition_number,
    column_store_segments.segment_id,
    column_store_segments.min_data_id,
    column_store_segments.max_data_id,
    column_store_segments.row_count
FROM sys.column_store_segments
INNER JOIN sys.partitions
ON column_store_segments.hobt_id = partitions.hobt_id
INNER JOIN sys.indexes
ON indexes.index_id = partitions.index_id
AND indexes.object_id = partitions.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
AND column_store_segments.column_id = 
     columns.column_id
WHERE tables.name = 'R***'

ORDER BY tables.name, columns.name, 
column_store_segments.segment_id;

(nome da tabela editado).

O resultado da análise é o seguinte: Postimg .

Minha pergunta é simples: Como é que o resultado da análise não corresponde ao nome das colunas nos índices? Como você pode ver na parte direita da imagem, os nomes das colunas na grade de resultados são diferentes do que é mostrado nas propriedades do índice columnstore.

Ambiente: SQL 2019 CU10 Standard; Servidor Windows 2019.

Obrigado antecipadamente por um feedback, tome cuidado S.