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

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

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

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.

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.

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

Conceptos