Compartilhar via

Pool de conexões do SQL Server (ADO.NET)

  • Artigo

Aplicável a: .NET Framework .NET .NET Standard

Baixar ADO.NET

Para se conectar a um servidor de banco de dados, existem, normalmente, várias etapas demoradas. Um canal físico, como um soquete ou um pipe nomeado, deve ser estabelecido, o handshake inicial com o servidor deve ocorrer, informações de cadeia de conexão devem ser analisadas, a conexão deve ser autenticada pelo servidor, verificações devem ser realizadas para a inscrição na transação atual e assim por diante.

Na prática, a maioria dos aplicativos usa apenas uma ou algumas configurações diferentes para conexões. Isso significa que, durante a execução do aplicativo, muitas conexões idênticas serão abertas e fechadas repetidamente. Para minimizar o custo de abertura de conexões, o Provedor de Dados Microsoft SqlClient para SQL Server usa uma técnica de otimização chamada pool de conexões.

O pool de conexões reduz o número de vezes que as novas conexões devem ser abertas. O pooler mantém a propriedade da conexão física. Para gerenciar as conexões, ele mantém um conjunto de conexões ativas para cada configuração de conexão específica. Sempre que um usuário chama Open em uma conexão, o pooler procura uma conexão disponível no pool. Se houver uma conexão agrupada disponível, ele retornará essa conexão para o chamador, em vez de abrir uma nova conexão. Quando o aplicativo chama Close na conexão, o pooler retorna essa chamada para o conjunto de conexões ativas agrupadas, em vez de fechar a conexão. Depois de retornada ao pool, a conexão está pronta para ser reutilizada na próxima chamada Open.

Somente conexões com a mesma configuração podem ser agrupadas. O Provedor de Dados Microsoft SqlClient para SQL Server mantém vários pools ao mesmo tempo, um para cada configuração. As conexões são separadas em pools pela cadeia de conexão e, quando o modo de segurança integrada é usado, pela identidade do Windows. As conexões também são agrupadas conforme estejam ou não inscritas em uma transação. Ao usar o ChangePassword, a instância SqlCredential afeta o pool de conexões. As instâncias diferentes de SqlCredential usarão pools de conexões diferentes, mesmo se a identificação do usuário e a senha forem iguais.

O pooling de conexões pode melhorar significativamente o desempenho e a escalabilidade do aplicativo. Por padrão, o pool de conexões é habilitado no Provedor de Dados Microsoft SqlClient para SQL Server. A menos que você explicitamente o desabilite, o pooler otimiza as conexões quando elas são abertas e fechadas no aplicativo. Você também pode fornecer vários modificadores de cadeias de conexão para controlar o comportamento do pool de conexões. Para obter mais informações, confira "Controlar o pool de conexões com palavras-chave da cadeia de conexão", mais adiante neste tópico.

Importante

Quando o pool de conexões estiver habilitado e se ocorrer um erro de tempo limite ou outro erro de logon, uma exceção será lançada, e as tentativas de conexão subsequentes falharão nos próximos cinco segundos, o "blocking period". Se o aplicativo tentar se conectar dentro do período de bloqueio, a primeira exceção será gerada novamente. Falhas subsequentes após o término do período de bloqueio resultarão em novos períodos de bloqueio, duas vezes maiores que o anterior, até um máximo de um minuto.

Observação

O mecanismo "blocking period" não se aplica ao SQL Server do Azure por padrão. Esse comportamento pode ser alterado modificando a propriedade PoolBlockingPeriod em ConnectionString, exceto para .NET Standard.

Criação e atribuições do pool

Quando uma conexão é aberta pela primeira vez, um pool de conexões é criado com base em um algoritmo compatível exato que associa o pool à cadeia de conexão na conexão. Cada pool de conexões é associado a uma cadeia de conexão distinta. Se a cadeia de conexão não for uma correspondência exata de um pool existente, quando uma nova conexão for aberta, um novo pool será criado.

Observação

As conexões são separadas em pool por processo, domínio do aplicativo, cadeia de conexão e, quando a segurança integrada é usada, por identidade do Windows. As cadeias de conexão também devem ser uma correspondência exata; as palavras-chave fornecidas em uma ordem diferente para a mesma conexão serão agrupadas separadamente.

Observação

Se MinPoolSize não for especificado na cadeia de conexão, nem estiver especificado como zero, as conexões no pool serão fechadas após um período de inatividade. Entretanto, se o MinPoolSize especificado for maior que zero, o pool de conexões não será destruído até que o AppDomain seja descarregado e o processo concluído. A manutenção de pools inativos ou vazios envolve sobrecarga mínima do sistema.

