SQL Server: Gestione delle transazioni
La gestione delle transazioni di SQL Server rappresenta un passo importante verso il tentativo di garantire la continuità delle attività aziendali e di evitare errori di blocco.
Tratto da "SQL Server DMV Starter Pack," pubblicato da Red Gate Books (2010).
Glenn Berry, Louis Davidson e Tim Ford
Esaminando granulari livelli di gestione delle operazioni di SQL Server, è necessario considerare con attenzione come si riesce relative alle transazioni Dynamic Management Objects (DMOs). Tutte le viste a gestione dinamica (DMV) nella categoria "delle transazioni correlate" di DMOs iniziano con "sys.dm_tran_" come a un'istruzione di apertura.
In definitiva, ogni istruzione eseguita su SQL Server è transazionale. Se si emette una singola istruzione SQL, una transazione implicita è iniziata sotto le coperte. Questa affermazione si avvia e completamento automatico. Se si utilizza esplicita BEGIN TRAN / TRAN commettere comandi, è possibile raggruppare queste insieme come una transazione esplicita — un insieme di istruzioni che deve fallire o avere successo insieme.
SQL Server implementa vari livelli di isolamento delle transazioni, per garantire le proprietà di atomicità, coerenza, isolamento e durevolezza (acido) di tali operazioni. In termini pratici, questo significa che usa serrature e chiavistelli per mediare transazionale accesso alle risorse di database condiviso e prevenire "interferenza" tra le operazioni.
Generalmente parlando, vostra strategia e processi per indagare e gestione delle transazioni di SQL Server sarà limitati a poche domande chiave:
- Ciò che le transazioni sono attive e sessioni di ciò che li eseguono? (session_transactions, active_transactions)
- Transazioni di ciò che stanno facendo più lavoro? (database_transactions)
- Quali operazioni causano problemi di blocco/blocco? (blocchi)
Queste ragioni, indagando blocco e blocco è di gran lunga il più comune uso di queste DMV. Un'area di indagine che continuerà a diventare sempre più comune è in attività generato quando si utilizza il livello di isolamento dello SNAPSHOT. Il livello di isolamento dello snapshot è stato introdotto in SQL Server 2005. L'isolamento dello snapshot elimina blocco e blocco critico da con un archivio di versione nel database tempdb per mantenere la concorrenza, piuttosto che istituire blocchi su oggetti di database. Ci sono un certo numero di DMV fornito per indagare su questo livello di isolamento.
Transazioni di lunga durata di monitor
Ora passiamo agli script. Salvo diversa indicazione, tutte queste query lavora con SQL Server 2005, 2008 e 2008 R2, e tutti richiedono l'autorizzazione VIEW SERVER STATE. Questo script utilizza due DMV. Il primo è sys.dm_tran_database_transactions, che è descritto nella documentazione di SQL Server on-line (BOL) come segue: "Restituisce informazioni sulle transazioni a livello di database."
Il secondo è sys.dm_tran_session_transactions, che semplicemente: "Restituisce informazioni di correlazione per le transazioni associate e sessioni."
La descrizione concisa data per database_transactions piuttosto smentisce la sua potenziale utilità. Lo script seguente fornisce una query che mostra, per ogni sessione, quali i database sono in uso da una transazione aperta da quella sessione, se la transazione è aggiornato a sola lettura in una qualsiasi delle basi di dati (per impostazione predefinita la maggior parte delle transazioni sono di sola lettura), quando la transazione aggiornato per lettura e scrittura per tale database, quanti record di log scritto e quanti byte sono stati utilizzati per conto di tali record del log:
SELEZIONARE st.session_id, DB_NAME(dt.database_id) AS database_name, caso in cui dt.database_transaction_begin_time È NULL allora 'read-only'
ELSE 'read-write' FINE AS transaction_state, dt.database_transaction_begin_time, read_write_start_time, dt.database_transaction_log_record_count, dt.database_transaction_log_bytes_usedFROM sys.dm_tran_session_transactions come st INNER JOIN sys.dm_tran_database_transactions come dt
ON st.transaction_id = dt.transaction_idORDER BY st.session_id, database_name
Questi tipi di query su database_transactions sono molto utili quando monitoraggio cose come:
- Sessioni con transazioni aperte per lettura e scrittura (particolarmente importante per le sessioni di sonno)
- Sessioni causando il log delle transazioni per crescere/gonfiare
- Il progresso delle operazioni di lunga durata (per operazioni di massa non registrate, ogni riga di indice colpite produrrà circa record di log di una transazione)
Blocco e blocco
Il nostro script di esempio per la categoria relative alle transazioni di DMV utilizza sys.dm_tran_locks DMV, che è descritto da BOL come segue:
"Restituisce informazioni sul blocco attualmente attiva Gestione risorse. Ogni riga rappresenta una richiesta attualmente attiva al gestore di blocco per un blocco che sia stata concessa o è in attesa di essere concessa. Le colonne del set di risultati sono divisi in due gruppi principali: risorse e richiesta. Il gruppo di risorse descrive la risorsa su cui la richiesta di blocco è stato fatto, e il gruppo di richiesta viene descritta la richiesta di blocco".
Questa DMV è utile nell'aiutare a identificare i problemi di blocco e blocchi su istanze di database:
-Guardare attivo Lock gestione risorse per il database corrente
SELEZIONARE request_session_id,
Db_name(resource_database_id) AS [Database], resource_type, resource_subtype, request_type, request_mode, resource_description, request_mode, request_owner_type
DA sys.dm_tran_locksWHERE request_session_id > 50 E resource_database_id = < > request_session_id DB_ID() E @ @ SPIDORDER BY request_session_id;
-Cercare di blocco
SELEZIONARE 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 sys.dm_tran_locks come tl INNER JOIN sys come wt ON tl.lock_owner_address = wt.resource_addressORDER BY wait_duration_ms DESC;
La prima query mostra i tipi di blocco e il loro status da SPID, filtrati dal database corrente ed eliminando la connessione corrente e SPID di sistema. La seconda query fornisce informazioni riguardanti qualsiasi blocco che potrebbero essere che si verificano, a livello di istanza. Si noti che questa seconda query si unisce a sys DMV per ottenere dati sulla lunghezza del tempo che un processo è stata attesa, a causa di blocco, e su quale risorsa.
A meno che non si dispone di gravi problemi di blocco, in genere è necessario per l'esecuzione di che ciascuna di queste query più volte per la cattura di blocco. Se si identifica due istruzioni di modifica dei dati, o una query e una modifica dei dati, che sono abbracciare nel bloccare gravi, o anche il deadlock, quindi avrete bisogno di estrarre il testo SQL per le query, esaminarle, eseguirli su un sistema di test — con Profiler traccia in esecuzione — e lavorare fuori un modo per ottimizzare le query, o aggiungere gli indici, per alleviare il problema.
.png)
Glenn Berry lavora come un architetto di database alla NewsGator Technologies in Denver, Colorado Egli è un MVP per SQL Server e ha un'intera collezione di certificazioni Microsoft, tra cui MCITP, MCDBA, MCSE, MCSD, MCAD e MCTS, che dimostra che gli piace a prendere le prove.
.png)
Louis Davidson è stato nel settore IT per 16 anni come uno sviluppatore di database aziendale e architetto. Egli è stato un Microsoft MVP per SQL Server per sei anni e ha scritto quattro libri sulla progettazione di database. Attualmente è l'architetto di dati e a volte DBA per la rete Christian Broadcasting, gli uffici di supporto in Virginia Beach, in Virginia e Nashville, Tenn.
.png)
Timothy Ford è un MVP per SQL Server e ha lavorato con SQL Server per più di 10 anni. Egli è il primario DBA ed esperto di materia per la piattaforma di SQL Server per la salute di spettro. È stato iscritto sulla tecnologia dal 2007 per una varietà di siti Web e mantiene il proprio blog a thesqlagentman.com, coprendo SQL come argomenti di sviluppo così come telelavoro e professionale.
Ulteriori informazioni su "SQL Server DMV Starter Pack" a red-gate.com/our-company/about/book-store.