Optimizando el Diseño de Base de Datos en SQL Server

Abstract

Palabras-Clave: SQL Server; base de datos; optimización; eficiencia; acceso a datos. In this article we are going to demonstrate the importance of correct database design to improve the efficiency of the applications that access them, the main database design optimization options are described, such as groups of files on different disks, use of standards, data types and relationships between tables, partitioned tables, columnar indexes and denormalization. Keywords: SQL Server; database; optimization; efficiency; data access. 1. Introducción Las organizaciones requieren cada vez mayor eficiencia en sus aplicaciones y una de las formas de conseguirlo es hacer un buen diseño de base de datos, esta debe ser amigable, escalable, simple, con estándares adecuados y usando todas las funcionalidades que el gestor de base de datos brinde, SQL Server brinda varias funcionalidades para mejorar el diseño de base de datos, desde la creación de los archivos y grupos de archivos hasta el uso de objetos como los índices ColumnStore para tablas con grandes cantidades de registros (Microsoft, Microsoft Learn, 2023). En el presente artículo se describirá como usar las siguientes funcionalidades para optimizar el diseño de base de datos: filegroups y archivos, tablas particionadas, uso de estándares, tipos de datos nchar y nvarchar, encriptación de objetos y datos, índices columnares y desnormalización, siempre que la opción de optimización mostrada en este artículo lo permita, se mostrará el beneficio frente a no utilizar la misma opción mostrando el tiempo que demora mostrar los datos utilizando el Plan de ejecución estimado, reporte que lo brinda la herramienta de administración de base de datos de SQL Server (Fritchey, 2012). El diseño optimizado de una base de datos requiere el uso adecuado de los tipos de datos en la definición de los campos de las tablas (Microsoft, Microsoft Learn, 2024), se han encontrado modelos donde, para relacionar las tablas se han utilizado datos de tipo entero (int) argumentando que la relaciones entre tablas es más rápida con este tipo de dato, en este artículo usamos SQL Server Management Studio para demostrar que no es la mejor opción. Filegroups y archivos de base de datos Las bases de datos en las organizaciones se almacenan el servidores de base de datos, estos tienen configurado un RAID de discos en los cuales se deben ubicar los archivos de la base de datos, los filegroups agrupan archivos de base de datos, existen tres tipos de archivos de base de datos en SQL Server: el principal que contiene la información inicial de la base de datos y contiene los metadatos de todos los archivos de la base de datos, los secundarios, los cuales, junto con el archivo principal permite almacenar la información de las tablas y los archivos de transacciones, que representan la zona de trabajo de SQL Server (Luvi Soft, s.f.).

Headnote

Resumen: La administración de la información en las organizaciones es uno de los pilares más importantes para eficiencia en sus procesos y la competitividad en el mercado, para la correcta toma de decisiones es necesario tener y procesar mucha información, esta información se guarda haciendo uso de diferentes herramientas que la administran, estas herramientas son los gestores de bases de datos. En este artículo vamos a demostrar la importancia del correcto diseño de base de datos para mejorar la eficiencia de las aplicaciones que acceden a estas, se describen las principales opciones de optimización del diseño de base de datos como grupos de archivos en diferentes discos, uso de estándares, tipos de datos y relaciones entre tablas, tablas particionadas, índices columnares y desnormalización.

Palabras-Clave: SQL Server; base de datos; optimización; eficiencia; acceso a datos.

Abstract: Information management in organizations is one of the most important pillars for efficiency in their processes and competitiveness in the market. For correct decision making, it is necessary to have and process a lot of information. This information is saved using different tools. that manage it, these tools are database managers. In this article we are going to demonstrate the importance of correct database design to improve the efficiency of the applications that access them, the main database design optimization options are described, such as groups of files on different disks, use of standards, data types and relationships between tables, partitioned tables, columnar indexes and denormalization.

Keywords: SQL Server; database; optimization; efficiency; data access.

1. Introducción

