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

[Quick Tip] – Error is usually caused by custom plug-ins that ignore errors from service calls and continue processing in Dynamics 365

Hi CRM Folks,

While we were working on Dynamics 365  On Premise 8.2 Version, we found this issue which is occurring on create of custom entity records in one of our environments. However we see the same customizations in both the environments.

After investigation by observing event logs, we found that this issue is caused due to some security issue. Later we identified that the plugins steps registered on create were running security context of user.

We were able to resolve this issue with the help of impersonation to run Plugins with higher privileges. For detailed info, see below reference.

Reference

Cheers,

PMDY

 

 

Unlock the lookup fields in a business process flow in D365

Problem statement:

Recently while working on issue in Dynamics 365, found that all the lookup fields included in a stage in the Business process flow are locked out and not able to edit and we were not able to move to next stage since these fields are mandatory. We modified to show the stage owner properly, but even though when that stage owner logged in these lookup fields used in the Business process flow are locked.

Solution:

Give Append and AppendTo privileges to the other entity so that these fields would be unlocked and at the same time you need to give read privileges on the related entity so that user could see this lookup field without any permission issue, you need to give write privileges too in order to edit and write data in to that field.

Hope this helps someone…

Cheers,

PMDY

Deep insight – All about adding Indexes and monitoring performance in Dynamics CRM

Hello,

Have you had a situation in Dynamics CRM where you need to delete some particular entity records satisfying some conditions using advanced find.

We have experienced a similar issue where in we were unable to delete records created in a Custom entity added into system created few mins. back. Even we were unable to find much complex relationships with other entities too. However the new records created were too not getting deleted using advanced find, even deletes from form also.

Verified and came to know that there weren’t any indexes available on that particular table. We added indexes on the columns on which we were retrieving data in advanced find and this solved our problem to delete the records created.

So here are few more tips about adding indexes in MS CRM. Don’t worry adding indexes on tables, columns or views is completely supported customization. However removing OOB indexes is not supported.

See this for more info.

Please check the below link to verify the system jobs which would run on a timely basis in Ms CRM on premise versions. These are configured during installation and are designed to optimize the database and reduce fragmentation. It’s good practice to re-configure these jobs to run out-of-hours.

Check the job editor available for versions starting with 2011 till 2016.

This job goes directly to the MSCRM_CONFIG database to update ScaleGroupOrganizationMaintenanceJobs table.

OOB jobs in Onpremise 

Check by running below queries

Verify if you need these jobs to run more often, you can monitor fragmentation with the following queries:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'ORG_MSCRM'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

Or you can be more specific at the table level

SELECT avg_fragmentation_in_percent,* FROM
sys.dm_db_index_physical_stats ( db_id('ORG_MSCRM'),
OBJECT_ID('PrincipalObjectAccess'), NULL, NULL, 'DETAILED')

Utilize DMV(Data Management views):

Dynamic management views store SQL transactional information which can be used to monitor health, performance and diagnose problems. There are a number of DMV’s to retrieve a different number of metrics for different purposes; we will be looking in particular at the Index related and Execution DMV’s:

  • Index
    • sys.dm_db_index_operational_stats
    • sys.dm_db_index_physical_stats
    • sys.dm_db_index_usage_stats
    • sys.dm_db_missing_index_columns
    • sys.dm_db_missing_index_details
    • sys.dm_db_missing_index_group_stats
    • sys.dm_db_missing_index_groups
  • Execution
    • sys.dm_exec_query_stats
    • sys.dm_exec_requests

Index Usage
It’s important you check for Index usage because indexes consume memory and disk I/O resources when they need to be rebuild to keep the most up to date data.

To check for index usage run the following query, it’s important you check for Index usage because indexes consume memory and disk I/O resources when they need to be rebuild to keep the most up to date data.

select OBJECT_NAME(a.object_id),b.name,a.user_seeks,a.user_scans,a.*
from sys.dm_db_index_usage_stats a join ORG_MSCRM.sys.indexes b on
a.object_id = b.object_id
and a.index_id = b.index_id
where
a.database_id = DB_ID('ORG_MSCRM')
and b.name like 'myIndex%'
order by
b.name asc

The output of this query would be something like:

Jump

From the above screenshot is clear that _DMV_09 has not been used since it was created and therefore should be deleted.

Finding missing indexes:

Below a nice script you could use with references to missing indexes DMV’s

SELECT   migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,   'CREATE INDEX [myIndex_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)   + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'   + ' 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 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (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
Hope this helps someone figuring out to add indexes into their MS CRM table to improve the performance.
That’s it for today…hope you had enjoyed reading the blog…please share feedback on this…will come up with another interesting article next time.
Cheers,
PMDY

