CRM in-line search fails with SQL error

After upgrading Microsoft CRM from earlier versions we found that the global search function when enabled failed to return any results, and once the index for the global search had run over a 24-hour period, the in-line search function for any entity would cause a crash and SQL error message to be displayed on page.

The problem

In our particular instance this CRM environment had been upgraded from much earlier versions of CRM and included an attempt to solve some upgrade issues by dropping indexes. Initially our thoughts were that the dropping of the indexes were responsible for the problems. However it appears retrospectively that was a fragmentation of indexes that cause the issue. I cannot be exactly sure why the maintenance procedure that is run on the SQL Server did not rebuild and reorganise the indexes sufficiently that the global social function. However the following solution did work for us.

 We had pretty much followed the recommendation of this discussion forum.

The Solution

After submitting a support ticket to Microsoft they requested us to:

• Run following command on CRM database to check fragmentation percentage:

 

SELECT object_id AS ObjectID,  index_id AS IndexID, avg_fragmentation_in_percent AS PercentFragment, fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag,  page_count AS NumPages FROM sys.dm_db_index_physical_stats(DB_ID(”), NULL, NULL, NULL , ‘DETAILED’) WHERE avg_fragmentation_in_percent > 0 ORDER BY ObjectID, IndexID

 

• In case the fragmentation percent is more than 25-30% we have to rebuild the indexes.

Reference: https://msdn.microsoft.com/en-us/library/ms189858.aspx

  the reference provided by Microsoft was helpful, but not as helpful as we would have liked. We ended up running the following query that automatically rebuilt all the indexes.

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
Exec (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

 

After doing this, we were then able to turn on the global search and weight the relevant period of time for it to complete the indexing. It appears to have fixed our problem with both global search returning valid results, and in-line search no longer broken when global search was unable.