Las organizaciones requieren cada vez mayor eficiencia en sus aplicaciones y una de las formas de conseguirlo es hacer un buen diseño de base de datos, esta debe ser amigable, escalable, simple, con estándares adecuados y usando todas las funcionalidades que el gestor de base de datos brinde, SQL Server brinda varias funcionalidades para mejorar el diseño de base de datos, desde la creación de los archivos y grupos de archivos hasta el uso de objetos como los índices ColumnStore para tablas con grandes cantidades de registros (Microsoft, Microsoft Learn, 2023). En el presente artículo se describirá como usar las siguientes funcionalidades para optimizar el diseño de base de datos: filegroups y archivos, tablas particionadas, uso de estándares, tipos de datos nchar y nvarchar, encriptación de objetos y datos, índices columnares y desnormalización, siempre que la opción de optimización mostrada en este artículo lo permita, se mostrará el beneficio frente a no utilizar la misma opción mostrando el tiempo que demora mostrar los datos utilizando el Plan de ejecución estimado, reporte que lo brinda la herramienta de administración de base de datos de SQL Server (Fritchey, 2012).

El diseño de las bases requiere conocer los procesos en las organizaciones de los que se desea almacenar la información, otro punto clave para el diseño de la base de datos es el alcance que tendrá la aplicación. Para un buen diseño inicial se debe considerar un proceso llamado normalización, es una técnica de diseño que tiene pasos definidos para evitar redundancia de datos y definir correctamente la integridad de estos a través de sus formas normales (Núñez Hervas, 2023). El diseño optimizado de una base de datos requiere el uso adecuado de los tipos de datos en la definición de los campos de las tablas (Microsoft, Microsoft Learn, 2024), se han encontrado modelos donde, para relacionar las tablas se han utilizado datos de tipo entero (int) argumentando que la relaciones entre tablas es más rápida con este tipo de dato, en este artículo usamos SQL Server Management Studio para demostrar que no es la mejor opción. La definición adecuada de las relaciones entre tablas y la cardinalidad entre estas influye significativamente en la eficiencia de las consultas desde varias tablas (Lee y otros, 2023).

En las organizaciones se configuran la infraestructura de los servidores con equipos que tienen un conjunto de discos fijos, este grupo de discos fijos definen la forma de almacenamiento de la información, es en estos discos donde se va a crear las bases de datos para las aplicaciones, los servidores tienen un array de discos (HardZone, 2024) llamados Raid por su descripción en inglés "redundant array of independent disks", al crear las bases de datos se deberían utilizar todos los discos para la ubicación de los archivos de base de datos organizados en grupos de archivos, la extracción de los datos de una base de datos con sus archivos distribuidos en varios discos duros en más rápida que extraer los datos de una base de datos con sus archivos en un mismo disco. Al diseñar una base de datos con varios archivos se puede particionar las tablas para que la extracción de los datos sea más rápida, estás tablas se les llama tablas particionadas, existen dos tipos de particiones de tablas: partición horizontal y partición vertical, las que se muestran en este artículo son las tablas particionadas horizontalmente, que son aquellas que tienen muchos registros (Microsoft, Microsoft Learn, 2023).

Uno de los puntos importantes para la mejora administración de las bases de datos es mantener la documentación de la misma actualizada, crear diccionario de datos, diagramas de modelo de datos actualizado y crear scripts para mostrar, cuando sea necesario, los metadatos, que es la información de los objetos de la base de datos como nombres de tablas, nombres de campos, nombres de restricciones, etc.

2. Alternativas de optimización

Como se describió líneas arriba, es este artículo se describirán las alternativas más importantes para la optimización de la base de datos, las cuales son:

2.1. Filegroups y archivos de base de datos

