Why Is CRM Displaying A Section That Has Been Defined As Hidden?

After being head down in CRM for the last 2 years, there are few things that surprise me, but the situation I’m about to describe, threw me for a loop:

Our client had an entity with multiple forms.  All the forms looked virtually identical with the 40 same basic fields, split up among 5 different sections.  4 of the sections were defined as visible, but one wasn’t.  It contained the name of the entity (which was programmatically calculated from various fields, which explains why it was on the form but hidden) along with a couple other fields that were required by the JavaScript of the form, in order to process the business rules of the entity, but the actual business user wouldn’t care about.

Everything had been working fine until we upgraded the on-premise version of CRM from rollup 11 to rollup 15.  After the rollup, we found a bug where on one of the forms, for a particular subset of data, the hidden section was mysteriously being displayed.  I tried everything I could think of, defaulting the section to being hidden, setting it to being hidden as the last function call of the onLoad JavaScript, even setting all of the fields within the hidden section as being hidden.  All to no avail. 

Finally, in a desperate attempt to determine why the section was being displayed, I searched for “setVisible” in the JavaScript of the page, and placed a break point everywhere in the source code.  20 breakpoints later one of the breakpoints was triggered and I finally had a call stack to point me to the problem.  Although it was all minified JavaScript, chopped full of 3 character variables and functions containing only a ‘$’ a letter and a number, one of the function’s names was in English and caught my eye, “setFocus”.  Why was Microsoft setting the focus on a random field on my custom form?  A few debugging steps later, and I had my answer:

CRM has JavaScript that runs after the onLoad function of the form to find the first field on the form that is not read-only, and then sets the focus on that field.  As a part of the setFocus function, it ensures the field is visible by not only setting the field as visible, but setting it’s parent section as visible as well.  So what was happening on this one particular form was all of the fields on the form before the hidden section had been marked as read-only, and when Microsoft’s JavaScript searched for the first field that was not read-only, it found the name field in the hidden section, set the focus on it, which made the section visible.

The solution was relatively simple, make all the fields in the section read-only, and update any JavaScript that updated them to always force submit.  Making the fields read-only removed them from being valid focusable candidates, and adding force submit to each field ensured that any updates the JavaScript made would still get posted back and updated in CRM.  Problem Solved.

Debugging Microsoft’s minified JavaScript is never fun or easy, but sometimes it’s a necessity.  Happy debugging!

Lessons Learned Deleting 312 Million Records From CRM’s PrincipalObjectAccess Table

Unrestrained growth of the POA (PrincipalObjectAccess) SQL table in CRM is nothing new.  Performance of on premise CRM installations can start to be noticeably affected at 10 million records or so.  You can imagine my surprise when I learned my client had just over 1/3 of a billion records!  Microsoft has a script to clean it up, which probably works great at cleaning up a couple million records, but is not really an option when needing to clean up 312 million. 

After multiple modifications to Microsoft's scripts in our test environments we ended up splitting/modifying the Microsoft into 4 different scripts.

1. Create and populate ToDeletePOAEntries Table.
2. Drop the existing indexes on the POA table, add a single index just with the columns needed for the join
3. Delete the POA records
4. Reset the index changes made in Script 2.

The smartest thing we did was probably sending an e-mail, every time the script deleted a million records.  This allowed us to see trends in how long it was taking to delete and attempt to fix the issue.

Here is a graph of the e-mails received for the first 82 million, which took just over 9 hours:

 

We wrongly assumed that the increase in deletions was due to the indexes needing to be rebuilt, but rebuilding the index didn’t change the trend.  We then decided to try rebuilding the ToDeletePOAEntries table and this is what we saw for the next 48 million over the course of 4 hours:

We then edited the script to make the cleanup of the ToDeletePOAEntries automatic.  If I had to do it over again, I’d add an Identity column to the ToDeletePOAEntries table, and update the where of the join for each loop to start at the next batch of entries.  This is what we then saw with the noticeable peeks required to recreate the ToDeletePOAEntries table every 25 million:

 

Here are the scripts that were used.  Hopefully they’ll be helpful for anyone else out there in the same position we were in. It took just over 30 hours to delete 312 million records with CRM itself completely shutdown.

Script 1: Creates ToDeletePOAEntries table, populating it with all of the records that need to be removed out of the POA table.  This is basically the first part of the Microsoft script,

USE [Contoso_MSCRM]
GO

IF NOT EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)

create table ToDeletePoaEntries
(
ObjectId uniqueidentifier,
Otc int
)
IF NOT EXISTS (SELECT * FROM sys.sysindexes si
INNER JOIN sys.sysobjects so ON si.id = so.id
WHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND si.name LIKE '%mainindex%')

CREATE UNIQUE NONCLUSTERED INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]
(
[ObjectId] ASC,
[Otc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

----- Insert records to be deleted in ToDeletePoaEntries

-- go through all user-owned entities which are not replicated and don't support duplicate detection
declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1

open entity_cursor

declare @baseTableName sysname
declare @otc nvarchar(20)
declare @primaryKey sysname
declare @totalCollected int = 0
declare @currentCollected int
declare @tempRowCount int = 0
declare @collectstatement nvarchar(max)fetch next from entity_cursor
into @otc, @baseTableName, @primaryKeywhile @@FETCH_STATUS = 0
begin
print 'Cleaning up POA for ' + @baseTableName

set @currentCollected = 0


set @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)
select distinct poa.ObjectId, poa.ObjectTypeCode
from PrincipalObjectAccess poa left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey +
' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;

print @collectstatement

exec(@collectstatement)
set @tempRowCount = @@ROWCOUNT
set @currentCollected = @currentCollected + @tempRowCount
print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName
set @totalCollected = @totalCollected + @currentCollected

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
end

close entity_cursor
deallocate entity_cursor

print CAST(@totalCollected as nvarchar(20)) + ' total records collected'

Script 2: Drop the existing indexes on the POA table, add a single index just with the columns needed for the join.  This isn’t included in Microsoft’s script, and I don’t have any data as far as how helpful this was, but it should result in the least amount of work required by the database in order to delete a record.

USE [Contoso_MSCRM]

go

/****** Object:  Index [ndx_PrimaryKey_PrincipalObjectAccess]    Script Date: 12/18/2013 08:44:05 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'ndx_PrimaryKey_PrincipalObjectAccess')
  ALTER TABLE [dbo].[principalobjectaccess]
    DROP CONSTRAINT [ndx_PrimaryKey_PrincipalObjectAccess]

go

/****** Object:  Index [ndx_Cover]    Script Date: 12/18/2013 08:44:15 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'ndx_Cover')
  DROP INDEX [ndx_Cover] ON [dbo].[PrincipalObjectAccess] WITH ( online = OFF )

go

/****** Object:  Index [fndx_Sync_VersionNumber]    Script Date: 12/18/2013 08:44:33 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'fndx_Sync_VersionNumber')
  DROP INDEX [fndx_Sync_VersionNumber] ON [dbo].[PrincipalObjectAccess] WITH (
  online = OFF )

go

/****** Object:  Index [cndx_PrincipalObjectAccess]    Script Date: 12/18/2013 08:44:45 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'cndx_PrincipalObjectAccess')
  DROP INDEX [cndx_PrincipalObjectAccess] ON [dbo].[PrincipalObjectAccess] WITH
  (
  online = OFF )

go

/****** Object:  Index [UQ_PrincipalObjectAccess]    Script Date: 12/18/2013 08:45:56 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'UQ_PrincipalObjectAccess')
  ALTER TABLE [dbo].[principalobjectaccess]
    DROP CONSTRAINT [UQ_PrincipalObjectAccess]

go

CREATE NONCLUSTERED INDEX [IX_CleanupIndex]
  ON [dbo].[PrincipalObjectAccess] ( [objectid] ASC, [objecttypecode] ASC )
  WITH (statistics_norecompute = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF
, drop_existing = OFF, online = OFF, allow_row_locks = ON, allow_page_locks = ON
) ON [PRIMARY]

go 

Script 3: Delete the POA records.  This script has the most changes from the Microsoft version.  It wasn’t until we actually started running the scripts in Prod that we finally figured out our main bottle neck, the join of the POA table to the ToDeletePOAEntries.  For every million records deleted, the next time through the loop, the SQL server would attempt to find records that had just been deleted first, before joining on the records not yet deleted.  There is vast rooms for improvement here I’m sure, but this is what we came up with over winter holiday.

USE [Contoso_MSCRM]

go

-- Delete query
-- This scripts cleans up orphaned POA records for selected entities
DECLARE @deleteBatchSize INT = 50000
DECLARE @deleteBatchSizeNVarChar NVARCHAR(10) = Cast(
        @deleteBatchSize AS NVARCHAR(10))
DECLARE @totalDeleted INT = 0
DECLARE @currentDeleted INT
DECLARE @deletestatement NVARCHAR(max)
DECLARE @retry INT = 1
DECLARE @tempRowCount INT = 0
DECLARE @recipientList NVARCHAR(1000) = 'MSmith@Contoso.com;JDoe@Contoso.com'
DECLARE @backupScript NVARCHAR(1000)
DECLARE @backupCount INT = 0
DECLARE @bodyText NVARCHAR(4000)
DECLARE @subjectText NVARCHAR(1000)
DECLARE @baseTableName SYSNAME
DECLARE @otc NVARCHAR(20)
DECLARE @primaryKey SYSNAME
DECLARE @totalCollected INT = 0
DECLARE @currentCollected INT
DECLARE @tempRowCountRecreate INT = 0
DECLARE @collectstatement NVARCHAR(max)

WHILE ( @retry > 0 )
  BEGIN
      BEGIN try
          SET @currentDeleted = 0
          SET @tempRowCount = 0
          -- delete all records of the current entity type which don't have corresponding object in the base table
          SET @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar
                                 +
') from PrincipalObjectAccess from PrincipalObjectAccess poa join ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'

    PRINT @deletestatement

    -- delete PrincipalObjectAccess records in batches
    EXEC(@deletestatement)

    SET @tempRowCount = @@ROWCOUNT
    SET @currentDeleted = @currentDeleted + @tempRowCount

    WHILE @tempRowCount = @deleteBatchSize
      BEGIN
          EXEC(@deletestatement)

          SET @tempRowCount = @@ROWCOUNT
          SET @currentDeleted = @currentDeleted + @tempRowCount

          PRINT Cast(@currentDeleted AS NVARCHAR(20))
                + ' records deleted '
                + Cast(Getutcdate() AS NVARCHAR(50))

          -- Every 1,000,000 records notify the team that we have deleted another 1,000,000 records, with the total record count
          IF ( @currentDeleted % 1000000 = 0 )
            BEGIN
                PRINT '1,000,000 records deleted'

                SET @bodyText = CONVERT(NVARCHAR(20), @currentDeleted)
                                + ' POA records deleted.';

                EXECUTE msdb.dbo.Sp_send_dbmail
                  @profile_name = 'SQLNotify Profile',
                  @recipients = @recipientList,
                  @body = @bodyText,
                  @subject = 'POA Cleanup Progress Email',
                  @importance = 'High'
            END

          -- Every 25,000,000 records Rebuild ToDeletePoaEntries
          IF ( @currentDeleted % 25000000 = 0
                OR @currentDeleted <= 100000 )
            BEGIN
                PRINT '25,000,000 records deleted'

                -- DROP AND RECREATE TABLE
                DROP INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]

                DROP TABLE todeletepoaentries

                CREATE TABLE todeletepoaentries
                  (
                     objectid UNIQUEIDENTIFIER,
                     otc      INT
                  )

                ----- Insert records to be deleted in ToDeletePoaEntries
                -- go through all user-owned entities which are not replicated and don't support duplicate detection
                DECLARE entity_cursor CURSOR local forward_only read_only FOR
                  SELECT DISTINCT e.objecttypecode,
                                  e.basetablename,
                                  a.physicalname
                  FROM   entityview e
                         INNER JOIN attributeview a
                                 ON e.entityid = a.entityid
                                    AND a.ispkattribute = 1
                  WHERE  e.isreplicated = 0
                         AND e.isduplicatechecksupported = 0
                         AND e.ownershiptypemask & 1 = 1

                OPEN entity_cursor

                SET @totalCollected = 0
                SET @tempRowCountRecreate = 0

                FETCH next FROM entity_cursor INTO @otc, @baseTableName,
                @primaryKey

                WHILE @@FETCH_STATUS = 0
                  BEGIN
                      PRINT 'Cleaning up POA for ' + @baseTableName

                      SET @currentCollected = 0
                      SET @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)  select distinct poa.ObjectId, poa.ObjectTypeCode  from PrincipalObjectAccess poa left join ' + @baseTableName
                                              + ' e on poa.ObjectId = e.' +
                                              @primaryKey
                                              + ' where e.' + @primaryKey
                                              +
                      ' is null and poa.ObjectTypeCode = '
                                              +
                                              @otc;

                      PRINT @collectstatement

                      EXEC(@collectstatement)

                      SET @tempRowCountRecreate = @@ROWCOUNT
                      SET @currentCollected =
                      @currentCollected + @tempRowCountRecreate

                      PRINT Cast(@currentCollected AS NVARCHAR(20))
                            + ' records collected for ' + @baseTableName

                      SET @totalCollected = @totalCollected + @currentCollected

                      FETCH next FROM entity_cursor INTO @otc, @baseTableName,
                      @primaryKey
                  END

                CLOSE entity_cursor

                DEALLOCATE entity_cursor

                CREATE UNIQUE NONCLUSTERED INDEX [mainindex]
                  ON [dbo].[ToDeletePoaEntries] ( [objectid] ASC, [otc] ASC )
                  WITH (statistics_norecompute = OFF, sort_in_tempdb = OFF,
                ignore_dup_key
                =
                OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
                allow_page_locks = ON) ON [PRIMARY]

                PRINT Cast(@totalCollected AS NVARCHAR(20))
                      + ' total records collected'
            END
      END

    -- We are done! Delete the ToDeletePoaTable and Notify the team
    DROP TABLE [dbo].[todeletepoaentries]

    EXECUTE msdb.dbo.Sp_send_dbmail
      @profile_name = 'SQLNotify Profile',
      @recipients = @recipientList,
      @body = 'All POA Records to be deleted have been deleted.',
      @subject = 'POA Cleanup Complete',
      @importance = 'High'

    -- Reset the @retry value to 0, which will allow the code to exit the WHILE loop
    SET @retry = 0
END try

    BEGIN catch
        PRINT 'Retry ' + CONVERT(NVARCHAR(10), @retry)

        SET @retry = @retry + 1
        -- Hit an error - Notify the team
        SET @bodyText = Error_message();
        SET @subjectText = 'POA Cleanup Progress Email - Error '
                           + CONVERT(NVARCHAR(10), @retry);

        EXECUTE msdb.dbo.Sp_send_dbmail
          @profile_name = 'SQLNotify Profile',
          @recipients = @recipientList,
          @body = @bodyText,
          @subject = @subjectText,
          @importance = 'High'

        -- There was an error so let's reindex
        ALTER INDEX ALL ON dbo.principalobjectaccess reorganize;

        UPDATE STATISTICS dbo.principalobjectaccess;
    END catch
END --END OF THE RETRY LOOP
PRINT 'EXECUTION SUCCEED' 

Script 4: Reset the indexes on the POA table to bring them back to their original condition:

USE [Contoso_MSCRM]

go

IF EXISTS (SELECT *
           FROM   sys.sysindexes si
                  INNER JOIN sys.sysobjects so
                          ON si.id = so.id
           WHERE  so.id = Object_id(N'[dbo].[ToDeletePoaEntries]')
                  AND Objectproperty(so.id, N'IsUserTable') = 1
                  AND si.name LIKE '%mainindex%')
  DROP TABLE [dbo].[todeletepoaentries]

go

USE [Contoso_MSCRM]

go

/****** Object:  Index [IX_CleanupIndex]    Script Date: 12/19/2013 16:02:29 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'IX_CleanupIndex')
  DROP INDEX [IX_CleanupIndex] ON [dbo].[PrincipalObjectAccess] WITH ( online =
  OFF )

go

/****** Object:  Index [cndx_PrincipalObjectAccess]    Script Date: 12/18/2013 08:52:57 ******/
CREATE CLUSTERED INDEX [cndx_PrincipalObjectAccess]
  ON [dbo].[PrincipalObjectAccess] ( [objectid] ASC, [principalid] ASC )
  WITH (pad_index = OFF, statistics_norecompute = OFF, sort_in_tempdb = OFF,
ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
allow_page_locks = ON, FILLFACTOR = 100) ON [PRIMARY]

go

/****** Object:  Index [fndx_Sync_VersionNumber]    Script Date: 12/18/2013 08:53:03 ******/
CREATE UNIQUE NONCLUSTERED INDEX [fndx_Sync_VersionNumber]
  ON [dbo].[PrincipalObjectAccess] ( [versionnumber] ASC )
WHERE ([VersionNumber] IS NOT NULL)
WITH (pad_index = OFF, statistics_norecompute = OFF, sort_in_tempdb = OFF,
ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
allow_page_locks = ON, FILLFACTOR = 100) ON [PRIMARY]

go

/****** Object:  Index [ndx_Cover]    Script Date: 12/18/2013 08:53:13 ******/
CREATE NONCLUSTERED INDEX [ndx_Cover]
  ON [dbo].[PrincipalObjectAccess] ( [objecttypecode] ASC, [principalid] ASC,
[accessrightsmask] ASC, [inheritedaccessrightsmask] ASC )
  WITH (pad_index = OFF, statistics_norecompute = OFF, sort_in_tempdb = OFF,
ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
allow_page_locks = ON, FILLFACTOR = 80) ON [PRIMARY]

go

/****** Object:  Index [ndx_PrimaryKey_PrincipalObjectAccess]    Script Date: 12/18/2013 08:53:21 ******/
ALTER TABLE [dbo].[principalobjectaccess]
  ADD CONSTRAINT [ndx_PrimaryKey_PrincipalObjectAccess] PRIMARY KEY NONCLUSTERED
  ( [principalobjectaccessid] ASC )WITH (pad_index = OFF, statistics_norecompute
  = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF, online = OFF,
  allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 100) ON [PRIMARY]

go

/****** Object:  Index [UQ_PrincipalObjectAccess]    Script Date: 12/18/2013 08:53:33 ******/
ALTER TABLE [dbo].[principalobjectaccess]
  ADD CONSTRAINT [UQ_PrincipalObjectAccess] UNIQUE NONCLUSTERED ( [principalid]
  ASC, [objectid] ASC, [objecttypecode] ASC )WITH (pad_index = OFF,
  statistics_norecompute = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF,
  online = OFF, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 100)
  ON [PRIMARY]

go