Dicas part-1 Transaction Log
Dicas part-2 Variáveis table e JOIN's & Criação de tabelas dentro de stored procedures
Dicas part-3 Keywords performance
Parte 4 - Ad Hoc Queries (Forced Parameterization)
Uma Consulta Ad Hoc é um tipo de consulta SQL em um banco de dados que é criada na hora,
no momento em que surge uma necessidade, a partir de um requisito específico.
Ad hoc é uma expressão em latim que significa “para este propósito“. Ou seja, a consulta é criada apenas para satisfazer aquela necessidade específica, aquele propósito, em um momento específico.
(Que não seja generalista, Que não seja utilizável em mais de um caso) e que não é salva no cache do SGBD como por exemplo fazemos com estored procedures, funções ou scripts, para que sejam reutilizados posteriormente.
É interessante notar que em ambientes onde queries Ad Hoc representam um percentual considerável do workload total, quantidade de compilações pode se tornar um problema, independente do tamanho do servidor.
O motivo é simples, o SQL Server, por padrão, produz diferentes planos de execução para cada query Ad Hoc
Por exemplo:
Query 1
SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = 'X'
Aqui o SQL compilaria e guardaria no cache o plano de execução utilizado para encontrar registros onde o valor = X.
Query 2
SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = 'Y'
Bem, numa execução parametrizada (Uma stored procedure, por exemplo), o SQL Server simplesmente poderia reutilizar o plano da primeira query já que a mudança foi mínima (apenas o valor utilizado no filtro), porém a engine relacional entende como uma nova query, ou seja,
durante o processo de otimização da query o Query Optmizer recebe um cache miss, que basicamente indica que não há um plano de execução existente para esta query e ele então é responsável por gerar e armazenar esse novo plano no cache, mesmo sendo praticamente igual ao plano anterior.
Quais opções eu tenho para diminuir o número de compilações?
Bem, se você acredita que a grande maioria das suas queries utilizam planos similares para serem executadas, Não faça o simplista SELECT * FROM puro, você poderia utilizar a opção system stored procedure sp_executesql. Quando utilizada, você está basicamente forçando a parametrização da query especifica.
** Por complexidade, estamos detalhando aos desenvolvedores apenas o Forced Parameterization, sem a necessidade de ativar o "optimize for ad hoc workloads" por Database, que ficaria sob responsabilidade mais detalhadas dos encarregados pela Base de dados (DBA's).
Por exemplo:
EXECUTE sp_executesql N'SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = @valor', N'@valor varchar(1)', @valor = 'X';
Nesta execução, se observarmos o plano armazenado em cache seria algo mais ou menos assim:
SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = @valor.
Ou seja, não há um valor específico armazenado junto ao plano, o que faz com que a próxima execução, independente do parâmetro utilizado, encontre um plano satisfatório já armazenado em cache.
Há uma pequena linha entre essa opção ser boa ou não para um determinado ambiente,
já que um plano pode ser realmente bom para várias queries e aí reduzir o número de compilações e consequentemente o tempo de CPU (e memória consumida) ou impactar várias das queries porque cada uma gosta de um plano específico tudo uma questão de Testar.
Boa sorte, uma reflexão por Jack Li;
"If things don’t work out, it’s easy to back it out. Over the course of troubleshooting performance issues, I have used this trick many times." Jack Li
Fique por dentro, e acompanhas essas e outras dicas aqui no blog.
Grande abraços a todos.
