SQL Server aumenta significativamente el espacio no usado para algunas tablas


Resumen


En este artículo se describe un escenario en el que Microsoft SQL Server aumenta significativamente el espacio no usado para algunas tablas. Después, el artículo analiza los dos métodos siguientes que puede usar para solucionar este problema:
  • Convertir tablas de montones en tablas que usan índices agrupados.
  • Establezca la opción de configuración abrir objetos en un valor alto.

Síntomas


Considere el siguiente escenario en SQL Server:
  • Una instancia de SQL Server tiene una o varias bases de datos de usuario.
  • El número acumulado de tablas de estas bases de datos es mayor que el umbral específico que se muestra en la tabla al final de esta sección. Estas tablas incluyen la tabla del sistema, la tabla de usuario y la tabla temporal.
  • Las aplicaciones que están conectadas a la instancia de SQL Server hacen referencia a la mayoría de estas tablas.
En este caso, es posible que observe los siguientes síntomas:
  • El espacio que usan estas bases de datos de usuario aumenta a una velocidad que es mucho más rápida que la tasa típica. En función de la configuración de crecimiento automático para estas bases de datos de usuario, los archivos de base de datos pueden crecer con mayor frecuencia de lo que aumentan en casos típicos.
  • La parte no usada del espacio que consumen estas bases de datos de usuario será mayor que la parte no usada típica.
  • Al ver las propiedades de las estructuras de almacenamiento de estas bases de datos de usuario, como la estructura de almacenamiento en el montón, el árbol de imágenes de texto y el índice agrupado, verá muchos espacios sin usar.
  • El espacio reservado para las entradas de índice en la tabla sysindexes aumenta en múltiplos de 8. Sin embargo, el espacio utilizado para las entradas de índice de la tabla sysindexes aumenta solo por un número pequeño, como 1 o 2. Es decir, por cada ocho páginas que se asignan en una nueva extensión, solo se usan unas pocas páginas desde ese alcance.
El umbral que se describe anteriormente en este artículo varía en función de las siguientes condiciones:
  • La edición específica de SQL Server que está usando
  • La memoria que ha configurado para SQL Server
En la tabla siguiente se enumeran estos umbrales para diferentes ediciones de SQL Server.
HomeMemoria (bytes)Umbral
Enterprise/Standard/DeveloperMás de 671.088.6408192
Enterprise/Standard/DeveloperMenos de 671.088.6402048
Enterprise/Standard/DeveloperMenos de 67.108.864512
Enterprise/Standard/DeveloperMenos de 8.388.608128
Personal/MSDENot applicable128
La memoria corresponde a la configuración de Max Server Memory para la instancia de SQL Server que está usando. Puede usar el procedimiento almacenado del sistema sp_configure para configurar la opción Max Server Memory .Nota En SQL Server 2005, puede consultar la vista de catálogo sys. dm_db_partition_stats para obtener la información de espacio usada y la información reservada. En SQL Server 2000, puede consultar la tabla sysindexes para obtener esta información.

Causa


SQL Server mantiene dos tipos de información en caché para la asignación de espacio en la memoria: caché de asignación y caché de espacio libre. La caché de asignación contiene los identificadores de extensión de las extensiones asignadas recientemente para un índice específico. La caché de espacio libre contiene los números de página de las páginas asignadas recientemente de estas extensiones asignadas. También contiene el estado de las páginas. Si estas memorias caché se vuelven vacías, se producirán los problemas que se describen en la sección "síntomas". Estas memorias caché estarán vacías por los siguientes motivos:
  • Ejecute diferentes comandos que deben realizar sincronizaciones entre la información de metadatos en memoria y la información del disco. Por ejemplo, ejecute uno de los siguientes comandos:
    • DBCC UPDATEUSAGE
    • ACTUALIZAR ESTADÍSTICAS
  • Se usan todos los descriptores de objeto que se configuraron con la opciónOpen Objects . Por lo tanto, no hay descriptores libres disponibles.

Solución alternativa


Como solución alternativa para este problema, utilice uno de los métodos siguientes:
  • Convertir tablas de montones en tablas que usan índices agrupados.
  • Establezca la opción de configuración abrir objetos en un valor alto.
Estos métodos pueden reducir la tasa de crecimiento del espacio no usado que se asigna a una tabla. En las secciones siguientes se describen estos dos métodos. Las secciones también describen por qué estos métodos ayudan a resolver el problema que describe este artículo.

Convertir tablas de montones en tablas que usan índices agrupados

