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 DBCC CLONEDATABASE

Comando DBCC CLONEDATABASE

Cuando inicie con los Comandos DBCC, mencione que dentro de la categoría de varios se encontraba este comando que crea una nueva base de datos que contiene el esquema de todos los objetos y estadísticas de la base de datos de origen especificada. Este comando es realmente una buena incorporación a los comandos de consola de base de datos que ha realizado Microsoft SQL Server, este comando inicia su funcionamiento en la versión Microsoft SQL Server 2017, aunque está presente en las versiones de Microsoft SQL Server 2012 en el paquete de servicio 4, Microsoft SQL Server 2014 en el paquete de servicio 2 y en Microsoft SQL Server 2016 en el paquete de servicio 1.

Es realmente importante notar que esta funcionalidad no está pensada para que la base de datos clonada se utilice en ambientes productivos ya que su principal objetivo es la resolución de problemas y diagnóstico. De tal forma que se recomienda que una vez que se ha generado la base de datos clonada, ésta sea separada del servidor y restaurada en algún otro servidor.

La sintaxis de este comando en su forma más simple es:

USE master;
GO

DBCC CLONEDATABASE (source_database_name, target_database_name);

Microsoft SQL Server llevara a cabo las acciones requeridas para hacer un clone de la base de datos identificada como source_database_name, generando la base de datos denominada target_database_name. Es importante notar que toda la información de la tarea solicitada se registrara en el registro de Microsoft SQL Server. Las acciones que se llevan a cabo incluyen la generación de la base de datos del tamaño especificado en la base de datos model, se establece el método de recuperación den SIMPLE, así como el método de verificación de página en CHECKSUM, la opción TRUSTWORTHY a OFF y DB_CHAINING a OFF para la base de datos identificada como target_database_name. Una vez que la operación se ha llevado a cabo, la base de datos identificada target_database_name quedara en el estado de solo lectura y se presentara la siguiente notificación en los resultados.

Database cloning for 'source_database_name' has started with target as 'target_database_name'.

Database cloning for 'source_database_name' has finished. Cloned database is 'target_database_name'.
Database 'source_database_name' is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Se puede observar que el último mensaje de la ejecución del comando indica que la base de datos clonada solo debe ser  utilizada con propósitos de diagnóstico y no será soportada en un ambiente de producción.

En términos generales podemos decir que esta operación lleva a cabo las siguientes operaciones:

•        Crea una nueva base de datos de destino con el nombre identificado en target_database_name, que utiliza el mismo diseño de archivo que la base de datos origen, pero con tamaños de archivo predeterminados como la base de datos model.
•        Crea una instantánea interna de la base de datos origen, identificada como source_database_name, para poder extraer los metadatos correspondientes.
•        Copia los metadatos del sistema de la base de datos origen, source_database_name, a la base de datos de destino, identificada como tarjet_database_name.
•        Copia todos los esquemas para todos los objetos desde la base de datos origen hasta la base de datos destino.
•        Copia las estadísticas para todos los índices desde el origen a la base de datos de destino. Aquí se ve la importancia de que esta base de datos deba ser utilizada con propósitos de diagnóstico.

Existen algunos argumentos opcionales que pueden ser utilizados con el comando, estos son:

NO_STATISTICS	Este argumento especifica si las estadísticas de tabla / índice deben excluirse en el clon. Si no se especifica esta opción, las estadísticas de tabla / índice se incluyen automáticamente. Esta opción está disponible comenzando con Microsoft SQL Server 2014 SP2 CU3 y Microsoft SQL Server 2016 Service Pack 1.
NO_QUERYSTORE	Este argumento especifica si el almacén de consultas necesita ser excluido en el clon. Si no se especifica esta opción, los datos del almacén de consultas se copian en el clon si están habilitados en la base de datos de origen. Esta opción está disponible comenzando con Microsoft SQL Server 2016 Service Pack 1.

Como se ha mencionado, este comando debe ser utilizado para crear una copia del esquema y las estadísticas de una base de datos de producción, para investigar los problemas de rendimiento de las consultas, por lo que es importante tomar en cuenta las restricciones y los objetos que son soportados en el uso de este comando, Microsoft ha indicado una serie de objetos que están permitidos para la clonación, mismos que están indicados en la documentación del comando. Las restricciones implican que el uso del comando genere mensajes de error y estas son:

• La base de datos de origen siempre debe ser especificada para una base de datos de usuario. La clonación de bases de datos del sistema (master, model, msdb, tempdb, distribution database, etc.) no está permitida.
• La base de datos de origen que se establece en el comando debe estar en línea o legible.
• El nombre de la base de datos que se establece como de destino no debe existir previamente al uso del comando.
• El comando no debe ser usado en una transacción de usuario.

Ejemplos de DBCC CLONEDATABASE

Mostraré algunos ejemplos de uso de este comando:

USE master;
GO

DBCC CLONEDATABASE (db1, db2);

Genera un clone de la base de datos indicada como db1 generando la base de datos indicada como db2, incluye el esquema, las estadísticas y en el caso de Microsoft SQL Server 2016 y posterior, también incluye el almacenamiento de consultas y deja la base de datos db2 en el estado de solo lectura.

USE master;

GO

DBCC CLONEDATABASE (AdventureWorks, AdventureWorks_Clone) WITH NO_STATISTICS;

Crea un clone de la base de datos AdventureWorks, solo el esquema, sin incluir las estadísticas, para el caso de Microsoft SQL Server 2014 y posterior,  pero incluye almacenamiento de consultas para Microsoft SQL Server 2016 y posterior.

USE master;

GO

DBCC CLONEDATABASE (db1, db1_Clone) WITH NO_STATISTICS, NO_QUERYSTORE;

Crea un clone de la base de datos db1, solo el esquema, sin incluir las estadísticas, ni el almacenamiento de consultas para Microsoft SQL Server 2016 y posterior. Este ejemplo no funcionará en versiones anteriores de Microsoft SQL Server 2016.

Comentarios

Este comando debe ser utilizado cuando se tiene un bajo desempeño de las consultas en una base de datos y se debe llevar a cabo un análisis para la resolución de los problemas y el diagnostico correspondiente, ya que como se ha visto se copia todo el esquema de la base de datos de destino y se permite mantener las estadísticas generadas con las consultas y los índices, así como el almacenamiento de las consultas, en las versiones de Microsoft SQL Server 2016 y posteriores.

Es necesario indicar que si bien existen argumentos que nos permiten generar únicamente la estructura, el uso de la base de datos clonada solo podrá servir para efectos de mantener una copia limpia de la base de datos original, la cual puede ser usada en otro servidor para desarrollo o pruebas.

SQL Server DBCC FREESESSIONCACHE y FREESYSTEMCACHE

Comandos DBCC FREESESSIONCACHE y FREESYSTEMCACHE

Ya se ha indicado, cuando comencé con los Comandos DBCC, sobre los comandos misceláneos, que existen estos dos comandos que nos ayudan a la limpieza del espacio denominado cache, uno para el relacionado con las conexiones generadas por las consultas distribuidas y otro para liberar toda el espacio de cache que no se esté utilizando en el sistema.

DBCC FREESESSIONCACHE

Este comando lleva a cabo el vaciado de la caché de conexión de consulta distribuida que se utiliza por las consultas distribuidas en una instancia de Microsoft SQL Server. Pero, ¿qué significa esto? Es importante indicar que muchas veces se llevan a cabo consultas que implican la existencia de datos en diferentes servidores y esto genera un espacio de cache para mantener la conexión de datos, en este sentido, este comando solo es útil cuando se llevan a cabo consultas distribuidas, ya que se liberará el espacio ocupado por la realización de este tipo de consultas.

La sintaxis que se usa para este comando es:

DBCC FREESESSIONCACHE;

Este comando libera el cache de consultas distribuidas utilizado en la base de datos actual. 

Existe un argumento opcional que puede ser utilizado con el comando, éste es:

WITH NO_INFOMSGS

Suprime todos los mensajes informativos.

DBCC FREESYSTEMCACHE

Ya se ha indicado que éste comando libera todas las entradas de caché no utilizadas de todas las memorias caché. Esto es, el Motor de base de datos de Microsoft SQL Server limpia de forma automática y en segundo plano todas las entradas de caché no utilizadas, lo que permite que haya memoria disponible para las entradas actuales. No obstante, es posible utilizar este comando para liberar de forma manual las entradas no usadas de todas las memorias caché o de una memoria caché especifica de un grupo del regulador de recursos.

La sintaxis que se usa más a menudo para este comando es:

