• Artículo

Optimizar consultas con acceso a columnas datetime correlativas

La opción SET de base de datos DATE_CORRELATION_OPTIMIZATION mejora el rendimiento de las consultas que realizan una unión de igualdad de dos tablas cuyas columnas date o datetime estén correlacionadas y especifiquen una restricción de fecha en el predicado de la consulta.

Las tablas cuyos valores de las columnas date o datetime están correlacionados y pueden beneficiarse de la habilitación de DATE_CORRELATION_OPTIMIZATION suelen formar parte de una relación uno a varios y se utilizan principalmente con fines de ayuda a la toma de decisiones, elaboración de informes o almacenamiento de datos.

Por ejemplo, en la base de datos de ejemplo AdventureWorks2008R2, la columna OrderDate de la tabla Purchasing.PurchaseOrderHeader y la columna DueDate de la tabla Purchasing.PurchaseOrderDetail son correlativas. Los valores de fecha de PurchaseOrderDetail.DueDate tienden a seguir de cerca a los valores de PurchaseOrderHeader.OrderDate.

Cuando la opción de base de datos DATE_CORRELATION_OPTIMIZATION está establecida en ON, SQL Server mantiene las estadísticas de correlación entre dos tablas cualesquiera de la base de datos que tengan columnas date o datetime y que estén vinculadas mediante una restricción FOREIGN KEY de una columna. De forma predeterminada, el valor de esta opción es OFF.

SQL Server utiliza estas estadísticas de correlación junto con la restricción de fecha especificada en el predicado de la consulta para deducir que las restricciones adicionales se puedan agregar a la consulta sin cambiar el conjunto de resultados. El optimizador de consultas utiliza estas condiciones deducidas cuando elige un plan de consulta. Es posible que se obtenga un plan de consulta más rápido, porque las restricciones agregadas permiten que SQL Server lea menos datos cuando está procesando la consulta. El rendimiento también mejora cuando ambas tablas tienen índices clúster definidos, y sus columnas date o datetime para las que se mantienen las estadísticas de correlación son la primera o la única clave del índice clúster.

Por ejemplo, suponga que prepara la base de datos AdventureWorks2008R2 para mantener la información de correlación para Purchasing.PurchaseOrderDetail y Purchasing.PurchaseOrderHeader ejecutando el siguiente script Transact-SQL:

USE AdventureWorks2008R2;
GO
-- Create a unique index to take the place of the existing 
-- primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX
IX_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID 
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,PurchaseOrderDetailID);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks2008R2
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

Ahora, suponga que ejecuta la consulta siguiente:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
INNER JOIN Purchasing.PurchaseOrderDetail AS d
    ON h.PurchaseOrderID = d.PurchaseOrderID
WHERE h.OrderDate BETWEEN '20060101' AND '20060201';

Los valores de PurchaseOrderDetail.DueDate devueltos por esta consulta en general pueden estar dentro de un cierto período de días, por ejemplo 14 días, de los valores de PurchaseOrderHeader.OrderDate. Por esto, es posible que SQL Server deduzca que la consulta anterior se puede expresar mejor usando una consulta similar a esta:

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/06' AND '2/1/06'
AND d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14;

La forma exacta de la condición agregada, especificada en la segunda cláusula AND, depende de la consulta original y de los valores de los datos de la base de datos. Una vez agregada una condición implícita, el optimizador la utiliza para generar un plan de ejecución. En este ejemplo, hay un índice clúster en PurchaseOrderDetail.DueDate por lo que el índice puede usarse para recuperar las filas que cumplen d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14. Si hay varios años con datos interesantes en Purchasing.PurchaseOrderDetail, es posible que esta consulta cause un descenso significativo (de varios subconjuntos) en el tiempo de ejecución comparado con la consulta original.

Antes de ejecutar un plan de consulta con una condición que se ha deducido al habilitar DATE_CORRELATION_OPTIMIZATION, SQL Server comprueba que la consulta produzca la respuesta correcta, en función del contenido actual de la base de datos.

Requisitos para el uso de la opción de base de datos DATE_CORRELATION_OPTIMIZATION

