SQL Server 2008

Rendimiento de consultas de almacén de datos

Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer

Resumen:

• Optimización de la combinación en estrella
• Paralelismo de tabla con particiones
• Compresión de ROW y PAGE
• Vistas indizadas con particiones alineadas

SQL Server 2008 ofrecerá capacidades de almacenamiento de datos relacionales mucho más eficaces que su antecesor, pero es posible que aún se esté preguntando cómo puede usar toda esta nueva tecnología para crear un almacén de datos que funcione bien para apoyar la toma de decisiones sobre miles de millones de filas. O

a lo mejor quiere saber qué características le ayudarán a lograr el mejor rendimiento para sus consultas e informes de apoyo a la toma de decisiones, o qué tipo de mejoras del rendimiento puede esperar siendo realista con la nueva versión de SQL Server®.

Ciertamente, hay muchas preguntas que surgen a medida que nos acercamos al lanzamiento de la aplicación. Esperamos que esta zambullida en algunas de las características más importantes del almacenamiento de datos de rendimiento de SQL Server 2008 le ayuden a prepararse.

Diseño de base de datos lógico: Modelado dimensional

Las aplicaciones de línea de negocio transaccionales tienen generalmente un esquema de base de datos normalizado. El diseño lógico de esquemas de base de datos para almacenes de datos relacionales pone menos énfasis en la normalización. Muchos diseños de almacenes de datos relacionales adoptan en la actualidad el enfoque de modelado dimensional que popularizaron Ralph Kimball y Margy Ross en su libro The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling.

Si pasa gran parte de su tiempo realizando actividades relacionadas con el almacenamiento de datos, estará probablemente familiarizado con los patrones comunes de esquema para almacenes de datos relacionales (como los esquemas de estrella y copo de nieve). El modelado dimensional distingue las tablas de dimensiones de las tablas de hechos. Las tablas de dimensiones son las que conservan los datos maestros (como productos, clientes, almacenes o países) mientras que las tablas de hechos almacenan los datos transaccionales (como ventas, pedidos, compras o rentabilidad).

Las tablas de dimensiones y las tablas de hechos están vinculadas por las relaciones de clave principal (CP) y clave externa (CE). Se dará cuenta de que muchos almacenes de datos no aplican las restricciones de CE como una manera de reducir los requisitos de almacenamiento. Esto ahorra la sobrecarga de almacenamiento de los índices subyacentes y reduce el costo de mantenimiento de la tabla de hechos. Las tablas de dimensiones incluidas en los almacenes de datos son generalmente bastante pequeñas (normalmente contienen miles o hasta varios millones de filas). La tabla de hechos, por otra parte, puede ser muy grande, llegando a albergar cientos o miles de millones de filas. Así, el diseño lógico necesita realmente prestar mucha atención a los requisitos de almacenamiento de la tabla de hechos.

El factor tamaño también tiene implicaciones a la hora de determinar la clave que se debe elegir de una tabla de dimensiones para mantener las relaciones entre las tablas de hechos y de dimensiones. Las claves compuestas basadas en la clave empresarial de la dimensión (es decir, el identificador real de la entidad representada por la dimensión) cubren generalmente varias columnas. Debe saber que esto es un problema para la tecla externa correspondiente de la tabla de hechos, ya que la clave compuesta de varias columnas se repetirá para cada fila de la tabla de hechos.

En respuesta a esto, una práctica común es la de usar claves suplentes pequeñas para implementar las relaciones entre una tabla de hechos y sus dimensiones. La clave suplente es una columna de identidad de tipo entero que actúa como una clave principal artificial de la tabla de dimensiones. Con la tabla de hechos refiriéndose a una clave suplente más pequeña, se produce una reducción considerable de los requisitos de almacenamiento para tablas de hechos grandes. La Figura 1 ilustra un esquema de almacén de datos modelado dimensionalmente usando tablas de hechos y de dimensiones con claves suplentes.

Figura 1** Ejemplo de esquema de estrella con una tabla de hechos y dos tablas de dimensiones **(Hacer clic en la imagen para ampliarla)

El diseño de esquema de copo de nieve propaga una o varias dimensiones a través de varios niveles (como cliente, país y región para una dimensión de cliente). Así se normalizan las dimensiones más grandes que pueden sufrir excesivas redundancias de datos. Los niveles están representados por tablas separadas, dando al esquema un aspecto de copo de nieve. El diseño de esquema de estrella, por otro lado, no propaga ninguna de sus dimensiones por las tablas. Como su propio nombre indica, una esquema de estrella tiene forma de estrella. Las tablas de dimensiones se agrupan alrededor de la tabla de hechos, que ocupa el lugar central.

Con esquemas de estrella o de copo de nieve modelados dimensionalmente, las consultas que apoyan la toma de decisiones siguen un patrón típico: la consulta selecciona varias medidas de interés de la tabla de hechos, une las filas de hechos con una o varias dimensiones a lo largo de las claves suplentes, sitúa predicados de filtro en las columnas empresariales de las tablas de dimensiones, agrupa por una o varias columnas empresariales y agrega las medidas recuperadas de la tabla de hechos durante un período de tiempo. A continuación se demuestra este patrón, también conocido como consulta de combinación en estrella:

select ProductAlternateKey,
CalendarYear,sum(SalesAmount)
from FactInternetSales Fact
     join DimTime 
on Fact.OrderDateKey = TimeKey
     join DimProduct 
on DimProduct.ProductKey =
   Fact.ProductKey
where CalendarYear between 2003 and 2004
      and ProductAlternateKey like 'BK%'
group by ProductAlternateKey,CalendarYear

Diseño físico

Muchas consultas SQL en su almacén de datos relacionales seguirán la estructura de consulta de combinación en estrella. No obstante, las consultas que apoyan la toma de decisiones varían generalmente con el tiempo porque los responsables de la toma de decisiones están continuamente intentando entender mejor sus datos empresariales fundamentales de nuevas formas. Por eso las cargas de trabajo de los almacenes de datos tienden a tener una proporción alta de consultas ad hoc. Esto hace que el diseño físico de consultas que apoyan la toma de decisiones y un esquema de almacén de datos modelado dimensionalmente sea todo un reto.

Con SQL Server, los diseñadores de almacenes de datos empiezan generalmente con un cianotipo o un diseño físico que ajustan y refinan a lo largo del tiempo a medida que las cargas de trabajo evolucionan. Adopte y varíe este cianotipo para su propio entorno de almacenamiento de datos. Si lo hace, tenga presente los procedimientos recomendados para el diseño físico de bases de datos, como la repercusión que tiene sobre el rendimiento el mantenimiento de índices para actualizaciones y los requisitos de almacenamiento para los índices.

La tabla de hechos

El diseño del cianotipo pretende anticipar la forma de consulta de estrella típica y crea índices sobre la tabla de hechos. El índice clúster de la tabla de hechos usa varias columnas de clave suplente de dimensiones (las columnas de clave externa) como claves de índice. Las columnas usadas con más frecuencia deben estar en la lista de claves de índice. Tómese su tiempo para comprobar que esto ofrece verdaderamente una buena ruta de acceso para las consultas ejecutadas con más frecuencia en su carga de trabajo.

Además, el cianotipo crea un índice no clúster de una sola columna para cada columna suplente (clave externa) de dimensiones de la tabla de hechos. Esto proporciona una ruta de acceso sumamente eficiente para las consultas que son muy selectivas en una de sus dimensiones.

El objetivo del índice clúster es proporcionar un buen rendimiento para la mayoría de las consultas de su carga de trabajo. El conjunto de índices no clúster tiene como objetivo las consultas que recuperan las medidas de la tabla de hechos para un cliente o un producto específico. Estos índices no clúster aseguran, por ejemplo, que no tenga que analizar la tabla de hechos para recuperar los datos de ventas correspondientes a un solo cliente.

Tablas de dimensiones

Cuando aplique el diseño de cianotipo a tablas de dimensiones, debe crear índices para cada tabla de dimensiones. Éstas incluyen un índice no clúster de restricciones de clave principal en la columna de claves suplentes de la dimensión y un índice clúster sobre las columnas de la clave empresarial de la entidad de la dimensión. Para tablas de dimensiones grandes, debe considerar también agregar índices no clúster sobre las columnas que se usan con frecuencia en predicados sumamente selectivos.

El índice clúster facilita la eficiente extracción, transformación y carga (ETL) durante el período de mantenimiento del almacén de datos, que es a menudo un proceso crítico en el tiempo. Con cambiar lentamente las dimensiones, por ejemplo, las filas existentes se actualizan en el sitio, mientras que las filas que todavía no están presentes en la dimensión se anexan a la tabla de dimensiones. Para tener éxito, este patrón de acceso requiere que los procesos de búsqueda y actualización funcionen bien para la tabla de dimensiones en el momento de realizar la ETL.

El diseño del cianotipo que describimos sirve como un buen punto de partida para los diseños físicos en almacenes de datos relacionales creados con SQL Server. Basado en esta instalación típica de almacén de datos relacionales, podemos explorar las nuevas características clave de SQL Server 2008.

Optimización de la combinación en estrella

El procesamiento de la tabla de hechos constituye generalmente el mayor costo a la hora de ejecutar una consulta de combinación de estrella en un almacén de datos relacionales modelado dimensionalmente. Esto es fácil de ver porque incluso las consultas sumamente selectivas recuperan más filas de la tabla de hechos que de cualquier dimensión. Por lo tanto, usar la mejor ruta de acceso a la tabla de hechos es esencial para lograr un buen rendimiento de consulta.

Con SQL Server, el optimizador de consultas elige automáticamente la ruta de acceso con el costo más bajo estimado de un conjunto de alternativas. En el contexto del almacenamiento de datos, el objetivo principal es asegurarse de que el optimizador de consultas considera las alternativas atractivas de rutas de acceso para el plan de ejecución de la consulta de combinación en estrella. SQL Server incluye varias funcionalidades en su optimizador de consultas para proporcionar automáticamente planes de ejecución de consultas de combinación en estrella de rendimiento óptimo.