Nota Después de convertir las tablas de montones en tablas que usan índices agrupados, puede realizar operaciones de mantenimiento de índices periódicamente para recuperar cualquier espacio que no se use de forma innecesaria. Por ejemplo, puede ejecutar los siguientes comandos:
DBCC DBREINDEXDBCC INDEXDEFRAG 
A continuación se presenta una secuencia típica de operaciones que se producen al insertar un registro en una tabla heap:
  • uno.Intente insertar una fila en la tabla.
  • 1.Consulte la memoria caché de espacio disponible para el índice con ID. 0 de esta tabla.
  • 2.Determinar si las páginas válidas están presentes en la caché de espacio disponible.
  • cuatro.Si el valor es sí:
    • 4.1.Si hay suficiente espacio en la página, inserte los datos en la página.
  • 4.En caso de no:
    • 5.1.SQL Server debe asignar una página nueva para esta fila.
    • 5.2.Vea la caché de asignación para el índice con ID. 0 de esta tabla.
    • 5.3.Determinar si las extensiones válidas están presentes en la caché de asignación.
    • 5.4. Si el valor es sí:
      • 5.4.1.Determine si se puede usar una de las ocho páginas en la extensión para esta solicitud de asignación de página nueva.
      • 5.4.2. Si el valor es sí:
        • 5.4.2.1.Asignar una página de esta extensión ya asignada.
        • 5.4.2.2.Vaya al paso 5.5.5.
      • 5.4.3.En caso de no:
        • 5.4.3.1.Vaya al paso 5.5.1.
    • 5.5.En caso de no:
      • 5.5.1.Use los datos del mapa de asignación global (GAM) y el mapa de asignación global (SGAM) secundario para encontrar una nueva medida que se va a asignar.
      • 5.5.2.Asignar una nueva extensión.
      • 5.5.3.Use una página de esta nueva medida para satisfacer la solicitud de asignación de página nueva del paso 5,1.
      • 5.5.4.Rellene la información sobre esta extensión recién asignada en la caché de asignación.
      • 5.5.5.Rellene la información sobre esta página recién asignada en la memoria caché de espacio disponible.
Si la caché de asignación y la caché de espacio libre están vacías entre operaciones de inserción posteriores, SQL Server asignará nuevas páginas a partir de nuevas extensiones para que las operaciones de inserción se realicen correctamente. Cuando se quitan los metadatos de la tabla de la memoria, también se quita la caché de asignación y la memoria caché de espacio disponible. Por lo tanto, la próxima vez que realice una operación de inserción que haga referencia a la tabla, estas memorias caché estarán vacías. En esta situación, SQL Server debe realizar el paso 5 y, a continuación, el paso 5,5. Este comportamiento hace que las extensiones asignadas recientemente muestren que se asignan ocho páginas cuando solo se usa una página. En el peor de los casos, se puede desperdiciar 56 kilobytes (KB) de espacio por cada operación de inserción que realice en la tabla. A continuación se incluye la secuencia típica de operaciones que se producen al insertar datos en una tabla que tiene un índice agrupado.
  • uno.Intente insertar una fila en la tabla.
  • 1. Recorrer el árbol B para buscar la página de datos en la que SQL Server debe almacenar la clave de agrupación.
  • 2. Determine si hay suficiente espacio en la página para la nueva fila.
  • cuatro. Si el valor es sí:
    • 4.1. Insertar datos en esta página.
  • 4. En caso de no:
    • 5.1. SQL Server debe asignar una página nueva para esta fila.
    • 5.2. Vea la caché de asignación para el índice con ID 1 de esta tabla.
    • 5.3. Determinar si las extensiones válidas están presentes en la caché de asignación.
    • 5.4. Si el valor es sí:
      • 5.4.1.Determine si puede usar cualquiera de las ocho páginas en el alcance de esta nueva solicitud de asignación de página.
      • 5.4.2. Si el valor es sí:
        • 5.4.2.1. Asignar una página de esta extensión ya asignada.
      • 5.4.3. En caso de no:
        • 5.4.3.1. Vaya al paso 5.5.1.
    • 5.5. En caso de no:
      • 5.5.1. Use los datos de GAM y SGAM para encontrar una nueva medida que se va a asignar.
      • 5.5.2. Asignar una nueva extensión.
      • 5.5.3. Use una página de este tamaño para cumplir con la solicitud de asignación de página nueva del paso 5,1.
      • 5.5.4. Rellene la información sobre esta extensión recién asignada en la caché de asignación.
Si la caché de asignación y la caché de espacio libre se han desactivado por razones que se describen en la sección "causa", no es necesario asignar una página nueva entre las operaciones de inserción más recientes de esta tabla. Esto es cierto siempre y cuando los datos insertados puedan ajustarse en la página existente, donde la clave de agrupación específica debe residir físicamente. Cuando la página de datos se llena y las cachés están vacías, SQL Server debe realizar el paso 5 y, después, paso 5,5. Como indica este artículo, si usa un índice agrupado, el escenario en el que se asignan las páginas nuevas tiene menos frecuencia que el escenario en el que se inserta un registro en una tabla de montones. A continuación se muestran dos casos en los que se produce el problema, y la caché de asignación y la caché de espacio libre están vacías. Se supone que el esquema de la tabla permite que las filas de 100 se ajusten en una página de datos.
  • Si la tabla solo tiene una estructura de almacenamiento de montones, SQL Server podría asignar una nueva extensión para todas las operaciones de inserción y usar solo una página en ese alcance.
  • Si la tabla tiene un índice agrupado, SQL Server podría asignar una nueva extensión para cada 100 operaciones de inserción y usar solo una página en ese ámbito.

