Mejoras de procesamiento de consultas en las tablas e índices con particiones

SQL Server 2008 mejora el rendimiento del procesamiento de las consultas sobre tablas con particiones en muchos planes paralelos, cambia la forma de representación de los planes paralelos y en serie y mejora la información sobre el particionamiento que los planes de ejecución en tiempo de compilación y en tiempo de ejecución proporcionan. En este tema se describen estas mejoras y se proporcionan consejos sobre la interpretación de los planes de ejecución de consultas sobre tablas e índices con particiones, así como las prácticas recomendadas para la mejora del rendimiento de las consultas en objetos con particiones.

Nota

Las tablas e índices con particiones solo se admiten en las ediciones Enterprise, Developer y Evaluation de SQL Server.

Nueva operación de búsqueda orientada a particiones

En SQL Server 2008, la representación interna de una tabla con particiones se ha cambiado con el fin de que el procesador de consultas vea la tabla como un índice de varias columnas con PartitionID como columna inicial. PartitionID es una columna calculada oculta, utilizada internamente para representar el identificador de la partición que contiene una determinada fila. Por ejemplo, suponga que la tabla T, definida como T(a, b, c), tiene una partición en la columna a y un índice clúster en la columna b. En SQL Server 2008, esta tabla con particiones se trata internamente como una tabla sin particiones con el esquema T(PartitionID, a, b, c) y un índice clúster en la clave compuesta (PartitionID, b). De esta manera, el optimizador de consultas puede realizar operaciones de búsqueda basadas en PartitionID sobre cualquier tabla o índice con particiones.

Ahora, la eliminación de particiones se lleva a cabo durante esta operación de búsqueda.

Además, el optimizador de consultas se ha ampliado de manera que puede realizarse una operación de búsqueda o examen con una condición sobre PartitionID (como la columna inicial lógica) y posiblemente otras columnas de clave de índice, para a continuación realizar una búsqueda de segundo nivel con una segunda condición sobre una o más columnas adicionales para cada valor distinto devuelto por la operación de búsqueda de primer nivel. De esta manera, esta operación, denominada búsqueda selectiva, permite al optimizador de consultas realizar una operación de búsqueda o examen en función de una condición con el fin de determinar las particiones a las cuales se va a obtener acceso junto con una operación Index Seek de segundo nivel en el seno de ese operador, que devolverá las filas de las particiones que cumplan con una condición diferente. Por ejemplo, considere la siguiente consulta.

SELECT * FROM T WHERE a < 10 and b = 2;

En este ejemplo, suponga que la tabla T, definida como T(a, b, c), tiene una partición en la columna a y un índice clúster en la columna b. La siguiente función define los límites de la partición para la tabla T:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Para llevar a cabo la consulta, el procesador de consultas realiza una operación de búsqueda de primer nivel para localizar todas las particiones que cumplan con la condición T.a < 10. Se identifican así las particiones a las cuales se va a tener acceso. A continuación, el procesador lleva a cabo en cada partición identificada una búsqueda de segundo nivel sobre el índice clúster de la columna b con el fin de localizar las filas que cumplan con la condición T.b = 2 y T.a < 10.

La siguiente ilustración es una representación lógica de la operación de búsqueda selectiva. En ella se muestra la tabla T con datos en las columnas a y b. Las particiones están numeradas del 1 al 4; las líneas discontinuas representan los límites de las particiones. Una operación de búsqueda de primer nivel en el particionamiento (no representado en la ilustración) ha determinado que las particiones 1, 2 y 3 cumplen con la condición de búsqueda impuesta por el particionamiento definido para la tabla y con el predicado de la columna a. Es decir, T.a < 10. La línea curva representa el camino recorrido por la fase de búsqueda de segundo nivel de la operación de búsqueda selectiva. Básicamente, la operación de búsqueda selectiva realiza en cada una de esas particiones una búsqueda de las filas que cumplan con la condición b = 2. El costo total de la operación de búsqueda selectiva es el mismo que el de tres búsquedas por índices independientes.

Visualización de la información del particionamiento en los planes de ejecución de consultas

