SQL Server Missing Indexes Feature and Trace Flag 2392

Background

Since the SQL Server 2005 release, SQL Server has had the default ability to collect metrics about what the query optimizer thinks are “missing” row store indexes, i.e. indexes that the query optimizer thinks would reduce the cost of particular individual queries that have been executed since SQL Server was last restarted.

The word missing is a little confusing to some people, since I have had many questions over the years about what could have happened to these “missing” indexes (implying that they used to be there, but somebody must have dropped them). Perhaps a better word would have been desired or candidate indexes.

At any rate, while far from perfect, I have found this feature to be a very useful method for helping to get the appropriate row store indexes in place for the overall SQL Server workload on a database. If you blindly add every new index that the query optimizer asks for, you will quickly have a large number of similar, narrow indexes on a table that probably could have been consolidated into fewer, wider indexes.

Using your common sense and experience as a DBA, your knowledge of your overall database workload and by running some queries to better characterize the volatility of a table, it is possible to do a pretty good job of index tuning using this feature. You also want to be on the lookout for indexes that can be dropped as part of your index tuning efforts.

These “missing indexes” are visible as the green missing index warnings in the graphical execution plan for a query, you can also find them by querying the plan cache looking for missing index warnings, and by running DMV queries to find them.

Recent Hotfix Reveals Trace Flag 2392

Microsoft has released a hotfix for the latest round of cumulative updates for SQL Server 2014, SQL Server 2016, and SQL Server 2017 that revealed trace flag 2392. This same fix is in SQL Server 2014 SP2 CU8, SQL Server 2016 SP1 CU6 and SQL 2017 CU2.

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

Just to be clear, this hotfix has nothing to do with how the missing index feature functions. It just reveals a previously undocumented trace flag (TF 2392) that can be used to turn the missing index feature off completely (as a workaround to the issue that is corrected by the hotfix). This trace flag has been in the product since SQL Server 2005.

Once I discovered this trace flag, I was curious as to how it actually behaves, which is somewhat different than I expected. First, it is a startup-only trace flag, in terms of what it actually does. You can enable/disable it to your hearts content while SQL Server is running, and it will show as being enabled/disabled (with no error message about only being a startup-only TF).

The problem is, it will not disable/enable missing index stats collection unless it is enabled at startup. If you set it as a startup TF and restart SQL Server, then no missing index stats are collected. If you then subsequently disable TF 2392 while SQL Server is running, it still won’t collect any missing index stats (despite what you may expect).

You have to remove the startup TF 2392 and then restart SQL Server for the change to actually take effect (both ways, enabling and disabling). This is from my testing on SQL Server 2017 CU2.

Personally, I don’t think this is a big deal (unless you really need to enable the workaround described in the KB article). I think most people are going to want to leave the missing index feature enabled. The performance overhead of the feature is probably pretty insignificant.

You just need to be aware of how TF 2392 actually works and know that a restart of SQL Server is required to actually turn the missing index feature off or back on. It would be nice if you did get a message about it being a startup-only trace flag if you tried to enable/disable it while SQL Server was running.