SQL Server: Administración de las transacciones
La administración de las transacciones de SQL Server es un paso importante para asegurar la fluidez de las operaciones y evitar los errores por bloqueos.
Extraído de "SQL Server DMV Starter Pack", publicado por libros de puerta roja (2010).
Glenn Berry, Louis Davidson y Tim Ford
Buscando en niveles específicos de administración de las operaciones de SQL Server, usted debe considerar cuidadosamente cómo se administran objetos dinámicos de gestión (OCD) relacionados con la transacción. Todas las vistas de administración dinámica (DMV) en la categoría "relacionados con la transacción" de OCD comienzan con "sys.dm_tran_" como una declaración de apertura.
En definitiva, cada instrucción ejecutada contra SQL Server es transaccional. Si se emite una sola instrucción SQL, se inicia una transacción implícita en las portadas. Esta declaración se iniciará y completar automáticamente. Si utiliza explícita BEGIN TRAN / comandos COMMIT TRAN, puede agrupar estos como una transacción explícita — un conjunto de instrucciones que deben fallar o tener éxito juntos.
SQL Server implementa diversos niveles de aislamiento de transacción, para garantizar las propiedades de atomicidad, coherencia, aislamiento y durabilidad (ACID) de estas transacciones. En términos prácticos, esto significa que utiliza bloqueos y pestillos para mediar el acceso transaccional a recursos de base de datos compartida y evitar "interferencias" entre las transacciones.
En términos generales, su estrategia y procesos de investigación y administración de transacciones de SQL Server se limitarán a algunas preguntas claves:
- ¿Qué transacciones están activas y qué sesiones les ejecutan? (session_transactions, active_transactions)
- ¿Qué transacciones están haciendo más trabajo? (database_transactions)
- ¿Que las transacciones están causando problemas de bloqueo bloqueo? (bloqueos)
Estas razones, investigando el bloqueo y el bloqueo es el más común uso de estas DMV. Es un área de investigación que seguirá convertido en cada vez más común en la actividad que se genera cuando se utiliza el nivel de aislamiento de instantánea. El nivel de aislamiento de instantánea fue introducido en SQL Server 2005. Aislamiento de instantánea elimina el bloqueo y los interbloqueos usando un almacén de versiones en la base de datos tempdb para mantener la concurrencia, en lugar de establecer bloqueos en los objetos de base de datos. Hay un número de DMV a investigar este nivel de aislamiento.
Transacciones de larga duración de monitor
Ahora pasemos a las secuencias de comandos. Salvo indicación en contrario, todas estas consultas funcionan con SQL Server 2005, 2008 y 2008 R2, y todos requieren permiso de ver estado del servidor. Esta secuencia de comandos utiliza dos DMV. La primera es sys.dm_tran_database_transactions, que se describe en los libros de SQL Server online (BOL) como sigue: "Devuelve información acerca de las transacciones a nivel de base de datos".
La segunda es sys.dm_tran_session_transactions, que simplemente: "Devuelve información de correlación para las transacciones asociadas y sesiones".
La descripción concisa para database_transactions bastante desmiente su posible utilidad. La siguiente secuencia de comandos proporciona una consulta que muestra, por sesión, qué bases de datos están en uso por una transacción abierta por ese período de sesiones, si la transacción se ha actualizado a la lectura y escritura en cualquiera de las bases de datos (por defecto la mayoría de las transacciones son de sólo lectura), cuando la transacción se actualiza para lectura y escritura para esa base de datos, cuántos registros escritos y cuántos bytes se utilizaron en nombre de los registros:
SELECT st.session_id, DB_NAME(dt.database_id) AS database_name, caso cuando dt.database_transaction_begin_time es NULL entonces de "sólo lectura"
ELSE 'lectura' END 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 como st INNER JOIN sys.dm_tran_database_transactions como dt
ON st.transaction_id = dt.transaction_idORDER por st.session_id, database_name
Este tipo de consultas en database_transactions es muy útil cuando se supervisa las cosas tales como:
- Sesiones con las transacciones abiertas de lectura y escritura (especialmente importantes para dormir sesiones)
- Sesiones causando el registro de transacciones para crecer y engordar
- El progreso de transacciones de larga duración (para granel no ha iniciado operaciones, cada fila del índice afectado producirá aproximadamente récord de registro de una transacción)
Bloqueo y bloqueo
Nuestro script de ejemplo para la categoría relacionada con la transacción del DMV utiliza la sys.dm_tran_locks DMV, que es descrita por BOL como sigue:
"Devuelve información sobre el bloqueo activo recursos de gestor. Cada fila representa una solicitud activa para el administrador de bloqueos para un bloqueo que se ha concedido o está esperando a ser concedido. Las columnas del conjunto de resultados se dividen en dos grupos principales: recursos y solicitud. El grupo de recursos describe el recurso en el que se está realizando la solicitud de bloqueo y el grupo de solicitud describe la solicitud de bloqueo".
Este DMV es útil para ayudar a identificar los problemas de bloqueo y bloqueo en las instancias de base de datos:
--Mirar recursos activos de administrador de bloqueos para base de datos actual
Seleccione request_session_id,
Db_name(resource_database_id) AS [base de datos], resource_type, resource_subtype, request_type, request_mode, resource_description, request_mode, request_owner_type
DE sys.dm_tran_locksWHERE request_session_id > 50 Y resource_database_id = < > DB_ID() Y de request_session_id @@ SPIDORDER BY request_session_id;
--Buscar bloqueo
Seleccione 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 como tl INNER JOIN sys.dm_os_waiting_tasks como wt ON tl.lock_owner_address = wt.resource_addressORDER BY wait_duration_ms DESC;
La primera consulta muestra tipos de bloqueo y su estado por SPID, filtrados por la base de datos actual y la eliminación de la conexión actual y el sistema de SPID. La segunda consulta proporciona información relativa a cualquier bloqueo podría ser que se produzca, toda la instancia. Observe que esta segunda consulta se une a la sys.dm_os_waiting_tasks DMV para obtener datos sobre la cantidad de tiempo que ha estado esperando un proceso, debido al bloqueo, y qué recursos.
A menos que tenga problemas graves de bloqueo, normalmente necesitará ejecutar que cada una de estas consultas varias veces para atrapar el bloqueo. Si identificar dos instrucciones de modificación de datos, o una consulta y una modificación de los datos, que se están adoptando en bloqueo grave, o incluso bloqueos, entonces tendrá que extraer el texto SQL para las consultas, examinan, ejecutarlos en un sistema de prueba — con analizador de seguimiento de ejecución — y trabajar de una manera de optimizar las consultas, o agregar índices, para aliviar el problema.
.png)
Glenn Berry funciona como un base de datos arquitecto en NewsGator Technologies en Denver, Colorado Es un MVP de SQL Server y tiene una colección completa de certificaciones de Microsoft, incluyendo MCITP, MCDBA, MCSE, MCSD, MCAD y MCTS, que demuestra que le gusta tomar exámenes.
.png)
Louis Davidson ha sido en la industria de TI durante 16 años como desarrollador de base de datos corporativa y arquitecto. Ha sido un MVP de Microsoft SQL Server durante seis años y ha escrito cuatro libros sobre diseño de base de datos. Actualmente es el arquitecto de datos y a veces DBA de la Christian Broadcasting Network, apoyar a las oficinas en Virginia Beach, Virginia y Nashville, Tenn.
.png)
Timothy Ford es un MVP de SQL Server y ha estado trabajando con SQL Server para más de 10 años. Es el principal DBA y experto en materia para la plataforma de SQL Server para la salud del espectro. Ha sido escrito acerca de la tecnología desde 2007 para una variedad de sitios Web y mantiene su propio blog en thesqlagentman.com, abarcando SQL como teletrabajo y profesional así como temas de desarrollo.
Conozca más acerca de "SQL Server DMV Starter Pack" en red-gate.com/our-company/about/book-store.