Reorganize Database Indexes
The automatic clean-up functionality of Connect removes a significant number of records resulting in fragmentation of the corresponding database indexes. To address this issue, the following SQL script should be scheduled for daily database maintenance:
DBCC INDEXDEFRAG(0, 'ContextIndices') DBCC INDEXDEFRAG(0, 'ContextLogs') DBCC INDEXDEFRAG(0, 'Contexts') DBCC INDEXDEFRAG(0, 'PerformanceRecords')
To reorganize all Connect application specific indexes, use th following SQL script:
DBCC INDEXDEFRAG(0, 'ConnectObjects') DBCC INDEXDEFRAG(0, 'ConnectObjectsHistory') DBCC INDEXDEFRAG(0, 'ContextIndices') DBCC INDEXDEFRAG(0, 'ContextLogs') DBCC INDEXDEFRAG(0, 'Contexts') DBCC INDEXDEFRAG(0, 'PerformanceRecords') DBCC INDEXDEFRAG(0, 'ScenarioRegistrations') DBCC INDEXDEFRAG(0, 'SecureStoreItems')
Delete obsolete data
If there is a need to delete all Contexts and Performance Records created before a specific date, the following script can be employed. Be aware that fragmented indexes may cause poor performance during deletion. If required, defragment these indexes prior to deleting data. Ensure to adjust the date in the first line of the script as needed:
DECLARE @DeleteBefore DATETIME = CAST('2000-12-31T00:00:00' AS DATETIME);
DELETE FROM Contexts WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore);
DELETE FROM ContextIndices WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore);
DELETE FROM ContextLogs WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore);
DELETE FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore;
DELETE FROM PerformanceRecords WHERE [Time] < @DeleteBefore;
DBCC INDEXDEFRAG(0, 'Contexts')
DBCC INDEXDEFRAG(0, 'ContextIndices')
DBCC INDEXDEFRAG(0, 'ContextLogs')
DBCC INDEXDEFRAG(0, 'PerformanceRecords')
Schedule index reorganization when using Azure SQL Database
Step 1: Setup Azure Automation account
If you do not have an existing Azure Automation account you need to setup a new one. Proceed as follows:
Navigate to the Azure portal.
Click on “Create a resource” and search for “Automation”.
Select “Automation” and click “Create”.
Enter a unique name for your Automation account.
Select an existing resource group or create a new one.
Choose the appropriate Azure region.
Click “Review + create” and then “Create”.
Step 2: Add credentials for database access
In the Azure portal, navigate to your newly created Automation account.
Under Shared Resources, select Credentials.
Click “Add credentials”.
Enter a Name (e.g., “ConnectDatabaseCredentials”).
Enter the database administrator user name and passwort and re-enter the password to confirm it.
Click “Create”.
Step 3: Create a Runbook
In the Azure portal, navigate to your newly created Automation account.
Under Process Automation, select Runbooks.
Click “Browse catalog” and search for “fragmentation”.
Select the template “Indexes tables in an Azure database if they have a high fragmentation“.
Click “Select”.
Enter a Name (e.g., “IndexHighFragmentationTables”).
Click “Import”.
Navigate to the first line of code starting with “workflow” (below the comments).
Replace the workflow name “Update-SQLIndexRunbook“ with the name of your Runbook (e.g., “IndexHighFragmentationTables”).
Click “Save”.
Step 4: Test and publish the Runnbook
Select tab “Test”.
On the left side enter the parameters:
SQLSERVER: The host name of the SQL Server
DATABASE: The name of the database
SQLCREDENTIALNAME: The name of the SQL Server credentials (e.g., “ConnectDatabaseCredentials”)Click Start to test the Runbook manually.
Once the test is completed successfully, click the “X” in the upper left corner to return to the editor. The browser shows a dialog box asking you to confirm this action.
Click “Publish” and then “Yes”.
Step 5: Create a Schedule
In the Azure portal, navigate to your Runbook.
Under Resources, select Schedules.
Click “Schedule”.
Select a Scheudle or create a new one.
Click “Parameters”.
Enter the parameters:
SQLSERVER: The host name of the SQL Server
DATABASE: The name of the database
SQLCREDENTIALNAME: The name of the SQL Server credentials (e.g., “ConnectDatabaseCredentials”)Click “OK”
Click “OK”