Deben cumplirse todas las condiciones siguientes para que las dos tablas se beneficien de habilitar la opción de base de datos DATE_CORRELATION_OPTIMIZATION:

  • Las opciones SET de base de datos deben estar establecidas tal como se indica a continuación. ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL y QUOTED IDENTIFIER deben estar establecidas en ON. NUMERIC_ROUNDABORT debe estar establecida en OFF.

  • Debe haber una relación de clave externa de una sola columna entre las tablas.

  • Ambas tablas deben tener columnas datetime que estén definidas como NOT NULL.

  • Como mínimo, una de las columnas datetime debe ser la columna de clave de un índice clúster (si la clave de índice es compuesta, debe ser la primera clave), o bien debe ser la columna de partición, si se trata de una tabla con particiones.

  • Ambas deben ser propiedad del mismo usuario.

Tenga en cuenta lo siguiente cuando establezca la opción de base de datos DATE_CORRELATION_OPTIMIZATION en ON:

  • SQL Server mantiene la información de correlación en forma de estadísticas. SQL Server actualiza estas estadísticas durante las operaciones INSERT, UPDATE y DELETE en las tablas aplicables, lo que puede afectar al rendimiento de estas operaciones. No debería habilitar DATE_CORRELATION_OPTIMIZATION en entornos de base de datos con muchas actualizaciones.

  • Si alguna de las columnas datetime para las que se mantienen las estadísticas de correlación no es la primera o única clave de un índice clúster, considere la posibilidad de crear un índice clúster en ella. Así generalmente se consigue un mejor rendimiento en los tipos de consultas afectados por las estadísticas de correlación. Si ya existe un índice clúster en las columnas de clave principal, puede modificar una tabla para que el índice clúster y la clave principal utilicen conjuntos de columnas diferentes.

  • Habilitar DATE_CORRELATION_OPTIMIZATION no proporciona ninguna ventaja en las situaciones siguientes:

    • No hay ningún par de tablas que cumpla los criterios mencionados anteriormente para mantener las estadísticas de correlación.

    • Hay pares de tablas que cumplen los criterios para mantener las estadísticas de correlación, pero las consultas que combinan estas tablas no especifican ninguna restricción de fecha en sus predicados.

Para establecer la opción de base de datos DATE_CORRELATION_OPTIMIZATION

Trabajar con estadísticas de correlación

Para todos los pares posibles de tablas coincidentes, se crean estadísticas de correlación de forma automática en forma de vistas indizadas cuando se establece la opción de base de datos DATE_CORRELATION_OPTIMIZATION en ON. Cuando el optimizador de consultas de SQL Server puede aprovechar la correlación entre pares de columnas datetime, utiliza estas estadísticas de correlación en su plan de consulta. Las estadísticas de correlación también se incluyen en la lógica de las instrucciones INSERT, UPDATE y DELETE que les afectan. Los nombres de las estadísticas de correlación tienen la forma siguiente: 

MPStats_Sys<constraint_object_id><GUID><FK_constraint_name>

<FK_constraint_name> es el nombre de la restricción FOREIGN KEY de la vista de catálogo sys.objects en la que se basa la coincidencia de datetime. <constraint_object_id> es una representación hexadecimal de 8 dígitos del id. de objeto de la restricción FOREIGN KEY.

Nota

SQL Server abrevia la parte FK_constraint_ del nombre de las estadísticas de correlación si el nombre supera el límite de longitud del identificador.

Al ejecutar una consulta mediante SET SHOWPLAN XML, los nodos de filtro derivados de las estadísticas de correlación incluyen el atributo siguiente:

DateCorrelationOptimization="true"

Por ejemplo, un nodo <Predicate> influido por las estadísticas de correlación tiene el siguiente aspecto:

<Predicate DateCorrelationOptimization="true">

Este atributo se incluye en todos los nodos de filtro generados completamente a partir de las estadísticas de correlación, o combinando un predicado influido por las estadísticas de correlación con algún otro predicado.

En general, cuando la opción de base de datos DATE_CORRELATION_OPTIMIZATION está establecida en ON, SQL Server crea estadísticas de correlación para todos los pares posibles de columnas datetime. SQL Server crea estadísticas de correlación adicionales cuando se realiza lo siguiente:

  • Crea restricciones FOREIGN KEY mediante CREATE TABLE o ALTER TABLE que cumplen los requisitos de optimización de la correlación datetime.

  • Crea un índice clúster en una columna datetime y esa columna se puede seleccionar para la coincidencia de correlación con la columna datetime de otra tabla.

    Nota

    No se crean estadísticas de correlación al crear índices clúster con la opción ONLINE = ON. Sin embargo, después de confirmarse la generación del índice, se pueden crear estadísticas de correlación que dependen del índice como resultado de un evento de otra transacción, como la creación de una restricción FOREIGN KEY.

  • Cambia la capacidad de nulabilidad o el tipo de datos de una columna para que se pueda seleccionar para la coincidencia de correlación con la columna datetime de otra tabla.

