SQL Server Propiedades de Base de Datos – Archivos y Grupos de Archivos

Introducción

Continuando con las propiedades de las bases de datos a través de Microsoft SQL Server, en esta ocasión hablare de los archivos y grupos de archivos, que se presentaran en las paginas correspondientes. Antes de ver el contenido de estas, es conveniente llevar a cabo una recapitulación de estos conceptos. Si bien, en la ventana de propiedades se presentan en dos paginas, estas propiedades están relacionadas y por lo mismo es importante hablar de ello en una sola entrega.

Archivos

Cuando una base de datos es creada, se generan de forma automática dos archivos de sistema operativo: 

Archivo de datos – como su nombre indica contiene los datos y objetos (tablas, indices, vistas, procedimientos almacenados y otros)

Archivo de registro - contiene la información necesaria para recuperar todas las transacciones en la base de datos. 

Es preciso indicar que los archivos de datos se pueden agrupar en grupos de archivos con fines de asignación y administración.

Antes de hablar de los grupos de archivo, es importante indicar que dentro de los tipos de archivo que podemos encontrar están, además de los mencionados archivos de datos y archivos de registro de transacciones, los archivos dispersos (sparse) y Filestream. 

Es necesario indicar que los archivos de datos dentro de Microsoft SQL Server se pueden encontrar dos tipos:

Primario - Contiene información de inicio para la base de datos y apunta a los otros archivos de la base de datos. Solo se puede tener un archivo de datos principal. La extensión de nombre de archivo, dentro del sistema operativo, para estos archivos es .mdf, este es asignado por omisión.

Secundario – Estos son opcionales y definidos por el usuario. Son utilizados cuando los datos se pueden distribuir en varios discos colocando cada archivo en una unidad de disco diferente. La extensión de nombre de archivo, dentro del sistema operativo, para estos archivos .ndf.

El archivo de registro de transacciones contiene la información que se utiliza para recuperar la base de datos. Ya se ha indicado que debe haber al menos un archivo de registro para cada base de datos. La extensión de nombre de archivo, dentro del sistema operativo, para los registros de transacciones es .ldf.

Cuando se realiza una instantánea de base de datos se utiliza un archivo disperso que es un archivo esencialmente vacío que no contiene datos del usuario y aún no se le ha asignado espacio en disco para los datos del usuario. La forma de archivo para almacenar los datos de copia en escritura depende de si es utilizado por la instantánea creada por un usuario o si se utiliza internamente, como en el caso de la creada cuando se llevan a cabo las operaciones del comando DBCC CHECKDB.

FILESTREAM permite que las aplicaciones basadas en Microsoft SQL Server almacenen datos no estructurados, como documentos e imágenes, en el sistema de archivos.

De forma predeterminada, los registros de datos y transacciones se colocan en la misma unidad y ruta para manejar sistemas de un solo disco. Es posible que esta elección no sea óptima para entornos de producción. Le recomendamos que coloque los datos y los archivos de registro en discos separados.

Grupo de archivos

Cuando se menciona grupo de archivos se piensa en dos características:

• El grupo de archivos contiene el archivo de datos principal y los archivos secundarios que no se colocan en otros grupos de archivos.
• Se pueden crear grupos de archivos definidos por el usuario para agrupar archivos de datos con fines administrativos, de asignación de datos y de ubicación.

Cuando se crean objetos en la base de datos sin especificar a qué grupo de archivos pertenecen, se asignan al grupo de archivos predeterminado. En cualquier momento, se designa exactamente un grupo de archivos como grupo de archivos predeterminado, denominado PRIMARY. Los archivos del grupo de archivos predeterminado deben ser lo suficientemente grandes para contener cualquier objeto nuevo que no esté asignado a otros grupos de archivos.

Todos los archivos de datos se almacenan en los grupos de archivos que se enumeran en la siguiente tabla.

PRIMARY El grupo de archivos que contiene el archivo principal. Todas las tablas del sistema forman parte del grupo de archivos principal.

