Dica do Mês – Conhecendo o Intelligent Query Processing através da Scalar User Defined Function Inlining no Microsoft SQL Server 2019

Conheça o Intelligent Query Processing uma nova abordagem de reconhecimento e processamento de recursos adicionada ao SQL Server a partir da versão 2019, em especial neste post a Scalar UDF Inlining.

Boa tarde, boa tarde a todos, em especial a você amante de banco de dados relacional, principalmente ao Microsoft SQL Server.

Que bom lhe encontrar aqui em mais um post da sessão Dica do Mês, a qual também está passando por algumas mudanças de layout e dinâmica no seu conteúdo para tentar neste momento em que estamos todos em casa oferecer algo mais simples, prático e direto as suas necessidades, bem como, tornando a leitura mais direta ao assunto sem mais enrolações.

Você já deve ter notado em posts de outras sessões, algumas novidades, como o uso de imagens que se relacionam com o tema, a possibilidade de download do conteúdo do post em arquivo .PDF, o acréscimo da sessão links relacionados ao tema, enfim, alguns ajustes e melhorias que vão sendo realizados ao longo do tempo, de acordo com as sugestões, críticas e observações enviadas diariamente pelos mais de 1200 seguidores aqui presentes neste momento.

Para os posts da sessão Dica do Mês, você vai poder observar também algumas novidades, além do conteúdo compartilhado em arquivo .PDF, teremos adicionalmente uma versão do código fonte compartilhada no formato .TXT, em paralelo a este conteúdo disponibilizado para que você possa trabalhar offline, os códigos fontes aqui presentes no post, serão publicados no formato de imagens (no post versão web, já o arquivo no formato .PDF terá todo o conteúdo do código fonte adicionado no formato original), contendo números de linhas de forma sequência, ao invés de postados de forma separada em blocos de código que as vezes se tornam ilegíveis ou de difícil compreensão, por fim, o uso das imagens é uma forma de lhe proporcionar uma navegação mais limpa, colorida e organizada.

Todavia, o post continuará sendo dividido com pequenas subdivisões, mas com novas nomenclaturas, dentre elas destaco: Introdução, Materiais e Métodos, Resultados e Conclusões, etc…

Enfatizo também o surgimento da subdivisão Código Fonte que vai contemplar a apresentar de todo código fonte em uma única área do post.

Uma outra mudança a ser evidenciada é a remoção das costumeiras enquetes que antes eram sempre adicionadas ao post como uma forma de contar com a sua participação, mas que infelizmente não estava nos últimos posts despertando muito interesse dos participantes.

Então vamos em frente! Seja bem-vindo ao post – Conhecendo o Intelligent Query Processing através da Scalar User Defined Function Inlining no Microsoft SQL Server 2019.


Introdução

Como o lançamento do Microsoft SQL Server 2019 realizado no ano passado, a Microsoft adicionou uma nova capacidade a este tão fantástico SGBD – Sistema Gerenciador de Banco de Dados a partir desta última versão chamado de Intelligent Query Processing.

Novo mecanismo inteligente para processamento de querys composto por um conjunto específico de novas formas de se analisar, estruturar, padronizar e executar nossas querys a partir do SQL Server 2019, com o objetivo de possibilitar e permitir ganhos de processamento de alguns dos mais tradicionais recursos sem exigir mudanças radicais no código-fonte da nossa aplicação, querys, stored procedures ou funções.

Para esta família de recursos de processamento de consultas que podemos denominar como inteligentes, o Intelligente Query Processing trouxe uma nova abordagem de para uso e processamento das tão conhecidas Scalar User Defined Function ou simplesmente Scalar UDF, que durante muitos anos era um dos principais recursos a serem implementados no SQL Server mas com diversas ressalvas justamente pela a forma de execução da mesma e seus possíveis impactos de processamento.

Esta nova abordagem a partir do SQL Server 2019 foi denominada como Scalar UDF Inlining, uma combinação da tradicionais Scalar UDF presentes destes as versões mais antigas do SQL Server, agora combinada com a uma nova forma inteligente de reconhecimento e processamento.

Vamos conhecer mais sobre esta nova abordagem a seguir.

Scalar User Defined Function Inlining

Nova forma de reconhecimento e execução das User Defined Function, a Scalar UDF Inlining, no SQL Server 2019 interpreta o código a partir da função escalar, e o integra no plano principal de consulta para formar um único plano. Assim, em vez de   chamadas de função de looping, ele executa a lógica de função em linha com o resto da consulta.

As Funções escalares também dão origem a outros problemas de desempenho. Por exemplo, o SQL Server não fará um bom trabalho de calcular o custo estimado do que acontece dentro da função, e bloqueará efetivamente o uso de planos paralelos, o que pode ser um problema real com grandes cargas de trabalho, como trabalhos de ETL de inteligência de negócios.

Nem toda função escalar é elegível a ser tornar inlineável, mesmo quando uma função “é” inlineável, não necessariamente será inlined em todos os cenários. Isso muitas vezes tem a ver com a complexidade da função, a complexidade da consulta envolvida, ou com a combinação de ambos.

Você vai poder observar em alguns trechos de código compartilhados neste post, que uma das maneiras de se observar quais funções podem ser reconhecidas e processadas é inlineável  através da execução da visão de catálogo sys.sql_modules.

De uma forma resumida podemos dizer que a Scalar UDF Inlining realiza:

  1. A transformação automática das UDFs escalares em expressões relacionais, no momento da chamada desta instrução pelo SQL Server, faz a incorporação dentro do bloco da consulta que será processada;
  2. Essa transformação melhora o desempenho das cargas de trabalho que aproveitam os UDFs escalares;
  3. As Scalar UDF Inlining facilitam a otimização de operações baseada em custos dentro dos UDFs. Os resultados são eficientes, orientados a conjuntos e paralelos em vez de planos de execução em série ineficientes, iterativos e serial;
  4. Esse recurso é ativado por padrão sob o nível de compatibilidade do banco de dados 150; e
  5. Por qualquer motivo, se você não quiser que uma determinada função (ou qualquer função em um banco de dados) seja inlineável, você não precisa confiar no nível de compatibilidade do banco de dados para controlar esse comportamento. Podemos pode controlar este comportamento diretamente aplicada as configurações do nosso banco de dados, fazendo uso da opção Inline = Off para desativar ou Inline = On para ativar.Podemos avançar mais um post e conhecer quais o Materiais e Métodos vamos utilizar para construção do nosso cenário de testes e aplicação da Scalar UDF Inlining.

Materiais e Métodos

Como de costume em todas as dicas do mês tenho sempre o cuidado em elaborar um cenário de uso simples e específico para demonstrar como podemos fazer uso de determinadas funcionalidades e características existentes no Microsoft SQL Server sem provocar qualquer tipo de impacto ao seu atual ambiente, este foi e sempre será o meu compromisso, desta forma, a partir de agora a subdivisão chamada Nosso Cenário ou Nosso Ambiente será denominada Materiais e Métodos, aqui você vai encontrar todos os recursos e configurações necessárias para a construção e execução do post, com uma nova abordagem, simples e enxuta.

Materiais:

  • Instância ou Servidor SQL Server instalado Localmente, Virtualizado ou
    Cloud;
  • Utilização do SQL Server Management Studio ou Azure Data Studio;
  • Acesso ao banco de dados Master;
  • Acesso as visões de sistemas:
  • All_objects; e
  • dm_exec_function_stasts.
  • Permissão para criação de tabelas e funções.

