Procedimentos armazenados vs. SQL embutido

Acredito que esse sentimento era verdadeiro em um ponto, mas não nas versões atuais do SQL Server. Todo o problema era que antigamente as instruções SQL ad hoc não podiam ser otimizadas corretamente porque o SQL Server só podia otimizar/compilar no nível de lote. Agora temos otimização em nível de instrução, portanto, uma consulta parametrizada corretamente proveniente de um aplicativo pode aproveitar o mesmo plano de execução dessa consulta incorporada em um procedimento armazenado.

Ainda prefiro os procedimentos armazenados do lado do DBA pelos seguintes motivos (e vários deles podem ter um grande impacto no desempenho):

• Se eu tiver vários aplicativos que reutilizam as mesmas consultas, um procedimento armazenado encapsula essa lógica, em vez de espalhar a mesma consulta ad hoc várias vezes em diferentes bases de código. Os aplicativos que reutilizam as mesmas consultas também podem estar sujeitos ao aumento do cache do plano, a menos que sejam copiados literalmente. Mesmo diferenças entre maiúsculas e minúsculas e espaços em branco podem levar ao armazenamento de várias versões do mesmo plano (desperdício).
• Posso inspecionar e solucionar problemas do que uma consulta está fazendo sem ter acesso ao código-fonte do aplicativo ou executar rastreamentos caros para ver exatamente o que o aplicativo está fazendo.
• Também posso controlar (e saber com antecedência) quais consultas o aplicativo pode executar, quais tabelas ele pode acessar e em que contexto, etc. Se os desenvolvedores estiverem escrevendo consultas ad-hoc em seu aplicativo, eles terão que venha puxar a manga da minha camisa toda vez que eles precisarem de acesso a uma mesa que eu não sabia ou não podia prever, ou se eu for menos responsável / entusiasmado e / ou preocupado com a segurança, vou promover isso usuário para dbo para que eles parem de me incomodar. Normalmente, isso é feito quando os desenvolvedores superam os DBAs ou os DBAs são teimosos. Esse último ponto é ruim, e precisamos ser melhores em fornecer as consultas que você precisa.
• Em uma nota relacionada, um conjunto de procedimentos armazenados é uma maneira muito fácil de inventariar exatamente quais consultas podem estar sendo executadas no meu sistema. Assim que um aplicativo tem permissão para ignorar procedimentos e enviar suas próprias consultas ad-hoc, para encontrá-los, tenho que executar um rastreamento que cobre todo um ciclo de negócios ou analisar todo o código do aplicativo (novamente, isso Eu posso não ter acesso) para encontrar qualquer coisa que se pareça com uma consulta. Ser capaz de ver a lista de procedimentos armazenados (e grep uma única fonte, sys.sql_modules, para referências a objetos específicos) torna a vida de todos muito mais fácil.
• Eu posso ir muito mais longe para evitar a injeção de SQL; mesmo se eu receber a entrada e executá-la com SQL dinâmico, posso controlar muito do que é permitido acontecer. Não tenho controle sobre o que um desenvolvedor está fazendo ao construir instruções SQL embutidas.
• Posso otimizar a consulta (ou consultas) sem ter acesso ao código-fonte do aplicativo, a capacidade de fazer alterações, o conhecimento da linguagem do aplicativo para fazê-lo de forma eficaz, a autoridade (não importa o incômodo) para recompilar e reimplantar o aplicativo, etc. Isso é particularmente problemático se o aplicativo for distribuído.
• Posso forçar certas opções definidas dentro do procedimento armazenado para evitar que consultas individuais sejam sujeitas a algumas das opções Lento no aplicativo, rápido no SSMS? problemas. O que significa que para dois aplicativos diferentes chamando uma consulta ad hoc, um poderia ter SET ANSI_WARNINGS ON, e o outro poderia ter SET ANSI_WARNINGS OFF, e cada um teria sua própria cópia do plano. O plano que eles recebem depende dos parâmetros em uso, estatísticas em vigor, etc. na primeira vez que a consulta é chamada em cada caso, o que pode levar a planos diferentes e, portanto, a um desempenho muito diferente.
• Eu posso controlar coisas como tipos de dados e como os parâmetros são usados, ao contrário de certos ORMs - algumas versões anteriores de coisas como EF parametrizar uma consulta com base no comprimento de um parâmetro, então se eu tivesse um parâmetro N'Smith' e outro N' Johnson' Eu teria duas versões diferentes do plano. Eles consertaram isso. Eles consertaram isso, mas o que mais ainda está quebrado?
• Eu posso fazer coisas que ORMs e outras estruturas e bibliotecas "úteis" ainda não são capazes de suportar.