Los planes de ejecución de consultas en tablas e índices con particiones pueden examinarse utilizando las instrucciones SET de Transact-SQL SET SHOWPLAN_XML o SET STATISTICS XML, o bien mediante la salida gráfica del plan de ejecución en SQL Server Management Studio. Por ejemplo, puede ver el plan de ejecución en tiempo de compilación haciendo clic en Mostrar plan de ejecución estimado en la barra de herramientas del editor de consultas y el plan en tiempo de ejecución haciendo clic en Incluir plan de ejecución real.

Estas herramientas le proporcionarán la siguiente información:

• Las operaciones tales como exámenes, búsquedas, inserciones, actualizaciones, mezclas y eliminaciones que tienen acceso a las tablas o índices con particiones.

• Las particiones a las que tiene acceso la consulta. Por ejemplo, el número total de particiones a las que se ha tenido acceso y los intervalos de particiones contiguas a los que se ha tenido acceso están disponibles en los planes de ejecución en tiempo de ejecución.

• Cuándo se utiliza la operación de búsqueda selectiva en una operación de búsqueda o de recorrido para recuperar datos de una o más particiones.

Para obtener más información sobre la visualización de los planes de ejecución, vea Temas de procedimientos de planes de ejecución.

Mejoras en la información sobre particiones

SQL Server 2008 proporciona una mejor información acerca del particionamiento tanto para los planes de ejecución en tiempo de compilación como para los planes de ejecución en tiempo de ejecución. Los planes de ejecución proporcionan ahora la siguiente información:

• Un atributo opcional Partitioned que indica que se está llevando a cabo una operación (por ejemplo búsqueda, examen, inserción, actualización, mezcla o eliminación) sobre una tabla con particiones.

• Un nuevo elemento SeekPredicateNew con un subelemento SeekKeys que incluye PartitionID como condiciones iniciales de la columna de clave de índice y del filtro que especifican las búsquedas de intervalo sobre PartitionID. La presencia de dos subelementos SeekKeys indica el uso de una operación de búsqueda selectiva sobre PartitionID.

• Información de resumen que proporciona el número total de particiones a las que se ha tenido acceso. Esta información está disponible únicamente en los planes en tiempo de ejecución.

Para ver cómo aparece esta información en la salida gráfica tanto del pan de ejecución como del plan de XML Showplan , considere la siguiente consulta sobre la tabla con particiones fact_sales. Esta consulta actualiza datos en dos particiones.

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

La siguiente ilustración muestra las propiedades del operador Clustered Index Seek en el plan de ejecución en tiempo de compilación para esta consulta. Para ver la definición de la tabla fact_sales y la definición de la partición, vea el “Ejemplo” en este tema.

Atributo Partitioned

Cuando un operador tal como un Index Seek se ejecuta sobre una tabla o índice con particiones, el atributo Partitioned aparece en los planes de tiempo de compilación y de ejecución y tiene asignado el valor True (1). El atributo no se muestra cuando su valor es False (0).

El atributo Partitioned puede aparecer en los siguientes operadores físicos y lógicos:

• Table Scan

• Index Scan

• Index Seek

• Insert

• Update

• Delete

• Merge

Como puede apreciarse en la ilustración previa, este atributo se muestra en las propiedades del operador en el que está definido. En la salida de XML Showplan, este atributo aparece como Partitioned="1" en el nodo RelOp del operador en el que está definido.

Predicado de nueva búsqueda

En la salida de XML Showplan, el elemento SeekPredicateNew aparece en el operador en el que está definido. Puede contener hasta un máximo de dos apariciones del subelemento SeekKeys. El primer elemento SeekKeys especifica la operación de búsqueda de primer nivel a nivel de identificador de partición del índice lógico. Es decir, esta búsqueda determina las particiones a las que se debe tener acceso para satisfacer las condiciones de la consulta. El segundo elemento SeekKeys especifica la parte correspondiente a la búsqueda de segundo nivel de la operación de búsqueda selectiva que se produce en cada partición identificada en la búsqueda de primer nivel.

Información de resumen de particiones

En los planes de ejecución de tiempo de ejecución, la información de resumen de particiones proporciona el número total y la identidad de las particiones a las que se ha tenido acceso. Esta información puede utilizarse para comprobar que la consulta tiene acceso a las particiones correctas y que todas las demás particiones no se consideran.

