SQL Server aumenta considerablemente el espacio no utilizado para algunas tablas

Resumen

Este artículo describe un escenario en el que aumenta considerablemente el espacio no utilizado para algunas tablas de Microsoft SQL Server. A continuación, el artículo describe los dos métodos siguientes que puede utilizar para solucionar este problema:
  • Convertir tablas de montón a tablas que utilizan los índices agrupados.
  • Establezca la opción de configuración Abrir objetos en un valor alto.

Síntomas

Considere la situación siguiente en SQL Server:
  • Una instancia de SQL Server tiene una o más bases de datos de usuario.
  • El número acumulado de tablas de estas bases de datos es mayor que el umbral específicos que se enumera en la tabla al final de esta sección. Estas tablas incluyen la tabla del sistema, la tabla de usuario y la tabla temporal.
  • La mayoría de estas tablas hacen referencia a las aplicaciones que están conectadas a la instancia de SQL Server.
En este escenario, puede observar los síntomas siguientes:
  • El espacio que consumen estas bases de datos de usuario crece a un ritmo que es mucho más rápido que la tasa típica. Dependiendo de la configuración de crecimiento automático de estas bases de datos de usuario, los archivos de base de datos pueden crecer con más frecuencia crecen en casos típicos.
  • La parte del espacio que consumen estas bases de datos de usuario no utilizada será mayor que la parte sin usar típica.
  • Al ver las propiedades de las estructuras de almacenamiento de información para estas bases de datos de usuario, como la estructura de almacenamiento del montón, el árbol de la imagen de texto y el índice agrupado, verá un montón de espacio no utilizado.
  • 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 en la tabla sysindexes aumenta en un número pequeño como 1 o 2. Es decir, para cada ocho páginas asignadas en una nueva extensión, nunca se utilizan sólo unas pocas páginas de esa medida.
El umbral que se describe anteriormente en este artículo varía, dependiendo de las condiciones siguientes:
  • La edición específica de SQL Server que está utilizando
  • La memoria que ha configurado para SQL Server
