Como medir ou encontrar o custo de criar um plano de consulta?

Como saber qual é o custo de criar um plano de consultas?

Você pode ver as propriedades do nó raiz no plano de consulta, por exemplo:

_{(captura de tela do Sentry One Plan Explorer gratuito )}

Essas informações também estão disponíveis consultando o cache do plano, por exemplo, usando uma consulta baseada nos seguintes relacionamentos:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    CompileTime = c.value('(QueryPlan/@CompileTime)[1]', 'int'),
    CompileCPU = c.value('(QueryPlan/@CompileCPU)[1]', 'int'),
    CompileMemory = c.value('(QueryPlan/@CompileMemory)[1]', 'int'),
    ST.[text],
    QP.query_plan
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) AS QP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
CROSS APPLY QP.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS N(c);

Para um tratamento completo das opções que você tem para lidar com esses tipos de consultas, consulte o artigo atualizado recentemente de Erland Sommarskog .

Supondo que "custo" seja em termos de tempo (embora não tenha certeza do que mais poderia ser em termos de ;-), então, pelo menos, você deve ser capaz de ter uma noção disso fazendo algo como o seguinte:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

SET STATISTICS TIME ON;

EXEC sp_help 'sys.databases'; -- replace with your proc

SET STATISTICS TIME OFF;

O primeiro item informado na aba "Mensagens" deve ser:

Tempo de análise e compilação do SQL Server:

Eu executaria isso pelo menos 10 vezes e calcularia a média dos milissegundos de "CPU" e "Decorrido".

Idealmente, você executaria isso em Produção para obter uma estimativa de tempo real, mas raramente as pessoas têm permissão para limpar o cache do plano em Produção. Felizmente, a partir do SQL Server 2008, tornou-se possível limpar um plano específico do cache. Nesse caso, você pode fazer o seguinte:

DECLARE @SQL NVARCHAR(MAX) = '';
;WITH cte AS
(
  SELECT DISTINCT stat.plan_handle
  FROM sys.dm_exec_query_stats stat
  CROSS APPLY sys.dm_exec_text_query_plan(stat.plan_handle, 0, -1) qplan
  WHERE qplan.query_plan LIKE N'%sp[_]help%' -- replace "sp[_]help" with proc name
)
SELECT @SQL += N'DBCC FREEPROCCACHE ('
               + CONVERT(NVARCHAR(130), cte.plan_handle, 1)
               + N');'
               + NCHAR(13) + NCHAR(10)
FROM cte;
PRINT @SQL;
EXEC (@SQL);

SET STATISTICS TIME ON;

EXEC sp_help 'sys.databases' -- replace with your proc

SET STATISTICS TIME OFF;

No entanto, dependendo da variabilidade dos valores sendo passados ​​para o(s) parâmetro(s) que causa(m) o plano em cache "ruim", há outro método a considerar que é um meio-termo entre OPTION(RECOMPILE)e OPTION(OPTIMIZE FOR UNKNOWN): SQL dinâmico. Sim, eu disse isso. E eu ainda quero dizer SQL dinâmico não parametrizado. Aqui está o porquê.

Você claramente tem dados que têm uma distribuição desigual, pelo menos em termos de um ou mais valores de parâmetro de entrada. As desvantagens das opções mencionadas são:

• OPTION(RECOMPILE)irá gerar um plano para cada execução e você nunca poderá se beneficiar de qualquer reutilização do plano, mesmo se os valores dos parâmetros passados ​​novamente forem idênticos à(s) execução(ões) anterior(es). Para procs que são chamados com frequência - uma vez a cada poucos segundos ou com mais frequência - isso o salvará de situações horríveis ocasionais, mas ainda o deixará em uma situação nem sempre tão boa assim.

• OPTION(OPTIMIZE FOR (@Param = value))gerará um plano baseado naquele valor específico, o que pode ajudar em vários casos, mas ainda deixa você aberto ao problema atual.

• OPTION(OPTIMIZE FOR UNKNOWN)gerará um plano baseado no que equivale a uma distribuição média, o que ajudará algumas consultas, mas prejudicará outras. Isso deve ser o mesmo que a opção de usar variáveis ​​locais.

O SQL dinâmico, no entanto, quando feito corretamente , permitirá que os vários valores transmitidos tenham seus próprios planos de consulta separados que são ideais (bem, tanto quanto eles serão). O principal custo aqui é que, à medida que a variedade de valores transmitidos aumenta, o número de planos de execução no cache aumenta e eles ocupam memória. Os custos menores são:

• necessidade de validar parâmetros de string para evitar SQL Injections

• possivelmente precisando configurar um certificado e um usuário baseado em certificado para manter a abstração de segurança ideal, já que o SQL dinâmico requer permissões diretas de tabela.

Então, aqui está como gerenciei essa situação quando tive procs que foram chamados mais de uma vez por segundo e atingiram várias tabelas, cada uma com milhões de linhas. Eu tentei, OPTION(RECOMPILE)mas isso provou ser muito prejudicial para o processo em 99% dos casos que não tinham o problema de detecção de parâmetro / plano de cache ruim. E lembre-se de que um desses procs tinha cerca de 15 consultas e apenas 3 a 5 deles foram convertidos em SQL dinâmico, conforme descrito aqui; O SQL dinâmico não foi usado, a menos que fosse necessário para uma consulta específica.

1. Se houver vários parâmetros de entrada para o procedimento armazenado, descubra quais são usados ​​com colunas que têm distribuições de dados altamente díspares (e, portanto, causam esse problema) e quais são usados ​​com colunas que têm distribuições mais uniformes (e não devem ser causando este problema).

2. Construa a string SQL dinâmica usando parâmetros para os parâmetros de entrada proc que estão associados a colunas distribuídas uniformemente. Essa parametrização ajuda a reduzir o consequente aumento de planos de execução no cache relacionados a essa consulta.

3. Para os parâmetros restantes associados a distribuições muito variadas, eles devem ser concatenados no SQL dinâmico como valores literais. Como uma consulta exclusiva é determinada por qualquer alteração no texto da consulta, ter WHERE StatusID = 1é uma consulta diferente e, portanto, um plano de consulta diferente de ter WHERE StatusID = 2.

4. Se algum dos parâmetros de entrada do proc que devem ser concatenados no texto da consulta forem strings, eles precisam ser validados para proteção contra SQL Injection (embora isso seja menos provável de acontecer se as strings passadas forem geradas pelo app e não um usuário, mas ainda assim). Pelo menos faça o REPLACE(@Param, '''', '''''')para garantir que as aspas simples se tornem aspas simples de escape.

5. Se necessário, crie um certificado que será usado para criar um usuário e assine o procedimento armazenado de forma que as permissões diretas da tabela sejam concedidas apenas ao novo usuário baseado em certificado e não a [public]ou a usuários que não deveriam ter tais permissões .

Processo de exemplo:

CREATE PROCEDURE MySchema.MyProc
(
  @Param1 INT,
  @Param2 DATETIME,
  @Param3 NVARCHAR(50)
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'
     SELECT  tab.Field1, tab.Field2, ...
     FROM    MySchema.SomeTable tab
     WHERE   tab.Field3 = @P1
     AND     tab.Field8 >= CONVERT(DATETIME, ''' +
  CONVERT(NVARCHAR(50), @Param2, 121) +
  N''')
     AND     tab.Field2 LIKE N''' +
  REPLACE(@Param3, N'''', N'''''') +
  N'%'';';

EXEC sp_executesql
     @SQL,
     N'@P1 INT',
     @P1 = @Param1;