Data Dill's questions

Deixe-me começar esta postagem dizendo que alguns eventos estão faltando em meu rastreamento, mas já os adicionei para a próxima vez que isso ocorrer.

Ultimamente temos visto surtos estranhos de tipos de espera HADR_SYNC_COMMIT em nosso ambiente (~40k tran/s). O "incidente" de hoje ocorreu às 4h58:

Antes de continuar, devo acrescentar que estávamos fazendo manutenção de índice ONLINE em uma grande tabela de auditoria (auditoria no sentido de que uma tabela OLTP aciona registros para esta tabela de auditoria em um volume alto) e que a reconstrução do índice foi bloqueada por aproximadamente 22 segundos. Claramente, isso desempenha um papel neste caso específico, mas não tenho certeza de como isso se relaciona com HADR_SYNC_COMMIT. Além disso, temos visto isso ocorrer durante o dia, quando não fazemos manutenção do índice.

Olhando para o rastreamento, aqui está o que vejo no primário:

...e tudo no secundário:

... e finalmente de volta ao primário:

Um problema semelhante aconteceu novamente em 01/12/2023 por volta das 4h11 e acredito ter visto o que está acontecendo. Infelizmente, não tenho eventos estendidos para este cenário, mas tenho alguns registros que podem mostrar uma imagem mais clara. A partir de 01/12/2023 04:10:18.5430090, a manutenção do índice de Ola registrou um horário de início para um índice no banco de dados em questão. Ola relatou um tempo de conclusão de 2023-12-01 04:11:13.9431563, mas acredito que o REBUILD WITH ONLINE = ON real foi concluído muito antes disso.

Ao revisar o DPA, percebi que as esperas de pagelatch_sh e pagelatch_ex aumentaram entre 4h11:03 e 4h11:04:

Imediatamente após essas esperas, a mesma consulta começou a ver HADR_SYNC_COMMIT e essa mesma espera caiu completamente às 4:11:13-4:11:14 da manhã, que é exatamente quando Ola relatou o término do índice. Minha suposição é que o índice REBUILD foi confirmado às 4h11min03s (cerca de 45 segundos de trabalho), o que fez com que as consultas INSERT não relacionadas que estavam no mesmo banco de dados simplesmente esperassem que todos esses blocos de log fossem reforçados no secundário . Assim que o índice foi concluído, os blocos de log restantes foram reforçados instantaneamente, pois eram apenas pequenos INSERTs.

Fundo:

Para manter as coisas simples, temos um cluster de 2 nós onde cada nó no cluster tem 3 instâncias do SQL instaladas: padrão + 2 nomeadas. Entendo que o empilhamento de instâncias é ruim e é algo do qual estamos nos afastando ativamente. 2/3 desses nós mencionados utilizam SQL Server AGs (padrão e 1 instância nomeada), enquanto o outro utiliza FCI para DR (esta última instância realmente não pertence à questão, mas adicioná-la de qualquer maneira). Todos os nossos ouvintes utilizam a porta 1433 porque a Microsoft recomendou essa abordagem anos atrás durante uma migração, pois adicionar uma porta não padrão exigiria a inclusão do número da porta no nome do ouvinte durante a conexão. em retrospectiva, não sei por que não seria possível simplesmente incluir a porta no ouvinte AG na criação, pois ela é ofuscada para o aplicativo por meio do CNAME. acredito que não

Pergunta:

No exemplo abaixo, quando a instância padrão do SQL é configurada em uma porta não padrão (por exemplo, 1400), todos os aplicativos se conectam conforme o esperado. No entanto, quando a porta padrão está sendo usada, todos os CNAMES roteiam inesperadamente para 1433 (supondo que seja porque o próprio ouvinte está na porta 1433). O que estou tentando determinar é quando um usuário tenta se conectar a 1433, mas não existe, o navegador identifica que não existe nenhuma instância com a porta 1433 e, portanto, faz algum tipo de lógica secundária de verificação/resolução para correlacionar o nome de o próprio ouvinte com a instância adequada do SQL?