La tabla siguiente enumeran estos umbrales para las distintas ediciones de SQL Server.
EdiciónMemoria (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/MSDENo aplicable128
La memoria se corresponde con la configuración de memoria máxima del servidor para la instancia de SQL Server que está utilizando. Puede utilizar el procedimiento almacenado del sistema sp_configure para configurar la configuración de memoria máxima del servidor .

Nota: En SQL Server 2005, puede consultar la vista de catálogo sys.dm_db_partition_stats para obtener la información de espacio utilizado 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 almacenada en caché para la asignación de espacio en la memoria: asignación de caché y caché de espacio libre. La caché de asignación contiene un grado IDs para extensiones asignadas recientemente para un índice específico. La caché de espacio libre contiene números de página para páginas asignadas recientemente desde estos asignados a extensiones. También contiene el estado de las páginas. Si estas cachés están vacías, se producen los problemas que se describen en la sección "Síntomas".

Estas cachés a quedar vacías por las siguientes razones:
  • Ejecutar comandos diferentes que deben realizar sincronizaciones entre la información de metadatos en memoria y la información en el disco. Por ejemplo, ejecute uno de los siguientes comandos:
    • DBCC UPDATEUSAGE
    • ACTUALIZAR ESTADÍSTICAS
  • Todos los descriptores de objetos que se configuran mediante el
    se utiliza la opción Abrir objetos . Por lo tanto, no hay descriptores libres están disponibles.

Solución alternativa

Para evitar este problema, utilice uno de los métodos siguientes:
  • Convertir tablas de montón a tablas que utilizan los índices agrupados.
  • Establezca la opción de configuración Abrir objetos en un valor alto.
Estos métodos pueden reducir la tasa de crecimiento para el espacio no utilizado que se asigna a una tabla. Las secciones siguientes 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 montón a tablas que utilizan los índices agrupados

Nota: Después de convertir las tablas de montón a tablas que utilizan los índices agrupados, puede realizar operaciones de mantenimiento del índice periódicamente para recuperar el espacio que se utiliza innecesariamente. Por ejemplo, puede ejecutar los siguientes comandos:
DBCC DBREINDEXDBCC INDEXDEFRAG 
La siguiente es una secuencia típica de operaciones que se producen cuando se inserta un registro en una tabla del montículo:
  • 1.intente insertar una fila en la tabla.
  • 2.vea la caché de espacio libre para el id de índice 0 de esta tabla.
  • 3.determinar si las páginas válidas están presentes en la caché de espacio libre.
  • 4.en caso afirmativo:
    • 4.1.si no hay suficiente espacio en la página, insertar datos en la página.
  • 5.si no es así:
    • 5.1.SQL Server debe asignar una nueva página para esta fila.
    • 5.2.vea la caché de asignación para el id de índice 0 de esta tabla.
    • 5.3.determinar si las extensiones válidas están presentes en la caché de asignación.
    • 5.4. en caso afirmativo:
      • 5.4.1.determinar si cualquiera de las ocho páginas de la extensión puede utilizarse para esta nueva solicitud de asignación de página.
      • 5.4.2. en caso afirmativo:
        • 5.4.2.1.asignar una página de esta magnitud ya asignada.
        • 5.4.2.2.vaya al paso 5.5.5.
      • 5.4.3.si no es así:
        • 5.4.3.1.vaya al paso 5.5.1.
    • 5.5.si no es así:
      • 5.5.1.utilizar los datos desde el mapa de asignación Global (GAM) y el mapa de asignación Global secundario (SGAM) para buscar una nueva extensión a asignarse.
      • 5.5.2.asignar una nueva extensión.
      • 5.5.3.utilizar una página de esta nueva extensión para satisfacer la solicitud de asignación de página nueva del paso 5.1.
      • 5.5.4.rellenar la información acerca de esta medida recién asignado en la caché de asignación.
      • 5.5.5.rellenar la información acerca de este recién asignar páginas en la caché de espacio libre.
Si la caché de asignación y la caché de espacio libre están vacías entre las operaciones de inserción posterior, SQL Server asignará nuevas páginas de nuevas extensiones para que puedan alcanzar el éxito de las operaciones de inserción. Cuando los metadatos de la tabla se quita de la memoria, la caché de asignación y la caché de espacio libre también se quitan. Por lo tanto, la próxima vez que realice una operación de inserción que hace referencia a la tabla, estas cachés están vacías. En esta situación, SQL Server debe realizar el paso 5 y, a continuación, paso 5.5. Este comportamiento hace extensiones asignadas recientemente mostrar que ocho páginas se asignan cuando se utiliza sólo una página. En el peor de los casos, puede que se desperdicie 56 kilobytes (KB) de espacio para cada operación de inserción que realiza en la tabla.

La siguiente es la secuencia típica de operaciones que se producen cuando se insertan datos en una tabla que tiene un índice agrupado.
  • 1.intente insertar una fila en la tabla.
  • 2. recorrer el árbol B para buscar la página de datos en la que SQL Server debe almacenar la clave de agrupación.
  • 3. determinar si hay suficiente espacio en la página de la nueva fila.
  • 4. en caso afirmativo:
    • 4.1. insertar datos en esta página.
  • 5. si no es así:
    • 5.1. SQL Server debe asignar una nueva página para esta fila.
    • 5.2. vea la caché de asignación para el id de índice 1 de esta tabla.
    • 5.3. determinar si las extensiones válidas están presentes en la caché de asignación.
    • 5.4. en caso afirmativo:
      • 5.4.1.determinar si puede utilizar cualquiera de las ocho páginas en la medida de esta nueva solicitud de asignación de página.
      • 5.4.2. en caso afirmativo:
        • 5.4.2.1. asignar una página de esta magnitud ya asignada.
      • 5.4.3. si no es así:
        • 5.4.3.1. vaya al paso 5.5.1.
    • 5.5. si no es así:
      • 5.5.1. utilizar los datos de GAM y SGAM para buscar una nueva extensión a asignarse.
      • 5.5.2. asignar una nueva extensión.
      • 5.5.3. utilizar una página de esta medida para satisfacer la solicitud de asignación de página nueva del paso 5.1.
      • 5.5.4. rellenar la información acerca de esta medida recién asignado en la caché de asignación.
Si la caché de asignación y la caché de espacio libre están desactivadas por motivos que se describen en la sección "Causa", no hay ninguna necesidad inmediata para asignar una nueva página entre las operaciones de inserción posterior para esta tabla. Esto es cierto siempre y cuando los datos insertados cabe en la página existente donde debe residir físicamente la clave de agrupación específica. Cuando se llena la página de datos y las cachés están vacías, SQL Server debe realizar el paso 5 y, a continuación, paso 5.5. Tal como se indica en este artículo, si se utiliza un índice agrupado, el escenario donde se asignan las nuevas páginas se produce con menor frecuencia que el escenario donde se inserta un registro en una tabla de montón.

Las siguientes son dos casos donde se produce el problema y la caché de asignación y la caché de espacio libre están vacías. Se supone que los esquemas de la tabla permiten 100 filas caber en una página de datos.
  • Si la tabla tiene sólo una estructura de almacenamiento de información del montón, SQL Server podría asignar una nueva extensión para cada operación de inserción y utilice sólo una página de esa extensión.
  • Si la tabla tiene un índice agrupado, SQL Server podría asignar una nueva extensión para cada operaciones 100 insert y utilizar sólo una página de esa extensión.

Establecer opción de configuración de los objetos abiertos en un valor alto

Este problema se produce principalmente porque SQL Server utiliza todas las ranuras en el área de memoria designado en función de la opción Abrir objetos . Cuando experimenta el problema que describe este artículo, puede establecer el valor de esta opción para adaptarse a casi todas las tablas que se hará referencia en la instancia concreta de SQL Server.

Para determinar un valor de opción de objetos abiertos que evitará este problema, siga estos pasos:
  1. Determinar el número total de tablas que están 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. Estimar un tamaño de búfer que da espacio para tablas temporales y tablas que puedan utilizar aplicaciones y consultas diferentes.
  3. Agregue el número de tablas de usuario y tablas del sistema en el búfer para determinar el número total de tablas que se puede tener acceso en esta instancia de SQL Server. Este número total es el valor que se debe establecer para la opción Abrir objetos de configuración.
Dado que el consumo de memoria que está preasignado durante el inicio de SQL Server se basa en este valor, no debe establecer la opción de configuración de objetos abiertos a un valor muy alto. Si establece la opción Abrir objetos en un valor muy alto, memoria que se utilizó originalmente para otros fines, como para la ejecución de la consulta y para los búferes de datos, se utiliza en su lugar para mantener los metadatos de la tabla en el área de memoria para la opción Abrir objetos .

No recomendamos que cambie la configuración de objetos abiertos en circunstancias normales. Sólo debe cambiar este valor si está seguro de que está experimentando el problema que describe este artículo.

Estado

Microsoft ha confirmado que se trata de un problema de los productos de Microsoft que se enumeran en la sección "Aplicable a".

Más información

¿Por qué la configuración de opción de objetos abiertos provocar este problema

Siempre que una consulta hace referencia a un objeto específico (tabla), SQL Server carga la información sobre el objeto en memoria. Todos los metadatos de índice tienen referencias a la información del objeto que se ha cargado en la memoria de SQL Server. Las cachés de asignación que se tratan en este artículo están asociadas con un índice específico. La opción Abrir objetos controla el número de descriptores de 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 mantener la versión en memoria de los metadatos del objeto. El número de ranuras asignados depende de la edición de SQL Server y en la memoria que está configurada para SQL Server, como se describe en la sección "Síntomas". Todas estas ranuras originalmente forman parte de una lista libre. Se utiliza un algoritmo específico para asignar ranuras de la lista libre de los metadatos del objeto. El algoritmo tiene los siguientes dos modos de operación.

Modo 1

Una vez que se inicia SQL Server, utiliza uno de los descriptores de memoria de la lista libre cada vez que se hace referencia a un objeto nuevo. SQL Server continúa utilizando todas las ranuras existentes cuando carga los metadatos acerca de un objeto al que se hace referencia en una consulta. Porque aún hay entradas de ranura en la lista libre, SQL Server continúa utilizando estas ranuras. Para las ranuras existentes, SQL Server no reutiliza la memoria utilizada por otro objeto.

Modo 2

Si un objeto es que recién se hace referencia en una consulta, y la lista libre se ha convertido en 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 de objeto en la ranura sólo siempre que una consulta hace referencia a la tabla. Una vez finalizada dicha consulta, SQL Server puede reutilizar la ranura si otra tabla hace referencia a una nueva consulta.

Cuando se quitan los metadatos del objeto de memoria, también se quitan todos sus metadatos de índice asociado. Cuando se produce este comportamiento, SQL Server también se pierde la caché de asignación y la caché de espacio libre. A continuación, cuando una consulta diferente hace referencia a esta tabla otra vez, todas las cachés de asignación están vacías. Por lo tanto, la próxima vez que SQL Server intenta asignar una página, SQL Server intenta asignar una nueva extensión.


Si continúa reclamar ranuras que contienen metadatos de objeto hasta que SQL Server no puede recuperar ninguna ranura de SQL Server, SQL Server aumenta el tamaño de la lista libre.

Referencias

Para obtener más información acerca de cómo utilizar el procedimiento almacenado sp_configure 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 Abrir objetos , 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 acerca de la arquitectura de tablas e índices en SQL Server, visite el siguiente sitio Web de MSDN:
Propiedades

Id. de artículo: 924947 - Última revisión: 14 ene. 2017 - Revisión: 1

Comentarios