You are viewing limited content. For full access, please sign in.

Question

Question

Missing Index Details from SQL Plan for Forms Database

asked on June 29, 2022 Show version history

Our DBAs have reached out to me to let me know that they have received a recommendation to create a nonclustered index on the cf_bp_worker_instnc_to_resume_archive table by the Query Processor. Would there be a problem with them adding this? See the message below:

/*
Missing Index Details from ExecutionPlan3.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 25.1568%.
*/

/*
USE [Forms]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[cf_bp_worker_instnc_to_resume_archive] ([worker_instance_id],[step_id])

GO
*/
(@p__linq__1 int,@p__linq__2 nvarchar(4000),@p__linq__0 datetime2(7))SELECT TOP (100) 
    [Project22].[id] AS [id], 
    [Project22].[history_id] AS [history_id], 
    [Project22].[instance_id] AS [instance_id], 
    [Project22].[step_id] AS [step_id], 
    [Project22].[process_id] AS [process_id], 
    [Project22].[origin_step_name] AS [origin_step_name], 
    [Project22].[creation_date] AS [creation_date], 
    [Project22].[last_assigned_start_date] AS [last_assigned_start_date], 
    [Project22].[last_assigned_finish_date] AS [last_assigned_finish_date], 
    [Project22].[last_assigned_resume_id] AS [last_assigned_resume_id], 
    [Project22].[last_assigned_status] AS [last_assigned_status], 
    [Project22].[last_assigned_target_snapshot] AS [last_assigned_target_snapshot], 
    [Project22].[last_assigned_team_id] AS [last_assigned_team_id], 
    [Project22].[last_assigned_owner_snapshot_id] AS [last_assigned_owner_snapshot_id], 
    [Project22].[bp_id] AS [bp_id], 
    [Project22].[status] AS [status], 
    [Project22].[due_date] AS [due_date], 
    [Project22].[finish_date] AS [finish_date]
    FROM ( SELECT [Project22].[C1] AS [C1], [Project22].[C2] AS [C2], [Project22].[id] AS [id], [Project22].[history_id] AS [history_id], [Project22].[instance_id] AS [instance_id], [Project22].[step_id] AS [step_id], [Project22].[process_id] AS [process_id], [Project22].[origin_step_name] AS [origin_step_name], [Project22].[creation_date] AS [creation_date], [Project22].[last_assigned_start_date] AS [last_assigned_start_date], [Project22].[last_assigned_finish_date] AS [last_assigned_finish_date], [Project22].[last_assigned_resume_id] AS [last_assigned_resume_id], [Project22].[last_assigned_status] AS [last_assigned_status], [Project22].[last_assigned_target_snapshot] AS [last_assigned_target_snapshot], [Project22].[last_assigned_team_id] AS [last_assigned_team_id], [Project22].[last_assigned_owner_snapshot_id] AS [last_assigned_owner_snapshot_id], [Project22].[bp_id] AS [bp_id], [Project22].[status] AS [status], [Project22].[due_date] AS [due_date], [Project22].[finish_date] AS [finish_date], row_number() OVER (ORDER BY [Project22].[C1] ASC, [Project22].[C2] ASC) AS [row_number]
        FROM ( SELECT 
            CASE WHEN ([Project21].[due_date] IS NOT NULL) THEN [Project21].[due_date] ELSE @p__linq__0 END AS [C1], 
            CASE WHEN ([Project21].[C1] IS NOT NULL) THEN [Project21].[C2] WHEN ([Project21].[C3] IS NOT NULL) THEN [Project21].[C4] ELSE [Project21].[origin_step_name] END AS [C2], 
            [Project21].[id] AS [id], 
            [Project21].[history_id] AS [history_id], 
            [Project21].[instance_id] AS [instance_id], 
            [Project21].[step_id] AS [step_id], 
            [Project21].[process_id] AS [process_id], 
            [Project21].[origin_step_name] AS [origin_step_name], 
            [Project21].[creation_date] AS [creation_date], 
            [Project21].[last_assigned_start_date] AS [last_assigned_start_date], 
            [Project21].[last_assigned_finish_date] AS [last_assigned_finish_date], 
            [Project21].[last_assigned_resume_id] AS [last_assigned_resume_id], 
            [Project21].[last_assigned_status] AS [last_assigned_status], 
            [Project21].[last_assigned_target_snapshot] AS [last_assigned_target_snapshot], 
            [Project21].[last_assigned_team_id] AS [last_assigned_team_id], 
            [Project21].[last_assigned_owner_snapshot_id] AS [last_assigned_owner_snapshot_id], 
            [Project21].[bp_id] AS [bp_id], 
            [Project21].[status] AS [status], 
            [Project21].[due_date] AS [due_date], 
            [Project21].[finish_date] AS [finish_date]
            FROM ( SELECT 
                [Project19].[id] AS [id], 
                [Project19].[history_id] AS [history_id], 
                [Project19].[instance_id] AS [instance_id], 
                [Project19].[step_id] AS [step_id],

Using Forms 10.4.4.444.

0 0

Answer

SELECTED ANSWER
replied on June 30, 2022

It is safe to add the nonclustered  index to the cf_bp_worker_instnc_to_resume_archive table. 

1 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.