We were using KingswaySoft to import hundreds of thousands of Activities (Emails, Phone Calls, Tasks, Appointments) into Microsoft Dynamics CRM 2011. Importing that many rows can be time consuming, so we opted to utilize Microsoft’s Balanced Data Distributor within the SQL Server Information Services package to speed up the processing. While testing, we where noticing that a significant number of transactions were erroring out with errors such as, “ActivityPointer With ID = 00000000-0000-0000-0000-000000000000 Does Not Exist” or the least helpful one, “Generic SQL error.” From experience, we knew that both of these errors tend to point towards there being deadlock issues while writing data to SQL Server from KingswaySoft in SSIS. SQL Server has more specific errors if you review the trace logs, but the errors returned from CRM through, SSIS and KingswaySoft are not as helpful.
If you do not know the underlying tables in Microsoft SQL Server that are being updated, I would strongly suggest running a SQL Server trace to identify the actual issues you’re experiencing as well is identify the tables and related CRM entities that are being effected.
In our case, we were loading Activities in general. While Emails, Phone Calls, Tasks and Appointments are all separate entities in CRM, they also share the common ActivityPointerBase entity within CRM and thus the ActivityPointerBase table within SQL Server.
Following the logic laid out by Chris Scott in his blog titled, “Resolving Microsoft Dynamics CRM Asynchronous Deadlocks,” it became clear that we needed to identify the underlying SQL Server tables that were being hit and verify that the row level and page level locking was enabled on the clustered indexes associated with those tables. Chris’ blog discusses how to check the settings of those tables via the SQL Server UI, so if you’re not into SQL Scripts, feel free to follow his recommendations. In my case, there were too many indexes and too many tables to make the UI a practical means to verify the settings so I created a few SQL scripts that 1) help identify the indexes in question and 2) generate scripts that can help solve the problem.
Make sure you’re using your organization’s MSCRM database and modify the following script to search for objects that relate to the entities that you’re experiencing issues with to see if there are any CLUSTERED INDEXES that do not have row level locking or page level locking enabled. In the WHERE clause, rename/remove the criteria as applicable so that you’re searching for your affected entities as opposed to just the activity related entities that I was having issues with.
SELECT
schemas.name AS SchemaName
, objects.name AS ObjectName
, indexes.name AS IndexName
, allow_row_locks
, allow_page_locks
, indexes.object_id
, index_id
, ChangeScriptToEnableRowAndPageLocks = 'ALTER INDEX ' + indexes.name + ' ON ' + schemas.name + '.' + objects.name + ' SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );'
FROM
sys.indexes
INNER JOIN sys.objects ON objects.object_id = indexes.object_id
INNER JOIN sys.schemas ON schemas.schema_id = objects.schema_id
WHERE
indexes.type_desc = 'CLUSTERED'
AND (
objects.name LIKE ( '%Email%' ) -- Modify this portion of the script to search for applicable entities that…
OR objects.name LIKE ( '%Phone%' ) -- … may need row level and/or page level locks enabled in your database.
OR objects.name LIKE ( '%Appointment%' )
OR objects.name LIKE ( '%Task%' )
OR objects.name LIKE ( '%Activit%' )
)
AND (
allow_page_locks = 0
OR allow_row_locks = 0
)
ORDER BY
schemas.name
, objects.name
, indexes.name;
The eighth column, “ChangeScriptToEnableRowAndPageLocks,” contains a generated script that you can copy and paste into SSMS to apply the row level locking to your CLUSTERED INDEXES. Copy each cell one at a time and execute that script to address the row level and page level locking on that table. Be careful with these results and DO NOT APPLY THEM ALL. Add them one at a time based on your knowledge of what is being deadlocked and test to see if each one resolves your issue. Use this change script column as a tool, not as an end-all solution.
In our case, I added the row level locking and page level locking to just ActivityPointerBase object first and then and saw immediate improvements.
ALTER INDEX ndx_PrimaryKey_ActivityPointer ON dbo.ActivityPointerBase SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );
We retested and our error counts went down significantly but not completely. After that I identified other related entities and enabled row and page level locking on those indexes:
ALTER INDEX PK_AppointmentBase ON dbo.AppointmentBase SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );
ALTER INDEX PK_CampaignActivityBase ON dbo.CampaignActivityBase SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );
ALTER INDEX PK_EmailBase ON dbo.EmailBase SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );
ALTER INDEX PK_PhoneCallBase ON dbo.PhoneCallBase SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );
ALTER INDEX PK_TaskBase ON dbo.TaskBase SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );
After applying those index changes, we retested and all of our errors were gone.
Why Microsoft does not enable Row and Page Level locking by default in Microsoft Dynamics CRM is a bit of a mystery. But enabling those options in the clustered indexes of the tables you’re having issues with does tend to address deadlock issues when bulk loading data via KingswaySoft and SSIS. You are changing the default Microsoft Dynamics CRM setting for this database setting, but while it’s not the default setting for CRM, it’s definitely a viable and workable setting for Microsoft SQL Server. A more conservative approach would be to change these settings before bulk inserting and change them back afterwards. To change them back,simply change the SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ); portion of the script to SET ( ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF ); on the tables you modified. But with that said, I know that allowing row level and page level locking within Microsoft SQL Server is a safe and generally a good practice. So I’m comfortable leaving row level and page level locking enabled in my CRM instances for the tables that had the issues to prevent such issues in the future.