Observação

O pool é limpo automaticamente quando ocorre um erro fatal, como um failover.

No exemplo de C# a seguir, três novos objetos SqlConnection são criados, mas apenas dois pools de conexões são necessários para gerenciá-los. Observe que a primeira e a segunda cadeias de conexão diferem pelo valor atribuído para Initial Catalog.

using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind"))
        {
            connection.Open();
            // Pool A is created.
        }

    using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=pubs"))
        {
            connection.Open();
            // Pool B is created because the connection strings differ.  
        }

    using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind"))
        {  
            connection.Open();
            // The connection string matches pool A.  
        }

Adicionar conexões

Um pool de conexões é criado para cada cadeia de conexão exclusiva. Quando um pool é criado, vários objetos de conexão são criados e adicionados ao pool para que o requisito de tamanho mínimo de pool seja atendido. As conexões são adicionadas ao pool conforme necessário, até o tamanho máximo de pool especificado (100 é o padrão). As conexões são retornadas para o pool quando fechadas ou descartadas.

Quando um objeto SqlConnection é solicitado, ele é obtido do pool caso haja uma conexão útil disponível. Para ser útil, uma conexão não deve estar em uso, deve ter um contexto de transação correspondente (ou não deve estar associada a nenhum contexto de transação) e deve ter um vínculo válido com o servidor.

O pool de conexões atende às solicitações de conexões realocando-as à medida que são retornadas ao pool. Se o tamanho máximo de pool for atingido e nenhuma conexão útil estiver disponível, a solicitação será colocada na fila. Então, o pooler tenta recuperar todas as conexões até que o tempo limite seja atingido (o padrão é 15 segundos). Se o pooler não puder atender à solicitação antes que se esgote o tempo limite de conexão, uma exceção será gerada.

Cuidado

É altamente recomendável sempre fechar a conexão quando você terminar de usá-la para que a conexão seja retornada ao pool. Você pode fazer isso usando os métodos Close ou Dispose do objeto Connection ou abrindo todas as conexões em uma instrução using no C# ou uma instrução Using no Visual Basic. As conexões que não são fechadas explicitamente não podem ser adicionadas nem retornadas ao pool. Para obter mais informações, confira Instrução using ou Como descartar um recurso de sistema para Visual Basic.

Observação

Não chame Close nem Dispose em um objeto Connection, em um DataReader nem em nenhum outro objeto gerenciado no método Finalize de sua classe. Em um finalizador, libere somente recursos não gerenciados que sua classe possui diretamente. Se a classe não tiver nenhum recurso não gerenciado, não inclua um método Finalize em sua definição de classe. Para obter mais informações, confira Coleta de lixo.

Para obter mais informações sobre os eventos associados à abertura e ao fechamento de conexões, confira Classe de Evento Audit Login e Classe de Evento Audit Logout na documentação do SQL Server.

Remover conexões

Se LoadBalanceTimeout (ou Connection Lifetime) for definido, quando uma conexão for retornada ao pool, o horário de criação dela será comparado com o horário atual e ela será destruída se esse intervalo de tempo (em segundos) exceder o valor especificado por LoadBalanceTimeout. Isso é útil nas configurações clusterizadas para forçar o balanceamento de carga entre um servidor em execução e um servidor que acabou de ficar online.

Se LoadBalanceTimeout (ou o Tempo de Vida da Conexão) não estiver definido (valor padrão = 0), o pooler de conexões removerá uma conexão do pool depois que ela estiver ociosa por aproximadamente quatro a oito minutos (em um intervalo aleatório de duas verificações) ou se detectar que a conexão com o servidor foi interrompida.

Observação

Uma conexão interrompida pode ser detectada somente após a tentativa de comunicação com o servidor. Se for encontrada uma conexão que não esteja mais conectada ao servidor, ela será marcada como inválida. As conexões inválidas são removidas de pool de conexões somente quando são fechadas ou recuperadas.

Se uma conexão com um servidor desapareceu, ela pode ser removida do pool mesmo se o pooler de conexões não tiver detectado a conexão interrompida e a marcado como inválida. Este é o caso porque a sobrecarga de verificar se a conexão ainda é válida eliminaria os benefícios de se ter um pooler, provocando outra viagem de ida e volta ao servidor. Quando isso ocorrer, a primeira tentativa de usar a conexão detectará que a conexão foi interrompida e uma exceção será gerada.

Limpar o poll