La consultas de combinación en estrella se pueden dividir en tres clases diferentes, tal y como se muestra en la Figura 2. Estas clases amplias ayudan también al motor de SQL Server a identificar los planes adecuados para esas consultas. El concepto principal sobre el que descansa SQL Server es la selectividad de esas consultas en la tabla de hechos. Una consulta se considera más selectiva cuantas menos filas consuma de la tabla de hechos. El porcentaje de filas recuperado de la tabla de hechos se usa para proporcionar un dato intuitivo acerca de las clases de consulta . Estos porcentajes representan valores de implementaciones típicas de cliente, pero no constituyen límites estrictos usados para generar las definiciones de ruta de acceso.

Figura 2** Intervalos de selectividad para consultas de combinación en estrella **(Hacer clic en la imagen para ampliarla)

La primera clase cubre consultas sumamente selectivas, que procesan hasta el 10 por ciento de las filas de la tabla de hechos. La segunda clase, con selectividad media, consiste en consultas que procesan más del 10 por ciento y hasta el 75 por ciento de las filas de la tabla de hechos. Las consultas de la tercera clase, con selectividad baja, requieren el procesamiento de más del 75 por ciento de las filas almacenadas en la tabla de hechos. Las cajas de la figura también destacan las selecciones básicas de planes de ejecución de consultas correspondientes a cada clase de selectividad.

Selección de planes en función de la selectividad

Puesto que las consultas de estrella de selectividad alta no recuperan generalmente más del 10 por ciento de las filas de la tabla de hechos, pueden proporcionar acceso aleatorio a la tabla de hechos. Por lo tanto, los planes de consulta para esa clase dependen mucho de uniones de bucle anidado en combinación con búsquedas de índice (no clúster) y de marcador en la tabla de hechos. Cuando éstos realizan acciones de E/S aleatoria en la tabla de hechos, son superados por otras acciones de E/S secuencial cuando necesitamos recuperar partes más grandes de la tabla de hechos. Esto motiva distintos planes de consulta cuando el número de filas de la tabla de hechos supera un número determinado.

Puesto que las consultas de estrella de selectividad media procesan una fracción considerable de las filas de la tabla de hechos, las uniones hash con análisis de tabla de hechos o análisis de intervalos de tabla de hechos son generalmente la elección preferida a la hora de optar por una ruta de acceso a la tabla de hechos. SQL Server usa filtros de mapa de bits para mejorar el rendimiento de las uniones hash.

La Figura 3 ilustra cómo SQL Server usa estos filtros de mapa de bits para mejorar el rendimiento de la unión durante la ejecución de una consulta de combinación en estrella. La figura muestra un plan para una consulta en dos tablas de dimensiones, Producto y Tiempo, que se unen con la tabla de hechos por sus claves suplentes. La consulta usa predicados de filtro, como cláusulas WHERE, en ambas tablas de dimensiones para que sólo se clasifique una fila por dimensión. Las pequeñas tablas rojas situadas junto a los dos operadores de unión indican esta circunstancia.

Figura 3** Plan de consulta de combinación en estrella con procesamiento de la reducción de la unión **(Hacer clic en la imagen para ampliarla)

La implementación de la unión para cada unión es una unión hash. Esto permite que SQL Server absorba la información acerca de las filas clasificadas de las tablas de dimensiones en lo que llamamos información de la reducción de la unión para ambas tablas de dimensiones. Las cajas verdes de la figura representan estructuras de datos de información de la reducción de la unión . Una vez rellenadas desde las tablas de dimensiones subyacentes, SQL Server mueve estas estructuras de datos automáticamente durante la ejecución de consultas al operador que procesa la tabla de hechos, como, por ejemplo, un análisis de tabla. Este operador utiliza la información sobre las filas de la tabla de dimensiones para eliminar las filas de la tabla de hechos que no se clasifiquen para las condiciones de unión frente a las dimensiones.

SQL Server elimina estas filas de la tabla de hechos en los primeros estadios del procesamiento de consultas (después de recuperar la fila de la tabla de hechos). Esto habilita los ahorros de CPU y los posibles ahorros de E/S de disco ya que las filas eliminadas no necesitan ser procesadas dentro de otros operadores del plan de consultas. SQL Server usa una representación de mapa de bits para implementar eficientemente las estructuras de datos de la información de la reducción de la unión en el momento de ejecutar la consulta.

Canalización de la optimización de la combinación en estrella

El proceso de optimización emplea la heurística estándar para la optimización de consultas de combinación para generar un conjunto inicial de alternativas de planes de ejecución de consultas. Entonces se invocan las extensiones destinadas a fines específicos para generar alternativas adicionales de planes de consulta.

En el caso del almacenamiento de datos, la extensión detecta esquemas de estrella, esquemas de copo de nieve y patrones de consulta de estrella, y estima la selectividad de la consulta en la tabla de hechos. Si el esquema y la forma de la consulta coinciden con los patrones, SQL Server agrega automáticamente los planes de consulta adicionales al espacio del plan, que es entonces cubierto por la optimización basada en el costo para elegir el plan de consulta más prometedor para la ejecución.