Datos optimizados para memoria Un grupo de archivos optimizado para memoria se basa en un grupo de archivos de flujo de archivos

FILESTREAM

Definido por el usuario Cualquier grupo de archivos que crea el usuario cuando el usuario crea por primera vez o posteriormente modifica la base de datos.

Página de archivos

Utilice esta página para crear una nueva base de datos o ver o modificar las propiedades de la base de datos seleccionada. Este tema se aplica a las propiedades de la base de datos (página de archivos) para las bases de datos existentes y a la nueva base de datos (página general).

La parte superior de esta pagina muestra la siguiente información:

Archivos de base de datos

Nombre de la base de datos - El nombre de la base de datos.

Propietario - El propietario de la base de datos. En caso de que se desee cambiar de propietario esto puede realizarse, escribiendo directamente el usuario u oprimiendo el botón de tres puntos, para que pueda ser seleccionado de la lista. Cuando se crean las bases de datos, el propietario es el que las creó de forma predeterminada. Esta propiedad otorga al creador permisos adicionales, y esto puede ser un problema en un entorno seguro bloqueado donde debemos respetar el principio de privilegio mínimo.

Utilice la indexación de texto completo - Esta casilla de verificación está marcada y deshabilitada porque la indexación de texto completo siempre está habilitada en Microsoft SQL Server 2019 (15.x).

La parte inferior de la pagina, muestra información de los archivos de base de datos, donde se puede ver, agregar, modificar o eliminar archivos para la base de datos, y una cuadricula que contiene las siguientes columnas o propiedades de los archivos:

Nombre lógico - Muestra el nombre interno del archivo, este puede ser el mismo en diferentes bases de datos, pero debe ser único entre los nombres de archivo lógico de la base de datos. Puede modificarse el nombre lógico en cualquier momento.

Tipo de archivo - Indica el tipo de archivo, puede ser Data, Log o Filestream Data. No puede modificarse en un archivo existente. Si se agrega un nuevo archivo, seleccione el tipo de archivo de la lista. Es importante seleccionar Filestream Data si está agregando archivos (contenedores) a un grupo de archivos optimizado para memoria. 

Para agregar archivos (contenedores) a un grupo de archivos de datos de Filestream, el uso de FILESTREAM debe estar habilitado. Puede habilitar el uso de FILESTREAM mediante el cuadro de diálogo Propiedades del servidor (página avanzada).

Grupo de archivos - Indica el grupo de archivos al que pertenece el archivo. Cuando se crea o adiciona un nuevo archivo, se debe seleccionar el grupo de archivos para el archivo de la lista. De forma predeterminada, el grupo de archivos es el denominado PRIMARY. 

Puede crearse un nuevo grupo de archivos seleccionando <nuevo grupo de archivos> e ingresando información sobre el grupo de archivos en el cuadro de diálogo Nuevo grupo de archivos. 

También se puede crear un nuevo grupo de archivos en la página Grupo de archivos. 

No puede modificar el grupo de archivos de un archivo existente. Al agregar archivos (contenedores) a un grupo de archivos optimizado para memoria, el campo Grupo de archivos se completará con el nombre del grupo de archivos optimizado para memoria de la base de datos.

Tamaño inicial - Se muestra el tamaño inicial del archivo en megabytes. Cuando se crea o adiciona un archivo, se mostrará, de forma predeterminada, el valor que se indica en la base de datos model. 

Éste campo no es válido para archivos FILESTREAM. Para archivos en grupos de archivos optimizados para memoria, este campo no se puede modificar.

Autocrecimiento - Indica la forma en que se llevará a cabo el crecimiento automático del archivo. Se debe recordar que controla cómo se expande el archivo cuando se alcanza su tamaño máximo de archivo. 

Pueden modificarse estos valores, para editar los valores de crecimiento automático, haga clic en el botón editar junto a las propiedades de crecimiento automático del archivo que desee y cambie los valores en el cuadro de diálogo Cambiar crecimiento automático. De forma predeterminada, estos son los valores de la base de datos model. 

