74

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

Category: Development
STATUS DETAILS
Needs Votes
Ideas Administrator

Thank you for this suggestion! Currently this is not on our roadmap. We are tracking this idea and if it gathers more votes and comments we will consider it in the future. Best regards, Business Central Team

Comments

B

https://www.yammer.com/dynamicsnavdev/#/Threads/show?threadId=2633827849379840

Category: Development

B

I support this idea!

Implementing this would provide real added value and help us to ensure performance for the customer and efficient utilisation of resources for the platform.

Together with the ideas mentioned in this topic, the analysis and handling of indexes would be greatly improved.


To summarise, these would be:

  • improve page "Database Missing Indexes"
  • add a new page "Database Index Usage"
  • add a new custom dimension to telemetry event RT0005: "Used SQL Index"

Category: Development