Estamos tendo alguns problemas que nos impedem de entrar em operação com uma nova plataforma de camada SQL 2016 Std Ed local para Azure SQL Managed Instance Business Critical, e gostaria de saber se alguém teve problemas semelhantes e teve conselhos para nós. Nossos problemas são:
- O problema nº 1 é a compilação inicial excessiva do plano de consulta para consultas em pelo menos uma dúzia de procedimentos armazenados e outras consultas (40 segundos a 174 segundos... Já vi exemplos de até 725 segundos!). Isso está levando a tempos limite de aplicativos frequentes e aleatórios , já que a maioria está definida como padrão de 30 segundos. Para esclarecer, não se trata de recompilações excessivas, mas de um CompileTime muito alto no plano de consulta.
As consultas e informações em https://erikdarling.com/are-long-compile-times-bringing-you-down/ foram muito úteis para identificar essas consultas (li https://littlekendra.com/2024/ 03/05/long-compilers-who-time-out-not-in-query-store/ também). Também vemos alguns tempos limite de compilação: https://www.brentozar.com/blitzcache/compilation-timeout/ .
Estamos tentando ajustar essas consultas e reduzir sua complexidade, pois nos disseram que isso deveria reduzir o CompileTime. Também tentamos atualizar o nível AZ MI para Business Critical, adicionando vCores e ajustando configurações de instância e banco de dados por meses.
Estamos trabalhando em um caso Sev B com a Microsoft há várias semanas e recebemos algumas dicas, mas ainda sem resolução. O suporte de nível 3 recomendou garantir que as consultas de tempo limite de compilação não tenham planos forçados, porque forçar o plano permite que o SQL Optimizer gaste 3x a duração do normal para compilar, o que aumenta a probabilidade de tempo limite. A única coisa que estava forçando os planos é o ajuste automático, que se aplica ao MI apenas para a opção FORCE_LAST_GOOD_PLAN de acordo com https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view= sql-server-ver16 e https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview?view=azuresql . Desativamos FORCE_LAST_GOOD_PLAN de acordo com a recomendação deles e isso ajudou alguns dos CompileTimes altos, mas não todos. Não estamos forçando planos com o guia de plano (USE PLAN N'<xml_plan>') ou forçando manualmente no Query Store. Eles recomendaram ajustar consultas para reduzir sua complexidade e executar um DUMP se desligar FORCE_LAST_GOOD_PLAN não funcionar. Espero agendar uma sessão de trabalho com a equipe de desempenho para solucionar esses problemas.
Atualização: durante nossa ligação em 28/05 com suporte de nível 3, percebemos que até mesmo algumas consultas triviais como SELECT COUNT(*) FROM Table estavam usando OptimizationLevel = FULL em vez de TRIVIAL. O suporte da Level 3 disse que não havia nenhuma configuração que pudéssemos alterar para afetar isso, mas mencionaria isso à equipe de produto.
- Nosso trabalho de manutenção de partição que faz ALTER PARTITION SPLIT é executado por muito mais tempo (dias a semanas) no MI BusCrit em comparação com o mesmo código no SQL 2016 Std Ed. Ele também consome muito espaço extra usado no arquivo de dados (e log) durante o processamento, o que não acontece no SQL 2016, como um banco de dados de 52 GB que cresceu para 588 GB! enquanto o trabalho estava em execução. O inchaço (e o desempenho?) pode estar parcialmente relacionado à Recuperação Acelerada de Banco de Dados (ADR), uma vez que está no SQL 2019+ e AZ MI: Tamanho da tabela na Instância Gerenciada de SQL do Azure versus SQL Server Local (consulte também https://learn. microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts?view=sql-server-ver16 ).
Estamos revisando o algoritmo que herdamos, pois parece não seguir as práticas recomendadas em https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql?view =sql-server-ver16 "Sempre mantenha partições vazias em ambas as extremidades do intervalo de partições. Mantenha as partições em ambas as extremidades para garantir que a divisão da partição e a mesclagem da partição não incorram em nenhuma movimentação de dados. A divisão da partição ocorre no início e a mesclagem da partição ocorre no final. Evite dividir ou mesclar partições preenchidas A divisão ou mesclagem de partições preenchidas pode ser ineficiente porque a divisão ou mesclagem pode causar até quatro vezes mais geração de log e também pode causar problemas graves. travamento." Mas estamos intrigados por que o mesmo código é executado por muito mais tempo e incha excessivamente o arquivo de dados no MI, quando isso não aconteceu no SQL 2016 Std Ed.
Outra recomendação que consideramos é migrar para VMs do Azure com SQL, mas isso adicionará meses ao lançamento da nossa nova plataforma para configurar, testar e operacionalizar esse ambiente, pois não temos nenhuma VM do Azure no momento. Também teremos que operacionalizar patches, backups, etc.
Eu aprecio sua ajuda! Mike
Obrigado por compartilhar esta informação importante.
Recentemente, tivemos um problema semelhante no SQLMI, alto tempo de compilação quando o plano de consulta era forçado automaticamente/manualmente. Também temos um ticket MS aberto para isso, eles confirmaram que a desativação de FORCE_LAST_GOOD_PLAN não está disponível no nível de consulta no estágio atual.
Pela minha investigação, altere o nível de compatibilidade para 140, o tempo de compilação pode voltar ao normal, pois os comportamentos do otimizador acima foram desativados.
Como você observou, o estrangulamento (IO) pode afetar severamente. Você precisa evitar operações de divisão de partição em partições que contêm dados e preparar uma nova partição, adicionada a uma partição vazia.
Em relação aos tempos excessivos de compilação: Você mexeu em ( database scopedCONFIGURATION ) LEGACY_CARDINALITY_ESTIMATION