En el momento de la ejecución de la consulta, SQL Server también supervisa la selectividad de la reducción de la unión en tiempo de ejecución. Si la selectividad cambia, SQL Server vuelve a organizar dinámicamente las estructuras de datos de la información de la reducción de la unión para que la más selectiva se aplique en primer lugar.

Heurística de la combinación en estrella

Muchos diseños físicos de almacenes de datos siguen el esquema de estrella, pero no especifican completamente las relaciones entre las tablas de hechos y de dimensiones, como hemos mencionado anteriormente en relación con las restricciones de clave externa, por ejemplo. Si no se especifican explícitamente las restricciones de clave externa, SQL Server debe apoyarse en la heurística para detectar los patrones de consulta de esquema de estrella. La heurística siguiente se aplica para detectar patrones de consulta de combinación en estrella:

1. La tabla más grande que forma parte de la unión n-aria se considera la tabla de hechos. Hay restricciones adicionales sobre el tamaño mínimo de la tabla de hechos. Por ejemplo, incluso si la tabla más grande no supera un tamaño específico, la unión n-aria no se considera una unión de estrella.
2. Todas las condiciones de unión de las uniones binarias de una consulta de combinación en estrella tienen que ser predicados de igualdad de columna únicos. Las uniones deben ser combinaciones internas. Aunque esto pueda parecer restrictivo, cubre la inmensa mayoría de uniones entre las tablas de hechos y de dimensiones de clave suplente en los esquemas de estrella típicos. Si una unión tiene una condición de unión más compleja que no encaja en el patrón descrito anteriormente, se excluye de la combinación en estrella. Una unión de cinco puntas, por ejemplo, puede generar una combinación en estrella de tres puntas (con dos uniones adicionales más adelante), si dos de las uniones tienen predicados de combinación más complejos.

Tenga en cuenta que éstas son reglas heurísticas. Hay pocas circunstancias reales que harán que la heurística escoja una tabla de dimensiones como la tabla de hechos. Esto influye la elección del plan pero no tiene repercusiones sobre la exactitud del plan seleccionado. Las uniones binarias implicadas en una combinación en estrella se clasifican entonces por selectividad decreciente. En este contexto, la selectividad de la unión se define como una relación de cardinalidad de entrada de la tabla de hechos y la cardinalidad resultante de la unión (la selectividad de la unión indica cuánto reduce una dimensión específica la cardinalidad de una tabla de hechos). Por regla general, queremos considerar en primer lugar las uniones con una selectividad más alta.

El procesador de consultas de SQL Server aplica la optimización automáticamente a las consultas que siguen el patrón de combinación en estrella y las condiciones referidas cuando los planes de consulta resultantes tienen costos estimados de consulta atractivos . De este modo, no necesita hacer ningún cambio a su aplicación para que se beneficie de esta mejora considerable del rendimiento. Tenga en cuenta, sin embargo, que algunas de las optimizaciones de combinación en estrella tales como la reducción de la unión están disponibles sólo en SQL Server Enterprise Edition.

Resultados del rendimiento de la combinación en estrella

Como parte del desarrollo de la optimización de la combinación en estrella en SQL Server 2008, realizamos varios estudios del rendimiento basados en bancos de pruebas y cargas de trabajo de clientes reales. Vale la pena echar un vistazo a los resultados de tres de estas cargas de trabajo.

Almacén de datos de la organización de ventas de Microsoft Esta carga de trabajo hace un seguimiento del rendimiento de un almacén de datos que se usa internamente para apoyar la toma de decisiones en la organización de ventas de Microsoft. Tomamos una instantánea de muestra de la base de datos con un tamaño aproximado de 750 GB (incluyendo los índices). Las consultas en esta carga de trabajo constituyen un reto para el procesamiento de consultas, ya que muchas de ellas tienen más de 10 uniones.

Cliente del sector de la venta al por menor Esta serie de experimentos se basa en un cliente de almacenamiento de datos en el sector de la venta al por menor (con una tienda convencional y presencia en línea). El cliente se caracteriza por un esquema de copo de nieve modelado dimensionalmente y consultas de combinación en estrella canónicas. Usamos unos 100 GB de datos sin procesar para rellenar una instantánea del almacén para nuestros experimentos.

Carga de trabajo de apoyo a la toma de decisiones Esta serie de experimentos investiga el rendimiento de una carga de trabajo de apoyo a la toma de decisiones en una base de datos de 100 GB modelada dimensionalmente. La Figura 4 muestra los resultados de estas tres cargas de trabajo. La figura traza medias geométricas normalizadas del tiempo de respuesta de la consulta sobre todas las consultas de la carga de trabajo. Esta métrica es un buen indicador del rendimiento de consulta esperado al ejecutar una consulta arbitraria de la carga de trabajo. Las barras de la figura comparan el rendimiento de línea de base (1.0) cuando no se usa la optimización de la combinación en estrella en el rendimiento optimizado de la combinación en estrella. Todas estas ejecuciones se realizaron con SQL Server 2008.

