sql server - resueltos - Optimización de los planes de ejecución para consultas de T-SQL parametrizadas que contienen funciones de ventana

procedimientos almacenados sql server pdf

Question

EDITAR: Actualicé el código de ejemplo y proporcioné implementaciones completas de tablas y vistas para referencia, pero la pregunta esencial permanece sin cambios.

Tengo una vista bastante compleja en una base de datos que estoy intentando consultar. Cuando intento recuperar un conjunto de filas de la vista mediante la codificación rígida de la cláusula WHERE a valores de clave externa específicos, la vista se ejecuta muy rápidamente con un plan de ejecución óptimo (los índices se utilizan correctamente, etc.)

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20

Sin embargo, cuando intento agregar parámetros a la consulta, de repente, mi plan de ejecución se desmorona. Cuando ejecuto la consulta a continuación, obtengo escaneos de índice en lugar de búsquedas por todas partes y el rendimiento de la consulta es muy bajo.

DECLARE @ForeignKeyCol int = 20

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol 

Estoy usando SQL Server 2008 R2. ¿Qué da aquí? ¿De qué se trata el uso de parámetros que están causando un plan subóptimo? Cualquier ayuda sería muy apreciada.

Para referencia, aquí están las definiciones de objetos para las que recibo el error.

CREATE TABLE [dbo].[BaseTable]
(
    [PrimaryKeyCol] [uniqueidentifier] PRIMARY KEY,
    [ForeignKeyCol] [int] NULL,
    [DataCol] [binary](1000) NOT NULL
)

CREATE NONCLUSTERED INDEX [IX_BaseTable_ForeignKeyCol] ON [dbo].[BaseTable]
(
    [ForeignKeyCol] ASC
)

CREATE VIEW [dbo].[ViewOnBaseTable]
AS
SELECT
    PrimaryKeyCol,
    ForeignKeyCol,
    DENSE_RANK() OVER (PARTITION BY ForeignKeyCol ORDER BY PrimaryKeyCol) AS ForeignKeyRank,
    DataCol
FROM
    dbo.BaseTable

Estoy seguro de que la función de la ventana es el problema, pero estoy filtrando mi consulta por un solo valor por el que la función de la ventana está particionando, por lo que esperaría que el optimizador filtre primero y luego ejecute la función de la ventana. Lo hace en el ejemplo codificado, pero no en el ejemplo parametrizado. A continuación se muestran los dos planes de consulta. El plan superior es bueno y el plan inferior es malo.

Answer 1

Creo que en este caso particular puede deberse a que los tipos de datos entre sus parámetros y su tabla no coinciden exactamente, por lo que SQL Server tiene que hacer una conversión implícita que no es una operación sargable.

Compruebe los tipos de datos de su tabla y haga que sus parámetros sean del mismo tipo. O haz el elenco tú mismo fuera de la consulta.

Answer 2

Cuando utilice la OPTION (RECOMPILE) asegúrese de mirar el plan posterior a la ejecución ("real") en lugar del plan anterior a la ejecución ("estimado"). Algunas optimizaciones solo se aplican cuando ocurre la ejecución:

DECLARE @ForeignKeyCol int = 20;

SELECT ForeignKeyCol, ForeignKeyRank
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol
OPTION (RECOMPILE);

Plan de pre-ejecución:

Plan post ejecución:

Probado en SQL Server 2012 compilación 11.0.3339 y SQL Server 2008 R2 compilación 10.50.4270

Antecedentes y limitaciones

