Primer Artículo – Archivos de Base de Datos

A finales del 2008 mis amigos del MUG Perú me invitaron a colaborar con un boletín que estaban lanzando. Si quieren ver el boletín completo lo pueden bajar haciendo clic aqui. Se puede decir este que fue el primer artículo público que escribí sobre SQL Server:
.
ESTRUCTURA FISICA DE UNA BASE DE DATOS SQL SERVER
En esta oportunidad revisaremos la composición física de una base de datos en cuanto a los archivos que la componen, así como diferentes parámetros que se utilizan para crear una base de datos, con respecto al almacenamiento. Trataré de centrarme en el tema ya que cada punto invita a ir más allá en la investigación. Que sirva este artículo como base y ojalá despierte el interés en desarrollar cada uno de los puntos tratados. Para los ejemplos se ha utilizado SQL Server 2005, pero los conceptos aplican para cualquier versión de SQL Server a partir de la versión 7.
.
ARCHIVOS DE BASE DE DATOS
En una base de datos almacenamos información relevante para nuestro negocio u organización y desde el punto de vista físico, la base de datos está conformada por dos tipos de archivos:
.
· Archivos de Datos: Contiene los datos de la base de datos. Internamente, está compuesto por páginas numeradas secuencialmente que representan la unidad mínima de almacenamiento. Cada página tiene un tamaño de 8 Kb de información. Existen diferentes tipos de páginas, a tener en cuenta:
Página de datos Es el tipo principal de páginas y son las que almacenan los registros de datos.
Páginas de espacio libre (PFS Page Free Space) almacenan información sobre la ubicación y el tamaño del espacio libre
Páginas GAM and SGAM Utilizadas para ubicar Extensiones
Páginas de Mapa de Ubicación de Índices