Se proporciona la siguiente información: Actual Partition Count y Partitions Accessed.

Actual Partition Count es el número total de particiones a las que la consulta ha tenido acceso.

Partitions Accessed, en la salida de XML Showplan, es la información de resumen de particiones que aparece en el nuevo elemento RuntimePartitionSummary del nodo RelOp del operador en el que está definido. El siguiente ejemplo muestra los contenidos del elemento RuntimePartitionSummary, que indica que se ha tenido acceso a un total de dos particiones (particiones 2 y 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Visualización de la información de la partición mediante otros métodos de Showplan

Los métodos de Showplan SHOWPLAN_ALL, SHOWPLAN_TEXT y STATISTICS PROFILE no proporcionan la información de la partición explicada en este tema, con la siguiente excepción. Como parte del predicado SEEK, las particiones a las que se va a tener acceso están identificadas por un predicado de intervalo definido sobre la columna calculada que representa el identificador de la partición. El siguiente ejemplo muestra el predicado SEEK para un operador Clustered Index Seek. Se obtiene acceso a las particiones 2 y 3, y el operador de búsqueda filtra las filas que cumplen con la condición date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))

                ORDERED FORWARD)

Interpretación de planes de ejecución para montones con particiones

En SQL Server 2008, un montón con particiones se trata como un índice lógico sobre el identificador de partición. La eliminación de una partición en un montón con particiones se representa en un plan de ejecución como un operador Table Scan con un predicado SEEK sobre el identificador de partición. El siguiente ejemplo muestra la información de Showplan proporcionada:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpretación de planes de ejecución para combinaciones colocadas

La combinación colocada puede darse cuando dos tablas presentan particiones que usan funciones de particionamiento iguales o equivalentes y las columnas de partición de ambos lados de la combinación se referencian en la condición de combinación de la consulta. El optimizador de consultas puede generar un plan en el que las particiones de cada tabla que tengan los mismos identificadores de partición se combinen de forma independiente. Las combinaciones colocadas pueden ser más rápidas que las no colocadas, puesto que requieren menos memoria y tiempo de procesamiento. El optimizador elige un plan no colocado o colocado en función de las estimaciones de costos.

En un plan colocado, la combinación Nested Loops lee una o varias particiones de índice o tabla combinada de la parte interna. Los números en el interior de los operadores Constant Scan representan los números de partición.

Cuando se generan planes paralelos para combinaciones colocadas para índices o tablas con particiones, aparece un operador Parallelism entre los operadores de combinación Constant Scan y Nested Loops. En este caso, los distintos subprocesos de la parte exterior de la combinación leen y trabajan en una partición diferente.

La siguiente ilustración muestra un plan de consulta paralela para una combinación colocada.

Estrategia de ejecución de consulta paralela para objetos con particiones

El procesador de consultas utiliza una estrategia de ejecución paralela para consultas que seleccionan en objetos con particiones. Como parte de la estrategia de ejecución, el procesador de consultas determina las particiones de tabla requeridas para la consulta y la proporción de subprocesos que se han de asignar a cada partición. En la mayoría de los casos, el procesador de consultas asigna a cada partición un número igual, o casi igual, de subprocesos y, a continuación, ejecuta la consulta en paralelo por las particiones. Los párrafos siguientes explican la asignación de subprocesos con más detalle.

Si el número de subprocesos es menor que el número de particiones, el procesador de consultas asigna cada subproceso a una partición diferente, dejando inicialmente una o más particiones sin un subproceso asignado. Cuando un subproceso termina de ejecutarse en una partición, el procesador de consultas lo asigna a la partición siguiente hasta que cada partición tenga asignado un solo subproceso. Este es el único caso en el que el procesador de consultas reasigna subprocesos a otras particiones.

Si el número de subproceso es igual al número de particiones, el procesador de consultas asigna un subproceso a cada partición. Cuando un subproceso finaliza, no se reasigna a otra partición.

