sql - optimización - Cambio en el plan de consulta y tiempo de ejecución con TOP y ESCAPE

select top sql server ejemplos

Question

Una de las consultas (dada a continuación) tarda más de 90 segundos en ejecutarse. Devuelve ~ 500 filas de una tabla bastante grande LogMessage. Si se elimina ESCAPE N''~'' de la consulta, se ejecuta en pocos segundos. Del mismo modo, si se quita TOP (1000) , se ejecuta en pocos segundos. El plan de consulta muestra Key Lookup (Clustered) PK_LogMessage, Index Scan (NonClustered) IX_LogMessage and Nested Loops (Inner Join) en el primer caso. Cuando se eliminan las cláusulas ESCAPE N''~'' o TOP (1000) , el plan de consulta cambia y muestra Clustered Index Scan (Clustered) PK_LogMessage . Mientras buscamos agregar más índices (probablemente en ApplicationName), nos gustaría entender qué está sucediendo actualmente.

La consulta se genera desde Entity Framework en caso de que se pregunte por qué se está escribiendo de esta manera. Además, la consulta real es más compleja, pero esta es la versión más corta posible que muestra el mismo comportamiento.

Consulta:

SELECT TOP (1000) 
    [Project1].[MessageID] AS [MessageID], 
    [Project1].[TimeGenerated] AS [TimeGenerated], 
    [Project1].[SystemName] AS [SystemName], 
    [Project1].[ApplicationName] AS [ApplicationName]
FROM
    (
        SELECT
            [Project1].[MessageID] AS [MessageID],
            [Project1].[TimeGenerated] AS [TimeGenerated],
            [Project1].[SystemName] AS [SystemName],
            [Project1].[ApplicationName] AS [ApplicationName]
        FROM
        (
            SELECT 
                [Extent1].[MessageID] AS [MessageID], 
                [Extent1].[TimeGenerated] AS [TimeGenerated], 
                [Extent1].[SystemName] AS [SystemName], 
                [Extent1].[ApplicationName] AS [ApplicationName]
            FROM
                [dbo].[LogMessage] AS [Extent1]
            INNER JOIN
                [dbo].[LogMessageCategory] AS [Extent2]
            ON
                [Extent1].[CategoryID] = [Extent2].[CategoryID]
            WHERE
                ([Extent1].[ApplicationName] LIKE N''%tier%'' ESCAPE N''~'')
        )  AS [Project1]
    )  AS [Project1]
ORDER BY
    [Project1].[TimeGenerated] DESC

Table LogMessage:

CREATE TABLE [dbo].[LogMessage](
    [MessageID] [int] IDENTITY(1000001,1) NOT NULL,
    [TimeGenerated] [datetime] NOT NULL,
    [SystemName] [nvarchar](256) NOT NULL,
    [ApplicationName] [nvarchar](512) NOT NULL,
        [CategoryID] [int] NOT NULL,
 CONSTRAINT [PK_LogMessage] PRIMARY KEY CLUSTERED 
(
    [MessageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[LogMessage]  WITH CHECK ADD CONSTRAINT [FK_LogMessage_LogMessageCategory] FOREIGN KEY([CategoryID])
    REFERENCES [dbo].[LogMessageCategory] ([CategoryID])

ALTER TABLE [dbo].[LogMessage] CHECK CONSTRAINT [FK_LogMessage_LogMessageCategory]

ALTER TABLE [dbo].[LogMessage] ADD  DEFAULT ((100)) FOR [CategoryID]

CREATE NONCLUSTERED INDEX [IX_LogMessage] ON [dbo].[LogMessage] 
(
    [TimeGenerated] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

Table LogMessageCategory:

CREATE TABLE [dbo].[LogMessageCategory](
    [CategoryID] [int] NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Description] [nvarchar](256) NULL,
 CONSTRAINT [PK_LogMessageCategory] PRIMARY KEY CLUSTERED 
(
    [CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Plan de consulta 1 (lleva más de 90 segundos)

Plan de consulta 2 (lleva ~ 3 segundos)

Answer 1

¿Por qué todas estas consultas anidadas? El siguiente código hace el mismo trabajo

        SELECT TOP(1000)
            [Extent1].[MessageID] AS [MessageID], 
            [Extent1].[TimeGenerated] AS [TimeGenerated], 
            [Extent1].[SystemName] AS [SystemName], 
            [Extent1].[ApplicationName] AS [ApplicationName]
        FROM
            [dbo].[LogMessage] AS [Extent1]
        INNER JOIN
            [dbo].[LogMessageCategory] AS [Extent2]
        ON
            [Extent1].[CategoryID] = [Extent2].[CategoryID]
        WHERE
            ([Extent1].[ApplicationName] LIKE N''%tier%'' ESCAPE N''~'')
        ORDER BY [Extent1].[TimeGenerated] DESC

También acepto que ESCAPE N ''~'' podría ser omitido ya que no puedo encontrar ninguna razón para usarlo.

Answer 2

¿Cómo funciona si haces esto?

Select * 
FROM
 (your whole scary framework query with the escape N) a
LIMIT 1000 
(or the mssql alternative if mssql does not support the correct syntax -- )

Porque si eso rueda ... hay una posibilidad de que puedas seguir usando ese framework y obtener un rendimiento decente de sql realmente malo (como ... esto implicaría que creas el rs completo y luego solo seleccionas 1k de él ...).

Answer 3

Esto parece un problema directo de olfateo de parámetros para mí.

Como quiera, TOP 1000 ordenado por TimeGenerated SQL Server puede escanear el índice en TimeGenerated y realizar búsquedas en la tabla base para evaluar el predicado en ApplicationName y detenerse cuando se ha encontrado la fila 1,000 o puede hacer un escaneo de índice agrupado, encuentre todas las filas que coincidan con el predicado ApplicationName y luego hagan un tipo TOP N de estas.

SQL Server mantiene estadísticas en columnas de cadenas. Es más probable que se elija el primer plan si cree que muchas filas terminarán haciendo coincidir el predicado ApplicationName sin embargo, este plan no es realmente adecuado para su reutilización como consulta parametrizada, ya que puede ser catastróficamente ineficiente en el caso de que haya pocas filas partido. Si hay menos de 1000 coincidencias, definitivamente tendrá que hacer tantas búsquedas clave como filas haya en la tabla.

Al probar este extremo, no pude encontrar ninguna situación en la que agregar o quitar ESCAPE redundante alterara las estimaciones de cardinalidad de SQL Server. Por supuesto, cambiar el texto de una consulta parametrizada significa que el plan original no puede ser utilizado sin embargo y necesita compilar uno diferente que probablemente será más adecuado para el valor específico bajo consideración actual.

Answer 4

Para empezar, simplificaría la consulta según lo especificado por @niktrs. Aunque el plan de ejecución parece ignorar las subconsultas, lo hace más amigable para los humanos y, por lo tanto, más fácil de manipular y comprender.

Entonces, tienes un INNER JOIN que me parece que podría desaparecer. ¿Existe una necesidad "real" de INNER JOIN LogMessage para LogMessageCategory? Puede hacer una comprobación rápida utilizando lo siguiente ...

SELECT LM.CategoryID AS FromLogMessage, LMC.CategoryID AS FromLogMessageCategory
FROM dbo.LogMessage AS LM
     FULL OUTER JOIN dbo.LogMessageCategory AS LMC ON LMC.CategoryID = LM.CategoryID
WHERE LM.CategoryID IS NULL OR LMC.CategoryID IS NULL