Eu tenho uma UDF de valor de tabela com várias instruções. Isso cria uma tabela temporária e a retorna como resultado.
A primeira execução leva 5 segundos, as execuções subsequentes com os mesmos parâmetros levam 1 segundo. Se eu alterar os parâmetros, leva 5 segundos na primeira vez e depois volta para 1 segundo. Se eu alterar os parâmetros de volta para os primeiros, ainda levará um segundo.
Não consigo descobrir o que está causando a demora na primeira vez em que um novo parâmetro é usado. As UDFs compilam um plano de consulta separado cada vez que parâmetros diferentes são usados? Posso monitorar isso no Profiler? Posso ver o evento sp_Recompile, mas não tenho certeza de qual evento é acionado quando um UDF é compilado.
Infelizmente, não posso postar o código, mas espero que haja informações suficientes para que alguém me dê algum tipo de explicação.
É improvável que esteja relacionado aos planos de consulta. Eu só vi consultas muito complicadas levarem mais de 1 segundo para o plano ser criado, muito menos 4 segundos como você descreve.
Parece que é o cache da página - basicamente o SQL Server mantém os dados usados recentemente na memória. Na primeira execução com cada conjunto de parâmetros, você está pagando o custo (normalmente caro) do acesso ao disco físico.
As execuções subseqüentes podem acessar as páginas de dados que já estão armazenadas na memória sem esperar pelo subsistema de disco.
Cada instrução no SQL Server tem um plano. O fato da instrução estar em um UDF ou proc ou adhoc geralmente não importa
Nesse caso, suspeito que sejam erros de cache que estão causando o atraso. Você pode testar por DBCC DROPCLEANBUFFERS (cuidado: afetará todos os processos nessa caixa). O tempo de compilação seria para cada alteração de parâmetro, não apenas para um não utilizado.
Um pensamento, porém: o UDF está processando ou agitando muitos dados, o suficiente para causar erros de cache?