Si el número de subproceso es mayor que el número de particiones, el procesador de consultas asigna un número igual de subprocesos a cada partición. Si el número de subprocesos no es un múltiplo exacto del número de particiones, el procesador de consultas asigna un subproceso adicional a algunas particiones para que se utilicen todos los subprocesos disponibles. Tenga en cuenta que si solo hay una partición, todos los subprocesos se asignarán a esa partición. En el diagrama siguiente hay cuatro particiones y 14 subprocesos. Cada partición tiene asignados 3 subprocesos, y dos particiones tienen un subproceso adicional, para un total de 14 asignaciones de subprocesos. Cuando un subproceso finaliza, no se reasigna a otra partición.

Aunque los ejemplos anteriores sugieren una manera sencilla de asignar subprocesos, la estrategia real es más compleja y considera otras variables que se producen durante la ejecución de la consulta. Por ejemplo, si la tabla tiene particiones y un índice clúster en la columna A, y una consulta tiene la cláusula de predicado WHERE A IN (13, 17, 25),, el procesador de consultas asignará uno o más subproceso a cada uno de estos tres valores de búsqueda (A=13, A=17 y A=25) en lugar de cada partición de tabla. Solo es necesario ejecutar la consulta en las particiones que contienen estos valores, y si se da la circunstancia de que todos estos predicados de búsqueda están en la misma partición de tabla, todos los subprocesos se asignarán a la misma partición de tabla.

He aquí otro ejemplo. Suponga que la tabla tiene cuatro particiones en la columna A con puntos de límite (10, 20, 30), un índice en la columna B y la consulta tiene una cláusula de predicado WHERE B IN (50, 100, 150). Dado que las particiones de tabla se basan en los valores de A, los valores de B pueden producirse en cualquiera de las particiones de tabla. Por lo tanto, el procesador de consultas buscará cada uno de los tres valores de B (50, 100, 150) en cada una de las cuatro particiones de tabla. El procesador de consultas asignará subprocesos proporcionalmente para poder ejecutar cada uno de estos 12 exámenes de consulta en paralelo.

Particiones de tabla basadas en la columna A	Busca la columna B en cada partición de tabla
Partición de tabla 1: A < 10	B=50, B=100, B=150
Partición de tabla 2: A >= 10 y A < 20	B=50, B=100, B=150
Partición de tabla 3: A >= 20 y A < 30	B=50, B=100, B=150
Partición de tabla 4: A >= 30	B=50, B=100, B=150

Prácticas recomendadas

Para mejorar el rendimiento de las consultas que tienen acceso a una cantidad grande de datos de tablas e índices grandes con particiones, recomendamos las siguientes prácticas:

• Distribuya cada partición entre varios discos.

• Cuando sea posible, utilice un servidor con memoria principal suficiente para alojar en ella las particiones con una mayor frecuencia de acceso o todas las particiones con el fin de reducir el costo de la E/S.

• Si no es posible alojar en memoria los datos que está consultando, comprima las tablas e índices. De esta manera se reducirá el costo de la E/S.

• Utilice un servidor con procesadores rápidos y tantos núcleos de procesador como pueda permitirse, con el fin de sacar partido de la capacidad de procesamiento de las consultas en paralelo.

• Asegúrese de que el servidor dispone de un ancho banda de controlador de E/S suficiente.

• Cree un índice clúster en cada tabla grande con particiones para sacar partido de las optimizaciones de examen de los árboles B.

• Siga las prácticas recomendadas en las notas del producto "Carga de grandes volúmenes de datos en una tabla con particiones“ cuando cargue grandes volúmenes de datos en tablas con particiones.

Ejemplo

El ejemplo siguiente crea una base de datos de prueba que contiene una única tabla con siete particiones. Utilice las herramientas descritas previamente al ejecutar las consultas de este ejemplo para ver la información del particionamiento para los planes de tiempo de compilación y los de tiempo de ejecución.

Nota

Este ejemplo inserta más de 1 millón de filas en la tabla. En función de su hardware, la ejecución de este ejemplo puede tomar varios minutos. Antes de ejecutar este ejemplo, compruebe que tiene más de 1,5 GB de espacio en disco disponible.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

Vea también

Referencia

Mostrar planes de ejecución gráficos (SQL Server Management Studio)

DBCC TRACEON (Transact-SQL)

Conceptos

Tablas e índices con particiones

Mostrar planes de ejecución mediante las opciones SET del plan de presentación (Transact-SQL)