8

I recently installed SQL Server Management Studio 2014 (Developer Edition), and I noticed that I am no longer seeing the missing index feature.

What I have done:

  • Ran the workload through Database Engine Tuning Advisor, so I know that the table is missing an index.
  • Found nothing online about enabling the feature
  • Sifted through every option that seemed related.

Any suggestions would be greatly appreciated. I was previously running SQL Server 2012 Developer Edition, prior to 2014.

UPDATE

I am able to get it to display the missing index in certain cases. Take a look at these two queries:

For this query, no missing index is displaying in the execution plan. The WHERE Clause field is a varchar(512) and has no indices on this column.

SELECT SomeVarchar FROM SomeTable WHERE Name='Adam'

For this query, it is showing a missing index in the execution plan. Age is an INT

SELECT SomeVarchar20 FROM SomeTable WHERE Age=30

Missing Index:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SomeTable] ([Age])
INCLUDE ([SomeVarchar20])

I also tried a query with a VARCHAR(500) field, and it did return a missing index. I am really scratching my head now.

Improve this question
8
  • Did you check the missing_index DMVs? Are you sure you are looking at a plan for a query that would actually benefit from the missing index? Just because you ran that advisor thing doesn't mean every query will have a missing index it could have used.
    – Aaron Bertrand
    Jul 7, 2015 at 13:36
  • Unless I'm misunderstanding what you are asking the missing index feature is based on the query plan. Unless you look at a query plan or the DMOs you aren't going to see it. And the DMOs are only filled in when a query plan is generated that it feels could benefit from an index.
    – Kenneth Fisher
    Jul 7, 2015 at 13:36
  • @AaronBertrand The query that I am testing is the exact same query in my advisor workload. It doesn't show up in in the sys.dm_db_missing_index_details table. I was, however, able to get a missing index (in the execution plan) to show up when I created a query based on the result of the missing_index_details. It seems pretty inconsistent on when it will actually display the missing index.
    – Adam Scharp
    Jul 7, 2015 at 14:46
  • As I and others have said - it will only show up if the query would actually benefit from the missing index. If you don't see the little missing index message in the actual plan results, then SQL Server was quite happy using a different index to satisfy the query.
    – Aaron Bertrand
    Jul 7, 2015 at 14:48
  • @AaronBertrand it would absolutely benefit from the missing index. It seems like the missing index in the execution plan is largely based on the data that is in the table. Not so much with the tuning advisor. See my answer below.
    – Adam Scharp
    Jul 7, 2015 at 15:47

1 Answer 1

Reset to default
10

Well just while your Tuning Advisor displays a missing index, this won't mean, that your current running query would benefit from this index. The missing index will just be displayed if the current plan would take a benefit from an index.

You can also take a look at one of these DMV's, they will display missing indices for the current db.

SELECT *
FROM sys.dm_db_missing_index_details

If you want additional information, you can also use this query from me:

SELECT  id.index_handle, DB_NAME(id.database_id), id.object_id, id.statement AS object_name, 
        gs.avg_user_impact, gs.avg_total_user_cost,
        id.equality_columns, id.inequality_columns, id.included_columns,
        gs.unique_compiles, gs.user_seeks, gs.last_user_seek, gs.last_system_seek, 
        gs.user_scans, gs.last_user_scan, gs.system_scans
FROM sys.dm_db_missing_index_details as id
INNER JOIN sys.dm_db_missing_index_groups as g
        ON id.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats as gs
        ON g.index_group_handle = gs.group_handle
ORDER BY gs.avg_user_impact DESC

Edit based on your last update

Well it's quite easy to determine why you won't get a suggestion.

First of all, why you'll get an proposed index for this query:

SELECT SomeVarchar20 FROM SomeTable WHERE Age=30

If you create the index, as suggested below, you'll completely serve the index out of the index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SomeTable] ([Age])
INCLUDE ([SomeVarchar20])

The SQL Server will be able to search for a data page using the Age, as defined in your index. In addition, due to the fact, that you only select the column SomeVarchar20, it is proposed to be added to the leaf level pages of your index (defined by the INCLUDE). This will help the SQL Server to find the right pages based on the Age in the B-Tree architecture. After he found the last row, which holds a pointer to the HEAP or the Clustered Index where all other properties lies, he also gets to the included columns on the leaf level pages. Due to the fact, that he found everything to display in the index itself, he won't go further to your HEAP or your Clustered Index. This will drastically improve your read (!) performance.

The fact, why you won't get a suggestion to add an index to this column is quite clear to me:

SELECT SomeVarchar FROM SomeTable WHERE Name='Adam'

You say that your Column Name is an varchar(512) this means one value can be 512 Bytes long. In fact a SQL Server page can hold round about 8060 bytes. This means you may store up to 16 names (maybe more if you don't use the 512 bytes all the way) per page. This won't improve performance drastically, as SQL Server might even need to iterate (seek) over many many pages. In fact, he needs to maintain all those pages additionally on each insert/update/delete.

If you just use SomeVarchar, which may be a varchar(512) too, it would drastically raise the leaf level pages, which will make the index even inefficient. This is the reason why your SQL Server might not provide a missing index hint for your SELECT.

Hopefully this will clear up all other things in your mind. Otherwise let me know.

Improve this answer
5
  • Thanks for the answer @Ionic. This didn't completely resolve my issue, but it led me down the right path.
    – Adam Scharp
    Jul 7, 2015 at 15:10
  • Ah ok, I've seen that you updated your original question. I'll take a look at your update.
    – Ionic
    Jul 7, 2015 at 17:23
  • 1
    I've updated my answer and describe your concrete case which you've added later on.
    – Ionic
    Jul 7, 2015 at 17:34
  • Very well said @Ionic. In a production environment, it is common to have 60 million records in this table, in which case I have seen great benefit in having an index on the Name column. Also, mentioned in the question, the tuning advisor suggests having an index on the Name column. Any idea why the tuning advisor suggests that? Just curious.
    – Adam Scharp
    Jul 7, 2015 at 17:43
  • 1
    Well both are different tools. The advisor can take a look in the inserts too. It can be surer in advising an index. The query itself can just take a look in its query. This may be a reason why the advisor suggest an index where the plan itself won't. This is really speculative but it may be a cause that the plan optimizer takes a look at the costs to initially build the index - the performance gain of it. In this case this may describe why the query optimizer won't suggest it. But as I said very speculative in this case.
    – Ionic
    Jul 8, 2015 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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