DBCC FREESYSTEMCACHE ('ALL');

En este caso se indica que se libere el espacio de cache utilizado por todas las memorias compatibles. 

Existen algunos argumentos opcionales que puede ser utilizado con el comando, los cuales son:

Pool_name	Especifica una cache de grupo del regulador de recursos, de tal forma que solo se liberaran las entradas asociadas a este grupo, se utiliza en conjunto con ‘ALL’.
MARK_IN_USE_FOR_REMOVAL	Libera asincrónicamente las entradas utilizadas actualmente de sus respectivas cachés después de que dejan de utilizarse. No se verán afectadas las nuevas entradas creadas en la memoria caché después de ejecutar el comando.
WITH NO_INFOMSGS	Suprime todos los mensajes informativos.

Ejemplos de DBCC FREESYSTEMCACHE

Mostraré algunos ejemplos de uso de este comando:

DBCC FREESYSTEMCACHE ('ALL', default); 

Se limpiará la memoria caché que está dedicada a un grupo de recursos de servidor del regulador de recursos denominado default.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL; 

En este ejemplo, se utiliza la cláusula MARK_IN_USE_FOR_REMOVAL para liberar las entradas de todas las memorias caché actuales una vez que las entradas dejen de ser utilizadas.

Comentarios

Si bien, el uso del comando DBCC FREESESSIONCACHE puede ser uno de los menos utilizados, conviene tenerlo presente cuando se trabaja con consultas distribuidas. No hay nada más que señalar con respecto a este comando.

Ahora bien, el uso del comando DBCC FREESYSTEMCACHE al borrar la memoria caché de planes, se provoca que se lleve a cabo la nueva compilación de todos los planes de ejecución posteriores y puede ocasionar una disminución repentina y temporal del rendimiento de las consultas. Para cada uno de los almacenes de caché que se ha borrado de la caché del plan, el registro de Microsoft SQL Server contendrá el siguiente mensaje informativo: " SQL Server ha detectado %d instancias de vaciado del almacén de caché '%s' (parte de la caché del plan) debido a operaciones 'DBCC FREEPROCCACHE' o 'DBCC FREESYSTEMCACHE'".

Se recomienda el uso de estos comandos con discreción, ya que el uso continuo causara la degradación del rendimiento de las consultas, dado que cada vez que se libera el espacio de cache, se llevara a cabo la re-compilación de las consultas.

SQL Server DBCC TRACEON / TRACEOFF

Comandos DBCC TRACEON / TRACEOFF y las marcas de seguimiento

Ya he indicado, cuando se inició con los Comandos DBCC, sobre los comandos varios o misceláneos, en esta ocasión hablare de dos comandos, el primero que habilita las marcas de seguimiento especificadas. Y el segundo que deshabilita las marcas de seguimiento especificados con el primer comando. Sin embargo es necesario mencionar que son las marcas de seguimiento y como pueden ayudarnos.

Se puede indicar que las marcas de seguimiento son utilizadas para establecer algunas características en el servidor de forma temporal, las que se habilitan o deshabilitan con los comandos que hemos indicado. Es importante mencionar que algunas marcas de seguimiento fueron introducidas para versiones específicas de Microsoft SQL Server, por lo que se hace necesario consultar el artículo asociado en Microsoft Support.

Actualmente se cuenta con 96 marcas de seguimiento, las cuales se clasifican en tres tipos de ámbito, global, sesión y consulta. Las 96 se utilizan en el ámbito global, esto es, se establecen en el nivel de servidor y se mantienen visibles para todas las conexiones en el servidor.  En el ámbito de sesión, se encuentran 45 de las 96 definidas, esto significa que las marcas de seguimiento se activarán únicamente para ser usadas en la conexión que las establece no están visibles para las demás conexiones, como es el caso de las globales. Cuando se habilita una marca de seguimiento en el ámbito de sesión, si se cierra la conexión, se desactiva la marca de seguimiento, dado que solo se habilito en esa conexión. Finalmente, las marcas de seguimiento de ámbito de consulta, son aquellas que solo se habilitan en el contexto de ejecución de la consulta, para ello se utiliza la opción QUERYTRACEON de la instrucción SELECT.

No indicaré cada una de las marcas de seguimiento que actualmente se manejan el Microsoft SQL Server, toda la información relacionada con las marcas de seguimiento se encuentra en los libros en linea de Microsoft SQL Server.

