¿Algún consejo sobre cómo puedo mejorar la velocidad de esta consulta SQL? [cerrado]

Cualquier consejo sobre cómo puedo mejorar la velocidad de esta consulta sería útil. Intenté forzar planes en el servidor SQL, lo que ayuda temporalmente, pero parece que la velocidad se ralentiza después de un par de semanas/meses. ¿Algún consejo sobre cómo puedo implementar una solución más permanente? Cualquier tema general que deba examinar o tablas específicas que deba modificar ayudaría. Todas las opciones de optimización son bienvenidas.

Hay dos partes. Aquí está la consulta externa que llama a una función.

@topAmount y @offsetAmountambos están configurados a un máximo de 1000

(@environmentid nvarchar(4000), @TestStepId uniqueidentifier, @topAmount int, @offsetAmount int)
SELECT 
    [t].[Id], 
    [t].[AdditionalContext], 
    [t].[AltResultId], 
    [t].[AnalysisConfiguration], 
    [t].[AnalysisRequestId], 
    [t].[CreatedBy], 
    [t].[CreatedTime], 
    [t].[CustomNotifyAliases], 
    [t].[EndDateTime], 
    [t].[EndUnit], 
    [t].[ExecutionConfiguration], 
    [t].[TestAnalysisTypeId], 
    [t].[TestStepId], 
    [t].[TestationGroupId], 
    [t].[ForayAppVisualLink], 
    [t].[IsUtc], 
    [t].[LastUpdatedBy], 
    [t].[LastUpdatedTime], 
    [t].[RoundingRuleOverrideId], 
    [t].[ScheduleTime], 
    [t].[ResultAvailableTime], 
    [t].[ResultId], 
    [t].[ResultNotificationEnabled], 
    [t].[StartDateTime], 
    [t].[StartUnit], 
    [t].[StateId], 
    [t].[StudyAdditionalContext], 
    [t].[StudyId], 
    [t].[StudyRoundingRuleId], 
    [t].[TaskId], 
    [t].[TimeUnitId], 
    [t].[EnvironmentId], 
    [e].[Id], 
    [e].[Name], 
    [t0].[Id], 
    [t0].[Name], 
    [a0].[Id], 
    [a0].[FriendlyName], 
    [a0].[Name], 
    [d].[Id], 
    [d].[Name], 
    [d].[SkipMinutes], 
    [d].[StartTime], 
    [d0].[Id], 
    [d0].[Name], 
    [d0].[SkipMinutes], 
    [d0].[StartTime]
FROM (
    SELECT 
        [a].[Id], 
        [a].[AdditionalContext], 
        [a].[AltResultId], 
        [a].[AnalysisConfiguration], 
        [a].[AnalysisRequestId], 
        [a].[CreatedBy], 
        [a].[CreatedTime], 
        [a].[CustomNotifyAliases], 
        [a].[EndDateTime], 
        [a].[EndUnit], 
        [a].[ExecutionConfiguration], 
        [a].[TestAnalysisTypeId], 
        [a].[TestStepId], 
        [a].[TestationGroupId], 
        [a].[ForayAppVisualLink], 
        [a].[IsUtc], 
        [a].[LastUpdatedBy], 
        [a].[LastUpdatedTime], 
        [a].[RoundingRuleOverrideId], 
        [a].[ScheduleTime], 
        [a].[ResultAvailableTime], 
        [a].[ResultId], 
        [a].[ResultNotificationEnabled], 
        [a].[StartDateTime], 
        [a].[StartUnit], 
        [a].[StateId], 
        [a].[StudyAdditionalContext], 
        [a].[StudyId], 
        [a].[StudyRoundingRuleId], 
        [a].[TaskId], 
        [a].[TimeUnitId], 
        [a].[EnvironmentId]
    FROM (
        SELECT * FROM [dbo].[fn_AnalysisTaskByEnvironment](@environmentid)
    ) AS [a]
    WHERE ([a].[TestStepId] = @TestStepId) AND [a].[ResultAvailableTime] IS NOT NULL
    ORDER BY [a].[Id] DESC
    OFFSET @offsetAmount ROWS FETCH NEXT @topAmount ROWS ONLY
) AS [t]
INNER JOIN [dbo].[TestAnalysisType] AS [e] ON [t].[TestAnalysisTypeId] = [e].[Id]
INNER JOIN [dbo].[TimeUnit] AS [t0] ON [t].[TimeUnitId] = [t0].[Id]
LEFT JOIN [dbo].[AnalysisTaskState] AS [a0] ON [t].[StateId] = [a0].[Id]
LEFT JOIN [dbo].[DateRangeRoundingRule] AS [d] ON [t].[StudyRoundingRuleId] = [d].[Id]
LEFT JOIN [dbo].[DateRangeRoundingRule] AS [d0] ON [t].[RoundingRuleOverrideId] = [d0].[Id]
ORDER BY [t].[Id] DESC OPTION (OPTIMIZE FOR (@environmentid = 'environment id'))