Encontrei outro documento da Microsoft que explica esse cenário, mas não explica como o SQL é capaz de relacionar um VNN de ouvinte com a própria instância. Parece-me que o recurso WSFC relaciona essas informações ao cliente para determinar qual ouvinte roteia para qual instância. No entanto, se houver uma instância em 1433, o SQL roteará tudo incorretamente para essa instância, assumindo que todos os ouvintes estão configurados em 1433 por qualquer motivo.

"Porta do ouvinte Ao configurar um ouvinte do grupo de disponibilidade, você deve designar uma porta via SSMS. Você pode configurar a porta padrão como 1433 para permitir a simplicidade das cadeias de conexão do cliente. Isso significa que, se você usar 1433, não Não é necessário incluir um número de porta em uma cadeia de conexão do seu aplicativo. Além disso, como cada ouvinte de grupo de disponibilidade terá um nome de rede virtual separado, cada ouvinte de grupo de disponibilidade configurado em um único WSFC pode ser configurado para referenciar a mesma porta padrão de 1433 .

Se você usar a porta padrão de 1433 para VNNs de ouvinte de grupo de disponibilidade, ainda precisará garantir que nenhum outro serviço no nó do cluster esteja usando essa porta; caso contrário, isso causaria um conflito de porta.

Se uma das instâncias do SQL Server já estiver escutando na porta TCP 1433 por meio do ouvinte de instância e não houver outros serviços (incluindo instâncias adicionais do SQL Server) no computador escutando na porta 1433, isso não causará um conflito de porta com o ouvinte do grupo de disponibilidade. Isso ocorre porque o ouvinte do grupo de disponibilidade pode compartilhar a mesma porta TCP dentro do mesmo processo. No entanto, várias instâncias do SQL Server (lado a lado) não devem ser configuradas para escutar na mesma porta porque uma delas falhará ao escutar as conexões.

Você também pode designar uma porta de ouvinte de grupo de disponibilidade não padrão. No entanto, você também precisa usar explicitamente a porta de destino na cadeia de conexão do aplicativo ao se conectar a um ouvinte. Você também precisa abrir permissão no firewall para esta porta.

Você pode se conectar ao ouvinte usando o nome e a porta (se não for 1433). A porta pode ser a porta do ouvinte ou a porta subjacente do SQL Server na qual está configurada para escutar." - https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability -group-listener-overview?view=sql-server-ver16

Estou tentando determinar se este é ou não um bug de relatório de plano de execução ou um recurso real que está funcionando conforme o esperado.

