No missing index requests on SQL Server 2012

Question

I am trying to optimize the database by creating missing indexes, but the dm_db_missing_index_details table does not have any data.

I have the same database on different server and there are many records in that table. So it looks there may be a configuration problem causing the missing index requests to not be saved.

Why are there missing index requests on one server, but there aren't any on the other server (for the same database)?

We have not restarted the database server for the last four days. I am sure there should be index issues, because the same db is running at a different location and that one has many missing index warnings (and we fixed those).

Answer 1

Depending on which version of SQL Server you're on, missing index (and other index metadata) may disappear when you rebuild indexes.

See Kendra Little's post for more information.

Answer 2

Check that no one has enabled trace flag 2392, as documented in:

FIX: Access violation when you cancel a pending query if the missing indexes feature is enabled in SQL Server

The workaround for this issue is to enable the Trace Flag (TF) 2392 which will suppress the collection of missing indexes.

It is not documented for SQL Server 2012, but that doesn't mean it wasn't subsequently back-ported, or functional but undocumented. Worth checking anyway.

More generally:

Unless you're running the same workload on both systems, the absence of missing index suggestions might be expected and normal.

SQL Server doesn't preemptively look for missing indexes, you have to run actual queries that could have used those indexes. Also, differences in data, statistics, hardware, and other things can make it so that the same queries don't generate the same plans on both systems, and hence different indexes are useful (or not).

Index creation should be scripted and version controlled (just like the source code in your app). If you have the same database installed in multiple locations, they should all be running the same codebase, which includes the same tables and index structures.

See also:

• Don't just blindly create those "missing" indexes! by Aaron Bertrand
• Are you using SQL’s Missing Index DMVs? by Bart Duncan