O "problema" de queries Ad Hoc e um pouco sobre Forced Parameterization

Hoje na lista de email dos PFEs de SQL Server do Brasil, meu amigo Diego Miranda encaminhou um post bacana do Jack Li (Escalation Engineer) falando sobre a opção Forced Parameterization (https://blogs.msdn.com/b/psssql/archive/2015/04/22/forced-parameterization-to-the-rescue.aspx).

Resolvi escrever um post rápido sobre isso.

É interessante notar que em ambientes onde queries Ad Hoc representam um percentual considerável do workload total, a quantidade de compliaçõ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 que não possui a mesma definição (incluindo valores).

Por exemplo:

Query 1

 SELECT Coluna1, Coluna2, Coluna3 FROM Tabela WHERE Coluna1 = 'A'

Aqui o SQL compilaria e guardaria no cache o plano de execução utilizado para encontrar registros onde o valor = A.

Query 2

 SELECT Coluna1, Coluna1, Coluna1 FROM Tabela WHERE Coluna1 = 'B'

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. É claro que se você executar a Query 1 novamente, como já existe um plano que satisfaça essa consulta em cache o SQL Server o reutilizaria.

A questão é, parâmetros podem afetar a efetividade da minha Query? A resposta: Sim, podem!
Imagine que para a a Query 1 existam 10 registros que correspondem ao filtro utilizado (A). Para a Query 2 há 10.000 registros. Nesse caso o plano de execução utilizado para a Query 1 pode ser ineficiente para a Query 2, o que poderia impactar a execução da Query 2 e consequemente degradar a performance da sua aplicação.

Isso é o que chamamos de Parameter Sniffing, muito comum em Stored Procedures. Por que em Stored Procedures? Simplesmente porque a grande diferença (ok! Há outras diferenças, claro) entre executar uma query Ad Hoc ou uma Stored Procedure é que o SQL Server otmiza a execução de Procedures por parametrizá-las, ou seja, reutilizar os planos de execução gerados na primeira execução da Procedure. Não vou falar muito sobre o Parameter Sniffing neste momento, pois o meu foco era pontuar a opção mencionada pelo Jack Li.

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, você poderia utilizar a opção que o Jack menciona no seu post, aqui também pode ser uma opção em ambientes os quais você não pode fazer mudanças nas queries em si.
Uma outra opção, que envolve mudança nas queries, seria a de executar suas queries utilizando a system stored procedure sp_executesql. Quando utilizada, você está basicamente forçando a parametrização da query. Por exemplo:

 EXECUTE sp_executesql 
 N'SELECT Coluna1, Coluna2, Coluna3 FROM Tabela WHERE Coluna1 = @valor',
 N'@valor varchar(1)',
 @valor = 'A'; 

Nesta execução, se observarmos o plano armazenado em cache seria algo mais ou menos assim: SELECT Coluna1, Coluna2, Coluna3 FROM Tabela WHERE Coluna1 = @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.

É claro que há muitos outros detalhes importantes a se avaliar.

Como eu mencionei na nossa discussão interna, 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.
 
O que eu realmente gostei do post do Jack Li foi o que ele mencionou no final: 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.

Muitas vezes a gente fica pensando, será que eu altero ou não?
Há coisas que só "testando" pra saber. A dica que eu dou é, investigue se a quantidade de queries Ad Hoc sendo executadas no seu ambiente está impactando o seu ambiente (alto consumo de CPU e/ou memória por elas é uma boa indicação + Avalie a quantidade de compilações por segundo).
Esses dois inputs geralmente vão determinar se mudanças como essas devem ser feitas ou não, Tenha sempre em mente que Procedures são preferíevis do que Ad Hoc - Best Practice.

Há outros aspectos que quero ir considerando nos meus posts futuros relacionados a performance, mas como eu disse, esse era pra ser um post rápido.   :)

Lembre-se performance é uma ciência "relativamente" exata, porém pode ser bem tricky, e varia consideravelmente dependendo do comportamente das suas aplicações.
 
Até o próximo post.

Um abraço,
Thiago Caserta,