Query not using index in exists statement

Question

I have the index IDX_tbl_SpeedRun_StatusTypeID_GameID_CategoryID_LevelID_PlusInclude on table dbo.tbl_SpeedRun below. The exists statement in the query below is taking a while (1m 10s) saying there is a missing index ON [dbo].[tbl_SpeedRun] ([StatusTypeID],[LevelID]).

Why is the exists statement not using the index I created? It already includes the columns [StatusTypeID],[LevelID].

Table:

CREATE TABLE [dbo].[tbl_SpeedRun] 
( 
    [OrderValue] [int] NOT NULL IDENTITY(1,1),
    [ID] [varchar] (50) NOT NULL, 
    [StatusTypeID] [int] NOT NULL,
    [GameID] [varchar] (50) NOT NULL,
    [CategoryID] [varchar] (50) NOT NULL,
    [LevelID] [varchar] (50) NULL,
    [SubCategoryVariableValues] [varchar] (1000) NULL,
    [PlayerIDs] [varchar] (1000) NULL,
    [PlatformID] [varchar] (50) NULL,
    [RegionID] [varchar] (50) NULL,
    [IsEmulated] [bit] NOT NULL,
    [Rank] [int] NULL,
    [PrimaryTime] [bigint] NULL,
    [RealTime] [bigint] NULL,
    [RealTimeWithoutLoads] [bigint] NULL,
    [GameTime] [bigint] NULL,
    [Comment] [varchar] (MAX) NULL,
    [ExaminerUserID] [varchar] (50) NULL, 
    [RejectReason] [varchar] (MAX) NULL, 
    [SpeedRunComUrl] [varchar] (2000) NOT NULL,
    [SplitsUrl] [varchar] (2000) NULL,
    [RunDate] [datetime] NULL,  
    [DateSubmitted] [datetime] NULL,   
    [VerifyDate] [datetime] NULL,   
    [ImportedDate] [datetime] NOT NULL CONSTRAINT [DF_tbl_SpeedRun_ImportedDate] DEFAULT(GETDATE()),
    [ModifiedDate] [datetime] NULL
) ON [PRIMARY] 
GO 

ALTER TABLE [dbo].[tbl_SpeedRun] 
    ADD CONSTRAINT [PK_tbl_SpeedRun] 
        PRIMARY KEY NONCLUSTERED ([ID]) WITH (FILLFACTOR=90) ON [PRIMARY] 
GO

CREATE CLUSTERED INDEX [IDX_tbl_SpeedRun_OrderValue] 
    ON [dbo].[tbl_SpeedRun] ([OrderValue]) WITH (FILLFACTOR=90) ON [PRIMARY] 
GO

CREATE NONCLUSTERED INDEX [IDX_tbl_SpeedRun_StatusTypeID_GameID_CategoryID_LevelID_PlusInclude] 
    ON [dbo].[tbl_SpeedRun] ([StatusTypeID], [GameID], [CategoryID],[LevelID]) 
       INCLUDE ([SubCategoryVariableValues], [PlayerIDs], [Rank],[PrimaryTime])
GO

Query:

SELECT 
    CASE 
       WHEN EXISTS (SELECT 1 FROM dbo.tbl_SpeedRun rn WITH (NOLOCK) 
                    WHERE rn.LevelID = l.ID AND rn.StatusTypeID = 1) 
          THEN 1 
          ELSE 0 
    END
FROM
    dbo.tbl_Level l WITH (NOLOCK)
WHERE 
    l.GameID = 'pd0wq901'
ORDER BY 
    l.OrderValue

Answer 1

This is the from clause of your subquery:

WHERE rn.LevelID = l.ID AND rn.StatusTypeID = 1

A helpful index for this predicate would involve the two columns, in any order.

Your existing index does not satisfy that requirement. It has columns:

[StatusTypeID], [GameID], [CategoryID], [LevelID]) 
INCLUDE ([SubCategoryVariableValues], [PlayerIDs], [Rank], [PrimaryTime])

Both columns are here, but buried within others - so the database cannot take advantage of it to speed up the subquery.

Bottom line: creating a large index that involves a lot of columns does not speed up queries by default. Instead, you can analyze each query individually and define the proper optimization.