Las vistas y funciones de administración dinámica (DMVs y DMFs) son una poderosa característica introducida en SQL Server 2005. Proporcionan estadísticas e información en tiempo real sobre el estado interno del servidor, ayudando a los usuarios a solucionar problemas y optimizar el rendimiento.

Las DMVs y DMFs han reemplazado muchas tablas del sistema y procedimientos almacenados, lo que facilita el acceso y análisis de los datos del servidor. Se pueden abordar utilizando nombres de dos, tres o cuatro partes y existen en el esquema sys, requiriendo el prefijo “sys” al usarlos.

Hay alrededor de 70 DMVs y funciones disponibles, cada una con un propósito específico. Veamos algunos ejemplos:

DMVs y funciones relacionadas con la base de datos

Sys.dm_db_file_space_usage proporciona información sobre el uso del espacio de archivos para cada archivo en la base de datos tempdb. Incluye detalles como el recuento de páginas de extensión no asignadas, el recuento de páginas reservadas para objetos de usuario y el recuento de páginas reservadas para objetos internos. Esta DMV se puede utilizar para solucionar problemas de espacio en disco insuficiente en tempdb.

Por ejemplo, para encontrar el número de páginas no asignadas en kilobytes, puede utilizar la siguiente consulta:

SELECT SUM(unallocated_extent_page_count) AS [páginas libres], 
       (SUM(unallocated_extent_page_count) * 8) AS [espacio libre en KB]
FROM sys.dm_db_file_space_usage;

DMVs y funciones relacionadas con la ejecución

Sys.dm_exec_sessions devuelve información sobre las sesiones autenticadas establecidas con SQL Server. Incluye detalles como el ID de sesión, la hora de inicio de sesión, el nombre del host, el nombre del programa, el nombre de inicio de sesión, el uso de memoria y el nivel de aislamiento de transacciones.

Por ejemplo, para determinar el número de sesiones establecidas por cada inicio de sesión, puede utilizar la siguiente consulta:

SELECT login_name, COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

DMVs y funciones relacionadas con los índices

Sys.dm_db_index_physical_stats proporciona información sobre el tamaño y la fragmentación de un índice de una tabla o vista especificada. Reemplaza la instrucción DBCC SHOWCONTIG. La columna avg_fragmentation_in_percent se puede utilizar para determinar el nivel de fragmentación del índice.

Por ejemplo, para encontrar la información de fragmentación para todas las bases de datos en la instancia de SQL Server, puede utilizar la siguiente consulta:

SELECT *
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL);

DMVs y funciones relacionadas con el sistema operativo

Sys.dm_os_memory_objects devuelve los objetos de memoria actualmente asignados por SQL Server. Sys.dm_os_performance_counters permite a los usuarios consultar los contadores de SQL Server relacionados con la instancia, como bloqueos, bases de datos, transacciones y administrador de memoria.

Sys.dm_os_wait_stats proporciona información sobre los hilos que esperan recursos en milisegundos. Esto puede ayudar a los usuarios a ajustar las aplicaciones para obtener una mejor concurrencia y rendimiento.

DMVs y funciones relacionadas con las transacciones

Sys.dm_tran_active_transactions devuelve información sobre las transacciones en una instancia de SQL Server, incluido el ID de transacción, el nombre, la hora de inicio y el tipo.

Sys.dm_tran_current_transaction proporciona información sobre la transacción actual en la sesión actual, como el ID de transacción y el nivel de aislamiento de instantánea.

Sys.dm_tran_database_transactions devuelve información sobre las transacciones en una base de datos específica.

Conclusión

Las vistas y funciones de administración dinámica son una herramienta valiosa para los administradores y desarrolladores de SQL Server. Proporcionan información en tiempo real sobre el rendimiento del servidor, el uso de la base de datos y los detalles de las transacciones. Al aprovechar estas DMVs y DMFs, los usuarios pueden solucionar problemas, optimizar el rendimiento y tomar decisiones informadas.

Para obtener una lista completa de DMVs y funciones, consulte la documentación en línea de SQL Server 2005 Books.