Las bases de datos en las organizaciones se almacenan el servidores de base de datos, estos tienen configurado un RAID de discos en los cuales se deben ubicar los archivos de la base de datos, los filegroups agrupan archivos de base de datos, existen tres tipos de archivos de base de datos en SQL Server: el principal que contiene la información inicial de la base de datos y contiene los metadatos de todos los archivos de la base de datos, los secundarios, los cuales, junto con el archivo principal permite almacenar la información de las tablas y los archivos de transacciones, que representan la zona de trabajo de SQL Server (Luvi Soft, s.f.).

2.2. Particion horizontal de tablas

En las bases de datos transaccionales existen tablas con grandes cantidades de datos, estás tablas deben tratarse de manera especial, debe optimizarse su diseño para que la extracción de datos sea más rápida y por consiguiente las aplicaciones que acceden a la base de datos sean más eficientes, la partición horizontal de tablas ayuda a que la extracción de los datos sea más eficiente (Noble, 2020), para conseguir el beneficio de una tabla particionada es necesario que la base de datos tenga varios grupos de archivos y los archivos de esta estén en varios discos del servidor, extraer la información de varios discos es más eficiente que extraer información de una tabla grande ubicada en un solo disco.

2.3. Estándares de base de datos

En las organizaciones se deben crear y establecer como regla estándares de base de datos, los estándares son reglas definidas en la organización por los administradores de base de datos (DBA) para nombrar los objetos creados en la base de datos, se definen desde los nombres de las bases de datos, archivos, tablas, campos y todos los objetos dentro de estas. Los estándares van a facilitar y reglamentar el trabajo con base de datos. Los estándares son definidos por los DBA o responsables de la base de datos, estos pueden variar de según la organización y su objetivo es que todos los que acceden a la base de datos entiendan el diseño y trabajen de manera similar respecto a nombrar objetos de la base de datos (OTCA, 2022).

2.4. Tipos de datos para campos clave

En el diseño del modelo relacional de la base de datos, las tablas van relacionadas a través de uno o más campos, estos campos por los que se relacionan las dos tablas involucradas debe ser del mismo tipo, se recomienda el uso de los tipos de datos nchar y nvarchar en los campos que forman la clave primaria (PK), por ser más eficientes para la extracción de los datos desde varias tablas y porque en este tipo de datos se pueden almacenar los identificadores de los registros de manera adecuada, el diseño de la tabla en ocasiones requiere que se tenga algunos datos (campos de la tabla) como claves candidatas, es decir, datos que también va a identificar a un registro en una tabla y que no es ningún campo de la PK. Es más beneficiosos en cuanto a la eficiencia de las consultas crear la PK con campos que son los identificadores de los registros, hay diseñadores de base de datos que utilizan el tipo de campo entero (int) para relacionar tablas considerando que la relación con ese tipo de dato en más eficiente, en este artículo demostramos que no es conveniente.

2.5. Índices columnares

Los índices en las tablas y vistas permiten el acceso a la información de las tablas con mayor eficiencia, existen varios tipos de índices, uno de estos tipos son los índices columnares (ColumnStore Index) que son eficientes con grandes cantidades de datos. El uso de estos tipos de índices tiene varios beneficios como: trabajar con algunas columnas, no existe límite para el número de columnas en el índice, trabajo con tablas particionadas y la lectura sólo de las columnas necesarias para la extracción de datos (Petkociv, 2020).

2.6.Desnormalización del diseño de base de datos

El proceso de creación del modelo de base de datos sigue un conjunto de reglas que evitan la redundancia de datos y asegura la integridad de estos, este proceso se llama Normalización (Puig, 2022), uno de los pasos finales de la normalización es la desnormalización, es decir, deshacer alguna de las reglas del proceso de normalización para optimizar el diseño sacrificando posiblemente espacio en el disco o pasando por alto redundancia de datos.

3. Optimizando el diseño de la base de datos

En el presente artículo se presenta uno a más ejemplos de cada una de las sugerencias para la optimización de una base de datos, mientras sea posible de mostrar los tiempos de usar y no usar la opción se va a utilizar la herramienta de administración de base de datos de SQL Server (Management Studio) usando el Plan de ejecución estimado (Microsoft, Microsoft Learn, 2023).