Este campo no es válido para archivos FILESTREAM. Para archivos en grupos de archivos optimizados para memoria, este campo debe ser Ilimitado.

Ruta - Muestra la ruta del archivo seleccionado. Cuando se crea o adiciona un nuevo archivo, para especificar una ruta para un nuevo archivo, haga clic en el botón editar junto a la ruta del archivo y navegue hasta la carpeta de destino. No puede modificarse la ruta de un archivo existente, utilizando esta ventana. 

Para los archivos FILESTREAM, la ruta es una carpeta. El motor de base de datos de SQL Server creará los archivos subyacentes en esta carpeta.

Nombre del archivo - Muestra el nombre del archivo físico.

Este campo no es válido para archivos FILESTREAM, incluidos los archivos en grupos de archivos optimizados para memoria.

Finalmente se aprecian dos botones, para ser usados con la cuadricula anterior:

• Añadir - Agregue un nuevo archivo a la base de datos.
• Eliminar - Elimina el archivo seleccionado en la cuadricula de la base de datos. Es necesario indicar que no se puede eliminar un archivo a menos que esté vacío. El archivo de datos principal y el archivo de registro no se pueden eliminar de la base de datos.

Página Grupos de Archivos

Esta página es utilizada para ver los grupos de archivos que tiene una base de datos o para agregar un nuevo grupo de archivos a la base de datos seleccionada. Actualmente los tipos de grupos de archivos se separan en los siguientes: 

• Grupos de archivos de datos - contienen datos regulares y archivos de registro

• Datos de FILESTREAM - contienen archivos de datos de FILESTREAM

• Grupos de archivos con optimización de memoria.

Los archivos de datos de FILESTREAM almacenan información sobre cómo se almacenan los datos de objetos grandes binarios (BLOB) en el sistema de archivos cuando se usa el almacenamiento FILESTREAM. Las opciones de estos grupos de archivo son las mismas que para los tipos de grupos de archivos de datos.

Recuérdese que, si FILESTREAM no está habilitado, la sección correspondiente a Filestream no estará disponible, por lo que será posible habilitar el almacenamiento de FILESTREAM utilizando Propiedades del servidor (página avanzada).

Es importante indicar que se requieren grupos de archivos optimizados para memoria para que una base de datos contenga una o más tablas optimizadas para memoria. Las tablas de datos optimizadas para memoria surgen en la versión de Microsoft SQL Server 2014, anteriormente no se usaba esta opción. 

Opciones de grupo de archivos de datos y FILESTREAM

Nombre - Muestra el nombre del grupo de archivos.

Archivos - Muestra el recuento de archivos en el grupo de archivos.

Solo lectura - Indica que el grupo de archivos esta en un estado de solo lectura.

Defecto - Esta opción indica que este grupo de archivos se indicara como predeterminado. Puede tener un grupo de archivos predeterminado para las filas y un grupo de archivos predeterminado para los datos de FILESTREAM.

Opciones de grupos de archivos de datos optimizados para memoria

Nombre - Indica el nombre del grupo de archivos optimizado para memoria.

Archivos de FILESTREAM - Muestra el número de archivos (contenedores) en el grupo de archivos de datos optimizados para memoria. Puede agregar contenedores en la página Archivos.

Se muestran dos botones al final de cada cuadricula con la siguiente funcionalidad

• Añadir - Agrega una nueva fila en blanco a la cuadrícula correspondiente que enumera los grupos de archivos para la base de datos.
• Eliminar - Elimina la fila del grupo de archivos seleccionado de la cuadrícula correspondiente.

Conclusión