Es importante mencionar que deben tenerse en cuenta las siguientes reglas para su aplicación:

•        Una marca de seguimiento de ámbito global debe habilitarse a nivel global, ya que en caso contrario no surtirá efecto. Es recomendable que se utilice la opción –T en la línea de comando de inicio de servicios de Microsoft SQL Server, lo cual garantizará que la marca permanezca activa después del reinició del servidor.
•        Una marca de seguimiento que puede ser usada en cualquiera de los ámbitos mencionados, puede ser habilitada en el ámbito adecuado, teniendo en cuenta que una marca de seguimiento habilitada en el nivel de sesión nunca afectara otra sesión y el efecto se perderá cuando el SPID que inicio la marca de seguimiento sea cerrado.

Como puede deducirse, la habilitación de las marcas de seguimiento se habilitan o deshabilitan con los comandos DBCC TRACEON y DBCC TRACEOFF, que se analizan más adelante. Como se ha mencionado anteriormente, también es posible utilizar la opción –T en la línea de comando de inicio de servicios, recordando que esta opción habilita a nivel global, no será posible habilitar una marca de seguimiento a nivel sesión con la opción –T en el inició de los servicios. Para el nivel de consulta debe utilizarse la opción QUERYTRACEON en la instrucción SELECT. Es posible validar que marcas de seguimiento se encuentran activas utilizando el comando DBCC TRACESTATUS.

No es mi intención analizar la instrucción SELECT y sus opciones, solo mencionare un ejemplo del uso de la opción QUERYTRACEON, para que se observe como se lleva a cabo. Solo por mencionar un par de marcas de seguimiento que se encuentran en el grupo de las que pueden utilizarse a nivel de consulta, sugiero que se revise bien la documentación correspondiente a las marcas de seguimiento.

Marca de Seguimiento	Descripción
4137	Permite que Microsoft SQL Server genere un plan usando mínima selectividad cuando estima predicados AND para filtrar teniendo en cuenta la correlación, en el modelo de estimación de cardinalidad del optimizador de consultas de Microsoft SQL Server 2012 y versiones anteriores.
4199	Habilita al optimizador de consultas (QO) los cambios publicados en Microsoft SQL Server actualizaciones acumulativas y Service Packs.

Teniendo esto en cuenta, podemos llevar a cabo la consulta utilizando estas marcas de seguimiento de la siguiente manera:

SELECT x

  FROM correlated

 WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137)

En esta instrucción se lleva cabo la consulta a la tabla identificada como correlated con la opción el uso de las marcas de seguimiento identificadas como 4199 y 4137. Por lo que esta consulta puede habilitar todas las revisiones que afectan al plan controladas por marcas de seguimiento indicadas para la consulta.

DBCC TRACEON

Como se ha indicado este comando habilita las marcas de seguimiento que se especifican. Tratándose de un servidor de producción, es recomendable habilita las marcas se seguimiento en todo el servidor para evitar un comportamiento impredecible, las cuales pueden habilitarse, utilizando la opción –T en la línea de comandos de Microsoft SQL Server, o utilizando el comando DBCC TRACEON.

Si bien las marcas de seguimiento son utilizadas para personalizar algunas características que controlen el funcionamiento de Microsoft SQL Server, las marcas de seguimiento habilitadas permanecerán hasta que sean deshabilitadas.

La sintaxis que puede utilizarse para habilitar una marca a nivel global es:

DBCC TRACEON (trace_num, -1);

En este caso se observa que se habilitara la marca de seguimiento trace_num a nivel global, indicado con el argumento -1,
La sintaxis que puede utilizarse para habilitar una marca a nivel de sesión es:

DBCC TRACEON (trace_num);

Ahora se observa que se habilitara la marca de seguimiento trace_num a nivel de sesión, la ausencia del argumento -1, indica que es a nivel sesión. 

Existen un argumento opcional que pueden ser utilizado con el comando, éste es:

WITH NO_INFOMSGS

Suprime todos los mensajes informativos.

Es preciso indicar que pueden indicarse más de un número asociado con la marca de seguimiento en el comando de tal forma que solo necesita separarse por comas.

Ejemplos de DBCC TRACEON

Mostraré algunos ejemplos de uso de este comando:

DBCC TRACEON (3205);