3.1. Filegroups y archivos de base de datos

Creando una base de datos en varios discos, para ello utilizamos un equipo con varias unidades (C:, D:, E:) que van a simular el uso de varios discos en el servidor de base de datos. El ejemplo crea la base de datos BDEmpresa con tres grupos de archivos y con los archivos ubicados en los diferentes discos. El Script primero crea las carpetas en los discos, luego se crea la base de datos especificando más de un archivo en cada grupo de archivos.

El script muestra la creación de la base de datos, para mayor eficiencia se han ubicado

los archivos de los diferentes grupos en los diferentes discos.

La ventana de propiedades de la base de datos se muestra como la siguiente figura.

Es conveniente anotar que la base de datos creada en un solo disco y con un solo archivo se puede modificar, se pueden agregar grupos de archivos, archivos de base de datos y los objetos dentro de estos se pueden mover para aumentar la eficiencia de la base de datos, este trabajo de mantenimiento se puede realizar sin necesidad de detener ni reiniciar el gestor de base de datos.

3.2. Partición horizontal de tablas

Las tablas que tienen o se proyecta que tendrán gran cantidad de registros se deberían particionar horizontalmente para que las consultas realizadas con estas sean más eficientes, para que se note el beneficio de una tabla particionada de manera horizontal, se debe tener una base de datos con varios grupos de archivos y los archivos de cada grupo deberían estar ubicados en varios discos como se presentan en este artículo.

Este ejemplo muestra la creación de una tabla particionada por la clave primaria y la partición de una tabla cuando ya existe. Para particionar una tabla de debe crear primero una función de partición que define los rangos por los que la tabla se va a particionar, luego crear un esquema de partición que define en que grupos de archivos se va a ubicar cada rango definido en la función de partición y luego la tabla particionada.

Al visualizar las propiedades de la tabla se muestra que se encuentra particionada. En la opción Storage, se muestra el número de particiones además de los nombres de la función de partición y del esquema de partición.

Después de insertar clientes, el listado de los registros con las particiones a la que

pertenece cada registro se muestra en la siguiente figura.

Al usar la función $partition se muestra el número de partición, lo que significa que los registros se han almacenado en diferentes discos, en el esquema de partición se definió que los registros de la partición 1 se almacenan en el grupo PRIMARY, los registros de las particiones 2 y 4 se almacenan en los archivos del grupo CONTABILIDAD y los registros de las particiones 3 y 5 se guardan en los archivos del grupo COMERCIAL.

3.3. Estándares de base de datos

Los estándares de base de datos ayudan muchos a las organizaciones a trabajar de manera ordenada, todos los que tengan acceso a creación o modificación de los objetos de la base de datos deberían seguir las reglas del estándar. No es todas las organizaciones es el mismo estándar, pero es conveniente que se cree y defina en este como nombrar todos los elementos de base de datos.

A continuación, se muestra un fragmento de una estándar para desarrollo de Proyectos.

3.4. Tipos de datos para campos clave

Es muchos diseños se utiliza el campo entero (int) para la relación de tablas en la base de datos argumentando que es más eficiente, en todos los diseños con el campo de tipo int como PK se tuvo que incluir un campo adicional para el identificador del registro. Eneste artículo se muestra que el uso del tipo de dato entero no es más eficiente, en lugar de este, se debería utilizar el tipo de dato nchar.

Para este punto se va a crear dos tablas, utilizaremos la base de datos Northwind, que es una base de datos libre que provee Microsoft, desde Northwind agregaremos registros para la creación de las dos tablas, una de Categorías y otra de Productos, primero teniendo en cuenta la relación con el campo tipo entero y otras dos tablas relacionadas con el campo de tipo nchar, las tablas relacionadas con el tipo nchar se han creado en el esquema Caracter. La figura muestra el diagrama creado con los dos pares de tablas.

