Missing Index – A factor to review before implementing performance changes


Have u ever faced any performance issue with any of your REST API hosted out in the internet. So here’s the background, our client has a Custom Asp.Net Portal(remembering the old day school of using Asp’s, interesting…??) which is built on using the REST API.

So this REST API was having some performance issues, this is where we got to check on the newly created entity for our API Implementation…after couple of hours of research, we found that few indexes are missing.

You should be knowing how it’s difficult to read a book without indexes, so in the same way, the C# code was finding it difficult to retrieve the data without any indexes and after adding them, the peformance had been improved and with a bit of code improvisation, we got a significant improvement in the performance.

For those trying to search for missing indexes, please try out the below code…


       CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],


       OBJECT_NAME(mid.[object_id]) AS [table_name],








       p.rows AS [table_rows],

       mid.[statement] AS [DatabaseSchemaTable],

         GETDATE() QueryExecustionTime

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

INNER JOIN sys.partitions AS p WITH (NOLOCK)

ON p.[object_id] = mid.[object_id]

WHERE mid.database_id = DB_ID(‘Starbucks_MSCRM’) –CHANGE DB NAME HERE –Starbucks_MSCRM

–AND mid.[object_id] = OBJECT_ID(‘dbo.ign_transactionbase’) –CHNAGE TABLE NAME HERE



Hope you found some interesting scenario to deal with your performance issues.




[Fix]-A record with these values already exists. A duplicate record cannot be created. Select one or more unique values and try again…and it’s consequenes..


Recently we faced an issue while deploying solution to other environment from Dev..getting below error in Dynamics 365 On premise 8.2 version..

A record with these values already exists. A duplicate record cannot be created. Select one or more unique values and try again.

This could be due to below reasons..

1. One entity has two CustomControlDefaultConfigs

Like this

We tried to delete one of the duplicated CustomControlDefaultConfig (randomly). This works in some cases.

2. Entity with unique  CustomControlDefaultConfigid, but solution import crashes with error customcontroldefaultconfig With Id = d3226572-022c-e611-80e6-00155dc26410 Does Not Exist. The solution contains only one CustomControlDefaultConfig for this entity






<controlDescriptions />





This could be due to corrupt config setting as in our case.


Delete the CustomControlDefaultConfigId present in the entity which is creating the issue, open the Customizations.xml in the solution and delete the highlighted ID.CaptureFor this use XRM Toolbox Bulk Delete tool to delete this ID as below, as this can’t be done using UI.


To Delete a record we would we would use Bulk Delete Tool



Here is fetch XML to be used and replace the below highlighted ID in green with the ID from the customizations.xml

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”> <entity name=”customcontroldefaultconfig”> <filter type=”and”> <condition attribute=”customcontroldefaultconfigid” operator=”eq” value=”{a88b6a06-3f20-e811-9661-00155d00a048}” /> </filter>



You can use Fetch XML Tester to verify if the record exists and after delete again you can run to verify if record got deleted..

Once record is deleted, try import the same solution again and it should work.

This works so that the solution from Dev would be imported to other environment but the Publish Customizations fails on import, you might encounter below issues.. when publishing..

  • Getting Dependency Calculation There was an error calculating dependencies for this component. Missing component id {0}
  • Failure trying to associate it with CustomControlDefaultConfig


If there is any orphan record exists in “CustomControlDefaultConfigBase” table in the target environment it would cause solution import failure or ‘Publish Customizations’ error.


CRM On-Premise

  • Check if any orphan records in CustomControlDefaultConfigBase tabel. (Use below queries)
    • SELECT * FROM CustomControlDefaultConfigBase WHERE PrimaryEntityTypeCode NOT IN (SELECT ObjectTypeCode FROM Entity)
  • Note: before continuing, a backup database is recommended.
  • Delete from the Target environment the CustomControlDefaultConfig records with orphaned Object Type Codes:
    • DELETE FROM CustomControlDefaultConfigBase WHERE PrimaryEntityTypeCode NOT IN (SELECT ObjectTypeCode FROM Entity)

Prevention Tip:

  • If you are adding an entity to your solution, always add ‘Primary key’ field
    • In the source environment open the Solution being imported
    • Open each Entity and expand Fields
    • Make sure that every entity has its “Primary Key” added to the solution.


Hope this helps..




Sharing thoughts and challenges on Power Platform & Azure along with respective solutions put together...

SharePains by Microsoft MVP Pieter Veenstra

Microsoft 365, Power Platform, SharePoint, Teams, Azure and Dynamics

D365 Demystified

A closer look at Microsoft Dynamics 365.

Vicky Rodgers - Microsoft Dynamics 365

Everything Microsoft Dynamics 365 for Customer Engagement

XRM Tricks (Power Platform & Dynamics CRM )

Power Platform & Dynamics CRM

Microsoft Dynamics 365 UK MCT

All about Microsoft Dynamics 365

High Voltages

Arduino | ESP | Raspberry pi | IoT

Mihail's space

Just another WordPress.com site

Arun Potti's MS CRM blog

Microsoft Dynamics CRM

Sander van de Velde

Microsoft MVP Azure | IoT Platform Architect | Speaker about IoT | Let me add some value

Power Platform Blog

Author : Dharanidharan Balasubramaniam

That API Guy

Making the world a better place one PowerApp/Flow at a time!

Dynamics Back Page

Tips, Tricks, and General Musings on Dynamics 365 (CRM), the Power Platform, Power Apps, Power Automate, Power BI and related technologies

Azure Integration services for Dynamics 365

Azure Integration services for Dynamics 365 Unified Operations, Logic Apps, Azure Functions, REST

Power Maverick

Incredible journey into #Dynamics365, #CDS, #PowerApps #Azure and all good stuff that #Microsoft releases

Unleashing CRM

My Technical Thoughts about Microsoft Dynamics CRM- Unleashed

Srikanth Alluri

My experiences in & around Power Platform + Azure

Debajit's Power Apps & Dynamics 365 Blog

All about Power Apps & Dynamics

Rajeev Pentyala - Microsoft Power Platform

Sharing my knowledge on Power Platform, Dynamics 365, Azure & .Net Stack

Bansal Blogs - Dynamics 365, PowerApps, Microsoft Flows, Power BI

Programming demands a lot more than simple mastery in tools and technologies

%d bloggers like this: