Como usar melhor a função CLR do ponto de vista do desempenho (repita dentro de cada banco de dados ou tenha uma função geral)?

Isso parece ser uma duplicata desta pergunta:

Configurando um procedimento armazenado CLR central / biblioteca de repositório de função para procs armazenados internos em outros bancos de dados para usar?

No entanto, não sinto que nenhuma das duas respostas seja adequada, pois não mencionam alguns dos aspectos mais importantes dessa questão.

Não há escolha óbvia aqui sobre qual local é melhor para objetos SQLCLR em geral , pois pode haver restrições impostas pelo que está sendo feito com o código SQLCLR. Existem alguns usos que exigirão que o Assembly esteja em cada banco de dados individual e um uso que exigiria que o Assembly esteja em um banco de dados centralizado. Tudo depende de alguns aspectos diferentes do que o código está fazendo. Portanto, precisamos examinar quais são esses aspectos para determinar se há uma escolha para começar e, em caso afirmativo, quais seriam os prós e os contras.

Aspectos funcionais específicos do SQLCLR

• Tipos Definidos pelo Usuário (UDTs): UDTs não podem ser referenciados em bancos de dados; eles não podem ser declarados com nomes de 3 partes (ou seja, DatabaseName.SchemaName.UserDefinedTypeName). Se algum UDT estiver sendo usado, o Assembly precisará ser adicionado a cada banco de dados no qual o UDT será usado. No entanto, se outros objetos SQLCLR estiverem sendo usados, supondo que haja a opção de colocar esses objetos em um banco de dados centralizado ou em cada banco de dados de cliente/aplicativo, você sempre poderá colocar os UDTs em um Assembly que é colocado em cada cliente /Application DB e outro Assembly contendo Functions / Stored Procedures / User-Defined Aggregates / Triggers.

• Segurança:

  • Quantos bancos de dados são afetados: O código CLR está fazendo algo que exija que o Assembly seja marcado com um PERMISSION_SETde EXTERNAL_ACCESSou UNSAFE? Em caso afirmativo, você está importando alguma DLL que foi assinada fora de seu controle e não pode ser renunciada? Normalmente, seriam bibliotecas .NET Framework sem suporte ou DLLs de terceiros. Quando você não tem controle sobre a assinatura de Assemblies que precisam ser marcados como EXTERNAL_ACCESSou UNSAFE, você pode ser forçado a definir o banco de dados que contém o(s) Assembly(s) como TRUSTWORTHY ON. Desde a configuração de um banco de dados paraTRUSTWORTHY ONé um risco de segurança, é preferível minimizar o número de bancos de dados que você precisa para fazer isso, caso em que colocar o código em um banco de dados centralizado parece ser uma abordagem melhor. E se você já tiver um banco de dados central para outro código e quiser realmente minimizar esse tipo de risco de segurança, poderá ter um segundo banco de dados centralizado apenas para esse código.

    Se você tem controle sobre a assinatura da(s) DLL(s), então você deve definitivamente criar um Certificado ou Chave Assimétrica no banco de masterdados baseado na DLL e, em seguida, criar um Login com base nesse Certificado ou Chave Assimétrica e, em seguida, atribuir ou a EXTERNAL ACCESS ASSEMBLYpermissão UNSAFE ASSEMBLYpara esse Login. Essas poucas etapas ali (e as únicas coisas sendo criadas são o Certificado ou Chave e o Login) permitirão que qualquer Assembly assinado com a mesma chave privada seja definido como EXTERNAL_ACCESSou UNSAFE(dependendo de qual permissão foi concedida ao Login), não importa em qual banco de dados ele é carregado. E se você for capaz de fazer isso, então você pode colocar Conjuntos definidos para um EXTERNAL_ACCESSouUNSAFEem todos os bancos de dados de clientes/aplicativos sem mais riscos de segurança do que você teria colocando o mesmo código em um banco de dados centralizado ^** .

  • Permissões diferentes para necessidades de clientes/aplicativos diferentes: Se, por qualquer motivo, alguns clientes/aplicativos precisarem ter permissões diferentes PERMISSION_SETde outros, isso exigiria o carregamento do(s) Assembly(s) em cada banco de dados de cliente/aplicativo. Isso permitiria que você tivesse alguns bancos de dados usando SAFEenquanto outros estavam usando EXTERNAL_ACCESS. Isso vai além do que pode ser feito com permissões em nível de objeto. Ao definir um Assembly que possui código para executar funções do sistema de arquivos SAFE, você garante que o código não funcionará, mesmo que alguém encontre uma maneira de contornar sua segurança regular e ainda possa EXECUTEusar o SQLCLR Stored Procedure.

• AppDomains: Este aspecto diz respeito à utilização e separação de memória/recursos. Esta é provavelmente a área de maior impacto em termos de considerações, mas também é provavelmente a menos compreendida. Portanto, vamos começar observando como os objetos T-SQL lidariam com o mesmo banco de dados central em relação a cada pergunta do banco de dados de cliente/aplicativo.

  Funções T-SQL e Stored Procedures, ao serem executadas, armazenam seus planos de execução no cache do plano (bem, não Inline TVFs), que fica na memória. Pensando apenas na utilização de memória, usar um banco de dados centralizado tem a vantagem de armazenar um único plano em vez de um plano para cada banco de dados cliente/aplicativo, especialmente se houver 100 ou mais bancos de dados. No entanto, ter um plano em cache leva à dúvida se é ou não um plano ideal para execuções subsequentes. É possível, com uma gama potencialmente ampla de variação em como ele está sendo executado em tantos bancos de dados de cliente/aplicativo, que um único plano seja ótimo para alguns, mas também bastante horrível para outros. Se você não quiser que o desempenho seja atingido ao especificarWITH RECOMPILE, implantá-lo em cada banco de dados cliente/aplicativo permitiria uma otimização mais individualizada. Resumindo: o banco de dados central é menos memória usada para cache de plano, mas desempenho potencialmente pior; bancos de dados separados são mais memória para o cache do plano, mas menos problemas de desempenho em potencial.

  Quando se trata de objetos SQLCLR, existem os mesmos prós e contras de cache de plano para cada abordagem. Mas agora que estamos lidando com domínios de aplicativos, há ramificações adicionais a serem consideradas. Domínios de aplicativos são espaços de memória/caixas de proteção que o .NET usa para executar código. Cada domínio de aplicativo é sua própria caixa de proteção separada. No SQL Server, os App Domains são criados para cada combinação de Database e Assembly Owner. Portanto, vários Assemblies no mesmo banco de dados pertencentes ao mesmo usuário compartilharão um domínio de aplicativo, mas os assemblies no mesmo banco de dados pertencentes a outro usuário terão um domínio de aplicativo diferente e os assemblies em outros bancos de dados estarão em seus próprios domínios de aplicativo. Com aquilo em mente:

  • O consumo de memória aumenta em uma taxa mais rápida ao implantar em bancos de dados de cliente/aplicativo individuais, pois os Assemblies usados ​​são carregados nos Domínios de aplicativos (mas eles não são carregados até serem usados ​​pela primeira vez). O domínio do aplicativo também contém todas as variáveis, manipuladores de recursos, etc (até que essas coisas sejam marcadas para coleta de lixo eGC decide que a lua e as estrelas estão alinhadas perfeitamente e toma isso como um sinal para correr). Portanto, um Assembly de 2 MB em um banco de dados usando um AppDomain que possui uma certa quantidade de memória reservada para variáveis ​​etc. que é compartilhado na memória em várias instâncias dele, mas não tenho certeza de como medir isso) mais 100 vezes a quantidade de espaço reservado para variáveis ​​etc.

    Um problema relacionado é se você estiver usando Expressões Regulares e fazendo uso da opção RegEx para a Compiledqual compila a expressão para a Linguagem Intermediária (MSIL). Isso acelera as coisas para expressões usadas repetidamente, mas depois que uma expressão é compilada, ela não pode ser coletada como lixo e permanecerá no AppDomain até que seja reiniciada. Se houver uma função RegEx comumente usada que esteja usando a Compiledopção, a memória usada para armazená-la será repetida por cada banco de dados se o Assembly for carregado em cada banco de dados. Nesse caso, pode fazer sentido colocar esse código em um banco de dados centralizado.

  • As limitações de recursos podem ser um problema ao usar um banco de dados centralizado. Dependendo de quais classes você está usando, você pode criar um gargalo de recursos sem saber. Por exemplo:

    • Ao usar os métodos RegEx estáticos em vez dos métodos de instância, as expressões regulares que você usa são armazenadas em cache. Mas o tamanho do cache padrão é de apenas 15 expressões. Se uma grande variedade de expressões estiver sendo enviada de um grande número de clientes ou aplicativos, as expressões não permanecerão no cache por muito tempo. Portanto, se esse for o único motivo para considerar o carregamento do Assembly em cada banco de dados, basta aumentar o tamanho do cache. Consulte a página do MSDN para RegEx.CacheSize para obter detalhes.

    • Da mesma forma, ao fazer WebRequestsisso, há um número máximo padrão de conexões ativas que podem ser feitas para um determinado URI. E esse padrão é apenas 2. Se você fizer mais solicitações para o mesmo URI (muito fácil de fazer se for um local estático e estiver usando um banco de dados centralizado para esse código), qualquer solicitação acima desse máximo simplesmente aguardará na fila por uma conexão atual para fechar (ou seja, bloqueio). Portanto, você teria que carregar o Assembly em cada banco de dados cliente/aplicativo ou aumentar o limite de conexões por URI. Você pode definir o máximo padrão para todos os URIs no domínio de aplicativo atual definindo ServicePointManager.DefaultConnectionLimit(isso pode ser definido uma vez por inicialização do App Domain, como em um construtor de classe estática) ou pode ser definido por URI criando um HttpWebRequest e, em seguida, definindo sua propriedade .ServicePoint.ConnectionLimit (isso precisa ser feito toda vez que o WebRequest for instanciado, pois o objeto tem um tempo máximo de vida e, uma vez coletado o lixo, o ConnectionLimit reverterá para o ServicePointManager.DefaultConnectionLimitvalor, conforme observado acima, quando uma nova instância for criada).

  • Se você estiver usando uma variável estática para armazenar em cache determinados valores (memória compartilhada - rara, mas ainda uma possibilidade), precisará decidir qual deve ser o escopo do compartilhamento desses valores. Se você deseja que o compartilhamento esteja contido em cada banco de dados cliente/aplicativo, carregue o Assembly em cada banco de dados cliente/aplicativo. Mas se você quiser compartilhar esses valores em todos os bancos de dados, coloque o Assembly em um banco de dados centralizado e compartilhado.