Como se ha visto, una base de datos, manejada por Microsoft SQL Server puede usar diversos archivos, agrupados en diversos tipos de archivo, lo que es importante recordar, es que solo puede contener un archivo primario, con la extensión .mdf y al menos un archivo de transacciones, con la extensión .ldf. El manejo de grandes objetos permitió la llegada de los archivos de FILESTREAM, y el manejo de las tablas optimizadas en memoria se hizo posible con la arquitectura de 64 bits, por lo que también estos grupos de archivos permiten el uso de este tipo de grupo de archivos de datos.

Si se requiere usar T-SQL para obtener la información que se muestra en las paginas indicadas, es posible usar las siguientes consultas.

Para la pagina de archivos y grupos de archivos se puede obtener:

/*******************************************************************************
-- Script : Get Database Files Properties 
-- Author : Julio J Bueyes
-- julio.bueyes@outlook.com
--
-- Description : This script helps to get a detailed view of the database properties – Files page.
--
-- DISCLAIMER. This Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 
--
-- THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, 
-- INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
**********************************************************************************/

-- Set the Database Name required

USE model;
GO

-- Files Page

SELECT db.name as [Database Name], sp.name as [Owner]
FROM sys.databases db 
    INNER JOIN sys.server_principals sp ON db.owner_sid = sp.sid

-- Get the database files information

SELECT df.name AS [Logical Name], df.type_desc AS [File Type], ds.name as [Filegroup], (size/1024) as [Size (MB)], 
    CASE df.is_percent_growth 
        WHEN 1 THEN 'By ' + CAST(df.growth/1024 as varchar) + ' %, ' + CAST(df.max_size/1024 as varchar) + ' MB'
        ELSE 'By ' + CAST(df.growth/1024 as varchar) + ' MB, ' + CAST(df.max_size/1024 AS varchar) + ' MB' END AS [Autogrowth / Maxsize],
    df.physical_name AS [Path and Name] 
FROM sys.database_files as df 
    LEFT OUTER JOIN sys.data_spaces as ds ON df.data_space_id = ds.data_space_id;

-- Filegroups Page

-- Get the database filegroups data information

SELECT ds.name, COUNT(df.FILE_ID) as [Files], fg.is_read_only as [Read-Only], ds.is_default as [Default], is_autogrow_all_files as [Autogrow All Files]
FROM sys.data_spaces ds 
    INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
    INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE ds.type = 'FG' ----<--- Indicate ROWS_FILEGROUP 
GROUP BY ds.name, fg.is_read_only, ds.is_default, is_autogrow_all_files;

-- Get the database filegroups FILESTREAM information
SELECT ds.name, COUNT(df.FILE_ID) as [FILESTREAM Files], fg.is_read_only as [Read-Only], ds.is_default as [Default]
FROM sys.data_spaces ds 
    INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
    INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE ds.type = 'FD' ----<--- Indicate FILESTREAM 
GROUP BY ds.name, fg.is_read_only, ds.is_default;

-- Get the database filegroups for Memory Optimized Files information
SELECT ds.name, COUNT(df.FILE_ID) as [FILESTREAM Files]
FROM sys.data_spaces ds 
    INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
WHERE ds.type = 'FX' ----<--- Indicate MEMORY_OPTIMIZED_DATA_FILEGROUP 
GROUP BY ds.name;

Como puede observarse es muy fácil obtener los datos directamente de la ventana de propiedades de la base de datos, en las páginas Archivos y Grupos de Archivos, no obstante, el script funciona para obtener los mismos valores proporcionados por las páginas.

SQL Server Consultas Dinámicas

Consultas Dinámicas en Microsoft SQL Server

Introducción

Hoy hablare de las consultas dinámicas, su funcionamiento y las prácticas que deben tenerse para lograr un buen desempeño. Antes de iniciar, veremos la definición de una consulta dinámica desde el punto de vista de Microsoft. Una consulta dinámica es código SQL que es generado programáticamente (en parte o totalmente) por un programa antes de que sea ejecutado. Esto es, durante la ejecución de un procedimiento almacenado, se lleva a cabo la creación de un comando de consulta, de tal forma que, antes de que termine la ejecución del procedimiento se ejecuta la consulta generada con las características requeridas.

