We are running an on-premise D365FO (Local Business Data environment).

On our PROD SQL instance we are also running DynamicsPerf (a well-known and well-respected tool by Microsoft engineer Rod Hansen).

I did notice the following query missing an index

(I cannot seem to add screenshot anymore)

It does get executed quite often, with an average duration of approximately 30 ms, which is OK. But the maximum time it took was almost 700 ms.

DynamicsPerf is telling us there is an index missing for this query on the SecurityPrivilege column.

A Clustered index SCAN is being done when I was looking at the query plan.

This is the proposed index:

CREATE NONCLUSTERED INDEX []

ON [dbo].[SECURITYROLEPRIVILEGEEXPLODEDGRAPH] ([SECURITYPRIVILEGE])

Currently only two indexes are available:

• RecId index
• index on SecurityRole, SecurityPrivilege fields

-> There are only two. The clustered index which was used has the SecurityRole column still in front of the SecurityPrivilege.

We don’t ask Microsoft to change the clustered index, but we would like to ask you to also add the proposed index by SQL. As there are only 2 indexes yet.

As we cannot do an extension on this table ourselves. It is not allowed by Microsoft to do an extension on this table and not possible in Visual Studio.

I did log this as a ticket at Microsoft together with a bunch more detail with regards to the query and query plan etc.

They asked me to log this as an Idea on this portal, so I did.