Aspectos funcionais gerais

• Acesso ao banco de dados: o código está fazendo referência a algum objeto específico do banco de dados? Lembre-se de que a execução do SQL usando o processo/ Context Connection = true;conexão será executada inicialmente com o banco de dados "atual" sendo definido para o banco de dados onde o objeto existe, não necessariamente de onde o objeto está sendo chamado. Portanto, o código em execução em um banco de dados de cliente/aplicativo e chamando um objeto em um banco de dados centralizado não poderá fazer referência a objetos usando apenas nomes de 2 partes. No entanto, você ainda pode usar um banco de dados centralizado para esse código, desde que tenha um parâmetro de entrada para @DatabaseName(use: [SqlFacet(MaxSize = 128)] SqlString DatabaseName) e depois passe DB_NAME()para ele. Então você pode usar DatabaseName.Valueno código SQLCLR para umUSEinstrução ou para concatenar no SQL dinâmico para criar os nomes de objeto totalmente qualificados apropriados (ou seja, nomes de 3 partes).

  Este provavelmente não é um fator determinante se você estiver apenas sys.databasesfazendo referência a objetos baseados no sistema (ou seja, ) que retornam as mesmas linhas, independentemente do banco de dados em que você esteja. passando o nome do banco de dados para a string de conexão, ou você apenas fará login no banco de dados padrão para fazer o login fazendo a conexão.

• Diferenças de agrupamento: Se os agrupamentos entre o banco de dados centralizado e os bancos de dados do cliente/aplicativo forem os mesmos, isso não será um fator determinante ao decidir entre esses dois modelos. Mas, se o seu sistema oferecer suporte a agrupamentos diferentes, você precisará estar ciente do que seu código está fazendo, pois pode ser afetado pela precedência de agrupamento. Se você estiver enviando strings que serão comparadas com outras strings, o comportamento pode não ser o esperado, mesmo que nenhum erro seja produzido. A collation usada para comparar variáveis ​​locais e strings literais será a Collation padrão onde o objeto (isto é, Stored Procedure ou Function) existe. Se este Collation for diferente do agrupamento do banco de dados "atual" quando esse objeto é chamado (se passar em um literal ou variável), ou diferente do campo que está sendo passado, pode haver uma variedade de diferenças em como essa comparação é feito. Portanto, se oferecer suporte a uma variedade de agrupamentos, as operações baseadas em string podem ser mais estáveis/consistentes quando o código for implantado em cada banco de dados cliente/aplicativo.

distrações

A seguir estão os motivos apresentados nesta pergunta e na pergunta duplicada vinculada no topo desta resposta, para preferir um método ou outro, mas que eu sinto que não são realmente relevantes para decidir qual método é mais adequado:

• O banco de dados centralizado é mais fácil de suportar: como assim? O código deve existir apenas uma vez no controle de origem. E supondo que o código carregado em cada banco de dados de cliente/aplicativo seja o mesmo, a solução de problemas deve ser aproximadamente a mesma em ambos os casos. No mínimo, colocar o código SQLCLR em um banco de dados comum, separado dos bancos de dados do cliente/aplicativo, adiciona uma camada de complicação com chamadas entre bancos de dados que podem ser vistas tecnicamente como um motivo para não centralizar esse (ou outro) código.
• O banco de dados centralizado é mais fácil nas implantações: se for esse o caso, eu diria que é necessário corrigir o processo de implantação. Se você estiver trabalhando com um modelo no qual duplica os bancos de dados do cliente/aplicativo para ter um esquema idêntico e apenas dados diferentes, já terá esse problema ao implantar alterações de esquema. A implantação do SQLCLR também é apenas instruções DDL. Pode ser mais difícil implantar o código SQLCLR ao tentar carregar o Assembly da DLL, mas não há motivo para fazer isso. Apenas certifique-se de ter um script SQL independente que faça um CREATE ASSEMBLYou ALTER ASSEMBLYusando os bytes hexadecimais (ou seja, FROM 0x4D5F000002C...).
• Bancos de dados de cliente/aplicativo individuais são melhores para backup/restauração: O argumento aqui é que, se houver um problema e você precisar restaurar, é mais fácil ter tudo contido no banco de dados de cliente/aplicativo que está sendo restaurado. Eu diria que se você precisar fazer uma restauração, restaurar 2 DBs (Client / App DB e Common DB) é praticamente o mesmo trabalho, e restaurar 101 DBs (100 Client / App DBs e 1 DB comum) ainda é bastante muito o mesmo. E, do ponto de vista da confiabilidade, seu processo de backup é confiável e pode ser confiável para fazer backup adequado de bancos de dados de cliente/aplicativo e banco de dados comum, ou você precisa corrigir seu processo de backup ;-).
• Bancos de dados individuais de cliente/aplicativo são mais fáceis para testar variações: até certo ponto, isso é verdade se você precisar testar uma versão do código sem alterar todas as referências. Embora geralmente isso deva ser feito testando em um ambiente completamente diferente para começar. Mas se houver um cliente que vai testar beta alguma alteração de código, por exemplo, essa situação pode ser mitigada com bastante facilidade usando o modelo sugerido por @AaronBertrand em sua resposta a essa pergunta que é muito semelhante (diz respeito principalmente Objetos T-SQL e não especificamente código SQLCLR): Criar função no banco de dados central ou repetir em cada banco de dados? . O modelo discutido nessa resposta é usar um banco de dados centralizado para a maioria/todos os casos epara criar sinônimos em cada banco de dados cliente/aplicativo para que o código local possa fazer referência a nomes locais. Então, se uma variação for necessária, o código pode ser colocado em um determinado banco de dados de cliente/aplicativo e os sinônimos serão removidos. Nesse caso, o código T-SQL local ainda faria referência aos mesmos nomes locais e não saberia a diferença. Pessoalmente, gosto muito dessa abordagem e não consigo pensar em nenhuma desvantagem específica além da necessidade de criar / remover os Sinônimos, o que não parece um preço alto a pagar pela flexibilidade adquirida.

Portanto: para a situação específica descrita nesta pergunta (ou seja, função escalar, sem recursos externos, sem acesso a objetos de banco de dados, sem limitações de recursos), parece que usar seu banco de dados de configuração única seria bom.

^** Se você está pensando "mas assemblies definidos como EXTERNAL_ACCESS ou UNSAFE são riscos de segurança por causa do que eles permitem que você faça": eu não estava dizendo que não há nenhum risco com assemblies definidos como EXTERNAL_ACCESS ou UNSAFE se você usar o Método baseado em certificado/chave assimétrica. O que estou dizendo é que, nessa configuração, qualquer risco existente não é diferente entre colocar o Assembly em um banco de dados centralizado e em cada banco de dados de cliente/aplicativo. Isso ocorre porque quaisquer possíveis problemas de segurança que possam resultar de assemblies definidos como EXTERNAL_ACCESS ou UNSAFE não estão localizados no banco de dados no qual esses assemblies existem (ao contrário da configuração TRUSTWORTHYcomo ON). Quaisquer problemas de segurança são de todo o sistema. Mas, ao definir bancos de dados paraTRUSTWORTHY ON, você terá problemas de segurança adicionais por banco de dados.