Figura 4** Mejoras del rendimiento con la optimización de la combinación en estrella **(Hacer clic en la imagen para ampliarla)

Como muestra la figura, todas las cargas de trabajo mejoran notablemente, entre un 12 y un 30 por ciento. Aunque su recorrido individual variará, esperamos que las cargas de trabajo de apoyo a la toma de decisiones en el motor de SQL Server mejoren aproximadamente entre un 15 y un 20 por ciento en función de la extensión de las optimizaciones específicas de combinación en estrella que son nuevas en SQL Server 2008.

Paralelismo de tabla con particiones

Para acelerar el procesamiento de consultas en almacenes de datos grandes, los administradores de bases de datos a menudo crean particiones por fecha de las grandes tablas de hechos. Esto coloca los datos en distintos grupos de archivos, reduciendo la cantidad de datos que deben buscarse al procesar filas incluidas en un intervalo específico de datos y utilizando también el rendimiento simultáneo del sistema de disco subyacente al implementar los grupos de archivos en un gran número de discos físicos.

SQL Server 2005 introdujo la capacidad de dividir una relación grande en pedazos lógicos más pequeños para mejorar la administración de tablas grandes. También se ha usado satisfactoriamente para mejorar el procesamiento de consultas, especialmente cuando se trata de grandes aplicaciones de apoyo a la toma de decisiones.

Desgraciadamente, algunos clientes que usan SQL Server 2005 han observado algunos problemas de rendimiento asociados con las consultas en estas tablas con particiones, en particular, al ejecutar equipos multiprocesadores de memoria compartida en paralelo. Al procesar consultas en paralelo en tablas con particiones en SQL Server 2005, es posible que se produzcan instancias si sólo se asigna un subconjunto de los subprocesos disponibles para ejecutar la consulta.

Piense en un equipo de 64 núcleos donde las consultas podrían usar hasta 64 subprocesos en paralelo y una consulta toca dos particiones. Con SQL Server 2005, sólo recibe 2 de los 64 subprocesos y por lo tanto podría usar sólo 2/64 (el 3,1 por ciento) de la energía de la CPU del equipo. Se sabe que con algunas consultas, el rendimiento obtenido con particiones podría ser como mínimo 10 veces inferior al rendimiento que se alcanza al ejecutar la misma consulta en el mismo equipo sobre una versión sin particiones de la misma tabla de hechos.

Debemos tener en cuenta que SQL Server 2005 se optimizó específicamente para consultas que tocan una sola partición. En este caso, el procesador de consultas asignará todos los subprocesos disponibles para realizar el análisis. Esta optimización especial tuvo como resultado una mejora considerable del rendimiento de las consultas de una sola partición que se ejecutan en equipos multinúcleo, por lo que era natural que los clientes esperaran el mismo comportamiento con las consultas que tocan varias particiones.

La nueva característica de paralelismo de tabla con particiones (PTP, Partitioned Table Parallelism) de SQL Server 2008 mejora el rendimiento de consulta en el caso de las particiones mediante una mejor utilización de la capacidad de procesamiento del hardware existente independientemente del número de particiones que toque una consulta o del tamaño relativo de las particiones individuales. En el escenario típico de un almacén de datos con una tabla de hechos con particiones, los usuarios pueden ver una mejora considerable de las consultas que se ejecutan en planes paralelos, especialmente si el número de núcleos del procesador disponible es superior al número de particiones afectadas por la consulta. Y esta característica nueva funciona desde el principio, sin necesidad de realizar ningún ajuste o configuración.

Imagine que tenemos una tabla de hechos que representa los datos de ventas organizados por fecha de venta a través de cuatro particiones. El diagrama de la Figura 5 le ayudará a visualizar este ejemplo. Tenga en cuenta que en vez de un solo índice clúster para el intervalo completo de fecha, como en el caso en que no hay particiones, hay normalmente un índice clúster en la columna de fecha para cada partición de la tabla de hechos. Ahora asuma que esa consulta Q resume las ventas correspondientes a los últimos siete días. Como hay nuevos datos de ventas entrando continuamente en la tabla de hechos a través de la última partición (con etiqueta P4), es probable que la consulta toque diferentes particiones según el momento de su ejecución. En la primera fila del diagrama se ilustra cómo la consulta Q1 toca una sola partición mientras que la consulta Q2 toca dos particiones, ya que los datos relevantes en el momento de la ejecución se extienden por las particiones P3 y P4.

Figura 5** Funcionamiento de la nueva característica de PTP **(Hacer clic en la imagen para ampliarla)

Ahora asuma que hay ocho subprocesos disponibles. La ejecución de las consultas Q1 y Q2 en SQL Server 2005 puede generar algún comportamiento inesperado. SQL Server 2005 dispone de una optimización por la cual si el optimizador sabe en el momento de la compilación que la consulta sólo tocará una partición, ésta se tratará como una sola tabla sin particiones y se generará un plan que tenga acceso a la tabla con todos los subprocesos disponibles.

