Hello,

Since a few versions we have the Missing Indexes in Business Central as a page.

This may be helpful in the first place but on a working customer environment where you have no control over all the users you will get many indexes suggested which are completely unnecessary.

You need much more information in adding an index to the database than only it is missing.

In the linked page of the statistics table which is linked and used you have the right tool:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql?view=sql-server-ver16#examples

SELECT

 CONVERT (varchar(30), getdate(), 126) AS runtime, mig.index_group_handle, mid.index_handle,

 CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,

 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns, '') + CASE

   WHEN mid.equality_columns IS NOT NULL

   AND mid.inequality_columns IS NOT NULL THEN ','

   ELSE ''

 END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

 migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

   INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

   INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Without the create Index column it is the complete tool. In OnPremise this is the first tool to use to identify the missing indexes which would help the most

Best regards

Bernhard Kloibmüller