En este ejemplo, se habilitara la marca de seguimiento identificada como 3205, que permite deshabilitar la compresión de hardware para los controladores de cinta, esta acción se lleva  cabo a nivel de sesión.

DBCC TRACEON (3205, -1);

En el ejemplo, se habilita la marca de seguimiento 3205, ahora a nivel global.

DBCC TRACEON (3205, 260, -1) WITH NO_INFOMSGS;

En este ejemplo, se habilitan las marcas 3205, para deshabilitar la compresión de hardware para controladores de cinta y la marca 260, que imprime información de versión sobre las bibliotecas de vínculos dinámicos (DLL) de procedimientos almacenados extendidos, de forma global.

DBCC TRACEOFF

Como se mencionó anteriormente, este comando deshabilita  las marcas de seguimiento que se indiquen, previamente habilitadas.

La sintaxis que puede utilizarse para deshabilitar una marca a nivel global es:

DBCC TRACEOFF (trace_num, -1);

En este caso se observa que se deshabilitará la marca de seguimiento trace_num a nivel global, indicado con el argumento -1,

La sintaxis que puede utilizarse para deshabilitar una marca a nivel de sesión es:

DBCC TRACEOFF (trace_num);

Aquí se observa que se deshabilitará la marca de seguimiento trace_num a nivel de sesión, la ausencia del argumento -1, indica que es a nivel sesión. La sintaxis e información completa de este comando se encuentran en los libros en línea de Microsoft SQL Server.

Existen un argumento opcional que pueden ser utilizado con el comando, éste es:

WITH NO_INFOMSGS

Suprime todos los mensajes informativos.

Como en el caso del comando DBCC TRACEON, pueden indicarse mas de un número asociado con la marca de seguimiento en el comando de tal forma que solo necesita separarse por comas.

Ejemplos de DBCC TRACEOFF

Mostraré algunos ejemplos de uso de este comando:

DBCC TRACEOFF (3205);

Se deshabilitará la marca de seguimiento identificada con el número 3205.

DBCC TRACEOFF (3205, -1) WITH NO_INFOMSGS;

En el ejemplo, se deshabilita la marca de seguimiento identificada con el número 3205, habilitada previamente a nivel global, sin emitir mensajes informativos.

Comentarios.

Las marcas de seguimiento son una de las herramientas que se tienen para el diagnóstico de problemas de rendimiento, la depuración de procedimientos almacenados o la determinación de problemas de sistemas complejos. Hay que revisar cada una de las características específicas de operación de las marcas de seguimiento para poder determinar cuál de ellas es la que puede ser utilizada con una versión especifica de Microsoft SQL Server y el propósito de diagnóstico requerido en su utilización.

Recuérdese que el uso del comando DBCC TRACEON para establecer una marca de seguimiento a nivel global solo permanecerá activa, mientras no se utilice el comando DBCC TRACEOFF para deshabilitarla o el servicio de Microsoft SQL Server no sea reiniciado, ya que una vez que se ha reiniciado la marca de seguimiento no se habilitara, si se desea que la marca de seguimiento se mantenga en operación debe utilizarse la opción –T en la línea de comando de inicio de servicios de Microsoft SQL Server.

Microsoft ha indicado que es posible que en futuras versiones de Microsoft SQL Server no se admita el comportamiento de algunas de las marcas de seguimiento, por lo que hay que validar el uso de alguna de las marcas de seguimiento actuales en versiones posteriores.

SQL Server DBCC FLUSHAUTHCACHE

Comando DBCC FLUSHAUTHCACHE

Cuando se inició con los Comandos DBCC, se mencionó que este comando pertenece al grupo de los identificados como misceláneos y que su uso vacía la caché de autenticación de base de datos que contiene información sobre los inicios de sesión y las reglas de firewall para la base de datos de usuario actual en base de datos. Esto significa que la caché de autenticación realiza una copia de los inicios de sesión y las reglas de firewall del servidor que se almacenan en master y las coloca en la memoria en la base de datos de usuario. 

Este comando solo es aplicable en Azure SQL Database, no puede utilizarse en un motor de base de datos on premise de Microsoft SQL Server.

La sintaxis de este comando en su forma más simple y que se usa más a menudo es:

DBCC FLUSHAUTHCACHE;

Como puede apreciarse se lleva a cabo el vaciado de la cache de autenticación de la base de datos actual. 