Métodos:

  • Criação do banco de dados ScalarUDFInlining2019;
  • Criação das Tabelas Idiomas e Empregados;
  • Inserção de duas linhas de registros lógicos na tabela Idiomas;
  • Inserção de aproximadamente 2.418 linhas de registros lógicos na tabela Empregados;
  • Criação da UDF Table Inline – F_PesquisarIdioma;
  • Execução da UDF Table Inline – F_PesquisarIdioma;
  • Validação das Estatísticas de processamento de Funções através da Sys.dm_exec_function_stats;
  • Simulação de uso e execução da UDF Table Inline com a opção InLine = ON, possibilitando ao Microsoft SQL Server reconhecimento da função como uma possível Scalar UDF Inlining; e
  • Simulação de uso e execução da UDF Table Inline com a opção InLine = Off, desativando o reconhecimento da função como uma possível Scalar UDF Inlining.

Código Fonte

A seguir apresento todo código fonte elaborado diretamente no Microsoft SQL Server 2019 através do uso do SQL Server Management Studio 18.5 e testado também no Azure Data Studio release de Maio 2020:

Observação: Aqui estão os trechos de código fonte elaborados para este post, os quais como eu já destaquei também serão compartilhados em sua integra e formato original no arquivo em .PDF e .TXT.

Estamos caminhando para o término do nosso post, agora vamos conhecer os resultados obtidos após a execução do código fonte aqui compartilhado e elaborado exclusivamente para conhecermos a Scalar UDF Inlining.

Resultados

Seguindo neste novo formato que estou adotando a partir de agora, você vai poder observar os resultados obtidos após o processamento do código fonte compartilhado acima. Para ilustrar e elucidar o que o Microsoft SQL Server nos apresentou de resultado, farei uso do Plano de Execução gerado por ele na execução do bloco de código denominado: Passo 4 – Executar novamente a UDF F_PesquisarIdiomas no Select — Verificar as alterações no Plano de Execução.

Pois bem, inicialmente este trecho de código será executado com o Nível de Compatibilidade 140 (nível padrão do SQL Server 2017) para o banco de dados ScalarUDFInlining2019, tendo seu resultado apresentado na Figura 1, logo após o mesmo trecho de código será alterado para nível de compatibilidade 150 (nível padrão do SQL Server 2019), e apresentação do resultado através da Figura 2:

Figura 1 – Resultado a execução da UDF – F_PesquisarIdioma, com nível de compatibilidade 140.

Figura 2 – Resultado a execução da UDF – F_PesquisarIdioma, com nível de compatibilidade 150.

Antes mais nada fique calmo se você obteve os mesmos resultados, talvez alguma sequência na execução dos passos pode ter sido esquecida por você.

Se compararmos os tamanhos dos planos de execução apresentados pode-se dizer que ao alterar o nível de compatibilidade para 150 o SQL Server teve mais trabalho ou até mesmo necessitou utilizar mais recursos para sua execução, na verdade isso não aconteceu.

Note que o operador Clustered Index Scan apresentado na Figura 1, teve um custo de processamento de 100%, o qual na Figura 2, teve sua carga de trabalho alterada para 43%, ou seja o Microsoft SQL Server 2019, reconheceu que nossa função F_PesquisarIdioma, poderia ser processada como uma Scalar UDF Inlining, sendo assim, adotou a nova estratégia de execução adicionando a função no mesmo plano de execução, possibilitando diminuir toda sobrecarga e centralização de processamento antes direcionada para o Clustered Index Scan.

Observe que no plano de execução apresentado na Figura 2, o query processor resolveu fazer uso dos operadores Constant Scan e Clustered Index Seek, em operações executadas de forma paralela, posteriormente direcionadas ao operador Nested Loops, estabelecendo assim uma junção natural dos dados que anteriormente estavam todos sendo pesquisados e lidos no operador Clustered Index Scan conforme apresenta a Figura 1.

Espero que você tenha entendido esta nova forma de execução adotado a partir do SQL Server 2019, é algo realmente que podemos chamar de inteligente.

Em suma, chegamos ao final deste post, torço para que você tenha gostado, do que foi aqui compartilhado, e principalmente desta nova abordagem e formato.

Versão em PDF e TXT

Agora você vai poder também baixar este post diretamente para seu device, fazer uso do mesmo no momento em que desejar, o qual poderá ser compartilhado livremente ou impresso.

