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:
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
ECELLORS CRM Blog 