Category: Onpremise

Missing Index – A factor to review before implementing performance changes

Hi,

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…

SELECT DISTINCT

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

       migs.last_user_seek,

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

       mid.equality_columns,

       mid.inequality_columns,

       mid.included_columns,

       migs.unique_compiles,

       migs.user_seeks,

       migs.avg_total_user_cost,

       migs.avg_user_impact,

       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

ORDER BY index_advantage DESC OPTION (RECOMPILE);

GO

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

Cheers,

PMDY

[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..

Hi,

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

<CustomControlDefaultConfigs>

<CustomControlDefaultConfig>

<PrimaryEntityTypeCode>10084</PrimaryEntityTypeCode>

<CustomControlDefaultConfigId>{bd771d8c-2cd6-e511-80d4-00155d0e4417}</CustomControlDefaultConfigId>

<ControlDescriptionXML>

<controlDescriptions />

</ControlDescriptionXML>

<IntroducedVersion>1.0</IntroducedVersion>

</CustomControlDefaultConfig>

</CustomControlDefaultConfigs>

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

Fix:

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.

Capture1

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

Capture2

Capture3

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>

</entity>

</fetch>

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

Reason:

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

Fix:

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.

solution-add-primary-key-field-of-entity

Hope this helps..

Cheers,

PMDY