CODEWITHSUNDEEP
sql-serveroptimizationindexing
Englishman Bob
Englishman Bob

Reputation: 483

Missing Index detection SQL Server 2014

I'm am trying to learn how to detect a missing Indexes in SQL Server 2014 Express (this is a self study project). I am using AdventureWorks 2012 and an online tutorial. Here is the code I'm using:

SELECT
    FirstName,
    MiddleName,
    LastName
FROM Person.Person
WHERE LastName = 'Harrson'
AND FirstName = 'Jesse';

The query returns an empty table, which I am okay with (again I'm try to learn how to use the optimizer).

The tutorial indicates that I should right click the operation and a fly will appear with the missing Index option. When I right click the only menu I see is the one below.

Execution Plan Screenshot

Upvotes: 0

Views: 341

Answers (1)

dfundako
dfundako

Reputation: 8314

The missing index details do not always appear (it will be green text). For your query, the clustered index is just fine and SQL Server sees no issue with it. You can try two other things:

  • make your query more complex and add some joins and filters in the where clause. This may produce a index recommendation.
  • Script out the current indexes on the table, save the scripts, then delete all indexes on the person.person table, and finally rerun your query. Chances are this will generate an index recommendation for the heap. Once you are done playing around, you can rerun your index scripts to put the indexes back.

Upvotes: 1

Related Questions