Dito tudo isso, essa questão provavelmente suscitará mais argumentos religiosos do que debates técnicos. Se virmos isso acontecendo, provavelmente vamos desligá-lo.

TLDR: Não há diferença de desempenho apreciável entre os dois, desde que seu sql embutido seja parametrizado.

Estes são os motivos pelos quais eu eliminei lentamente os procedimentos armazenados:

• Executamos um ambiente de aplicação 'beta' - um ambiente paralelo à produção que compartilha o banco de dados de produção. Como o código do banco de dados está no nível do aplicativo e as alterações na estrutura do banco de dados são raras, podemos permitir que as pessoas confirmem novas funcionalidades além do controle de qualidade e façam implantações fora da janela de implantação de produção, mas ainda forneçam funcionalidade de produção e correções não críticas. Isso não seria possível se metade do código do aplicativo estivesse no banco de dados.

• Praticamos devops no nível do banco de dados (octopus + dacpacs). No entanto, embora a camada de negócios e superior possam basicamente ser limpas e substituídas e a recuperação exatamente o contrário, isso não é verdade para as alterações incrementais e potencialmente destrutivas que devem ir para os bancos de dados. Consequentemente, preferimos manter nossas implantações de banco de dados mais leves e menos frequentes.

• Para evitar cópias quase exatas do mesmo código para parâmetros opcionais, geralmente usamos um padrão 'onde @var é nulo ou @var=table.field'. Com um proc armazenado, é provável que você obtenha o mesmo plano de execução, apesar de intenções bastante diferentes, e, portanto, experimente problemas de desempenho ou elimine planos em cache com dicas de 'recompilação'. No entanto, com um simples pedaço de código que acrescenta um comentário "assinatura" ao final do sql, podemos forçar planos diferentes com base em quais variáveis ​​eram nulas (não deve ser interpretado como um plano diferente para todas as combinações de variáveis ​​- apenas nulo vs. não nulo). Atualização 8/2020 - Esse bit não parece mais ser verdade ou agora é muito mais difícil de fazer, pois as versões posteriores do sql server tornaram-se bastante inteligentes em ignorar partes triviais de código.

• Eu posso fazer mudanças dramáticas nos resultados com apenas pequenas alterações em tempo real no sql. Por exemplo, posso ter uma declaração que fecha com dois CTEs, "Raw" e "ReportReady". Não há nada que diga que ambos os CTEs devem ser usados. Minha instrução sql pode então ser:

  ...

  selecione * de {(formato)}"

Isso me permite usar exatamente o mesmo método de lógica de negócios para uma chamada de API simplificada e um relatório que precisa ser mais detalhado, garantindo que eu não duplique lógica complicada.

• quando você tem uma regra "somente procs", você acaba com uma tonelada de redundância na grande maioria do seu sql que acaba sendo CRUD - você liga todos os parâmetros, você lista todos esses parâmetros na assinatura do proc, (e agora você está em um arquivo diferente em um projeto diferente), você mapeia esses parâmetros simples para suas colunas. Isso cria uma experiência de desenvolvimento bastante desconexa.

Existem razões válidas para usar procs:

• Segurança - Você tem outra camada aqui pela qual o aplicativo deve passar. Se a conta de serviço do aplicativo não tiver permissão para tocar em tabelas, mas apenas tiver permissão de 'executar' em procs, você terá alguma proteção extra. Isso não o torna um dado, pois tem um custo, mas é uma possibilidade.

• Reutilização - Embora eu diga que a reutilização deve acontecer em grande parte na camada de negócios para garantir que você não esteja ignorando regras de negócios não relacionadas a banco de dados, ainda temos o tipo ocasional de baixo nível "usado em todos os lugares" de procedimentos e funções de utilitário.

Existem alguns argumentos que realmente não suportam procs ou são facilmente mitigados IMO:

• Reutilização - eu mencionei isso acima como um "plus", mas também queria mencionar aqui que a reutilização deve acontecer em grande parte na camada de negócios. Um proc para inserir um registro não deve ser considerado "reutilizável" quando a camada de negócios também estiver verificando outros serviços não db.

• Inchaço do plano de cache - a única maneira de isso ser um problema é se você estiver concatenando valores em vez de parametrizar. O fato de você raramente obter mais de um plano por proc, na verdade, muitas vezes o prejudica quando você tem um 'ou' em uma consulta

• Tamanho da instrução - um kb extra de instruções sql sobre o nome do proc normalmente será insignificante em relação aos dados que retornam. Se está tudo bem para as Entidades, está tudo bem para mim.

• Vendo a consulta exata - Tornar as consultas fáceis de encontrar no código é tão simples quanto adicionar o local da chamada como um comentário ao código. Tornar o código copiável do código c# para o ssms é tão fácil quanto alguma interpolação criativa e uso de comentários:

        //Usage /*{SSMSOnly_}*/Pure Sql To run in SSMS/*{_SSMSOnly}*/
        const string SSMSOnly_ = "*//*<SSMSOnly>/*";
        const string _SSMSOnly = "*/</SSMSOnly>";
        //Usage /*{NetOnly_}{InterpolationVariable}{_NetOnly}*/
        const string NetOnly_ = "*/";
        const string _NetOnly = "/*";
  

• Sql Injection - Parametrize suas consultas. Feito. Isso pode realmente ser desfeito se o proc estiver usando sql dinâmico.

• Ignorando a implantação - Também praticamos devops no nível do banco de dados, portanto, isso não é uma opção para nós.

• "Lento no aplicativo, rápido no SSMS" - Este é um problema de cache de plano que afeta ambos os lados. As opções de conjunto apenas fazem com que um novo plano seja compilado que parece corrigir o problema para O ÚNICO CONJUNTO de variáveis. Isso apenas responde por que você vê resultados diferentes - as próprias opções definidas NÃO corrigem o problema de sniffing de parâmetros.

• Os planos de execução sql embutidos não são armazenados em cache - Simplesmente falso. Uma instrução parametrizada, assim como o nome do proc é rapidamente hash e, em seguida, um plano é pesquisado por esse hash. É 100% igual.

• Para ser claro, estou falando de código sql inline bruto não gerado a partir de um ORM - usamos apenas o Dapper, que é um micro ORM na melhor das hipóteses.

https://weblogs.asp.net/fbouma/38178

https://stackoverflow.com/a/15277/852208

Embora eu respeite o remetente, discordo humildemente da resposta fornecida e não por "razões religiosas". Em outras palavras, acredito que não haja nenhum recurso fornecido pela Microsoft que diminua a necessidade de orientação para usar procedimentos armazenados.

Qualquer orientação fornecida a um desenvolvedor que favoreça o uso de consultas SQL de texto bruto deve ser preenchida com muitas ressalvas, de modo que acho que o conselho mais prudente é incentivar bastante o uso de procedimentos armazenados e desencorajar suas equipes de desenvolvedores de se envolverem na prática de incorporar instruções SQL no código ou enviar solicitações SQL brutas e antigas baseadas em texto, fora dos SQL SPROCs (procedimentos armazenados).

Eu acho que a resposta simples para a pergunta de por que usar um SPROC é como o remetente supôs: SPROCs são analisados, otimizados e compilados. Como tal, seus planos de consulta/execução são armazenados em cache porque você salvou uma representação estática de uma consulta e, normalmente, estará variando apenas por parâmetros, o que não é verdade no caso de instruções SQL copiadas/coladas que provavelmente se transformam de página para página e componente/camada, e muitas vezes são variáveis ​​na medida em que diferentes tabelas, até mesmo nomes de banco de dados, podem ser especificadas de chamada para chamada. Permitindo este tipo de dinâmica ad hocO envio de SQL diminui muito a probabilidade de o DB Engine reutilizar o plano de consulta para suas instruções ad hoc, de acordo com algumas regras muito rígidas. Aqui, estou fazendo a distinção entre consultas ad hoc dinâmicas (no espírito da questão levantada) versus o uso do eficiente System SPROC sp_executesql.

Mais especificamente, existem os seguintes componentes:

