SQL Server Query Store - O que é considerado uma consulta 'ad-hoc'?

Depois de um pouco de pesquisa, não consegui encontrar uma fonte concreta e satisfatória na documentação da Microsoft para responder a essa pergunta. Existem muitas boas descrições e definições de terceiros de adhoc/ad-hoc/ad hoc por aí, mas para a especificidade dessa pergunta, acho que uma próxima à fonte é ideal.

Passando por definições genéricas (mas ainda precisas) como esta postagem SO (obrigado SqlWorldWide), se observarmos o que a documentação diz sobre o tópico, ela concorda com o que você mencionou sobre a definição ser baseada no número de execuções , acho que a ponto de podermos tomá-lo como fato.

O artigo sobre otimização para fluxos de trabalho ad hoc diz:

Quando essa opção é definida como 1, o Mecanismo de Banco de Dados armazena um pequeno stub de plano compilado no cache do plano quando um lote é compilado pela primeira vez, em vez do plano compilado completo. Isso ajuda a aliviar a pressão da memória, não permitindo que o cache do plano seja preenchido com planos compilados que não são reutilizados. O stub de plano compilado permite que o Mecanismo de Banco de Dados reconheça que esse lote ad hoc foi compilado antes, mas armazenou apenas um stub de plano compilado, portanto, quando esse lote é invocado (compilado ou executado) novamente, o Mecanismo de Banco de Dados compila o lote ... e adiciona o plano compilado completo ao cache do plano.

Portanto, parece que a opção de configuração do servidor de consulta ad hoc também usa a definição de uma única execução como a definição de ad hoc. Se a consulta continuar a ser executada e gerar o mesmo plano, ela não será mais tratada como tal.

O artigo sobre Práticas recomendadas para armazenamento de consultas também se alinha a isso,

Compare o número de valores de query_hash distintos com o número total de entradas em sys.query_store_query. Se a proporção estiver próxima de 1, sua carga de trabalho ad-hoc gerará consultas diferentes.

Isso, é claro, é para consultas que ainda não são usadas como procedimentos armazenados, parametrizadas, etc. porque podem ser reconhecidas e tratadas adequadamente logo de cara.

Então, com base em tudo isso, podemos dizer que uma consulta é tratada como ad hoc se:

• Não é parametrizado
• Não é armazenado programaticamente no banco de dados (proc armazenado, função, gatilho, etc.)
• A mesma consulta é executada apenas uma vez OU A mesma consulta é executada várias vezes, mas gera um plano de consulta diferente para cada execução subsequente.

Para consultas ad-hoc, a coluna object_idsys.query_store_query na DMV será 0, conforme indicado na documentação sys.query_store_query :

object_id :

ID do objeto de banco de dados do qual a consulta faz parte (procedimento armazenado, gatilho, CLR UDF/UDAgg etc.). 0 se a consulta não for executada como parte de um objeto de banco de dados (consulta ad-hoc).

Você pode identificar consultas ad-hoc com base nesse valor, mesmo que não seja explicitamente declarado como "esta é a definição de consultas ad-hoc ". :)

O termo ad-hoc é usado para se referir a consultas que foram executadas apenas uma vez. Isso é consistente com a definição usada para a configuração do banco de dados "Otimizar para cargas de trabalho ad-hoc".

O link que você faz referência sobre a exclusão de consultas ad-hoc inclui esta definição específica "Excluir consultas ad-hoc: isso exclui as consultas que foram executadas apenas uma vez e que têm mais de 24 horas".

Ad-hoc da coluna sys.dm_exec_cached_plans dmv objtypeem learn.microsoft.com.

Adhoc: consulta ad hoc. Refere-se ao Transact-SQL enviado como eventos de idioma usando osql ou sqlcmd em vez de chamadas de procedimento remoto.

Um uso desta coluna é ver no caso de consultas com vários planos para ver se os vários planos são causados ​​por Adhoc.

    SELECT  DISTINCT 
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
FROM ( 
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY MultipleQ.PlanCount DESC