Desarrollo

Por ejemplo, tenemos la siguiente consulta:

-- Declarar parametros

DECLARE @IdCategory         nvarchar(7)   = NULL

          ,@IdUnitMeasure   smallint      = 1

          ,@IdCurrency      smallint      = NULL

-- Establece consulta

SELECT Category

         ,Classification

         ,BrandName

         ,IdProduct

         ,[Description]

         ,PartNumber

         ,ISNULL(InventoryActual, 0) InventoryActual

         ,IsSerializable

  FROM Catalog.VProduct

 WHERE (IdCategory = @IdCategory OR @IdCategory IS NULL)

   AND (IdCurrency = @IdCurrency OR @IdCurrency IS NULL)

   AND (IdUnitMeasure = @IdUnitMeasure OR @IdUnitMeasure IS NULL)

 ORDER BY DisplayOrder, Classification, BrandName, IDProduct

Es posible notar que se ha generado una consulta que tiene 3 condiciones de filtro que pueden ser indicados con nulos, de tal forma que en este caso las condiciones de IdCategory y IdCurrency se han indicado con nulo y solo IdUnitMeasure se ha especificado con valor, esta consulta puede ser generada dinámicamente para que solo sea ejecutada la condición deseada, de la siguiente forma:

-- Declarar parametros

DECLARE @IdCategory         nvarchar(7)   = NULL

          ,@IdUnitMeasure   smallint      = 1

          ,@IdCurrency      smallint      = NULL

-- Declarar variables

DECLARE @SqlText nvarchar(1000)

DECLARE @cond int = 0

-- Genera Consulta Dinamica

SET @SqlText = N'SELECT Category

         ,Classification

         ,BrandName

         ,IdProduct

         ,[Description]

         ,PartNumber

         ,ISNULL(InventoryActual, 0) InventoryActual

         ,IsSerializable

  FROM [Catalog].VProduct

 WHERE ';

 IF @IdCategory IS NOT NULL

 BEGIN

       SET @SqlText += N' IdCategory = ''' + @IdCategory +'';

       SET @cond +=1;

END

IF @IdCurrency IS NOT NULL

BEGIN

       IF @cond > 0 SET @SqlText += N' AND ';

    SET @SqlText += N'IdCurrency = ' + CAST(@IdCurrency as nvarchar);

    SET @cond += 1;

END

IF @IdUnitMeasure IS NOT NULL

BEGIN

       IF @cond > 0 SET @SqlText += N' AND ';

       SET @SqlText += N'IdUnitMeasure = ' + CAST(@IdUnitMeasure as nvarchar);

END

SET @SqlText += N' ORDER BY DisplayOrder, Classification, BrandName, IDProduct';

Este código genera el comando siguiente para la consulta:

SELECT Category

         ,Classification

         ,BrandName

         ,IdProduct

         ,[Description]

         ,PartNumber

         ,ISNULL(InventoryActual, 0) InventoryActual

         ,IsSerializable

  FROM [Catalog].VProduct

 WHERE IdUnitMeasure = 1 ORDER BY DisplayOrder, Classification, BrandName, IDProduct

La ejecución de esta consulta dinámica se lleva a cabo con el comando EXEC, como se indica a continuación:

EXEC (@SqlText)

Hasta aquí, la ejecución de la consulta dinámica se hace sencilla, sin embargo, esta ejecución tiene un problema, no se guarda el plan de ejecución, ya que se ha establecido con el uso del comando EXEC que se ejecute como única vez, además de que la consulta no tiene parámetros, se han sustituido los valores de los parámetros en la consulta. Ahora si queremos que la consulta se ejecute y se mantenga un plan de ejecución será necesario hace unas pequeñas modificaciones al código anterior, para quedar:

-- Declarar parametros

DECLARE @IdCategory         nvarchar(7)   = NULL

          ,@IdUnitMeasure   smallint      = 1

          ,@IdCurrency      smallint      = NULL