• Planos de consulta serial e paralela que não mantêm o contexto do usuário e permitem a reutilização pelo mecanismo de banco de dados.
• Contexto de execução que permite a reutilização de um plano de consulta por um novo usuário com diferentes parâmetros de dados.
• Cache de procedimento que é o que o mecanismo de banco de dados consulta para criar as eficiências que buscamos.

Quando uma instrução SQL é emitida de uma página da Web, denominada "instrução ad hoc", o mecanismo procura um plano de execução existente para lidar com a solicitação. Como este é um texto enviado por um usuário, ele será ingerido, analisado, compilado e executado, se for válido. Neste momento, ele receberá um custo de consulta de zero. O custo da consulta é usado quando o mecanismo de banco de dados usa seu algoritmo para determinar quais planos de execução devem ser removidos do cache.

As consultas ad hoc recebem um valor de custo de consulta original de zero, por padrão. Após a execução subsequente do mesmo texto de consulta ad hoc, por outro processo de usuário (ou o mesmo), o custo da consulta atual é redefinido para o custo de compilação original. Como nosso custo de compilação de consulta ad hoc é zero, isso não é um bom presságio para a possibilidade de reutilização. Obviamente, zero é o inteiro de menor valor, mas por que ele seria despejado?

Quando surgem pressões de memória, e elas ocorrerão se você tiver um site usado com frequência, o mecanismo de banco de dados usa um algoritmo de limpeza para determinar como ele pode recuperar a memória que o cache de procedimento está usando. Ele usa o custo de consulta atual para decidir quais planos remover. Como você pode imaginar, os planos com custo zero são os primeiros a serem despejados do cache porque zero significa essencialmente "nenhum usuário atual ou referência a este plano".

• Nota: Planos de execução ad hoc - O custo atual é aumentado por cada processo do usuário, pelo custo de compilação original do plano. No entanto, o custo máximo de nenhum plano pode ser maior do que seu custo de compilação original... no caso de consultas ad hoc... zero. Então, ele será "aumentado" por esse valor...zero - o que significa essencialmente que continuará sendo o plano de menor custo.

Portanto, é bastante provável que tal plano seja despejado primeiro quando surgirem pressões de memória.

Portanto, se você tiver um servidor construído com muita memória "além de suas necessidades", talvez não tenha esse problema com a mesma frequência que um servidor ocupado que possui apenas memória "suficiente" para lidar com sua carga de trabalho. (Desculpe, a capacidade e a utilização da memória do servidor são um pouco subjetivas/relativas, embora o algoritmo não seja.)

Agora, se eu estiver realmente incorreto sobre um ou mais pontos, certamente estou aberto a ser corrigido.

Por fim, o autor escreveu:

"Agora temos otimização em nível de instrução, portanto, uma consulta parametrizada corretamente proveniente de um aplicativo pode aproveitar o mesmo plano de execução dessa consulta incorporada em um procedimento armazenado".

Acredito que o autor esteja se referindo à opção "otimizar para cargas de trabalho ad hoc".

Em caso afirmativo, esta opção permite um processo de duas etapas que evita o envio imediato do plano de consulta completo para o cache de Procedimento. Ele só envia um stub de consulta menor para lá. Se uma chamada de consulta exata for enviada de volta para o servidor enquanto o stub de consulta ainda estiver no cache de procedimento, o plano de execução de consulta completo será salvo no cache de procedimento naquele momento. Isso economiza memória, que durante incidentes de pressão de memória pode permitir que o algoritmo de despejo elimine seu stub com menos frequência do que um plano de consulta maior que foi armazenado em cache. Novamente, isso depende da memória e da utilização do servidor.

No entanto, você deve ativar essa opção, pois ela está desativada por padrão.

Por fim, quero enfatizar que, muitas vezes, a razão pela qual os desenvolvedores incorporam SQL em páginas, componentes e outros locais é porque desejam ser flexíveis e enviar consultas SQL dinâmicas ao mecanismo de banco de dados. Portanto, em um Caso de Uso do mundo real, é improvável que o envio do mesmo texto, chamada sobre chamada, ocorra, assim como o cache/eficiências que buscamos ao enviar consultas ad hoc ao SQL Server.

Para informações adicionais, consulte:

https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

Melhor,
Henrique