En este comando no existen opciones o argumentos adicionales que puedan utilizarse en el comando.

Comentarios

Este comando no se aplica a la base de datos master, dado que la base de datos master contiene el almacenamiento físico de la información sobre los inicios de sesión y las reglas de firewall. El usuario que ejecute el comando y otros usuarios que estén conectados permanecerán conectados.

Es importante mencionar que este comando no es soportado en SQL Data Warehouse (Microsoft ha actualizado su nombre como Azure Synapse Analytics).

SQL Server DBCC dllname (FREE)

Comando DBCC dllname (FREE)

Ya hemos indicado, cuando se inició con los Comandos DBCC, que este comando, perteneciente al grupo de misceláneos, que descarga un procedimiento almacenado extendido especificado DLL de la memoria. Es importante indicar que cuando se ejecuta un procedimiento almacenado extendido, la DLL permanece cargada por la instancia de Microsoft SQL Server hasta que el servidor se cierra. Este comando permite que se pueda descargar de la memoria una DLL sin tener que cerrar Microsoft SQL Server. Para mostrar los archivos DLL cargados que actualmente están en el servidor de Microsoft SQL Server, se sugiere la ejecución de sp_helpextendedproc.

Dentro de los comandos de DBCC, este comando en particular no tiene interacción con las bases de datos, por ello no se requiere que se establezca un uso previo. La sintaxis de este comando en su forma más simple y que se usa más a menudo es:

DBCC dll_name (FREE)

Como puede apreciarse se lleva a cabo la descarga del procedimiento almacenado extendido denominado dll_name. 

Existe una opción que pueden ser utilizada con el comando, ésta es:

WITH NO_INFOMSGS

Suprime todos los mensajes informativos.

Ejemplos de DBCC dllname (FREE)

Mostraré algunos ejemplos de uso de este comando:

DBCC sp_inicio (FREE)

Daremos por supuesto que sp_inicio se encuentra definido sobre sp_inicio.dll, de tal forma que éste comando efectuará la descarga del archivo sp_inicio.dll asociado con el procedimiento extendido sp_inicio.

DBCC sp_inicio (FREE) WITH NO_INFOMSGS

Este comando efectuará la descarga como en el ejemplo anterior, sin mostrar los mensajes informativos.

Comentarios

Si bien, se indicado que se lleve a cabo la ejecución de sp_helpextendedproc para obtener un listado de los archivos dll que se encuentran definidos y el nombre de la librería de enlace dinámico (ddl) al que el procedimiento pertenece. Si por ejemplo lo ejecutamos en la  base de datos master, podíamos obtener algo como lo que se muestra a continuación:

name	dll
xp_availablemedia	xpstar.dll
xp_cmdshell	(server internal)FILESTREAM stores binary large objects (BLOBS) on the file system.

Si observamos el procedimiento xp_availablemedia tiene asociada la líbreria xpstar.dll, en este caso el uso del comando DBCC xp_availablemedia (FREE) descargara de memoria xpstar.dll asociado. Sin embargo la ejecución DBCC xp_cmdshell (FREE) no descargara de memoria, ya que es un procedimiento interno.
Microsoft ha indicado que el procedimiento sp_helpextendedproc será depreciado en el futuro, habrá que validar su uso.

SQL Server DBCC UPDATEUSAGE

Comando DBCC UPDATEUSAGE          

Como se ha mencionado, cuando hablamos de los Comandos DBCC , que este comento pertenece al grupo de mantenimiento y nos informa sobre las imprecisiones de recuento de filas y páginas de las vistas de catálogo y en caso necesario las corrige. Las imprecisiones pueden causar la devolución de informes incorrectos sobre uso de espacio por parte del procedimiento almacenado del sistema sp_spaceused.

Microsoft SQL Server ha indicado que el  Comando DBCC CHECKDB se ha mejorado para detectar si los recuentos de páginas o filas devuelven valores negativos. En caso de que esta situación se presente, la salida del comando DBCC CHECKDB contiene una advertencia y una recomendación para que se ejecute el comando DBCC UPDATEUSAGE con el fin de solucionar el problema.

La sintaxis de este comando en su forma más simple es:

USE master;
GO

DBCC UPDATEUSAGE(database_name);