El Plan de ejecución estimado del listado de los productos incluyendo el nombre de la categoría en ambos casos muestra el mismo resultado, lo que comprueba que la relación de las tablas con el tipo de dato int no es mas eficiente. Por otro lado, al crear una tabla con el campo int como clave primaria, se debe incluir otro campo como clave alterna para guardar el código de los registros. La figura siguiente la tabla de categorías con los campos ID de tipo entero y al campo código tipo nchar.

Al buscar un registro en la tabla Categorías se utiliza el código, en la tabla que no tiene un ID de tipo entero, el código es la clave primaria, en la tabla con ID de tipo entero, el código de la categoría es clave alterna, al realizar la búsqueda de un registro, la tabla con campo entero para la relación es mucho menos eficiente que la tabla que tiene la clave primaria con el código de tipo nchar.

La figura muestra el script para la búsqueda de una categoría cuyo código es 000004.

Los planes de ejecución muestran los tiempos de cada listado, la búsqueda en la tabla con la clave alterna tiene un costo de 0.0065704 (Fig. 11) y la búsqueda en la tabla con el campo nchar tiene un costo de 0.0032831 (Fig. 12). Se puede notar la eficiencia de la tabla con la clave primaria de tipo nchar.

3.5. Índices columnares

Los índices son objetos de la base de datos que mantienen un orden de los registros de una tabla o vista por uno o más campos. Existen índices agrupados, definidos por la clave primaria de la tabla, índices no agrupados, definidos para ordenar los registros por otros campos que no sea la clave primaria, índices particionados, para tablas particionadas e índices columnares. Los índices columnares usan el concepto de almacenamiento dedatos en base a las columnas de la tabla, lo que permite conseguir una eficiencia en las consultas de hasta diez veces más efectiva que los índices basados en filas.

Para este artículo se va a crear una tabla con clientes, primero con el índice basado en filas y otra con el índice columnar, se compara los rendimientos de la misma consulta para comprobar la eficiencia de los índices columnares. La figura muestra el script de la creación de las tablas, después se agregan la misma cantidad de registros en ambas tablas.

La figura siguiente muestra los planes de ejecución estimados que muestran los tiempos de cada consulta, el listado sin ordenamientos ni filtros de ambas tablas puede verse el tiempo de cada listado, la tabla llamada ClientesFilas con el índice por filas tiene un costo de 0.0041228, y la tabla ClientesColumnas con el índice ColumnStore tiene un costo de 0.0031507, se demuestra una reducción de más de 23% en la eficiencia de la consulta.

3.6.Desnormalización del diseño de base de datos

El objetivo en el diseño de base de datos del proceso de normalización es evitar redundancia de datos a través de las formas normales (Pacheco, 2018). Este proceso no tiene en cuenta el rendimiento de la base de datos y en ocasiones es mejor y necesario desnormalizar el diseño de la base de datos, en este proceso de duplica intencionalmentealgunas columnas (desnormalización) para mejorar el rendimiento de la base de datos

(Coronel y otros, 2011).

En este artículo se van a crear dos tablas, una con Clientes y Pedidos, luego hacer un listado de los pedidos incluyendo el nombre del cliente, usando Joins, luego se crea otra tabla de pedidos donde se incluya el nombre y dirección del cliente (tabla desnormalizada) y se muestra el mismo listado. La figura siguiente muestra las tablas.

El listado de los pedidos, incluyendo el nombre y dirección del cliente muestra un rendimiento bastante diferente, para las tablas normalizadas es necesario un join y para la tabla desnormalizada no es necesario. La Fig. 16 muestra los listados.

Al mostrar el plan de ejecución estimado de ambos listados podemos ver que los costos son diferentes, aun mostrando el mismo listado, las tablas normalizadas tienen un costo de 0.0450401, y la tabla desnormalizada 0.0153061, esto es 66% más eficiente con la tabla desnormalizada (Fig. 17).

4. Conclusiones

