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.