Em um post anterior verificamos que o DataServer SQL pode criar várias conexões para cada banco de dados utilizado pela aplicação. O número exato varia conforme os parâmetros do DataServer e a complexidade dos programas.
Criar e destruir conexões de banco de dados repetidamente é um processo lento, que gera um grande impacto no desempenho geral da aplicação. Por este motivo, o DataServer SQL utiliza um pool de conexões e mantém algumas conexões sempre abertas. Quando algum programa precisa realizar um acesso a dados através de uma nova conexão, o DataServer localiza uma conexão livre no pool e a utiliza. Se não houverem conexões livres no pool, uma nova conexão será aberta e utilizada.
O DataServer sempre criará uma conexão principal (identificada na documentação e nos logs como Master Connection) e zero ou mais conexões secundárias (identificadas como Firehose Connection). A conexão principal é utilizada para todas as transações que modificam dados (INSERT, UPDATE, DELETE) e para acessos via cursores. As conexões secundárias são utilizadas somente para leitura de dados utilizando default result sets, pois este modo de leitura bloqueia a conexão até a leitura dos dados ser concluída. O número padrão de conexões secundárias criadas pelo DataServer para cada banco de dados é 5. Este número pode representar conexões demais ou de menos, dependendo da aplicação.
Ao final da execução da aplicação, o DataServer grava no arquivo de log dataserv.lg informações sobre quantas conexões secundárias foram utilizadas:
Firehose cursor secondary connection:
Needed Connections: 7, (Needed 2 more than requested)
Peak Connections: 8, (Peaked at 3 more than requested)
O valor Needed Connections é o número de conexões que o DataServer acredita ser o ideal para a aplicação, com base na análise dos programas executados.
O valor Peak Connections é o número máximo de conexões utilizadas durante a sessão. Este valor poderá ser maior que o valor Needed Connections se houver uma demanda extra de conexões em um ponto isolado da aplicação.
Para evitar que o DataServer abra e feche muitas conexões durante a execução da aplicação, deve-se configurar o número inicial de conexões secundárias (o padrão é 5) para o valor mais próximo possível do número Needed Connections. Veremos como ajustar o número inicial de conexões no próximo post.
É possível identificar qual conexão foi utilizada para enviar cada comando ao banco de dados através do arquivo dataserv.lg. Para que os comandos sejam registrados neste log, é necessário ativar a opção SQL do qt_debug. Para ativar a opção, é necessário adicionar -Dsrv qt_debug,SQL no arquivo de parâmetros ou no atalho de conexão:
-db pedidos -RO
-db sql2k8 -ld blog -dt MSS -U teste -P teste
-Dsrv qt_debug,SQL
Em um post futuro abordaremos as várias opções do qt_debug com mais detalhes.
Apesar da possibilidade de monitorar as conexões através do log dataserv.lg, esta análise precisa ser realizada para cada log (normalmente cada estação tem seu próprio dataserv.lg) e dificilmente pode-se analisar enquanto os processos estão em execução (os valores de Needed e Peak Connections são gravados apenas ao final da sessão). Uma forma mais interessante é analisar os dados coletados no próprio SQL Server.
Para monitorar em tempo real a abertura e fechamento de conexões, bem como os comandos enviados ao servidor por cada aplicação pode-se usar o Profiler (ou as APIs do SQL Trace). Esta ferramenta permite capturar muito mais detalhes do que é possível com o dataserv.lg (por exemplo, os locks adquiridos ou os planos de execução das consultas), e também permite realizar filtros por usuário, por banco de dados, por objeto acessado, por tempo de duração de cada comando, etc.
O SQL Server também disponibiliza várias Dynamic Management Views (DMVs) que podem ser consultadas para obter informações sobre as conexões e os processos em execução. As principais DMVs para monitorar conexões são sys.dm_exec_connections e sys.dm_exec_sessions. Com estas DMVs é possível verificar a data/hora que a conexão foi criada, o protocolo de rede utilizado, forma de autenticação, o IP e nome da estação que criou a conexão, a API de conexão (sempre será ODBC para o DataServer), a data/hora da última requisição utilizando esta conexão, nome do usuário e mais uma variedade de detalhes. Abaixo um exemplo utilizando a DMV sys.dm_exec_sessions para gerar uma lista do número de conexões para cada usuário, filtrando apenas conexões geradas pela API ODBC e aplicação com nome 'Datasul EMS 2.06B'. Note que para filtrar na coluna program_name é necessário informar um nome de aplicação no momento da conexão.
SELECT login_name, COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND client_interface_name = 'ODBC'
AND program_name = 'Datasul EMS 2.06B'
GROUP BY login_name
Outras DMVs úteis são a sys.dm_exec_requests, para monitorar as requisições ativas, e sys.dm_exec_query_memory_grants, para verificar quais requisições estão consumindo uma grande quantidade de memória. Informações sobre estas e dezenas de outras DMVs estão disponíveis na documentação do SQL Server.
Assinar feeds (RSS)