8

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.

6
  • 1
    Is one second fast enough? – Erik Darling Mar 27 '17 at 19:31
  • It is taking less than 1 second – somu Mar 27 '17 at 19:47
  • 4
    Okay... is that fast enough? – Erik Darling Mar 27 '17 at 19:48
  • I think so its fast as account table has 900 rows.and account featuresetting has 21385 rows – somu Mar 27 '17 at 19:53
  • 1
    I would say add more rows like 100x more than you actually have in a test db and Post the actual Execution Plan. then Post it again after you added this index and you will see and get to know why others would renommend to or not to apply this index. – Magier Mar 27 '17 at 22:49
15

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.
1
  • It's also worth asking how many accounts you expect to have. If you're taking orders for jetliners you might not have that huge of a customer base, but if it's a social media site your main goal is to get that number into the millions as quickly as possible. One more thing--In some cases I would lean toward making the index now so that I don't have to go looking for what is making the application slow a year later when the number of accounts takes off for some reason. – msouth Mar 29 '17 at 2:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.