Change tracking solution for Dynamics 365

 Lets take a look at recently released feature for Dynamics 365 – Change tracking solution for Dynamics 365!!!

Large Microsoft Dynamics 365 organizations that synchronizes data with external systems uses change tracking. This feature was used to keep the integration system/data warehouse in Sync with CRM data.

Till now it would be difficult to find which System Administrators/System Customizers had made change to the system. It was difficult to track these details. However with this solution, which was built on Dynamics 365 and as works well on Dynamics CRM 2016 (Online/ On premises). Released to track data changes in a more efficient way.

This feature can track changes like who updated an entity, JavaScript, assemblies and processes along with the time of update. Changes made by System Adminstraors and Customizers will also be recorded.

How does Change tracking solution work?

This uses two plugins and batch job namely captures

  • Publish – User who had published the component, name and time of publish
  • Publish All – Captures when a set of components or solution was published
  • Assemblies & Workflows – This job runs every hour, capturing these changes and update in the change tracking entity.

Downloadable solution available at Change tracking solution for Developers. Please do note that this change tracking solution works for Dynamics 365 On Premise and Online Version both. However there is also an add on that can be installed on your instance using this link.

User guide for Admins

Change tracking user guide

Hope this helps…!!!

Open source tools, Solutions available for Developers and Admins

So today, I would like to give you a list of most useful tools available till now for Dynamics CRM which increases the developer productivity and helps Admins.