Esquemas para las tablas en la parte inferior de la consulta.

Tipo de análisis de prueba

COLUMN_NAME DATA_TYPE   CHAR_MAX_LENGTH   IS_NULLABLE   KEY
Id          int         NULL              NO            PRIMARY
Name        nvarchar    50                NO            UNIQUE

Unidad de tiempo

COLUMN_NAME DATA_TYPE   CHAR_MAX_LENGTH   IS_NULLABLE   KEY
Id          int         NULL              NO            PRIMARY
Name        nvarchar    50                NO            UNIQUE

AnálisisTareaEstado

COLUMN_NAME     DATA_TYPE   CHAR_MAX_LENGTH   IS_NULLABLE   KEY
Id              int         NULL              NO            PRIMARY
Name            nvarchar    50                NO            UNIQUE
FriendlyName    nvarchar    50                NO

Regla de redondeo de intervalo de fechas

COLUMN_NAME     DATA_TYPE   CHAR_MAX_LENGTH   IS_NULLABLE   KEY
Id              int         NULL              NO            PRIMARY
Name            nvarchar    50                NO            UNIQUE
StartTime       time        NULL              NO
SkipMinutes     int         NULL              NO

Y aquí está la función interna a la que llama la consulta externa.

CREATE FUNCTION [dbo].[fn_AnalysisTaskByEnvironment]
(
    @environmentid NVARCHAR(256)
)
RETURNS TABLE
WITH SCHEMABINDING
RETURN (
    WITH EnvironmentStudies AS (
        SELECT
            [Id] AS [ProgramId],
            [EnvironmentId],
            [TestStepId],
            [TestGroupId],
            [TestAnalysisTypeId],
            [RoundingRuleId],
            [IsUtc],
            [AdditionalContext],
            [StartDateTime],
            [EndDateTime]
        FROM
            [dbo].[AnalysisProgram] WITH (NOLOCK)
        WHERE
            [EnvironmentId] = @environmentid AND
            [IsDeprecated] = 0 AND
            [TestStepId] IS NOT NULL
    )
    SELECT
        t.[Id],
        t.[Id] AS [TaskId],
        t.[ProgramId],
        ws.[EnvironmentId],
        ws.[TestStepId],
        ws.[TestationGroupId],
        ws.[TestAnalysisTypeId],
        ws.[AdditionalContext] AS [ProgramAdditionalContext],
        ws.[RoundingRuleId] AS [ProgramRoundingRuleId],
        ws.[IsUtc],
        t.[TimeUnitId],
        t.[StartUnit],
        t.[EndUnit],
        t.[RoundingRuleOverrideId],
        t.[AnalysisConfiguration],
        t.[ExecutionConfiguration],
        t.[AnalysisItemId] AS [AnalysisRequestId],
        t.[ResultId],
        s.[Id] AS [AltResultId],
        t.[ForayAppVisualLink],
        t.[ResultAvailableTime],
        t.[ResultNotificationEnabled],
        t.[CustomNotifyAliases],
        t.[StateId],
        t.[AdditionalContext],
        t.[CreatedTime],
        t.[CreatedBy],
        t.[LastUpdatedBy],
        t.[LastUpdatedTime],
        t.[ScheduleTime],
        ws.[StartDateTime],
        ws.[EndDateTime]
        FROM
            [dbo].[AnalysisTask] t WITH (NOLOCK)
            INNER JOIN EnvironmentStudies ws
                ON t.[ProgramId] = ws.[ProgramId]
            LEFT JOIN [dbo].[Result] S WITH (NOLOCK)
                ON t.Id = S.TaskId
)
GO

Los esquemas para la consulta anterior:

Programa de análisis

