Olá!
Em uma série de posts, gostaríamos de compartilhar nossa experiência no desenvolvimento e operação de sistemas da família MS Dynamics AX (ex-Axapta).
Sobre nós
Somos uma cadeia de varejo relativamente jovem de supermercados de mercearia "Da!" No momento em que este livro foi escrito, tínhamos pouco mais de 100 lojas. Os principais processos das atividades operacionais da empresa são automatizados em um pacote de sistemas MS Dynamics Ax 2012 + MS Dynamics 365 FO. Os sistemas operam 24 horas por dia, 7 dias por semana. Em média, cerca de um milhão de linhas de recebimento e cerca de 70.000 linhas de pedido de montagem passam pelo sistema por dia.
Guias de planejamento
O ajuste de desempenho do Axapts pode ser feito de diferentes maneiras. A maneira mais eficiente é, obviamente, otimizar o código-fonte do aplicativo. Mas há situações em que isso é problemático. Então você pode usar a ferramenta fornecida pelo MS SQL Server DBMS. É chamado de guia de plano. Apareceu na versão do SQL Server 2005. Mas de acordo com nossos sentimentos na comunidade Axapters (principalmente se a empresa não tiver um DBA profissional), ele nem sempre é conhecido e utilizado. Embora, em alguns casos, seu uso possa ser muito eficaz.
Pelo que?
Aqui estão os principais motivos pelos quais você deve olhar para esta ferramenta:
1. Dados inconsistentes nos parâmetros de consulta. O resultado da amostragem (número de registros) para o mesmo critério de amostragem (conjunto de campos) difere dependendo dos valores das variáveis nos critérios de amostragem. Ou, de forma mais simples, quando a mesma consulta para diferentes parâmetros de entrada resulta em um número radicalmente diferente de registros (às vezes um, às vezes dez mil). Isso se deve à chamada “detecção” dos parâmetros da solicitação. Quando um plano de consulta é criado uma vez sob a máscara de consulta e, em seguida, retirado do cache. Não olhando para os valores desses mesmos parâmetros.
Este é freqüentemente o caso com consultas que envolvem tabelas InventSum e InventDim. Por exemplo, quando em análise, há apenas uma parte para alguns nomenklatura e para outros - vários milhares. A primeira solicitação ao banco de dados pode passar por um item para o qual a contabilidade de lote está desativada. O otimizador construirá um plano de consulta para ele. E coloque no cache. A próxima solicitação ao banco de dados pode passar por um item que tenha a contabilidade de lote ativada. E distribuir vários milhares de registros na seleção para InventSum e InventDim. E para tal amostra, o plano do cache não será o ideal.
Uma maneira de resolver esse problema é usar a dica forceLiterals no corpo da solicitação. Isso sinaliza ao mecanismo SQL para gerar um novo plano de consulta a cada vez. Mas isso dá uma carga tangível adicional na CPU. E com as mesmas solicitações restantes usando InventDim não é uma opção aceitável. Bem, você precisa entender que o otimizador do SQL Server não é perfeito e às vezes, mesmo com estatísticas completas, gera planos estranhos.
E neste caso, o Plan Guide vem em seu socorro, com a ajuda do qual você pode escolher um plano de consulta que dê uma velocidade de execução aceitável para qualquer parâmetro de entrada de consulta. E anexe este plano à máscara de consulta usando o Guia de Plano.
2. O otimizador escolhe um índice que resulta em bloqueios longos. Usando o Guia de Plano, você pode “definir” o uso de um índice específico, o que restringirá a amostra e reduzirá o número de bloqueios.
3. A origem (local no código) da consulta do problema não pode ser identificada rapidamente e o problema de queda no desempenho do banco de dados deve ser resolvido imediatamente.
4. O código-fonte do aplicativo não pode ser alterado por algum motivo (solução de parceiro, solicitações do kernel, etc.). Isso é especialmente verdadeiro para D365, que proíbe sobreposições.
Como?
Não vou descrever em detalhes um guia passo a passo para a criação de um Guia de Plano. Há uma boa descrição no site do fornecedor (estamos interessados no tipo de plano - SQL) E a rede tem um mar de tutoriais.
Mas é importante saber que existe outra ferramenta do SQL Server que será de grande ajuda se você precisar criar um novo Guia de Plano. É denominado Query Store. Surgido em 2016. Uma descrição detalhada aqui .
A ideia principal da ferramenta é que além do plano de consulta atual no cache, ela armazene todo o histórico dos planos que o otimizador formou ao longo de um determinado tempo. Se você sabe que a funcionalidade problemática funcionava "normalmente" antes. Não diminuiu a velocidade. Você só precisa encontrar o plano de que precisa no repositório e fazer um Guia de Plano com base nele. Infelizmente, devido às peculiaridades do Axapta, é impossível criar um Guia de Plano com um botão de “plano de força”. Você terá que copiar o plano de consulta do repositório e criar o Guia de Plano manualmente. Mas isso ainda simplifica muito a tarefa.
Também deve-se ter em mente que o uso do Query Store proporciona uma pequena sobrecarga aos recursos computacionais do servidor DBMS utilizado. Mas, em nossa prática, eles são insignificantes e isso pode ser negligenciado.
Exemplos de
Aqui estão alguns exemplos de Guia de Plano de nossa base de batalha real. Observe que esses são apenas exemplos relevantes para nossos processos de negócios específicos. Eles podem não ser aplicáveis ou mesmo prejudiciais à sua instalação.
1. InventSum
Este guia de plano resolve o problema de planos subótimos no caso de consultas para itens com um pequeno número de registros na tabela InventDim. Usando este guia, você sempre pode usar o plano ideal para amostragem com um grande número de combinações de SKU InventDim. As consultas de itens com contagens de SKU baixas serão um pouco mais lentas. Mas este não é um preço alto a pagar por uma velocidade estável e previsível para qualquer combinação de parâmetros de entrada.
Essas consultas são geradas principalmente pelo método InventSum :: findSum (). E dependendo do agrupamento, os padrões de consulta podem ser ligeiramente diferentes. Portanto, na realidade, temos um Guia de Plano mais semelhante adaptado para diferentes grupos.
2. InventSumDelta
Este guia de plano permite que você construa um plano de consulta ideal para a tabela InventSumDelta, evitando bloqueios desnecessários nesta tabela. A especificidade desta tabela é tal que os dados não são armazenados nela. Mas eles são adicionados / removidos muito intensamente. É essencialmente uma tabela de semáforo. A este respeito, as estatísticas normais não podem ser coletadas nesta tabela. Portanto, o otimizador às vezes gerava planos abaixo do ideal, resultando em bloqueio.
Um pouco offtopic - você também precisa desabilitar bloqueios de página em índices para esta tabela. Como a seleção dessa tabela é sempre feita por um ID exclusivo, escalar bloqueios para o nível da página não faz sentido e até mesmo é prejudicial aqui.
conclusões
Mas no caso geral, deixe-me chamar sua atenção mais uma vez, você não deve abusar dessa ferramenta. Se o código for escrito de maneira ideal, as estatísticas serão atualizadas regularmente, os índices não serão muito fragmentados - o otimizador na maioria dos casos selecionará o próprio plano correto. Mas se o guia de plano estiver configurado, os critérios de entrada da solicitação podem cair de tal forma que o guia de plano só causará danos.