Cuando se trata de la supervisión de las bases de datos del sistema SQL Server, la base de datos tempdb es una de las más importantes a tener en cuenta, ya que esta contiene la mayoría de los objetos creados internamente.
Así mismo de algunas características únicas de la base de datos tempdb en sí (como el almacén de versiones, e.g.), por ejemplo), que se abordarán más adelante de forma separada, su rendimiento y uso del espacio, son las áreas cruciales para el monitoreo (y el ajuste, también), porque tempdb es la base de datos más activa y el recurso común en un entorno de SQL Server.
Por lo general, la configuración y el rendimiento de la base de datos tempdb depende en gran medida del entorno mismo (recursos físicos), la carga de trabajo (cantidad de operaciones paralelas como la creación de objetos temporales, por ejemplo) y las aplicaciones que usan los recursos de SQL Server.
Este artículo se va a centrar solo en las técnicas básicas de monitoreo de tempdb, en relación con el uso del espacio en disco tempdb sus archivos de base de datos, ligado con información específica sobre la base de datos en sí (fecha de creación y modelo de recuperación), y verificar el uso de espacio de elementos específicos de tempdb como usuario objetos, objetos internos y la tienda de versiones.
Supervisar los archivos de base de datos tempdb y recopilar otra información
Para poder monitorear las estadísticas de la base de datos tempdb de SQL Server, se puede ejecutar la siguiente consulta. La primera instancia de la consulta nos revela el tamaño actualmente asignado del archivo de datos, el archivo de registro y el espacio utilizado por el archivo de registro:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
--First part of the script SELECT instance_name AS 'Database', [Data File(s) Size (KB)]/1024 AS [Data file (MB)], [Log File(s) Size (KB)]/1024 AS [Log file (MB)], [Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)] FROM (SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)', 'Log File(s) Used Size (KB)') AND instance_name = 'tempdb') AS A PIVOT (MAX(cntr_value) FOR counter_name IN ([Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)])) AS B GO -- --Second part of the script SELECT create_date AS [Creation date], recovery_model_desc [Recovery model] FROM sys.databases WHERE name = 'tempdb' GO |
La segunda instancia nos muestra exactamente cuándo se creó tempdb y qué modelo de recuperación está utilizando.
Para poder obtener el tamaño total de la base de datos sin detalles, utilice la siguiente consulta:
|
1 2 |
SELECT SUM(size)/128 AS [Total database size (MB)] FROM tempdb.sys.database_files |
Dado que SQL Server ha creado automáticamente la base de datos tempdb desde cero en cada inicio del sistema, y el hecho de que su tamaño inicial predeterminado de archivo de datos es de 8 MB (a menos que esté configurado y ajustado de manera diferente según las necesidades del usuario), es más sencillo revisar y poder monitorear los archivos de base de datos estadísticos utilizando la consulta anterior. En este caso, se establece el valor de tamaño inicial predeterminado, y el resultado nos muestra que la base de datos se ha expandió por un factor de cinco veces, y este tamaño total de la base de datos apunta a una ligera actividad media en un período de tiempo más largo de un servidor SQL específico donde reside tempdb.
Si es necesario, como para cualquier otra base de datos regular, los usuarios pueden monitorear los índices contenidos en la base de datos tempdb.
Supervisar el uso específico del espacio de objetos tempdb
Estos objetos son los siguientes:
- Espacio libre en la base de datos,
y tres elementos, únicos de tempdb:
- Objetos internos: creados por SQL Server para poder procesar las consultas (reconstrucción de índices en línea, tablas del sistema y recuperación de vistas, hash join, etc.)
- Objetos de usuario: creados por usuarios (tablas temporales, variables de tabla, etc.)
- Almacén de versiones: archivo de páginas de datos, necesario para poder admitir aplicaciones que utilizan la función de control de versiones de filas en SQL Server (por ejemplo, desencadenadores)
Utilice la siguiente consulta para poder obtener información sobre el uso del espacio por parte de objetos específicos tempdb:
|
1 2 3 4 5 6 7 8 |
SELECT (SUM(unallocated_extent_page_count)/128) AS [Free space (MB)], SUM(internal_object_reserved_page_count)*8 AS [Internal objects (KB)], SUM(user_object_reserved_page_count)*8 AS [User objects (KB)], SUM(version_store_reserved_page_count)*8 AS [Version store (KB)] FROM sys.dm_db_file_space_usage --database_id '2' represents tempdb WHERE database_id = 2 |
Como se mencionó anteriormente, el tempdb utilizado para la referencia en este artículo se utiliza en SQL Server con una actividad generalmente ligera y actualmente, no se utiliza ninguna de las funcionalidades de versiones de fila (columnaAlmacén de versiones (KB)).
Supervisar el uso del espacio de tablas temporales
Adicionalmente, si se almacena una gran cantidad de datos en tablas temporales debido a la mayor actividad del servidor SQL, tiene que verificar el número de filas y el espacio utilizado/reservado de cada una de las tablas temporales que se crearon en una base de datos específica:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE <database_name> SELECT tb.name AS [Temporary table name], stt.row_count AS [Number of rows], stt.used_page_count * 8 AS [Used space (KB)], stt.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS prt INNER JOIN tempdb.sys.dm_db_partition_stats AS stt ON prt.partition_id = stt.partition_id AND prt.partition_number = stt.partition_number INNER JOIN tempdb.sys.tables AS tb ON stt.object_id = tb.object_id ORDER BY tb.name |
En este caso, vamos a utilizar una base de datos de prueba personalizada. Los objetos temporales anteriormente marcados en la captura de pantalla son tablas creadas por el usuario.
Tenga en cuenta que todos los objetos temporales se eliminarán en el servicio de SQL Server o en el reinicio de la máquina y la información sobre ellos solo se puede recuperar durante la sesión activa del usuario.
- Cómo poder monitorear la base de datos tempdb del SQL Server - December 5, 2019