El resultado es que la consulta Q1 que implica una sola partición (P3) tendrá como resultado un plan procesado por ocho subprocesos (no se muestra). En el caso de la consulta Q2, que toca dos particiones, el ejecutor asigna un solo subproceso a cada partición incluso si el hardware subyacente tiene subprocesos adicionales disponibles. Así, la consulta Q2 sólo utilizará una fracción muy pequeña de la energía de la CPU disponible y se ejecutará, con toda probabilidad, bastante más despacio que la consulta Q1.

La ejecución de las consultas Q1 y Q2 en SQL Server 2008 tiene como resultado una mejor utilización del hardware disponible, una mejora del rendimiento y un comportamiento más previsible. En el caso de la consulta Q1, el ejecutor vuelve a asignar los ocho subprocesos disponibles para procesar los datos en P2 (no se muestra). La consulta Q2, mientras tanto, tendrá como resultado un plan paralelo en el que el ejecutor asigna todos los subprocesos disponibles a P3 y a P4 según el estilo round robin, produciendo el efecto ilustrado en la fila inferior del diagrama, donde cada una de las dos particiones recibe cuatro subprocesos. La CPU sigue utilizándose en su totalidad y el rendimiento de las consultas Q1 y Q2 es comparable.

Esta asignación round robin de los subprocesos permite que el rendimiento de las consultas sea cada vez mejor a medida que aumenta el número de núcleos de procesador en comparación con el número de particiones a las que tiene acceso la consulta. Desgraciadamente, hay casos en los que la asignación de subprocesos a las particiones no es tan sencilla como en este ejemplo.

Las mejoras de rendimiento obtenidas de SQL Server 2005 a SQL Server 2008 en los escenarios de tabla con particiones en un equipo de procesador multinúcleo se ilustran en la Figura 6. Este gráfico destaca el rendimiento de análisis para tablas con particiones. Para esta prueba concreta, realizada en un sistema de 64 núcleos y 256 GB de RAM, dividimos una sola tabla de 121 GB en 11 particiones de 11 GB cada una. Para el conjunto de pruebas representadas en esta figura, usamos una organización de archivo de montón con inicios de búfer caliente y de búfer frío. Todas las consultas realizan análisis sencillos de los datos.

Figura 6** Rendimiento de análisis de SQL Server con la nueva característica de PTP habilitada **(Hacer clic en la imagen para ampliarla)

El eje Y muestra el tiempo de respuesta (en segundos) y el eje X indica el grado de paralelismo (DOP), que es análogo al número de subprocesos asignados a la consulta. Como puede ver, en los casos de inicio frío y caliente, los tiempos de respuesta decrecen hasta que el DOP alcanza la cifra de 22. En este momento, el sistema de E/S se satura para el caso de inicio frío. Esto se debe al hecho de que la consulta usada en este ejemplo es dependiente de E/S. Puede que esta limitación no exista para más cargas de trabajo dependientes de CPU, o que ocurra sólo a niveles de DOP más altos.

Sin embargo, la curva que representa el caso de inicio en caliente muestra descensos de los tiempos de respuesta a medida que aumentan los niveles de DOP. En SQL Server 2005, ambas curvas empezarían a estabilizarse alrededor del nivel 11 de DOP, puesto que el número de subprocesos por partición estaría limitado a 1 al tratar con varias particiones.

Es importante indicar que en la práctica la ganancia de tiempos de respuesta para los números crecientes de DOP nunca son lineales. En su lugar, el comportamiento esperado se parece más al de una función escalón (esto refleja que la consulta esencialmente espera en la subparte más lenta). Así, por ejemplo, agregando simplemente otro subproceso a un análisis no se mejorará el tiempo de finalización de una consulta hasta que todos los análisis restantes hayan recibido subprocesos adicionales que les permitan terminar también más rápido.

Hemos realizado experimentos adicionales para probar el comportamiento del nuevo PTP con otras configuraciones de hardware y archivo. Al hacerlo, observamos un comportamiento semejante en términos de ajuste de escala de capacidad de proceso cuando el nivel de DOP supera un subproceso o partición.

Por último, pero por ello no menos importante, la nueva característica de PTP de SQL Server 2008 también mejora la legibilidad de los planes de consulta y proporciona una mejor perspectiva de la ejecución de ciertas cargas de trabajo. Por ejemplo, como parte de la característica de PTP, se ha mejorado la forma en la que se representan los planes paralelos y serie en el plan de presentación XML y se ha ampliado la información de partición que se proporciona en los planes de ejecución de tiempo de compilación y tiempo de ejecución.

Compresión de datos

A medida que la inteligencia empresarial pasa a ser cada vez más común, los negocios vierten más y más datos en sus almacenes de datos para el análisis. El resultado es un crecimiento exponencial del tamaño de los datos administrados. En 1995, la primera encuesta de Winter Corporation sobre tamaños de bases de datos puso de manifiesto que el sistema más grande del mundo contenía un terabyte de datos. Diez años más tarde, la base de datos más grande era aproximadamente 100 veces mayor. El hecho más asombroso es que el tamaño de los almacenes de datos se triplica cada dos años. Esto crea nuevos desafíos a la hora de administrar dichas cantidades grandes de datos y de proporcionar niveles aceptables de rendimiento para consultas de almacenamiento de datos. Estas consultas son normalmente complejas, implican muchas uniones y agregados y tienen acceso a grandes cantidades de datos. Y no es raro que muchas consultas de la carga de trabajo sean dependientes de E/S.

