Pool de conexões do SQL Server (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 abrir conexões, o ADO.NET usa uma técnica de otimização chamada pooling de conexão.
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 ADO.NET 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 ADO.NET. 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, consulte "Controlando o pool de conexões com palavras-chave de cadeias de conexão" posteriormente neste tópico.
Observação
Quando o pool de conexões é habilitado e ocorre um erro de tempo limite ou outro erro de logon, é gerada uma exceção e as tentativas subsequentes de conexão falham nos próximos cinco segundos, "o período bloqueio". 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 maior que o anterior, até um máximo de um minuto.
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. As conexões são agrupadas por processo, por domínio do aplicativo, por cadeia de conexão e, quando o modo de segurança integrada é usado, pela 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.
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.
}
Importante
A Microsoft recomenda usar o fluxo de autenticação mais seguro disponível. Se você estiver se conectando ao SQL do Azure, as Identidades gerenciadas para recursos do Azure é o método de autenticação recomendado.
Se Min Pool Size 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 Min Pool Size 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.
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 que você sempre feche a conexão quando terminar de usá-la, para que a conexão seja devolvida 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 a 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
O pooler de conexões remove uma conexão do pool após ela estar ociosa por aproximadamente oito minutos ou caso detecte que a conexão com o servidor foi interrompida. Observe que uma conexão interrompida pode ser detectada somente após uma 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 ADO.NET 2.0 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. 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.
' Assumes that command is a SqlCommand object.
command.Text = "USE DatabaseName"
Using connection As New SqlConnection(connectionString)
connection.Open()
command.ExecuteNonQuery()
End Using
// Assumes that command is a SqlCommand object.
command.Text = "USE DatabaseName";
using (SqlConnection connection = new SqlConnection(
connectionString))
{
connection.Open();
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.