No debería hacer referencia a las estadísticas de correlación directamente en las aplicaciones, porque SQL Server puede decidir quitarlas en cualquier momento. Puede decidir quitar estadísticas de correlación individuales si determina que el costo de mantenerlas afecta al rendimiento. El valor predeterminado de los permisos DROP en las estadísticas de correlación se establece en los miembros del rol fijo de servidor sysadmin, los roles fijos de base de datos db_owner y db_ddladmin y el propietario del par de tablas en el que se definen las estadísticas de correlación. Estos permisos no se pueden transferir.

Las estadísticas de correlación se quitan en las situaciones siguientes:

  • Cuando se establece la opción de base de datos DATE_CORRELATION_OPTIMIZATION en OFF, se quitan todas las estadísticas de correlación creadas por SQL Server.

  • Se quitan las estadísticas de correlación que requieren mantener un almacenamiento excesivo o que no se prevé que sean beneficiosas.

  • Cuando se quita una restricción FOREIGN KEY mediante DROP TABLE o ALTER TABLE, se quitan todas las estadísticas de correlación asociadas a esa restricción.

  • Cuando una operación provoca que las tablas que participan en la coincidencia de correlación dejen de pertenecer al mismo usuario, se quitan las estadísticas de correlación correspondientes.

  • Cuando se ejecuta una instrucción ALTER TABLE…SWITCH y la tabla de origen o la tabla de destino tiene estadísticas de correlación definidas, se quitan estas estadísticas de correlación.

  • Cuando se crea un índice clúster en una columna datetime y se generan estadísticas de correlación en una columna datetime diferente de la misma tabla, se quitan las estadísticas de correlación. SQL Server puede crear estadísticas de correlación basadas en el índice clúster recién creado, si está disponible.

  • Cuando se quita un índice clúster cuya clave de índice inicial es una columna datetime, se quitan las estadísticas de correlación asociadas si existe otra columna datetime en la misma tabla donde se pueden crear nuevas estadísticas de correlación.

  • Cuando se ejecuta ALTER TABLE para cambiar el tipo de datos o la nulabilidad de una columna que participa en las estadísticas de correlación, se quitan esas estadísticas.

Las estadísticas de correlación se crean o se quitan como parte de la misma transacción que provocó que se crearan o se quitaran. Esta transacción no se realiza en línea ni es asincrónica.

Cuando se utiliza el Asistente para la optimización de motor de base de datos en un escenario de optimización simple basado en un servidor para optimizar directamente el servidor de producción, este asistente tiene en cuenta los costos y los beneficios de las estadísticas de correlación. Sin embargo, cuando se utiliza el Asistente para la optimización de motor de base de datos en un escenario de servidor de producción de prueba, este asistente no tiene en cuenta las estadísticas de correlación como objetos internos del sistema. Por lo tanto, el Asistente para la optimización de motor de base de datos no utiliza las estadísticas de correlación en la optimización de consultas durante su análisis de optimización de índices. En un escenario de producción de prueba, es posible que desee omitir las recomendaciones que haga el Asistente para la optimización de motor de base de datos sobre las vistas indizadas que mantienen las estadísticas de correlación, porque este asistente conoce sus costos, pero no sus ventajas. En ambos escenarios, es posible que el Asistente para la optimización de motor de base de datos no recomiende la selección de ciertos índices, como los índices clúster en las columnas datetime, lo que sería beneficioso cuando la opción DATE_CORRELATION_OPTIMIZATION está habilitada.

Consultar metadatos acerca de las estadísticas de correlación

Para ver el valor de la opción de base de datos DATE_CORRELATION_OPTIMIZATION, seleccione la columna is_date_correlation_on de la vista de catálogo sys.databases.

Para determinar si una vista se basa en las estadísticas de correlación, seleccione la columna is_date_correlation_view de la vista de catálogo sys.views.