Desempenho EXEC vs SP_EXECUTESQL

Isso é principalmente uma preferência devido à segurança e consistência e não tem nada a ver com o desempenho (embora isso possa ter sido mais uma preocupação nas versões antigas do SQL Server).

Por que usar EXEC()algumas vezes quando você deveria usar sp_executesqlsempre que tiver parâmetros? EXEC()força você a concatenar todas as suas variáveis ​​em uma única string, e isso a torna pronta para abuso.

Escrevi sobre isso com mais detalhes aqui:

• Maus Hábitos para Chutar: Usando EXEC() em vez de sp_executesql

Eu também escrevi sobre como se proteger da injeção de SQL aqui:

• Protegendo-se da injeção de SQL no SQL Server - Parte 1
• Protegendo-se da injeção de SQL no SQL Server - Parte 2

A injeção de SQL é um grande negócio, e muitas outras pessoas escreveram sobre isso também .

Finalmente, ao chamar os procedimentos do sistema, certifique-se de usar a caixa apropriada para corresponder ao que está armazenado sys.all_objects- deve ser tudo em letras minúsculas. Caso contrário, se o seu código for implantado em uma instância que diferencia maiúsculas de minúsculas, tudo começará a falhar.

Antes de mais nada, vamos verificar o que significam ambos os comandos:
sp_executesql: Executa uma instrução Transact-SQL ou lote que pode ser reutilizado muitas vezes, ou um que foi construído dinamicamente. A instrução ou lote Transact-SQL pode conter parâmetros incorporados.
exec: executa uma cadeia de comando ou cadeia de caracteres em um lote Transact-SQL ou um dos seguintes módulos: procedimento armazenado do sistema, procedimento armazenado definido pelo usuário, procedimento armazenado CLR (tempo de execução de linguagem comum), função definida pelo usuário com valor escalar ou procedimento armazenado estendido. A instrução EXECUTE pode ser usada para enviar comandos de passagem para servidores vinculados.

algumas das principais deferências:

1. sp_executesqlpermite que as instruções sejam parametrizadas, portanto é mais seguro que o EXEC em termos de injeção de SQL
2. sp_executesqlpode aproveitar os planos de consulta em cache, A string TSQL é criada apenas uma vez, depois disso, toda vez que a mesma consulta é chamada com sp_executesql, o SQL Server recupera o plano de consulta do cache e o reutiliza.
3. As tabelas temporárias criadas no EXEC não podem usar o mecanismo de cache de tabelas temporárias.

Referências:
https://blogs.msdn.microsoft.com/turgays/2013/09/17/exec-vs-sp_executesql/
https://msdn.microsoft.com/en-us/library/ms188001.aspx
https:// /msdn.microsoft.com/en-us/library/ms188332.aspx

Edit:
Encontrei o seguinte artigo sobre o desempenho:
O desempenho é uma questão de debate entre esses dois métodos para procedimentos armazenados. Como o próprio nome sugere, sp_executeé em si um procedimento armazenado, que armazena no banco de dados do sistema. SP_ExecuteSQLdeve exigir a passagem de strings SQL para ele, exceto por mostrar maiores chances de armazenamento em cache, consequentemente levando a um desempenho melhor quando executado pela segunda vez ou mais tarde.

Em outras palavras, seu T-SQL dinâmico parametrizado incentiva sua reutilização. Além disso, sp_execute deve ter maiores chances de evitar compilações desnecessárias ao executar uma consulta dinâmica sobre arquivos exec(). Mas alguns especialistas consideram isso tão enganoso quanto pensam que, para ambos os métodos, um plano será armazenado em cache. De fato, para as consultas não parametrizadas de SP_ExecuteSQLmostra as mesmas características da última.

http://www.technovisitors.com/2014/07/SPExecuteSQL-Vs-Execute-SQL-Server.html

O EXEC usa o cache ad hoc e pode causar inchaço no plano ad hoc, enquanto o sp_executesql não, depende de quão estáveis ​​são seus planos.