Cuando se agregaron funciones de ventanas en SQL Server 2005, el optimizador no tenía forma de pasar las selecciones más allá de estas nuevas proyecciones de secuencia. Para abordar algunos escenarios comunes en los que esto causó problemas de rendimiento, SQL Server 2008 agregó una nueva regla de simplificación, SelOnSeqPrj , que permite la SelOnSeqPrj selecciones adecuadas donde el valor es una constante. Esta constante puede ser un literal en el texto de consulta, o el valor OPTION (RECOMPILE) de un parámetro obtenido a través de OPTION (RECOMPILE) . No hay ningún problema en particular con los NULLs aunque es posible que la consulta deba tener ANSI_NULLS OFF para ver esto. Que yo sepa, aplicar la simplificación a valores constantes es una limitación de la implementación; no hay ninguna razón particular por la que no se pueda extender para trabajar con variables. Mi recuerdo es que la regla SelOnSeqPrj los problemas de rendimiento más comunes.

Parametrización

La regla SelOnSeqPrj no se aplica cuando una consulta se parametriza automáticamente con éxito . No hay una manera confiable de determinar si una consulta se parametrizó automáticamente en SSMS, solo indica que se intentó realizar un auto-parámetro. Para ser claros, la presencia de marcadores de posición como [@0] solo muestra que se intentó la parametrización automática. Una forma confiable de saber si un plan preparado se almacenó en la memoria caché para reutilizarlo es inspeccionar el caché del plan, donde el ''identificador del plan parametrizado'' proporciona el vínculo entre los planes preparados y ad-hoc.

Por ejemplo, la siguiente consulta parece ser auto-parametrizada en SSMS:

SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;

Pero el caché del plan muestra lo contrario:

WITH XMLNAMESPACES
(
    DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan''
)
SELECT
    parameterized_plan_handle =
        deqp.query_plan.value(''(//StmtSimple)[1]/@ParameterizedPlanHandle'', ''nvarchar(64)''), 
    parameterized_text =
        deqp.query_plan.value(''(//StmtSimple)[1]/@ParameterizedText'', ''nvarchar(max)''),
    decp.cacheobjtype,
    decp.objtype,
    decp.plan_handle
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
WHERE
    dest.[text] LIKE N''%ViewOnBaseTable%''
    AND dest.[text] NOT LIKE N''%dm_exec_cached_plans%'';

Si la opción de la base de datos para la parametrización forzada está habilitada, obtenemos un resultado parametrizado, donde no se aplica la optimización:

ALTER DATABASE Sandpit SET PARAMETERIZATION FORCED;
DBCC FREEPROCCACHE;

SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20;

La consulta de caché del plan ahora muestra un plan en caché parametrizado, vinculado por el identificador del plan parametrizado:

Solución

Siempre que sea posible, mi preferencia es volver a escribir la vista como una función de valores de tabla en línea, donde la posición deseada de la selección se puede hacer más explícita (si es necesario):

CREATE FUNCTION dbo.ParameterizedViewOnBaseTable
    (@ForeignKeyCol integer)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT
        bt.PrimaryKeyCol,
        bt.ForeignKeyCol,
        ForeignKeyRank = DENSE_RANK() OVER (
            PARTITION BY bt.ForeignKeyCol 
            ORDER BY bt.PrimaryKeyCol),
        bt.DataCol
    FROM dbo.BaseTable AS bt
    WHERE
        bt.ForeignKeyCol = @ForeignKeyCol;

La consulta se convierte en:

DECLARE @ForeignKeyCol integer = 20;
SELECT pvobt.*
FROM dbo.ParameterizedViewOnBaseTable(@ForeignKeyCol) AS pvobt;

Con el plan de ejecución:

Answer 3

Siempre se puede ir por el camino de la CRUZ.

ALTER VIEW [dbo].[ViewOnBaseTable]
AS
SELECT
    PrimaryKeyCol,
    ForeignKeyCol,
    ForeignKeyRank,
    DataCol
FROM (
    SELECT DISTINCT
        ForeignKeyCol
    FROM dbo.BaseTable
) AS Src
CROSS APPLY (
    SELECT
        PrimaryKeyCol,
        DENSE_RANK() OVER (ORDER BY PrimaryKeyCol) AS ForeignKeyRank,
        DataCol
    FROM dbo.BaseTable AS B
    WHERE B.ForeignKeyCol = Src.ForeignKeyCol
) AS X