(IAM – Index Allocation Map)
Contiene información sobre el almacenamiento de páginas de una tabla o índice en particular
Página de Índices Utilizada para almacenar registros de índices
.
También es importante mencionar el concepto de Extent o Extensión. Un Extent es una unidad de almacenamiento compuesta por 8 páginas de datos contiguas, representando 64Kb. El acceso a los datos en los archivo de datos es aleatoria, ya que cualquiera de las páginas, sin un orden en particular, puede ser leída o modificada, dependiendo de las operaciones que realicen los usuarios.
.
· Archivos de Registro de Transacciones: El propósito principal del registro de transacciones es la recuperación de datos a un momento en el tiempo o complementar una restauración de copia de respaldo completa (full backup). El registro de transacciones no contiene páginas, sino entradas con todos los cambios realizados en la base de datos, como son las modificaciones de datos, modificaciones de la base de datos y eventos de copia de seguridad y restauración. El acceso a datos es secuencial, ya que el registro de transacciones se actualiza en el mismo orden cronológico en el que se hacen las modificaciones. Este archivo no puede ser leído por herramientas de usuario de SQL Server, aunque existen herramientas de terceros que leen este archivo para recuperar los cambios efectuados. Dependiendo de la versión de SQL Server, el registro de transacciones se utiliza para otros propósitos como por ejemplo bases de datos espejo (mirror) y transporte remoto de transacciones (log shipping).
.
CREANDO LA BASE DE DATOS
Se puede crear una base de datos ejecutando la siguiente secuencia de comandos:
clip_image001[15]
.
En este ejemplo no se especifica ninguna de las características físicas de la base de datos, de modo que SQL Server va a aplicar los valores por defecto. En la siguiente figura se observa que se han creado un archivo de datos y uno de registro de transacciones y que dichos archivos se han ubicado físicamente en el directorio por defecto de SQL Server. También se les ha asignado el tamaño por defecto:
image
.
Utilizar de esta manera el comando CREATE DATABSE solo es útil si deseamos hacer algún tipo de prueba, pero no es la forma adecuada para crear bases de datos de producción. Lo mejor que podemos hacer es especificar cada uno de los parámetros físicos. En la siguiente secuencia de comandos especificamos los valores físicos de la base de datos y a continuación explicamos cada uno de ellos:
clip_image004
.
Si deseamos conocer el detalle de los archivos físicos de la base de datos que acabamos de crear ejecutamos la siguiente secuencia de comandos:
clip_image006
A continuación una breve explicación sobre los parámetros de almacenamiento utilizados en la creación de la base de datos dbTest
.
· NAME – Nombre lógico de los archivos.- Es el nombre que se va a utilizar en las diferentes operaciones con archivos de datos.
.
· FILENAME – Ubicación y nombre de los archivos.- Es importante definir la ubicación física de los archivos. Estos pueden ser ubicados en cualquiera de las particiones disponibles en el equipo donde se está instalando la base de datos. Se recomienda que el archivo de datos y el de registro de transacciones se ubiquen en discos físicos diferentes, para efectos de seguridad y desempeño.
.
· SIZE – tamaño inicial de los archivos.- Es importante estimar el tamaño inicial del archivo de datos. Para ello, es necesario estimar el tamaño de cada tabla, multiplicando el largo de un registro (suma de bytes de todos los campos de la tabla) por el total de registros que se estima tener en un periodo de tiempo. Al final debemos sumar todos los valores obtenidos. Adicionalmente debemos contemplar el espacio que ocupa el modelo de la base de datos así como los índices no agrupados.
.
Ya hemos mencionado que un archivo de datos esté conformado por páginas de 8Kb. Entonces imaginemos que crear el archivo de datos es como comprar un cuaderno en el cual vamos a escribir la información de un curso que vamos a estudiar. Debemos estimar el contenido del curso para saber cuantas páginas debe tener nuestro cuaderno. En ese cálculo, podemos excedernos o quedarnos cortos. Si nos excedemos, vamos a tener páginas sobrantes y si nos quedamos cortos vamos a tener que conseguir más páginas para nuestro cuaderno.
.
De la misma manera, podemos cambiar el tamaño del archivo de datos para incluir más páginas haciendo crecer el archivo o quitar las páginas sobrantes encogiendo el archivo. Para ello utilizamos la sentencia ALTER DATABASE junto con la cláusula MODIFY FILE para hacerlo crecer y DBCC SHRINKFILE para reducirlo, teniendo en cuenta que en la reducción solo se puede quitar las páginas en blanco.
.
Para el registro de transacciones también debemos definir un tamaño inicial. De manera general se recomienda que este archivo tenga un tamaño inicial equivalente entre el 10% y 15% del archivo de datos. Pero esto va a depender del tamaño de la base de datos. Si tenemos una base de datos de 500Mb está bien tener un registro de transacciones de 50Mb. Pero para una base de datos de 10Gb sería adecuado un registro de transacciones de no más de 500Mb, lo cual representa tan solo un 5%. El archivo de registro de transacciones también se puede modificar en tamaño, de la misma manera que un archivo de datos.
.
· MAXSIZE – Tamaño máximo del archivo.- Especifica el tamaño máximo del archivo de datos. Siempre se recomienda configurar el tamaño máximo para evitar que se consuma el espacio libre del disco duro. Claro que no se debe llegar a este punto ya que el crecimiento de la base de datos debe ser monitoreado periódicamente por el administrador. En el ejemplo propuesto se observa que para el archivo de datos se ha especificado el parámetro UNLIMITED, es decir, crecimiento sin restricciones, mientras que al registro de transacciones si se le ha limitado el crecimiento hasta 80Mb.
.
· FILEGROWTH – crecimiento automático del archivo.- El crecimiento automático se puede desactivar pero se recomienda especificar estos valores y permitir que la base de datos crezca si se consumen todas las páginas en el archivo d datos, en especial si no hay un administrador de base de datos con experiencia a cargo. Se debe indicar un tamaño razonable de crecimiento automático para evitar que esto suceda muy a menudo ya que eso conlleva a una degradación en el desempeño de la base de datos y a un fraccionamiento del archivo físico.
El crecimiento automático solo va ser permitido hasta que la base de datos alcance el tamaño indicado en MAXSIZE
.
CONCLUSIONES:Diseñar la estructura física y definir el almacenamiento es importante cuando creamos una base de datos, en especial si contamos con infraestructura de almacenamiento como arreglos, RAID o SAN. Conociendo los diferentes parámetros podemos definir con precisión los valores de almacenamiento y tener un mejor control del comportamiento de la base de datos y de otros recursos relacionados, como son el espacio en disco duro.
.
Adicionalmente podemos contemplar el uso de diferentes grupos de archivos con más de un archivo de datos, distribuidos en diferentes particiones de almacenamiento, con el objetivo de optimizar el desempeño, inclusive implementando procesamiento paralelo. Pero este tema lo desarrollaremos en un artículo posterior.
.

Deja un comentario

Tu dirección de correo electrónico no será publicada.