SQL locks on StoragePoint database causing index rebuild job hung. (4316172)

When a SQL Server Database maintenance plan is implemented that includes the rebuilding of indexes and  other job for updating statistics jobs get blocked by the locks created by the Content Migrator Timer Job. And no externalization can be performed or takes long time to externalize content. 

• StoragePoint Content Migrator Job will migrate information for the Asynchronous Endpoints and Archived Content.
• Initially this jobs is configured to run as much times as possible to ensure a performance of moving just blobs to the respective endpoints from the cache between each run.  This will ensure that not many blobs are stored in the cache. 
• Content Migrator job do lock records on tables but not entire tables or indexes. This type of performance is basic managing information.
• SQL server must be capable on managing request to the database and work with records locks where information is been updated and also the SQL commands  that try to query that information. 
• In this case a recurring job Migrator will run as many times over as defined by the user.
• In this action, locks records in the migration queue table is part of the procedure, this locks takes the least of time possible.
• If at the same time a Rebuild of index jobs try to work with that same table and locks at table level this  will create a competition for resources of the same table if the later doesn't end in a timely fashion.
• Each time the Content Migrator Job triggers a new job it will block records that need to be externalized and move to endpoints.
• This is not a common issue but when the SQL SERVER is running low on performance it becomes an issue, and externalization start to work on and off or takes much time to externalize o load a file in sharepoint or stop running giving many types of errors in the ULS logs.
• This will degenerate in a scenario where SQL Jobs and the normal jobs block each other for control of the tables in the Storagepoint Database, meaning that externalization can't perform on time, takes too much time, or start giving errors that cannot externalize content.  

Steps to pin point the issue:

1. Check that profiles and jobs are setup correctly, that Endpoints are accessible with the accounts used for Service stp.   

2. Do a manual externalization of single file manually. If the operation finish correctly retract manually the file and perform a second externalization of the same file. 

3. A error message will show up: On the pop-window for externalization, a top error will indicate this BLOB (or any of its versions) cannot be externalized.

Error: "Detail:  Missing suitable target endpoint (hint: try to reconsider StoragePoint profile Endpoint settings". 

4. Open the ULS logs and check for these 2 errors:

Error: "unable to insert/update migrate queue item....Execution Time out expired".

Error: The sql.client.command says that "Boolean Async, int32 time out" for the profile where the manual externalization has performed. 

5.  As the DBA to confirm is its running a rebuild index job, and check to see if the jobs are running.  It might be part of a maintenance plan at the SQL server level. 

6. If it is running a rebuild index job, ask the DBA to stop the rebuild index job, and also if it has a job that updates statistics job for the Storagepoint Database. 

7. Do a manual externalization and it should work back correctly.   

Step to avoid the issue in the future:

• StoragePoint administrator can change the values where where the migration job runs. 
• Make sure that the Administrator knows that changing the recurrence of the Migration job will help give more time to DBA administrators do their job.
• At the same time if the sharepoint environment has lots of activity that involves externalization, it will be advisable to perform this actions in non-office hours and follow Microsoft Windows best practices on performing a maintenance on rebuilding indexes.