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.
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.
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_countFROM sys.dm_db_index_physical_stats(DB_ID(N'ORG_MSCRM'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESCOr you can be more specific at the table level
SELECT avg_fragmentation_in_percent,* FROMsys.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 ona.object_id = b.object_idand a.index_id = b.index_idwherea.database_id = DB_ID('ORG_MSCRM')and b.name like 'myIndex%'order byb.name ascThe output of this query would be something like:
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) DESCDiscover more from ECELLORS CRM Blog
Subscribe to get the latest posts sent to your email.
ECELLORS CRM Blog 