Basta clicar nas opções de imagens abaixo para escolher o formato do arquivo desejado:

#28 - Para que serveImage result for text file

Por questões de compatibilidade com a plataforma WordPress, o arquivo .txt foi renomeado, também o acréscimo da extensão .docx, sendo assim, após a realização do download a mesma deve ser removida.

Referências 

  1. https://sqlperformance.com/2019/01/sql-performance/scalar-udf-sql-server-2019
  1. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15
  1. https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15
  1. https://aka.ms/IQPDemos
  1. https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019
  1. https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver15
  1. https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-center-for-sql-server-database-engine-and-azure-sql-database?view=sql-server-ver15
  1. https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15
  1. https://sqlsunday.com/2019/04/08/scalar-function-inlining-in-sql-server-2019/

Posts Anteriores

  1. https://pedrogalvaojunior.wordpress.com/2020/01/30/dica-do-mes-concatenando-a-relacao-de-municipios-por-estados-atraves-do-uso-da-funcao-stuff/
  2. https://pedrogalvaojunior.wordpress.com/2019/09/29/dica-do-mes-identificando-os-numeros-ausentes-em-uma-sequencia-numerica/
  3. https://pedrogalvaojunior.wordpress.com/2019/05/28/dica-do-mes-analisando-o-comportamento-do-comando-dbcc-clean-table/
  4. https://pedrogalvaojunior.wordpress.com/2019/03/27/dica-do-mes-aplicando-analise-combinatoria-no-microsoft-sql-server/
  5. https://pedrogalvaojunior.wordpress.com/2019/01/22/dica-do-mes-temporal-table-e-o-calor-uma-combinacao-muito-quente/

Conclusão

Mais uma vez fico surpreendido com toda a capacidade de processamento, evolução e melhorias que as equipes de desenvolvimento do Microsoft SQL Server adicionam a cada nova versão ou atualização ao produto.

Neste post temos a exata visão da responsabilidade e compromisso da Microsoft em manter esta tradicional SGBD o mais versátil e inteligente possível, mesmo já sendo um produto com quase 30 anos de estrada.

A partir da versão 2019, esta nova família de processamento inteligente de querys adicionadas ao SQL Server com certeza vai ajudar em muito aos Administradores de Bancos de dados, bem como, os desenvolvedores a melhorarem suas implementações de funcionalidades de código de forma simples e com poucos esforços ou mudanças radicais no que se refere ao código fonte.

Através do uso da Scalar UDF Inline, ficou claro observar como uma tradicional User Defined Function criada em versões anteriores a 2019 poderá ser reconhecida e até mesmo utilizada com esta nova forma de execução.

Este é o fantástico Microsoft SQL Server, produto tão fascinante que a cada dia eu não consigo deixar de querer estudar, que nos possibilita aprender sempre, transformando conceitos e conhecimentos bem difundidos em fontes de sabedoria e alternativas modernas para a solução de problemas.

Agradecimentos

Agradeço a você por sua atenção e visita ao meu blog. Fique à vontade para enviar suas críticas, sugestões, observações e comentários.

Nos encontramos no próximo post da sessão Dica do Mês a ser publicado em breve.

Um forte abraço.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP – Faculdade de Informática e Administração Paulista de São Paulo. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove – Campus São Roque. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1994 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator – Administrador de Banco de Dados – SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, entre outros recursos. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, etc. Trabalhei entre 2011 e 2017 como Administrador de Banco de Dados e Coordenador de TI no FIT – Instituto de Tecnologia da Flextronics, atualmente exerço a função de Professor Universitário na FATEC São Roque. CTO da Galvão Tecnologia, consultoria especializada em Gestão de TI, Administração de Servidores Windows Server, Bancos de Dados Microsoft SQL Server e Virtualização. Possuo titulação Oficial Microsoft MVP e reconhecimentos: MCC, MSTC, MIE e MTAC.