No meu plano de execução, ao usar o MAXDOP 1, estou vendo a tabela inteira sendo verificada ( https://www.brentozar.com/pastetheplan/?id=HkPFquLdc - Muito inferior do plano O objeto 11 mostra ~ 2,5 milhões de linhas lidas, o que é toda a mesa).

No entanto, quando deixo o mecanismo escolher seu próprio plano sem dicas, ele fica paralelo ( https://www.brentozar.com/pastetheplan/?id=r12p5OUdc ) e faz uma correspondência de bitmap/hash e esse mesmo Object11 só mostra ~ 534k linhas lidas apesar de fazer uma varredura de índice e ter vários predicados em outras colunas que não estão no índice clusterizado.

Eu esperaria que o SQL tivesse que ler cada linha na tabela para avaliar cada predicado, mas talvez o PROBE IN (você não pode ver isso em colar o plano) no Objeto 11 no plano paralelo é capaz de "Filtrar páginas " pois a sonda está no PK/CX.

Eu criei uma sessão de eventos estendida que observa o tipo de evento module_start e filtra com base no object_name: equal_i_sql_unicode_string]([object_name])

O objetivo desta sessão é simplesmente registrar informações básicas sempre que um proc na lista de filtros for chamado para que eu possa responder às perguntas dos desenvolvedores com 99,9% de garantia se um proc ainda é chamado em produção. A ideia é executar isso por ~ 1 mês 24 horas por dia, 7 dias por semana (sim, não leva em conta as coisas que são executadas anualmente, mas é o que é).

O problema que estou enfrentando é que a lista de procs que o desenvolvedor me deu tem cerca de 90 ou mais e a lista de filtros de uma sessão EE é limitada a 3.000 caracteres. A única ideia que tive para aumentar a taxa na qual podemos rastrear os procs é ter 2 sessões de EE separadas que são idênticas, exceto que os predicados de filtro são diferentes.

Não estou perguntando "qual será o impacto da CPU", mas mais ou menos é a preocupação deles em executar 2 das mesmas sessões de EE com diferentes predicados de filtro? É estranho para mim que a Microsoft limite a lista de filtros a 3.000 caracteres quando 'mais filtragem == melhor desempenho' porque a maneira como o EE é incorporado ao mecanismo é muito otimizado, ao contrário de um rastreamento que age mais como um proxy do que um "gatilho baseado em um evento".

É seguro assumir que, seja qual for o impacto no desempenho de executar 1 sessão, posso multiplicá-lo por 2 ou não estou considerando outras preocupações?

Acho que posso saber a resposta com base em minha pesquisa, mas estou procurando confirmação de como/por que o mecanismo compila o plano da maneira que faz com

Parâmetros sendo passados: @ID int ,@OtherID INT

SELECT  b.Column1
        ,b.Column2
        ,b.Column3
        ,b.Column4
        ,b.Column5
        ,c.Column1
        ,b.Column1
        ,e.Column1
FROM    Table1 AS b 
        inner join Table2 AS t
        on b.ID = t.ID
        left join [LINKED SERVER].[DB].dbo.Table3 as c
        on b.ID = c.ID
        left join Table4 AS e
        on b.ID= e.ID
where   (b.ID = @ID or @ID= 0)
        And b.ID = @OtherID
        And b.ID IS NOT NULL
        and e.ID = 1

Agora eu determinei que a causa da varredura de índice é por causa desta linha: where (b.ID = @ID or @ID= 0). Mais especificamente, @ID = 0. Para esclarecer ainda mais, 0 para esse campo de ID não existe como um valor na tabela subjacente, foi simplesmente algo que um desenvolvedor fez para permitir que um usuário recuperasse todos os resultados passando 0 para o parâmetro e, em seguida, verificando se esse parâmetro é 0, como resultado, mais linhas são puxadas de volta (normalmente, você retornaria apenas 1-3 resultados).

Agora, o que é extremamente estranho, é que se eu adicionar OPTION RECOMPILE, o mecanismo é capaz de criar um plano muito melhor ao custo de sobrecarga (tempo de compilação), é claro:

O que eu gostaria de saber é como isso é possível. Pelo que li online, usando OPTION RECOMPILE, o mecanismo substituirá literalmente o valor pelo valor real passado para o parâmetro e pode ver muito facilmente que @ID 1234 não é igual a 0. No entanto, se você não usar OPTION RECOMPILEo mecanismo pegará o número total de registros, que é 120.000, e o dividirá pelo número total de possibilidades distintas, 107.000. Isso resulta em cerca de 1,1 linhas estimadas sendo retornadas e confirmei isso observando as propriedades estimadas do plano que tem a verificação de índice, mas por que o mecanismo continuaria a verificação de índice se a estimativa estiver correta? Até atualizei as estatísticas só para ter certeza.

recentemente eu implantei uma mudança em nosso ambiente de produção que ALTER'd um proc armazenado existente e essencialmente toda a adição fez foi INSERT em uma tabela eu criei os parâmetros passados ​​pelo usuário chamando esse proc armazenado.

Exemplo: O usuário de finanças passa em 15/20 parâmetros possíveis em seu aplicativo front-end. Esses parâmetros exatos são primeiro INSERIDO na minha tabela onde cada coluna é um parâmetro. Se o usuário não digitar nada para um determinado parâmetro (porque são opcionais), a célula ficará vazia (não NULL).

Então, novamente, preciso agregar e descobrir qual é a combinação mais comum de parâmetros que são realmente usados ​​(não me importo com os valores reais, apenas se o parâmetro foi usado ou não).

Para esclarecer minha tabela.. Cada linha é uma chamada de proc armazenada e cada coluna é um parâmetro.

Minha mesa: