Início / dba / Perguntas / 108593
Accepted
sebeid
Asked: 2015-07-31 09:32:03 +0800 CST

Procedimento armazenado com recompilação

  • 772

Eu tenho este procedimento armazenado. cada chamada (não é o ideal, mas foi uma tentativa de fazê-lo funcionar de forma mais confiável e rápida a cada vez, em vez de usar um plano antigo em cache)"

O que eles estão dizendo é correto? Existe alguma maneira de fazer isso sem recompilar 

   create PROCEDURE [dbo].[VERIFIER_QUEUE]
   @cas_name varchar(20) = NULL,
   @instance_name varchar(50) = NULL,
   @verifier_id int = NULL,
   @applicant_type VARCHAR(20) = NULL

    WITH RECOMPILE     
    AS

   BEGIN 
SET NOCOUNT ON

DECLARE @cas_name_x varchar(20)
DECLARE @instance_name_x varchar(50)
DECLARE @verifier_id_x int
DECLARE @InstanceId INT
...............
sql-server performance

1 respostas

  1. Best Answer

    Michael Green está certo: os desenvolvedores estão tentando impedir a detecção de parâmetros, que acontece quando o SQL Server compila um plano que é ótimo para um conjunto de valores de parâmetro, mas horrível para outros.

    Você vai querer usar OPTION (RECOMPILE)na(s) declaração(ões) com problemas, não WITH RECOMPILEno procedimento. E eu não recomendaria o "truque" das variáveis ​​locais - apenas torna o código mais confuso; melhor usar OPTIMIZE FOR UNKNOWNem versões modernas se esse for o método que funciona melhor em seu cenário. (Para saber muito mais sobre esse assunto, veja este ótimo post de Paul White .)

    Além disso, se muitos parâmetros forem opcionais (para que a consulta tenha coisas como WHERE col = @param or @param IS NULL), isso é o que chamo de "a pia da cozinha" - às vezes, o SQL dinâmico pode ser uma solução muito mais eficaz. Você não mostrou o resto do seu código, apenas que já estava usando o truque das variáveis ​​locais, mas basicamente ficará assim:

    DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ... WHERE 1 = 1';

IF @cas_name IS NOT NULL
  SET @sql += N' AND cas_name = @cas_name';

IF @instance_name IS NOT NULL
  SET @sql += N' AND instance_name = @instance_name';

IF @verified_id IS NOT NULL
  SET @sql += N' AND verifier_id = @verifier_id';

...

SET @sql = @sql + N' OPTION (RECOMPILE);';
PRINT @sql;

EXEC sys.sp_executesql @sql,
  N'@cas_name VARCHAR(20), @instance_name VARCHAR(50), @verifier_id INT, ...',
  @cas_name, @instance_name, @verifier_id, ...;

    Essa abordagem de apenas adicionar cláusulas para parâmetros que são realmente fornecidos protege você de armazenar em cache planos com base em diferentes conjuntos de parâmetros (por exemplo, se eu fornecer @FirstNamena primeira execução, o plano de busca naquela coluna que é armazenado em cache não ajudará quando Eu peço @LastName LIKE N'%s%'). O OPTION (RECOMPILE);no final protege você de planos que podem variar muito com base nos valores dos mesmos parâmetros de execução para execução (por exemplo, WHERE name LIKE N'%s%'deve gerar uma forma de plano diferente de WHERE name LIKE N'Q%').

    Isso geralmente funciona melhor com a configuração do servidor optimize for ad hoc workloads, sobre a qual você pode ler aqui e aqui . Essencialmente, o que isso faz é impedir que o cache do plano seja preenchido com todas essas pequenas variações de plano, a menos que sejam usadas mais de uma vez. (Sim, com OPTION (RECOMPILE), o ponto é discutível; no entanto, a configuração do servidor não pode prejudicar o restante de sua carga de trabalho de consulta ad hoc e nunca encontrei uma desvantagem em tê-lo ativado.)

    Isso é bastante seguro contra injeção de SQL, já que você não precisa se preocupar em concatenar a entrada do usuário em strings SQL (todos os parâmetros são fortemente tipados), mas não custa nada ler estes tópicos sobre SQL dinâmico:

    Tenho vídeos sobre minha solução para "a pia da cozinha" aqui e aqui , bem como uma postagem no blog sobre isso .

    • 7

relate perguntas