La compresión de datos nativos pretende abordar este problema. SQL Server 2005 SP2 introdujo un nuevo formato de almacenamiento de longitud variable, el formato de almacenamiento vardecimal, para datos numéricos y decimales. Este formato nuevo del almacenamiento puede reducir notablemente el tamaño de sus bases de datos. Los ahorros de espacio, en cambio, ayudan a mejorar el rendimiento de las consultas dependientes de E/S de dos maneras. Primero, hay menos páginas que leer, y en segundo lugar, puesto que los datos se mantienen comprimidos en el grupo del búfer, aumenta la esperanza de vida de página (en otras palabras, mejora la posibilidad de que la página solicitada se encuentre en el búfer). Por supuesto, los ahorros de espacio logrados con la compresión de datos tienen un costo de CPU debido al proceso de compresión y descompresión de datos.

SQL Server 2008 se ha construido sobre el formato de almacenamiento vardecimal y ofrece dos clases de compresión: la compresión ROW y la compresión PAGE. La compresión ROW amplía el formato de almacenamiento vardecimal mediante el almacenamiento de todos los tipos de datos de longitud fija en un formato de almacenamiento de longitud variable.

Algunos ejemplos de tipos de datos de longitud fija son los tipos de datos integer, char y float. Aunque SQL Server almacena estos tipos de datos en un formato de longitud variable, la semántica de los tipos de datos no varía (desde la perspectiva de la aplicación, un tipo de datos sigue siendo un tipo de datos de longitud fija). Esto significa que puede beneficiarse de las ventajas de la compresión de datos sin tener que realizar ningún cambio en sus aplicaciones.

La compresión PAGE minimiza la redundancia de datos en columnas en una o más filas de una página dada. Usa una implementación propiedad del algoritmo LZ78 (Lempel-Ziv), almacenando los datos redundantes sólo una vez en la página y haciendo referencia a ellos desde las múltiples columnas. Tenga en cuenta que al usar la compresión PAGE, la compresión ROW está incluida también.

Los tipos de compresión ROW y PAGE se pueden habilitar en una tabla, en un índice o en una o más particiones para las tablas e índices con particiones. Esto le da una flexibilidad total para elegir tablas, índices y particiones para la compresión, permitiéndole encontrar el equilibrio perfecto ente el ahorro de espacio y su impacto en la CPU. La Figura 7 ilustra esto con una tabla de ventas cuyas particiones se han realizado de diferentes maneras con índices alineados.

Figura 7** Tabla cuyas particiones se han realizado con configuraciones de compresión diferentes **(Hacer clic en la imagen para ampliarla)

Cada partición representa un trimestre, siendo oct-dic el último trimestre. Suponga que el acceso a las dos primeras particiones no es frecuente, que la tercera partición es moderadamente activa y que la última partición es la más activa. En este caso, una configuración posible es habilitar la compresión PAGE en las dos primeras particiones para obtener el máximo ahorro de espacio con una repercusión mínima sobre el rendimiento de la carga de trabajo, la compresión ROW en la tercera partición y ninguna compresión en la última.

Puede habilitar la compresión, ya sea con o sin conexión, usando las instrucciones Alter Table o Alter Index Data Definition Language (DDL). SQL Server también proporciona un procedimiento almacenado para estimar los ahorros de espacio. Los ahorros de espacio conseguidos dependerán de la distribución de los datos y del esquema del objeto que se esté comprimiendo.

En función de los resultados obtenidos de las pruebas realizadas con muchas bases de datos de clientes, parece que la mayoría de los clientes podrá reducir el tamaño de sus bases de datos en un 50–65 por ciento y mejorar el rendimiento de las consultas dependientes de E/S notablemente. Sin embargo, estimar la repercusión del rendimiento de las consultas dependientes de CPU es un poco más complicado y depende de la complejidad de la consulta. En SQL Server, sólo se incurre en costos de descompresión al obtener acceso a los índices o tablas. Si el costo relativo de CPU resultante del análisis de operadores es bajo comparado con el costo general de CPU de la consulta, como ocurre normalmente en el escenario del almacenamiento de datos, debería obtener un impacto sobre la utilización de CPU inferior al 20-30 por ciento.

Vistas indizadas con particiones alineadas

En SQL Server 2008, las vistas indizadas con particiones alineadas le permiten crear y administrar agregados de resumen en su almacén de datos relacionales de manera más eficaz y usarlos en escenarios donde antes no podía usarlos con eficacia. Esto mejora el rendimiento de consulta. En un escenario típico, contamos con una tabla de hechos con particiones organizadas por fecha. Las vistas indizadas (o agregados de resumen) se definen en esta tabla para acelerar las consultas. Cuando cambia una nueva partición de tabla, las particiones coincidentes de las vistas indizadas con particiones alineadas definidas en la tabla con particiones también cambian, y lo hacen de manera automática.