-- Declarar variables

DECLARE @SqlText nvarchar(1000)

DECLARE @cond int = 0

-- Genera Consulta Dinamica

SET @SqlText = N'SELECT Category

         ,Classification

         ,BrandName

         ,IdProduct

         ,[Description]

         ,PartNumber

         ,ISNULL(InventoryActual, 0) InventoryActual

         ,IsSerializable

  FROM [Catalog].VProduct

 WHERE ';

 IF @IdCategory IS NOT NULL

 BEGIN

       SET @SqlText += N' IdCategory = @IdCat ';

       SET @cond +=1;

END

IF @IdCurrency IS NOT NULL

BEGIN

    IF @cond > 0 SET @SqlText += N' AND ';

    SET @SqlText += N'IdCurrency = @IdCur ';

    SET @cond += 1;

END

IF @IdUnitMeasure IS NOT NULL

BEGIN

       IF @cond > 0 SET @SqlText += N' AND ';

       SET @SqlText += N'IdUnitMeasure = @IdUnit ';

END

SET @SqlText += N' ORDER BY DisplayOrder, Classification, BrandName, IDProduct';

-- Ejecuta consulta dinamica

EXEC sp_executesql @statement = @SqlText,

                                  @params = N'@IdCat nvarchar(7), @IdUnit smallint, @IdCur smallint',

                                  @IdCat = @IdCategory,

                                  @IdUnit = @IdUnitMeasure,

                                  @IdCur = @IdCurrency

Se observa que se ha incluido una llamada a un procedimiento almacenado del sistema que se denomina sp_executesql, este procedimiento ejecuta una instrucción de Transact-SQL o un lote de instrucciones que puede reutilizarse muchas veces, o uno que se ha generado dinámicamente, como es el caso anterior. La instrucción o lote de Transact-SQL puede contener parámetros incorporados, como se ha indicado en el ejemplo.

En la declaración del procedimiento almacenado se pueden identificar tres componentes principales de parámetros, el que se identifica como @statement que es el que se asocia con la consulta dinámica, @params que debe contener todos los parámetros que puedes ser utilizados en la consulta dinámica y por último cada uno de los parámetros indicados con su valor correspondiente. En el caso de nuestro ejemplo se tienen tres posibles parámetros y se declaran 3 posibles valores.

Es importante mencionar que, la ejecución de la consulta dinámica utilizando el procedimiento almacenado del sistema, permite mantener el plan de ejecución de la consulta dinámica en memoria, ya que al poder manejar parámetros, estos permiten la reutilización de la consulta, solo cambiará el valor del parámetro, de esta forma, para el ejemplo que tenemos, es posible que se mantengan 8 planes de ejecución, dependiendo de la presencia de los parámetros, con lo cual se hace mas eficiente el uso de la consulta dinámica.

Existe la posibilidad de que algún desarrollo requiera el uso de múltiples consultas dinámicas, lo que generara una gran cantidad de espacio para mantener los planes de ejecución de las mismas, es por ello que en estos casos conviene validar el valor de la opción de configuración denominada “optimize for ad hoc workloads”, está opción se usa para mejorar la eficacia de la memoria caché del plan para cargas de trabajo que contienen muchos lotes ad hoc de un solo uso. Cuando esta opción se establece en 1, el Motor de base de datos almacena un pequeño apéndice del plan compilado en la memoria caché de planes de ejecución cuando se compila un lote por primera vez, en lugar del plan completo compilado. Esto ayuda a aliviar la presión de la memoria al no permitir que la memoria caché del plan de ejecución se llene con planes compilados que no se reutilizan.

Conclusión

Las consultas dinámicas son muy útiles cuando se desean manejar consultas que requiere la presencia de varios parámetros, que pueden contener valores o bien la ausencia de ellos, en cualquier caso, la consulta debe obtener información. En nuestro caso se trata de una consulta que se utiliza para filtrar la presentación de la información.