Some great tools you should check out to see if they could work in your organization.
  1. Fiddler – web debugging proxy
  2. Task Workflow Tools – custom workflow steps to extend your workflows
  3. Mobile Task Flows – walk users through tasks in the mobile or phone app
  4. Ribbon Workbench – visually edit the ribbon
  5. CRM Bookmarklets – JavaScript saved as bookmarks to “hack” your CRM (edit read only fields, show hidden fields, etc.)
  6. XRM Toolbox – It was shipped with 20 84  plugins as of April 2018 to handle several administration, customization or configuration tasks. Wiki
    • Access Checker
    • Assembly Recovery Tool
    • Attribute Bulk Updater
    • Audit Center
    • FetchXml Tester
    • Bulk Form Attribute Manager
    • FLS Bulk Updater
    • Form Libraries Manager
    • Form Parameter Manager
    • Import/Export NN relationships
    • Iconator
    • Metadata Browser
    • Metadata Document Generator
    • Privileges Discovery
    • Role Updater
    • Script Finder
    • SiteMap Editor
    • Solution Components Mover
    • Solution Import
    • Solution Transfer Tool
    • Sync Filter Manager
    • Synchronous events execution order editor
    • Easy Translator
    • User Roles Manager
    • User Settings Utility
    • View Layout Replicator
    • View Transfer Tool
    • Web Resources Manager
  7. Metablast – visual utility for exporting metadata
  8. CRM Snoop – Plugin Troubleshooting tool
  9. LinqPad – Easily run .NET code
  10. CRM DevTools – Chrome extension visible in the F12 developer area with lots of helpful functions for admins. It is worth it just to remember the debug and diagnostic links
  11. KingswaySoft – SSIS Integration toolkit
  12. Developer tool kit – New developer tool kit which was officially released
  13. Microsoft Fakes framework to unit test your CRM Code  – Test your undeveloped CRM Custom code using Microsoft Fakes framework
  14. CRM JavaScript Web API Service Utility – Used to write the latest javascript code using Web API
  15. Link Buddy –  It’s useful for converting logging/tracing details from CRM plug-ins, custom apps, etc. into click-able links that open the CRM record for which the GUID represents.
  16. MS CRM Security Roles to Excel Export – A Tool to export CRM Security roles into a Excel workbook.
  17. FetchXML Tester Online – Allows you to test FetchXML queries online without importing any solution or downloading any separate application.
  18. ILMerge – a .NET tool/utility which merges a number of dll’s into one dll, which is useful for creating plugins for online CRM.
  19. Tool to track performance – Check the CRM latency and bandwidth at https://CRMUrl.com/tools/diagnostics/diag.aspx (make sure to replace CRMUrl.com with the actual CRM URL).
  20. Convert FetchXML to SQL query – people find difficulties in converting the fetch xml query created from advanced find to a SQL query to retrieve records from Backend, use this tool to help the same
  21. Convert SQL query to Fetch XML – Two way round, use this to convert your backend queries to fetch XML to use them in your Code to retrieve data and metadata.
  22. Diagnostics Tool for On premise – Helps CRM developers and administrators to enable trace and devErrors on CRM server. Whereas its name only mentions CRM 2011, it is compatible with CRM 2013 and 2015 too (OnPremise only)
  23. Fetch XML Formatter 1, Fetch XML Formatter 2– Two similar tools for the same functionality, use according to your convenience. These were lightweight windows application that tool will be help you when you are extensively working with FetchXML in Javascript / C# code to get desired result. Takes fetchXML from Advance Find as an input and convert that into desired format, which can be used into Javascript / C# for further coding.
  24. Local option set to Global optionset converter – It’s purpose is to automate the task of converting a Local Option Set (an option set that is defined by and specific to a single entity. e.g. a CRM 4.0 Picklist), into a Global Option Set.
  25. Win Merge – A great tool to compare customizations.xml files taken from solutions
  26. CRM Rest Builder – A tool for CRM 2011/2013/2015/2016 Online & On Premise to generate JavaScript code using the 2011 & Web API REST endpoints.
  27. CRM Visual Studio Code JS Snippets – JavaScript code snippets for Dynamics 365/CRM 2011 – 2016 (5.0 – 8.2)
  28. TableTools2 – Add on for only Mozilla firefox to improve the CRM Grids. You can use the addon in grids that don’t display a filter or just in general with any page that displays a table like structure.
  29. CRM Theme Generator – Use this tool to generate the feature and create themes in CRM, supporting latest versions
  30. Advanced Query outer Join – Plugin to enable outer join queries from Advanced Find. This plugin enabled you to do that. The plugin runs on pre-RetrieveMultiple and alter the query that gets sent to the pipeline, with the correct outer join conditions
  31. Easy VS Code in Action – Use this to work with JavaScript file types from your desktop itself and without opening visual studio
  32. Custom code validation tool – Between CRM 2015 and CRM 2016 there are no differences for the checking of unsupported customizations of Js code, so if you get results to fix with the 2015 custom code validation tool means that you need to fix as well for 2016
  33. CRM DevTools – from Sonoma Partners is a Chrome extension which quickly provides CRM record and environment information and makes troubleshooting and testing so much easier. Once it’s installed, you can access the features by going to the Chrome DevTools (F12 key) and clicking the CRM DevTools tab.
  34. Solution to Create “Does Not Have” Queries using Advanced Find – This solution allows you to create “does not have” or “does not exist” queries using the standard Advanced Find interface.
  35. Dynamics 365 developer extensions(tool kit) – Tool to search for CRM related MSDN content, and tooling to assist with managing and deploying web resources, plug-ins, and reports
  36. Network Visualisation tool – Visualize the relationships between accounts, contacts and activities in a network graph, refer to this video to get insights
  37. Level up for dynamics CRM – Works with Edge and Chrome only. Gives form helpers and Navigation helpers, an amazing tool
  38. Personal view to System view – use this to convert created personal view to system view
  39. Beautify your JS, JSON code – improve the readability of your JS code, even minify your code
  40. Ribbon workbench 2016 – Most user friendly and universally accepted tool to customize ribbon, note that this version don’t need Silverlight within your browser
  41. ilspy – Used to decompile your source code from your .NET DLL, helpful to find your source code from plugin DLL’s
  42. Autonumbering solution – free autonumbering solution used to generate a Unique Reference Number for new records so they can easily be tracked, and is also useful for integrations.
  43. ReSharper – on the fly code analysis, identifies problems and provides quick fixes, best addin to visual studio
  44. Integrate Microsoft Dynamics CRM – Find the ETL tools and other key useful integration tools supported from MSDN.
  45. Call workflows and actions using Javascript – Use this tool to call workflows and actions using JavaScript without much hassles without too much taking care of code.
  46. Scribe Insights – Good data migration platform to move data between two systems and create new records in MS CRM. Reasonably the best in terms of performance with such a kind of UI and easy to learn. Comes with one month free trial.
  48. SOAP UI – Most widely used tool for testing web services and other API’s without much hassles.
  49. DLL Extractor – Extract your plugin DLL’s located on your CRM Database, this applies only for On premise applications. Login to your Client network and specify the server and database name with Integrated security or SQL Authentication. Click on List Plugins which shows the assemblies registered and download the                    required ones.
  50. Portal Checker – Troubleshoot your Dynamics 365 portals
  51. Autonumber No Code solution – Autonumbering generation solution.
  52. Commander – is a pre-configured software package that provides you with an awesome terminal emulator, and best shell you’ll ever have on Windows
  53. GuidGen – GuidGen is Microsoft recommended tool which programmatically generates GUID’s.

Hope you found some tool which had increased your productivity developing CRM…

Cheers,

PMDY