Esto constituye una mejora considerable sobre SQL Server 2005, donde tiene que desechar las vistas indizadas definidas en la tabla con particiones antes de usar la operación ALTER TABLE SWITCH para cambiar una partición. La característica de vistas indizadas con particiones alineadas de SQL Server 2008 le ofrece las ventajas de las vistas indizadas en tablas con particiones grandes al tiempo que evita el costo de volver a crear agregados en toda una tabla con particiones. Estas ventajas incluyen el mantenimiento automático de agregados y vistas indizadas coincidentes.

Extensión de bloqueo en el nivel de partición

SQL Server es compatible con la partición de intervalos, que le permite crear particiones de datos para mejorar la capacidad de administración y agrupar los datos según su patrón de uso. Así, por ejemplo, se pueden crear particiones de los datos de ventas en franjas mensuales o trimestrales. Puede asignar una partición a su propio grupo de archivos y asignar éste a un grupo de archivos. Esto ofrece dos ventajas clave. Primero, puede realizar copias de seguridad y restaurar una partición como una unidad independiente. Segundo, puede asignar un grupo de archivos a o subsistema lento o rápido de E/S en función del patrón de uso o la carga de consultas.

Un punto interesante es el patrón de acceso de los datos. Puede que las consultas y las operaciones de DML sólo necesiten tener acceso o manipular un subconjunto de particiones. Si está, por ejemplo, analizando los datos de ventas correspondientes a 2004, sólo necesita tener acceso a las particiones pertinentes y, idealmente, no debería verse afectado, salvo por los recursos del sistema, por las consultas que tienen acceso simultáneamente a los datos en otras particiones. En SQL Server 2005, el acceso simultáneo de datos en otras particiones puede generar un bloqueo de tabla que puede tener un impacto sobre el acceso a otras particiones.

Para minimizar esta interferencia, SQL Server 2008 introduce una opción en el nivel de tabla para controlar la extensión de bloqueo en el nivel de partición o de tabla. De forma predeterminada, la extensión de bloqueo está habilitada en el nivel de tabla, como es el caso en SQL Server 2005. Sin embargo, puede omitir la directiva de extensión de bloqueo para la tabla. Así, por ejemplo, puede establecer la extensión de bloqueo de la siguiente manera:

Alter table <mytable> set (LOCK_ESCALATION = AUTO)

Este comando instruye a SQL Server para que elija la granularidad de la extensión de bloqueo correcta para la esquema de tabla. Si la tabla no tiene particiones, la extensión de bloqueo está en el nivel de TABLA. Si la tabla tiene particiones, la granularidad de extensión de bloqueo está en el nivel de partición. SQL Server también usa esta opción como una sugerencia para desfavorecer la granularidad de bloqueo en el nivel de tabla.

Conclusión

Esto es sólo una breve introducción a las características mejoradas que encontrará en SQL Server 2008 y que le ayudarán a lograr un mejor rendimiento de las consultas que apoyan la toma de decisiones sobre almacenes de datos relacionales. Tenga presente que aunque unos tiempos de respuesta competitivos para las consultas de apoyo a la toma de decisiones son esenciales, hay otros requisitos clave que superan el ámbito de este artículo.

Las siguientes son algunas funciones adicionales relacionadas con el almacenamiento de datos relacionales:

• Compatibilidad con la sintaxis MERGE en T-SQL para actualizar, eliminar o insertar (dimensionar) datos con una instrucción y viaje de ida y vuelta a la base de datos.
• Rendimiento de registro optimizado del motor de SQL Server para permitir una ETL más eficaz.
• Conjuntos de agrupamiento para facilitar la escritura de consultas de apoyo a la toma de decisiones agregadas en T-SQL.
• Compresión de copia de seguridad para reducir los requisitos de E/S para copias de seguridad completas e incrementales.
• Gobierno de recursos para controlar la asignación de recursos del sistema a distintas cargas de trabajo.

Le animamos a que consulte información más detallada acerca de todas estas fascinantes características en la página web de SQL Server, disponible en microsoft.com/sql.

Queremos dar las gracias a Boris Baryshnikov, Prem Mehra, Peter Zabback y Shin Zhang por ofrecernos su experiencia técnica.

Sunil Agarwal es administrador jefe de programas en el Grupo de motor de almacenamiento de SQL Server en Microsoft. Es responsable de procesos masivos de importación/exportación, simultaneidad, índices, tempdb, LOBS y compatibilidad.

Torsten Grabs es administrador jefe de programas de motores principales de almacenamiento en el equipo de Microsoft SQL Server. Tiene un doctorado en sistemas de base de datos y 10 años de experiencia en SQL Server.

Dr. Joachim Hammer es administrador de programas en el grupo de procesamiento de consultas de Microsoft. Está especializado en la optimización de consultas para aplicaciones de almacenamiento de datos a gran escala, así como en la formulación distribuida de consultas, ETL y la integración de información.

© 2008 Microsoft Corporation and CMP Media, LLC. Reservados todos los derechos; queda prohibida la reproducción parcial o total sin previa autorización.