En las organizaciones es muy importante el almacenamiento de las transacciones de todos sus procesos, las bases de datos deben ser diseñadas de manera óptima, las sugerencias de este artículo muestran las mejoras en almacenamiento y extracción de datos.

Es importante y necesario distribuir en grupos de archivos en varios discos la ubicación de la base de datos para hacer más eficiente el almacenamiento de datos, las tablas con grandes cantidades de datos es necesario particionarla en los diferentes grupos de archivos de la base de datos para que la búsqueda y extracción de datos tenga menos costo.

Por el lado del trabajo en grupo, los estándares de base de datos van a permitir un trabajo mejor organizado, los miembros del equipo de los proyectos asignarán los nombres a los objetos de base de datos de manera ordenada.

En el diseño de las tablas, no es más eficiente la relación de las tablas con los datos de tipo entero, se demuestra que las relaciones con los datos de tipo caracter son igual de eficientes, pero no la búsqueda de registros por claves alternas.

Es conveniente en tablas con grandes cantidades de datos el uso de los tipos de indices columnares, se demuestran con un 23% más eficientes que los índices por filas.

La normalización en el diseño de base de datos ha demostrado se ineficiente, la desnormalización o repetición intencionada de columnas en el diseño se ha demostrado que puede ser más de 50% más eficiente en la extracción de los datos.

References

Referencias

Coronel, C., Morris, S., & Rob, P. (2011). Base de datos: Diseño, implementación y administración. México.

Fritchey, G. (2012). SQL Server Execution Plans. Springfield,: SQL Handbooks. HardZone. (2024). HardZone. https://hardzone.es/tutoriales/montaje/raid-discos-

duros/

Lee, K., Anshuman, D., Vivek, N., & Chaudhuri, S. (2023). Analyzing the Impact of Cardinality Estimation on Execution Plans in Microsoft SQL Server. 49th International Conference on Very Large Data Bases. Vancouver.

Luvi Soft. (s.f.). Manual SQL Server. Manual SQL Server: https://manualsqlserver. com/?p=75

Microsoft. (04 de Setiembre de 2023). Microsoft Learn. https://learn.microsoft.com/ es-es/sql/relational-databases/indexes/indexes?view=sql-server-ver16

Microsoft. (23 de Mayo de 2023). Microsoft Learn. https://learn.microsoft.com/ es-es/sql/relational-databases/performance/display-the-estimated-execution- plan?view=sql-server-ver16

Microsoft. (2023). Microsoft Learn. https://learn.microsoft.com/es-es/sql/relational- databases/indexes/columnstore-indexes-overview?view=sql-server-ver16

Microsoft. (14 de 11 de 2023). Microsoft Learn. https://learn.microsoft.com/es-es/sql/ relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server- ver16

Microsoft. (21 de Mayo de 2024). Microsoft Learn. https://learn.microsoft.com/es-es/

sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

Noble, E. (2020). Pro T-SQL 2019. Rosweel, GA: Apress.

Núñez Hervas, R. (2023). Gestión de Bases de Datos. Madrid: Rama.

OTCA, O. d. (16 de Septiembre de 2022). Estándares para la Base de Datos. https:// oraotca.org/base-de-conocimiento/documentation/data_base/estandares-para- la-base-de-datos/

Pacheco, M. (2018). Normalización de Bases de Datos. Unidades de Apoyo para el aprendizaje: https://repositorio-uapa.cuaieed.unam.mx/repositorio/moodle/ pluginfile.php/2760/mod_resource/content/1/UAPA-Normalizacion-Base-Datos/ index.html

Petkociv, D. (2020). SQL Server 2019 A beginners guide. McGraw-Hill Education. Puig, L. (10 de 10 de 2022). Alura Latam. Normalización en base de datos - Estructura:

https://www.aluracursos.com/blog/normalizacion-en-base-de-datos

Revista Ibérica de Sistemas e Tecnologias de Informação Iberian Journal of Information Systems and Technologies

Recebido/Submission: 20/08/2024 Aceitação/Acceptance: 25/10/2024