SQL Server Execution Plan – Memory Grant Not Showing

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.

The most likely explanation is that your sessions have different settings. SQL Server has various session settings that can affect the execution plan selected (and the results!)

The values for these settings can depend on how you connect to SQL Server, since different tools set the options different ways when they connect, and some (like SQL Server Management Studio) allow you to override the defaults as well.

For example:

The image above is reproduced from Erland Sommarskog's definitive article on this topic:

Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

The whole thing is well worth reading, but you should definitely read the section titled, "The Default Settings"

If you make sure all the settings have the same value on all connections, you should get the same execution plans.

For maximum compatibility with features like indexed views, you should ensure your settings are as follows:

Many of these settings are maintained for backward compatibility only. It is strongly recommended you set them as shown in the table above, or use a tool that sets them the right way automatically.

Books Online references:

• SET Statements (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• SET ANSI_PADDING (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)
• Create Indexed Views

Update after plans were provided

The slow plan includes:

CardinalityEstimationModelVersion="70"

...whereas the fast plan says:

CardinalityEstimationModelVersion="120"

So the explanation is that one of you is using the original cardinality estimator, and other is using the new SQL Server 2014 CE. The difference in estimated row counts is enough for the new CE to choose a parallel execution plan. Under the original CE, the estimated cost for the serial plan is below the cost threshold for parallelism.

As to why different estimators are being used, I would guess that you have different context databases when the statements are run. One where the compatibility level of the database defaults to the new CE, and one where the original CE is used. The database you are "in" when the query executes determines the CE model, not the database(s) used in the query.

For example, you may have different default databases associated with your logins. If you USE Klasje; before running the statements, both connections should use the same CE model.

Final update: it turned out the target database was indeed set to an older compatibility level. Running the query with master as the context database produced the better plan. Be aware that changing to use the new CE for all queries may cause regressions. You will need to test your workload before changing the database compatibility level in production.