Con este comando, se lleva a cabo la tarea de actualizar las imprecisiones en la base de datos denominada database_name que es el nombre de la base de datos que se desea actualizar.  Es importante indicar que aunque se puede indicar el identificador de base de datos (database_id), éste es un número que está asociado al nombre de la base de datos, por lo que es más común el uso del nombre de la base de datos.  Los nombres de base de datos deben cumplir las reglas de identificadores.

No podemos decir de una sintaxis que se use más a menudo, dado que con este comando, se puede indicar, ya sea una tabla o vista específica, o bien puede ser indicado únicamente un índice, de esta forma podemos escribir el comando de la siguiente forma; para el caso de querer indicar una tabla o vista se usará:

USE master;
GO

DBCC UPDATEUSAGE (database_name, table_name);

Que lleva a cabo la tarea de actualizar las imprecisiones de espacio en la tabla denominada table_name de base de datos denominada database_name. Los nombres de base de datos y de tablas deben seguir, como se ha establecido, las reglas para los identificadores. Es posible que se utilice el número de identificación de la base de datos (database_id) o de la tabla (table_id) o de una vista (view_id) si se prefiere. Ahora bien, como se indicó anteriormente, se puede optar por indicar un índice, por lo que el comando se usara como:

USE master;
GO

DBCC UPDATEUSAGE (database_name, table_name, index_name);

En este caso se ejecutara la tarea en el índice indicado como index_name, de la tabla table_name perteneciente a la base de datos database_name, como se ha indicado en los otros niveles, es posible que se indique el número de identificación del índice (index_id), pero es más común el uso de los nombres, nuevamente indicaremos que deben cumplir con las reglas aplicables para los identificadores.

Existen algunos argumentos opcionales que pueden ser utilizados con el comando, estos son:

WITH NO_INFOMSGS	Suprime todos los mensajes de información.
COUNT_ROWS	Especifica que la columna row count se actualiza con el recuento actual del número de filas de la tabla o la vista.

Ejemplos de DBCC UPDATEUSAGE

Mostraré algunos ejemplos de uso de este comando:

USE db1;
GO

DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS; 

En este ejemplo, se ésta indicando que se lleve a cabo la actualización del recuento de filas o páginas de la base de datos actual, sin emitir mensajes informativos. Hay que recordar que en muchos de los comandos, cuando se establece 0 en la identificación de la base de datos se hace referencia a la base de datos actual. En caso de que no sea la base de datos la que se desee, utilice la instrucción USE, antes para seleccionar la base de datos.

USE master;

GO

DBCC UPDATEUSAGE (db1); 

En este ejemplo, se generara la tarea para la actualización en la base de datos denominada Db1.

USE master;
GO

DBCC UPDATEUSAGE (Db1, [dbo.tb1]); 

En este ejemplo, se generara la tarea para la actualización en la tabla dbo.tb1 de la base de datos denominada Db1.

USE master;
GO

DBCC UPDATEUSAGE (Db1, [dbo.tb1], IX_tb1_clm1); 

En este ejemplo, se generara la tarea para la actualización en el índice IX_tb1_clm1 de la tabla dbo.tb1 de la base de datos denominada Db1.

USE master;
GO

DBCC UPDATEUSAGE (Db1, [dbo.tb1], IX_tb1_clm1) WITH COUNT_ROWS; 

En este ejemplo, se generara la tarea para la actualización en el índice IX_tb1_clm1 de la tabla dbo.tb1 de la base de datos denominada Db1, actualizando el número de renglones de la tabla. 

Comentarios

Como se ha mencionado, este comando debe utilizarse como una recomendación del comando DBCC CHECKDB, por ello se recomienda que se evite la ejecución del comando de forma rutinaria. Dado que la ejecución puede tardar algún tiempo en ejecutarse con tablas o bases de datos grandes, por ello no se debería utilizar a menos que sospeche que el procedimiento sp_spaceused está devolviendo valores incorrectos, o que sea sugerido.

Hay que considerar que la ejecución de este comando de forma habitual, por ejemplo cada semana, debe llevarse a cabo solo si la base de datos sufre con frecuencia modificaciones asociadas al uso del Lenguaje de definición de datos (DDL), por ejemplo, con las instrucciones CREATE, ALTER o DROP. Esta situación es común en un ambiente de desarrollo, sin embargo, en los nuevos ambientes denominados DevOps, puede llegar a presentarse con más frecuencia.