O Provedor de Dados Microsoft SqlClient para SQL Server introduziu dois novos métodos para limpar o pool: ClearAllPools e ClearPool. ClearAllPools limpa os pools de conexões de um provedor específico e ClearPool limpa o pool de conexões associado a uma conexão específica.

Observação

Se houver conexões em uso no momento da chamada, elas serão devidamente marcadas. Quando fechadas, serão descartadas, em vez de retornadas ao pool.

Suporte à transação

As conexões são removidas do pool e atribuídas com base no contexto de transação. A menos que Enlist=false seja especificado na cadeia de conexão, o pool de conexões verifica se a conexão está inscrita no contexto de Current. Quando uma conexão é fechada e retornada ao pool com uma transação System.Transactions inscrita, ela é reservada para que a próxima solicitação desse pool de conexões com a mesma transação System.Transactions seja retornada à mesma conexão, se disponível. Se uma solicitação desse tipo for emitida, e não houver conexões agrupadas disponíveis, uma conexão será removida da parte não transacionada do pool e depois inscrita. Se não houver conexões disponíveis em nenhuma área do pool, uma nova conexão será criada e inscrita.

Quando uma conexão for fechada, ela será retornada ao pool e à subdivisão apropriada com base no contexto de transação. Portanto, é possível fechar a conexão sem gerar erros, mesmo que uma transação distribuída ainda esteja pendente. Isso permite confirmar ou anular a transação distribuída posteriormente.

Controlar o pooling de conexão com palavras-chave da cadeia de conexão

A propriedade ConnectionString do objeto SqlConnection dá suporte a pares chave-valor de cadeias de conexão que podem ser usados para ajustar o comportamento da lógica do pool de conexões. Para obter mais informações, consulte ConnectionString.

Fragmentação do pool

A fragmentação de pool é um problema comum em muitos aplicativos da Web nos quais o aplicativo pode criar um grande número de pools que não são liberados até que o processo seja encerrado. Isso deixa um grande número de conexões abertas e consumindo memória, o que resulta em baixo desempenho.

Fragmentação do pool devido à segurança integrada

As conexões são agrupadas de acordo com a cadeia de conexão e a identidade do usuário. Portanto, se você usa autenticação Básica ou autenticação do Windows no site, bem como um logon de segurança integrada, pode obter um pool por usuário. Embora isso melhore o desempenho de solicitações de bancos de dados subsequentes feitas por um único usuário, esse usuário não pode aproveitar as vantagens das conexões feitas por outros usuários. Isso também resulta em, pelo menos, uma conexão por usuário com o servidor de banco de dados. Trata-se de um efeito colateral de uma arquitetura de aplicativo Web específico que os desenvolvedores devem ponderar em relação aos requisitos de segurança e auditoria.

Fragmentação do pool devido a muitos bancos de dados

Vários provedores de serviços de Internet hospedam vários sites em um único servidor. Eles podem usar um único banco de dados para confirmar um logon de autenticação de formulários e para abrir uma conexão com um banco de dados específico desse usuário ou grupo de usuários. A conexão com o banco de dados de autenticação é agrupada e usada por todos. Entretanto, há um pool de conexões separado para cada banco de dados, que aumenta o número de conexões com o servidor.

Trata-se também de um efeito colateral de projeto do aplicativo. Existe uma forma relativamente simples de evitar esse efeito colateral sem comprometer a segurança quando você se conectar ao SQL Server. Em vez de se conectar a um banco de dados separado para cada usuário ou grupo, conecte-se ao mesmo banco de dados no servidor e execute a instrução Transact-SQL USE para alternar para o banco de dados desejado.

O fragmento de código a seguir demonstra como criar uma conexão inicial com o banco de dados master e depois alternar para o banco de dados desejado especificado na variável da cadeia de caracteres databaseName.

// Assume that connectionString connects to master.  
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
    connection.Open();
    command.Connection = connection;
    command.Text = "USE DatabaseName";
    command.ExecuteNonQuery();
}

Funções de aplicativo e pooling de conexões

Depois de ativada uma função de aplicativo do SQL Server ao chamar o procedimento armazenado do sistema sp_setapprole, o contexto de segurança dessa conexão não pode ser redefinido. Entretanto, se o pool estiver habilitado, a conexão será retornada a ele e ocorrerá um erro quando a conexão agrupada for reutilizada.

Alternativas de função de aplicativo

Recomendamos que você aproveite todas as vantagens dos mecanismos de segurança que podem ser usados no lugar de funções de aplicativo.

Confira também