SQL Server: Gerenciamento de transações
O gerenciamento de transações do SQL Server é uma etapa importante para garantir operações tranquilas e para evitar erros de bloqueio.
Extraído de "SQL Server DMV Starter Pack," publicado pela Red Gate Books (2010).
Glenn Berry, Louis Davidson e Tim Ford
Olhando em níveis específicos de gerenciamento de operações do SQL Server, você deve considerar cuidadosamente como você gerencia relacionadas à transação Dynamic Management Objects (DMOs). Todas as exibições de gerenciamento dinâmico (DMVs) na categoria "transação relacionados" de DMOs começam com "sys.dm_tran_" como uma declaração de abertura.
Em última análise, cada instrução executada contra o servidor SQL é transacional. Se você emitir uma única instrução SQL, uma transação implícita é iniciada nos bastidores. Esta declaração será iniciado e auto-completar. Se você usar explícita BEGIN TRAN / COMMIT TRAN comandos, você pode agrupar estas como uma transação explícita — um conjunto de instruções que deve falhar ou suceder juntos.
SQL Server implementa vários níveis de isolamento de transação, para garantir as propriedades atomicidade, consistência, isolamento e durabilidade (ácido) destas transacções. Em termos práticos, isso significa que ele usa bloqueios e travas para mediar o acesso transacional para recursos de banco de dados compartilhado e impedir a "interferência" entre as transações.
De um modo geral, sua estratégia e processos para investigar e Gerenciando transações SQL Server será limitados a algumas questões-chave:
- Quais transações estão ativos e quais sessões são executá-los? (session_transactions, active_transactions)
- Quais transações estão fazendo mais trabalho? (database_transactions)
- Quais transações estão causando problemas de travamento/bloqueio? (bloqueios)
Estas razões, investigar o bloqueio e bloqueio é de longe o mais comum uso desses DMVs. Uma área de investigação que continuarão a se tornar cada vez mais comum é a atividade gerada ao usar o nível de isolamento de instantâneo. O nível de isolamento de instantâneo foi introduzido no SQL Server 2005. Isolamento de instantâneo elimina o bloqueio e deadlocking usando um armazenamento de versão no banco de dados tempdb para manter a simultaneidade, em vez de estabelecimento de bloqueios em objetos de banco de dados. Há uma série de DMVs fornecido para investigar este nível de isolamento.
Transações de monitor de longa duração
Vamos agora passar para os scripts. Salvo indicação em contrário, todas essas consultas trabalham com SQL Server 2005, 2008 e 2008 R2, e todas exigem permissão VIEW SERVER STATE. Este script usa dois DMVs. O primeiro é sys.dm_tran_database_transactions, que é descrita no servidor SQL books online (BOL) da seguinte forma: "Retorna informações sobre transações no banco de dados".
A segunda é sys.dm_tran_session_transactions, que simplesmente: "Retorna informações de correlação para transacções associadas e sessões".
A descrição concisa para database_transactions um pouco desmente sua utilidade potencial. O script a seguir fornece uma consulta que mostra, por sessão, quais bancos de dados estão em uso por uma transação aberta por essa sessão, se a transação foi atualizado para leitura-gravação em qualquer um dos bancos de dados (por padrão a maioria das operações são somente leitura), quando a transação atualizado para leitura e gravação para esse banco de dados, como muitos registros de log escritos e quantos bytes foram usados por esses registros de log:
Selecione st.session_id, DB_NAME(dt.database_id) AS database_name, caso quando dt.database_transaction_begin_time é nula então 'read-only'
ELSE 'leitura-gravação' FINAL AS transaction_state, dt.database_transaction_begin_time como read_write_start_time, dt.database_transaction_log_record_count, dt.database_transaction_log_bytes_usedFROM sys.dm_tran_session_transactions como st INNER JOIN sys.dm_tran_database_transactions como dt
EM st.transaction_id = dt.transaction_idORDER BY st.session_id, database_name
Esses tipos de consultas contra database_transactions são muito úteis ao monitorar as coisas tais como:
- Sessões com transações de leitura-gravação abertas (especialmente importantes para sessões de dormir)
- Sessões fazendo com que o log de transações para crescer/inchar
- O progresso das operações de longa duração (para operações registradas não em massa, cada linha afetada irá produzir aproximadamente registro de log de uma transação)
Bloqueio e bloqueio
Nosso script de exemplo para a categoria relacionados com transacções de DMVs usa o dm _ tran_locks DMV, que é descrita por BOL da seguinte forma:
"Recursos do Gerenciador retorna informações sobre bloqueio atualmente ativo. Cada linha representa uma solicitação atualmente ativa para o Gerenciador de bloqueio para um bloqueio que tenha sido concedido ou está aguardando para ser concedido. As colunas no conjunto de resultados são divididas em dois grupos principais: recurso e pedido. O grupo de recursos descreve o recurso no qual está sendo feita a solicitação de bloqueio, e o grupo de solicitação descreve a solicitação de bloqueio".
Essa DMV é útil para ajudar a identificar problemas de bloqueio e bloqueio em suas instâncias de banco de dados:
-Olhar ativo bloqueio Manager recursos para o banco de dados atual
Selecione request_session_id,
Db_name(resource_database_id) AS [banco de dados], resource_type, resource_subtype, request_type, request_mode, resource_description, request_mode, request_owner_type
DE sys.dm_tran_locksWHERE request_session_id > 50 E resource_database_id = DB_ID () E request_session_id < > @ @ SPIDORDER BY request_session_id;
-Procure bloqueio
Selecione tl.resource_type, tl.resource_database_id, tl.resource_associated_entity_id, tl.request_mode, tl.request_session_id, wt.blocking_session_id, wt.wait_type, wt.wait_duration_msFROM dm _ tran_locks como tl INNER JOIN sys.dm_os_waiting_tasks como wt ON tl.lock_owner_address = wt.resource_addressORDER BY wait_duration_ms DESC;
A primeira consulta mostra tipos de bloqueio e seu estatuto por SPID, filtrados pelo banco de dados atual e eliminando a conexão atual e o sistema SPIDs. A segunda consulta fornece informações sobre qualquer bloqueio que podem estar ocorrendo, toda a instância. Observe que esta segunda consulta junta-se a sys.dm_os_waiting_tasks DMV para obter dados sobre quanto tempo que um processo tem esperado, devido ao bloqueio, e no qual o recurso.
A menos que você tenha graves problemas de bloqueio, você geralmente precisará executar que cada uma dessas consultas várias vezes para pegar o bloqueio. Se você identificar duas instruções de modificação de dados, ou uma consulta e a modificação de dados, que estão abraçando no bloqueio grave, ou mesmo deadlocks, então você precisará extrair o texto SQL para as consultas, examiná-los, executá-los em um sistema de teste — com Profiler rastreamento em execução — e descobrir uma maneira de ajustar as consultas, ou adicionar índices, para atenuar o problema.
.png)
Glenn Berry trabalha como arquiteto de banco de dados no NewsGator tecnologias em Denver, Colorado Ele é um MVP do SQL Server e possui uma coleção inteira de certificações da Microsoft, incluindo MCITP, MCDBA, MCSE, MCSD, MCAD e MCTS, que prova que ele gosta de fazer testes.
.png)
Louis Davidson foi no setor de TI há 16 anos como arquiteto e desenvolvedor de banco de dados corporativo. Ele tem sido um MVP do Microsoft SQL Server para seis anos e já escreveu quatro livros sobre design de banco de dados. Atualmente ele é o arquiteto de dados e, por vezes, DBA para o Christian Broadcasting Network, escritórios de apoio em Virginia Beach, VA. e Nashville, Tennessee.
.png)
Timothy Ford é um MVP do SQL Server e tem vindo a trabalhar com o SQL Server para mais de 10 anos. Ele é o principal DBA e especialista no assunto para a plataforma do SQL Server para a saúde do espectro. Ele tem sido escrito sobre tecnologia desde 2007 para uma variedade de sites da Web e mantém seu próprio blog em thesqlagentman.com, cobrindo SQL como tópicos de desenvolvimento bem como teletrabalho e profissional.
Saiba mais sobre "SQL Server DMV Starter Pack" em red-gate.com/our-company/about/book-store.