COLUMN_NAME                     DATA_TYPE           CHAR_MAX_LENGTH   IS_NULLABLE   KEY
Id                              int                 NULL              NO            PRIMARY_KEY
Name                            nvarchar            1000              NO    
Description                     nvarchar            3000              NO    
AnalysisPortfolioId             int                 NULL              NO            FOREIGN_KEY
TestStepId                      uniqueidentifier    NULL              YES   
LegacyTestId                    int                 NULL             YES 
TestAnalysisTypeId              int                 NULL              NO            FOREIGN_KEY
PrimaryDataSourceId             int                 NULL              NO            FOREIGN_KEY
IsAuto                          bit                 NULL              NO    
IsActive                        bit                 NULL              NO    
IsDeprecated                    bit                 NULL              NO    
DeprecateReason                 nvarchar            3000             YES    
CloneFromProgramId              int                 NULL             YES    
BaseOnTemplate                  nvarchar            1000             YES    
CreatedTime                     datetimeoffset      NULL              NO    
CreatedBy                       nvarchar            50                NO    
LastUpdatedTime                 datetimeoffset      NULL              NO    
LastUpdatedBy                   nvarchar            50                NO    
RoundingRuleId                  int                 NULL             YES           FOREIGN_KEY
IncludeFlightBurnInPeriod       bit                 NULL              NO    
IsUtc                           bit                 NULL              NO    
MetricSetVersion                nvarchar            1000             YES    
ForayProfileNameOverride        nvarchar            1000             YES    
ValidFinalResultDurationDays    nvarchar            1000             YES    
UseFlightAllocationIdFilter     bit                 NULL             YES    
ParentProgramId                 int                 NULL             YES           FOREIGN_KEY
AnalysisProgramGroupId          int                 NULL             YES           FOREIGN_KEY
IsParametrizationEnabled        bit                 NULL             YES    
AdditionalContext               nvarchar            -1               YES    
StartDateTime                   datetimeoffset      NULL             YES    
EndDateTime                     datetimeoffset      NULL             YES    
EnvironmentId                   nvarchar            1000             YES    
CanaryTaskConfiguration         xml                 -1               YES    
ManagementGroup                 nvarchar            1000             YES    
TestGroupId                     nvarchar            1000             YES    

AnálisisTarea

COLUMN_NAME                   DATA_TYPE         CHAR_MAX_LENGTH IS_NULLABLE KEY
Id                            int               NULL                NO PRIMARY_KEY
ProgramId                     int               NULL                NO  FOREIGN_KEY
TimeUnitId                    int               NULL                NO  FOREIGN_KEY
StartUnit                     int               NULL                YES 
EndUnit                       int               NULL                YES 
AnalysisConfigurationTypeId   int               NULL                NO  FOREIGN_KEY
AnalysisConfiguration         xml               -1                  NO  
ExecutionConfiguration        xml               -1                  NO  
PriorityId                    int               NULL                NO  FOREIGN_KEY
ForceRun                      bit               NULL                NO  
AlertingNotificationEnabled   bit               NULL                NO  
AnalysisItemId                uniqueidentifier  NULL                YES 
AnalysisRequest               xml               -1                  YES 
CreatedTime                   datetimeoffset    NULL                NO  
CreatedBy                     nvarchar          50                  NO  
LastUpdatedTime               datetimeoffset    NULL                NO  
LastUpdatedBy                 nvarchar          50                  NO  
ResultNotificationEnabled     bit               NULL                NO  
IsDriResolved                 bit               NULL                YES 
CustomNotifyAliases           nvarchar          800                 YES 
RoundingRuleOverrideId        int               NULL                YES FOREIGN_KEY
StateId                       int               NULL                YES FOREIGN_KEY
ResultId                      int               NULL                YES 
ForayAppVisualLink            varchar           4096                YES 
AdditionalContext             nvarchar          -1                  YES 
ResultAvailableTime           datetimeoffset    NULL                YES 
ManagementGroup               nvarchar          1000                YES 
ScheduleTime                  datetimeoffset    NULL                YES 

Resultado

COLUMN_NAME DATA_TYPE   CHAR_MAX_LENGTH IS_NULLABLE KEY
Id          int         NULL            NO          PRIMARY_KEY
TaskId      int         NULL            NO          FOREIGN_KEY/UNIQUE

En cuanto a los planes de ejecución, el 91% del costo está en una búsqueda clave (índice agrupado) para [AnalysisTask].[Id] https://www.brentozar.com/PasteThePlan/?id=rJlR8mZaF

Answer