Execution plan showing missing index but query is fast

Question

While looking at an actual execution plan, it's showing missing indexes even though the query is taking less than 1 second.

SELECT
    Account.AccountID,
    Account.Name
FROM
    account      
    LEFT OUTER JOIN accountfeaturesetting afs 
ON afs.accountid = account.accountid   
and afs.featureid = 'Schedules'
 and  
afs.settingid = 'EditReasons'           
WHERE
    ISNULL(afs.Value, '0') = '0'  
    AND EXISTS 
(SELECT 1 FROM program WHERE program.AccountID = account.AccountID
 AND program.Active = 1 
AND (program.ScheduleEditReasonFlags <> 0 
OR program.ScheduleEditReasonFields <> 0))
    AND account.IsMaster = 0
    AND account.BeginDate IS NOT NULL

The execution plan is showing:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Account] ([IsMaster],[BeginDate])
INCLUDE ([AccountID],[Name])

Do we need to create the index even though query is only taking 1 second? On what basis should an index be created?

I am going to run this query as a daily job.

Answer 1

You don't need to add the index right now. All the "missing index" message means is this might be helpful. It might not be honestly, or there might be an index that helps even more.

As to when you need to add the new index? Well let's say you have 100 rows in the account table but it's not really in use yet. In a couple of months you are up to 10,000 rows and the query is now taking half an hour or more. At that point you might consider adding the index. And when I say consider, I mean test it in a test environment and see if it actually improves things.

Other considerations include:

• How many indexes do I already have on this table? If you have 10+ indexes then I'd be more concerned than if you had say 2.
• How important is this query? You say you are running it every night. At 1 second that's fine (assuming you only run it once). If on the other hand it's part of a process and being run 1000's of times each night then it might be more of an issue. If it get's to a point where it's taking long enough to cause you issues (causing problems with another process, or running into times when users are in the system etc) then you need to look at performance and adding indexes.