Add Find-DbaDbMissingIndex #10770

Activity

1. deepeshd87

   added

   feature

   new commandFeature request that would need a new commandFeature request that would need a new command

   triage requiredNew issue that has not been reviewed by maintainersNew issue that has not been reviewed by maintainers

   on Oct 3, 2026
2. potatoqualitee commented on Oct 3, 2026

   

   potatoqualitee

   on Oct 3, 2026

   Member

   More actions

   ive long wanted a command like this and like the approach. what do you think @niphlod and @andreasjordan ?
3. andreasjordan commented on Oct 5, 2026

   

   andreasjordan

   on Oct 5, 2026

   Collaborator

   More actions

   I like it too, and the gap is real. A few points from looking at the DMVs and the sibling commands:

   Name and shape. I'd call it Find-DbaDbMissingIndex, next to Find-DbaDbDuplicateIndex, Find-DbaDbUnusedIndex and Find-DbaDbDisabledIndex (and your own Find-DbaDbQueryStoreRegression), and take -InputObject from Get-DbaDatabase like they do. Running the query per database with WHERE mid.database_id = DB_ID() also covers Azure SQL Database, where these DMVs only return rows for the database you're connected to.

   Score. Please keep avg_total_user_cost in the score. Microsoft's own example uses avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), and Glenn Berry's "Index Advantage" uses the same factors (seeks only). avg_user_impact is a percentage of the query's cost, so without the cost a 95% gain on a query that costs almost nothing ranks as high as a 95% gain on an expensive one. My lab test below produced exactly that: cost 0.08, impact 95.17. Returning the three inputs as their own properties lets people rank differently if they want.

   How much history the numbers cover. The rows are cleared on restart, failover and database offline, and also for a single table whenever its metadata changes or any ALTER INDEX runs on it. With nightly index maintenance, a table's counters may only cover the time since its last rebuild. I'd return the instance start time and last_user_seek/last_user_scan with each row, and say this in the help. SQL Server also stops collecting at 600 missing index groups, which deserves a warning when the count reaches it.

   Query text on 2019+. sys.dm_db_missing_index_group_stats_query (SQL Server 2019+, Azure) has the query hash and last_sql_handle, so you can show which query asked for the index. For "a candidate for a human to evaluate", that's the most useful column. It has one row per query rather than per group, so it should be added alongside the group_stats join, not replace it, or the counts get added up twice.

   Covered by an existing index: flag, don't skip. Creating an index clears the table's missing-index rows. So when a suggestion shows up next to an existing index, that index usually doesn't serve the query as it is (wrong key order or a missing include), and that's the case a human most wants to see. Microsoft's guidance is to widen the existing index rather than add a near-duplicate. I'd name the overlapping index in the output instead of filtering the row out.

   CREATE statement. The DMV doesn't give a key order. Microsoft's order is equality columns, then inequality columns, then the rest in INCLUDE. I'd leave ONLINE = ON and similar options out, since they depend on the edition.

   Versions and permissions. The DMVs exist since 2005, so SQL Server 2005 is the floor and 2000 gets a clean skip. They need VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on 2022+.

   Tests. I tried a fixture on SQL Server 2019 (15.0.4430) in my lab. 20,000 rows with a clustered primary key, and SELECT OrderId, Amount FROM dbo.Orders WHERE CustomerId = 42 run five times: no suggestion, because trivial plans never produce one. The same query with AND 1 = (SELECT 1) forces full optimization and gave one suggestion ([CustomerId], 1 seek) after a single run. Dropping the database removed the rows, so the usual Remove-DbaDatabase cleanup is enough. The tests will need to pass low thresholds, or the default minimum seek count filters the fixture out.

   One more thing: Invoke-DbaDiagnosticQuery -QueryName "Missing Indexes All Databases" (and "Missing Indexes" per database) already returns these rows through Glenn Berry's script. It doesn't filter, generate DDL or check overlap, so it's no reason not to do this. It's still worth linking in the help.

   ---

   This text was created by Claude and reviewed by Andreas Jordan.
4. andreasjordan

   removed

   triage requiredNew issue that has not been reviewed by maintainersNew issue that has not been reviewed by maintainers

   on Oct 5, 2026
5. deepeshd87 commented on Oct 5, 2026

   

   deepeshd87

   on Oct 5, 2026

   ContributorAuthor

   More actions

   This is incredibly helpful. Thank you for the depth here, especially the lab testing. All of it makes sense: I'll keep avg_total_user_cost in the score (and return the three inputs separately), name the overlapping index rather than filter it, add the 2019+ query-text join alongside group_stats, surface the coverage caveats (instance start, last_user_seek/scan, the 600-group ceiling), and use the equality -inequality-INCLUDE order with no edition-specific options. I'll build to this and open a PR. Thanks again.
6. niphlod commented on Oct 6, 2026

   

   niphlod

   on Oct 6, 2026

   Contributor

   More actions

   LGTM too as an idea. Once it comes up a quick target to "know databases" may point out some smaller refinements in "the default score to use" but IMHO it'd be shelled out once is out rather than thinking ahead.
7. deepeshd87

   changed the title [-]Find-DbaMissingIndex - surface missing index suggestions from DMVs with impact scoring[/-] [+]Add Find-DbaDbMissingIndex[/+] on Oct 11, 2026