Establecer la opción de configuración de Open Objects en un valor alto

Este problema se produce principalmente porque SQL Server usa todas las ranuras del área de memoria designada en función de la opción Open Objects . Cuando experimente el problema que se describe en este artículo, puede establecer el valor de esta opción para dar cabida a casi todas las tablas a las que se hará referencia en la instancia determinada de SQL Server.To determinar un valor de la opción Open Objects que evitará este problema, siga estos pasos:
  1. Determinar el número total de tablas presentes en la instancia específica de SQL Server.Nota Las tablas incluyen tablas de usuario y tablas del sistema. Debe incluir las tablas de las bases de datos del sistema.
  2. Calcular un tamaño de búfer que ofrece espacio para tablas temporales y tablas de tablas que pueden usar diferentes consultas y aplicaciones.
  3. Agregue el número de tablas de usuario y tablas del sistema al búfer para determinar el número total de tablas a las que se puede tener acceso en esta instancia de SQL Server. Este número total es el valor que debe establecer para la opción de configuración abrir objetos .
Debido a que el consumo de memoria que se ha preasignado durante el inicio de SQL Server se basa en este valor, no debe establecer la opción de configuración de Open Objects en un valor muy alto. Si establece la opción Open Objects en un valor muy alto, en su lugar se usa la memoria que se usó originalmente para otros propósitos, como la ejecución de consultas y los búferes de datos, para mantener los metadatos de la tabla en el área de memoria de la opción Open Objects . No se recomienda cambiar la configuración de los objetos abiertos en circunstancias típicas. Solo debe cambiar este valor si está seguro de que tiene el problema que describe este artículo.

Estado


Microsoft ha confirmado que se trata de un problema de los productos de Microsoft enumerados en la sección "Se aplica a".

Más información


Por qué la configuración de las opciones de Open Objects conduce a este problema

Siempre que una consulta hace referencia a un objeto específico (tabla), SQL Server carga la información sobre el objeto en la memoria. Todos los metadatos de índice tienen referencias a la información de objeto que SQL Server ha cargado en memoria. Las memorias caché de asignación descritas en este artículo están asociadas a un índice específico. La opción Open Objects controla el número de descriptores que la instancia de SQL Server puede cargar en la memoria. Cuando se inicia SQL Server, SQL Server asigna un conjunto específico de ranuras, o descriptores, para que contenga la versión de la memoria de los metadatos del objeto. El número de ranuras asignadas depende de la edición de SQL Server y de la memoria configurada para SQL Server, como se describe en la sección "síntomas". Inicialmente, todas estas ranuras forman parte de una lista gratuita. Se usa un algoritmo específico para asignar ranuras de la lista gratuita para los metadatos del objeto. El algoritmo tiene los dos modos de funcionamiento siguientes.

Modo 1

Una vez que se inicia SQL Server, se usa uno de los descriptores de memoria de la lista de subscriptores libres siempre que se hace referencia a un nuevo objeto. SQL Server continúa usando todas las ranuras existentes cuando carga metadatos sobre un objeto al que se hace referencia en una consulta. Debido a que todavía hay entradas de ranura en la lista gratuita, SQL Server continúa usando estas ranuras. En el caso de las ranuras existentes, SQL Server no reutiliza la memoria usada por otro objeto.

Modo 2

Si se hace referencia a un objeto en una consulta y la lista libre está vacía, SQL Server examina la lista existente de todos los objetos para determinar si hay una ranura cuyos metadatos de objeto se pueden quitar de la memoria. SQL Server debe mantener los metadatos del objeto en la ranura solo siempre que una consulta haga referencia a la tabla. Una vez finalizada la consulta, SQL Server puede reutilizarla si una nueva consulta hace referencia a una tabla diferente. Cuando se quitan los metadatos del objeto de la memoria, también se quitan todos sus metadatos de índice asociados. Cuando se produce este comportamiento, SQL Server también pierde la caché de asignación y la memoria caché de espacio libre. Después, cuando otra consulta haga referencia a esta tabla, todas las cachés de asignación estarán vacías. Por lo tanto, la próxima vez que SQL Server intente asignar una página, SQL Server intentará asignar una nueva extensión. Si SQL Server continúa reclamando ranuras que contienen metadatos de objetos hasta que SQL Server no puede reclamar ninguna ranura, SQL Server aumenta el tamaño de la lista libre.

Referencias


Para obtener más información sobre cómo usar el sp_configure procedimiento almacenado para cambiar las opciones de configuración, visite el siguiente sitio web de Microsoft Developer Network (MSDN): Para obtener más información acerca de la opción Open Objects , visite el siguiente sitio web de MSDN: Para obtener más información acerca de la opción de configuración Max Server Memory , visite el siguiente sitio web de MSDN: Para obtener más información sobre la arquitectura de tabla e índice en SQL Server, visite el siguiente sitio web de MSDN: